Help with query: Join, Limit and Count total rows? - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Help with query: Join, Limit and Count total rows? (/showthread.php?tid=13298) |
Help with query: Join, Limit and Count total rows? - El Forum - 11-17-2008 [eluser]little brittle[/eluser] I am having trouble coming up with a query to retrieve blog posts based on abstracted tags. Here is what I want to do: - Return posts.* tagged with "motorcycles", LIMIT 20 - Return users.username for each post by joining 'users.id' with 'posts.userid' - Return number of total possible unique posts that match "motorcycles", either with CI query caching, or with SQL_CALC_FOUND_ROWS. Here is some sample data from my tables. Could someone help guide me in creating a query to pull the right results? Code: TABLE "posts" Help with query: Join, Limit and Count total rows? - El Forum - 11-17-2008 [eluser]fesweb[/eluser] No guarantees, but this should be close: Code: // bring back all results I usually find it much easier to paste that query into the sql box in phpmyadmin to adjust the query until it brings back what I'm expecting, then parse it back out into active record speak. Help with query: Join, Limit and Count total rows? - El Forum - 11-17-2008 [eluser]fesweb[/eluser] one way to limit the output Code: $i = 0; Help with query: Join, Limit and Count total rows? - El Forum - 11-18-2008 [eluser]little brittle[/eluser] I keep getting this error no matter what I try: #1054 - Unknown column 'posts.userid' in 'on clause' If I take out the JOIN, it returns results without the user.username field. What can I do to fix this? Help with query: Join, Limit and Count total rows? - El Forum - 11-18-2008 [eluser]Armchair Samurai[/eluser] This will retrieve the first 20 posts with motorcycle tag as well as give the total number of 'motorcycle' posts contained in the database. Code: $data = new stdClass; Help with query: Join, Limit and Count total rows? - El Forum - 11-18-2008 [eluser]little brittle[/eluser] That was exactly what I was looking for. Thanks a lot for the help. |