MySQL query question
Posted: Sat Apr 12, 2003 5:11 pm
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
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