Page 1 of 1

MySQL query question

Posted: Sat Apr 12, 2003 5:11 pm
by kdeveron
I am using php and mysql as the backend for a screenshot page. I want three screenshots across the page, and as many down as the recordset will support. Therefore, I need three queries, each of which pulls a roughly equal, but different set of records from my table.

I have the following three queries:

mysql_select_db($database_conn_guard, $conn_guard);
$query_rs_sc1 = "SELECT `tbl_screenshots`.`screenshots_id`, `tbl_screenshots`.`screenshots_author_id`, `tbl_screenshots`.`screenshots_images`, `tbl_screenshots`.`screenshots_caption`, `tbl_screenshots`.`screenshots_date`, `tbl_users`.`user_fname`, `tbl_users`.`user_lname`, `tbl_users`.`user_email`, `tbl_users`.`user_id`, `tbl_rank`.`rank_id`, `tbl_rank`.`rank_value`, `tbl_rank`.`rank_abr`, `tbl_users`.`user_rank_id`, CONCAT(`tbl_users`.`user_fname`,' ',`tbl_users`.`user_lname`) as name FROM `tbl_screenshots` INNER JOIN `tbl_users` ON (`tbl_screenshots`.`screenshots_author_id` = `tbl_users`.`user_id`) INNER JOIN `tbl_rank` ON (`tbl_users`.`user_rank_id` = `tbl_rank`.`rank_id`)";
$rs_sc1 = mysql_query($query_rs_sc1, $conn_guard) or die(mysql_error());
$row_rs_sc1 = mysql_fetch_assoc($rs_sc1);
$totalRows_rs_sc1 = mysql_num_rows($rs_sc1);

mysql_select_db($database_conn_guard, $conn_guard);
$query_rs_sc2 = "SELECT `tbl_screenshots`.`screenshots_id`, `tbl_screenshots`.`screenshots_author_id`, `tbl_screenshots`.`screenshots_images`, `tbl_screenshots`.`screenshots_caption`, `tbl_screenshots`.`screenshots_date`, `tbl_users`.`user_fname`, `tbl_users`.`user_lname`, `tbl_users`.`user_email`, `tbl_users`.`user_id`, `tbl_rank`.`rank_id`, `tbl_rank`.`rank_value`, `tbl_rank`.`rank_abr`, `tbl_users`.`user_rank_id`, CONCAT(`tbl_users`.`user_fname`,' ',`tbl_users`.`user_lname`) as name FROM `tbl_screenshots` INNER JOIN `tbl_users` ON (`tbl_screenshots`.`screenshots_author_id` = `tbl_users`.`user_id`) INNER JOIN `tbl_rank` ON (`tbl_users`.`user_rank_id` = `tbl_rank`.`rank_id`)";
$rs_sc2 = mysql_query($query_rs_sc2, $conn_guard) or die(mysql_error());
$row_rs_sc2 = mysql_fetch_assoc($rs_sc2);
$totalRows_rs_sc2 = mysql_num_rows($rs_sc2);

mysql_select_db($database_conn_guard, $conn_guard);
$query_rs_sc3 = "SELECT `tbl_screenshots`.`screenshots_id`, `tbl_screenshots`.`screenshots_author_id`, `tbl_screenshots`.`screenshots_images`, `tbl_screenshots`.`screenshots_caption`, `tbl_screenshots`.`screenshots_date`, `tbl_users`.`user_fname`, `tbl_users`.`user_lname`, `tbl_users`.`user_email`, `tbl_users`.`user_id`, `tbl_rank`.`rank_id`, `tbl_rank`.`rank_value`, `tbl_rank`.`rank_abr`, `tbl_users`.`user_rank_id`, CONCAT(`tbl_users`.`user_fname`,' ',`tbl_users`.`user_lname`) as name FROM `tbl_screenshots` INNER JOIN `tbl_users` ON (`tbl_screenshots`.`screenshots_author_id` = `tbl_users`.`user_id`) INNER JOIN `tbl_rank` ON (`tbl_users`.`user_rank_id` = `tbl_rank`.`rank_id`)";
$rs_sc3 = mysql_query($query_rs_sc3, $conn_guard) or die(mysql_error());
$row_rs_sc3 = mysql_fetch_assoc($rs_sc3);
$totalRows_rs_sc3 = mysql_num_rows($rs_sc3);

Any ideas on what i need to do to get the three to pull the top, middle, and bottom third of the screenshots given that I dont know the number of records at any given time?

You can view the page at:

http://www.imperial-dominion.net/databa ... nshots.php

Posted: Mon Apr 14, 2003 8:39 am
by Rob the R
I am unclear as to why you need three separate queries. Can't you just have one query to pull all the screenshots you want and use PHP to create the HTML as needed?