MySQL query question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
kdeveron
Forum Newbie
Posts: 2
Joined: Sat Apr 12, 2003 5:11 pm

MySQL query question

Post 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
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post 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?
Post Reply