Date: 06/28/05 (PHP Development) Keywords: mysql, database, sql, google I'm working on a site at the moment and I'm trying to optimise the MySQL queries, to place less of a strain on the server and make generally neater code. I'm currently using this technique to pull the last 10 topics flagged as news from the database: $query = mysql_query( "SELECT * FROM frm_topics t WHERE t.tpc_news = '1' AND t.tpc_news_mod_id > 0 ORDER BY t.tpc_sticky DESC, t.tpc_time DESC LIMIT 10", $_GLOBALS[ 'sql' ] ); Then in the loop created by $results = mysql_fetch_array( $query ) using a second query to pull the content of the post: $news_sql = mysql_query( "SELECT * FROM frm_posts p WHERE p.pst_tpc_id = '".$results[ 'tpc_id' ]."' ORDER BY pst_time ASC LIMIT 1", $_GLOBALS[ 'sql' ] ); Now, although this works, it currently uses 11 queries, plus various others I'm using to create each page; so I'm trying to find a neater solution. I've tried some tutorials on sub-queries found on Google, to no avail. The following works if there's only one news topic but falls over if there's more: $query = mysql_query( "SELECT * FROM frm_posts p, frm_topics t WHERE p.pst_tpc_id = ( SELECT tpc_id FROM frm_topics t WHERE t.tpc_news = '1' AND t.tpc_news_mod_id > 0 ORDER BY t.tpc_sticky DESC, t.tpc_time DESC LIMIT 10 ) AND t.tpc_id = p.pst_tpc_id ORDER BY p.pst_time ASC LIMIT 1", $_GLOBALS[ 'sql' ] ); I'd really appreciate it if anyone could lend a hand or point me in the right direction. Source: http://www.livejournal.com/community/php_dev/59389.html
|