StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHelp with limiting a joined mysql database query
text
Body
copied!<p>I have written a query which returns all records with some many-to-many joins correctly for the entire set or an individual article using WHERE a.id = ?</p> <pre> SELECT a.id, date_added, title, content, category_id, person_id, organization_id, c.name AS category_name, firstname, lastname, o.name AS organization_name FROM articles AS a LEFT OUTER JOIN articles_categories AS ac ON a.id=ac.article_id LEFT OUTER JOIN categories AS c ON c.id=ac.category_id LEFT OUTER JOIN articles_people AS ap ON a.id=ap.article_id LEFT OUTER JOIN people AS p ON p.id=ap.person_id LEFT OUTER JOIN articles_organizations AS ao ON a.id=ao.article_id LEFT OUTER JOIN organizations AS o ON o.id=ao.organization_id ORDER BY date_added </pre> <p>BUT!</p> <p>I've hit a brick wall trying to work out how to limit the articles to a specific number of IDs, for working with pagination.</p> <p>I'm ideally trying to use as simple and clear SQL statements as possible because I'm using the codeigniter framework with their active record class. <a href="http://codeigniter.com/user_guide/database/active_record.html" rel="nofollow">http://codeigniter.com/user_guide/database/active_record.html</a></p> <p>Would really appreciate some help as I don't want to revert to using multiple queries for this as I've tried to reduce it down to a single query for database efficiency.</p> <p>Have search around and tried some alternatives but nothing seems to work. Many thanks!</p> <p>For example the results I return are like this</p> <pre> --------------------------------------------------------------------- id title category_id person_id organization_id --------------------------------------------------------------------- 1 test 1 1 1 1 test 2 1 1 1 test 1 2 1 1 test 1 1 2 1 test 5 1 1 1 test 8 1 1 1 test 1 4 1 1 test 1 4 2 1 test 1 1 1 2 test 2 2 1 1 2 test 2 1 2 1 2 test 2 1 1 2 2 test 2 5 1 1 2 test 2 8 1 1 2 test 2 1 4 1 2 test 2 1 4 2 </pre> <p>I need the results like this so that I can create sub-arrays in the php like this:</p> <pre><code> $articles = $query->result_array(); $output = array(); foreach ($articles as $article) { // set up article details $article_id = $article['id']; // add article details $output[$article_id]['article_id'] = $article_id; $output[$article_id]['date_added'] = $article['date_added']; $output[$article_id]['title'] = $article['title']; $output[$article_id]['content'] = $article['content']; // set up people details and add people array with details if exists if (isset($article['person_id'])) { $person_id = $article['person_id']; $output[$article_id]['people'][$person_id]['person_id'] = $person_id; $output[$article_id]['people'][$person_id]['lastname'] = $article['lastname']; $output[$article_id]['people'][$person_id]['firstname'] = $article['firstname']; } // set up organizations details and add organizations array with details if exists if (isset($article['organization_id'])) { $organization_id = $article['organization_id']; $output[$article_id]['organizations'][$organization_id]['organization_id'] = $organization_id; $output[$article_id]['organizations'][$organization_id]['organization_name'] = $article['organization_name']; } // set up categories details and add categories array with details if exists if (isset($article['category_id'])) { $category_id = $article['category_id']; $output[$article_id]['categories'][$category_id]['category_id'] = $category_id; $output[$article_id]['categories'][$category_id]['category_name'] = $article['category_name']; } } </code></pre> <p>But if I just use LIMIT (with offset etc) 1</p> <p>the results I get are</p> <pre> --------------------------------------------------------------------- id title category_id person_id organization_id --------------------------------------------------------------------- 1 test 1 1 1 </pre> <p>instead of</p> <pre> --------------------------------------------------------------------- id title category_id person_id organization_id --------------------------------------------------------------------- 1 test 1 1 1 1 test 2 1 1 1 test 1 2 1 1 test 1 1 2 1 test 5 1 1 1 test 8 1 1 1 test 1 4 1 1 test 1 4 2 1 test 1 1 1 </pre> <p>which is my desired result.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload