"Better" or more proper way to select data on a page

spacepoet · February 17, 2012

Hello:

I wanted to see what might be a better way to select data from 3 tables and display it on one page.

I am currently doing this:

<?php

include("include/db.php");
include('include/myNav.php');

$con = mysql_connect($db_host, $db_user, $db_password);

mysql_select_db($db_table);

?>


<html>

<head></head>

<body>

<div id="mainBody">


<div id="leftColumn">

<?
$query = "SELECT * FROM mySchedule ORDER BY sch_listorder";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

if($num_rows==0) {
print "";
}
else {

print "<h3>". date('Y')." Schedule</h3>";
print "<div style='padding: 10px;'>";
print "<ul>";

for($i=0;$i<$num_rows;$i++) {
	$row = mysql_fetch_array($result);
	print "<li id='arrayorder_".$row['sch_id']."'>";
	//print $row['photo_caption'];

	print "<span class='mySideScheduleTitle'>" .$row['sch_player_title']. "</span><br />";
	print "<span class='mySideScheduleDate'>Date: " .$row['sch_player_number']. "</span><br />";
	print "<span class='mySideScheduleContent'>" .$row['sch_photo_caption']. "</span>";
	print "<span class='mySideScheduleContent'>" .$row['sch_years_played']. "</span><div style='clear: both; margin: 0 0 5px 0;'></div>";


	print "</li>\n";
	print "<hr class='sideBar2' />";
}

print "</ul>";
print "</div>";

}
?>

<br /><br />

<?
$query = "SELECT * FROM myShoutout ORDER BY shout_listorder";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

if($num_rows==0) {
print "<h3>No Electronic Shout-Outs</h3>";
}
else {

print "<h3>". date('Y')." Electronic Shout-Outs</h3>";
print "<marquee scrollamount='2' direction='up'>";
print "<div style='padding: 10px;'>";
print "<ul>";

for($i=0;$i<$num_rows;$i++) {
	$row = mysql_fetch_array($result);
	print "<li id='arrayorder_".$row['shout_id']."'>";
	//print $row['photo_caption'];

	print $row['shout_photo_caption'];


	print "</li>\n";
	print "<hr class='sideBar' />";
}
print "</ul>";
print "</div>";
print "</marquee>";

}
?>


</div>


<div id="mainColumn">



<?
$query = "SELECT id, photo_filename, player_title, player_number, years_played, photo_caption FROM myRoster ORDER BY listorder";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

if($num_rows==0) {
print "<strong>There are currently no players in the database.</strong><br><br>";
}
else {
print "<table style='border-collapse:collapse' width='100%' cellpadding='4' cellspacing='4'>";
$i=0;	
for($j=0;$j<$num_rows;$j++) {
	$row = mysql_fetch_array($result);
	$id = $row['id'];
	$photo = $row['photo_filename'];
	$title = $row['player_title'];

	if($i==0) print "<tr>";
	print "<td valign='bottom' align='center'><a href='rosterPlayer2.php?id=".$id."#myPlayer'>";
	if($photo=="") {
		echo "<img src='uploads/tb_0.gif' border='0' width='100' />";
	}
	else {
		if(file_exists('uploads/'.$photo))
			echo "<img src='uploads/".$photo."?t=".strtotime("now")."' border='0' width='100' />";
		else
			echo "<img src='uploads/tb_0.gif' border='0' width='100' />";
	}
	print "</a><br/><a href='rosterPlayer2.php?id=".$id."#myPlayer'>".$title."</a></td>";
	$i++;
	if($i==4) {
		$i=0;
		print "</tr><tr><td colspan='4'> </tr>";
	}
}
print "</table>";
}
?>



</div>


</div>

</body>

</html>
<?
mysql_close($con);
?>

It works, but is there a more proper way to do this?

Is this where "joining" tables comes into play (I don't know much about that).

Thanks.

kicken · February 17, 2012

It works, but is there a more proper way to do this?

Since your queries appear to be unrelated to each other, having them separate like that is pretty much what you'd want to do.

Is this where "joining" tables comes into play (I don't know much about that).

Joining is when your trying to pull information from multiple tables, which is all related together via some other bit of information. In your case, there is no relation between your tables (that I saw) so there's no reason to join them.

If you ever find yourself doing something like this:

$res=mysql_query('SELECT ...');
while ($row=mysql_fetch_array($res)){
    //Some other query that uses some information from $row
    $res2=mysql_query('SELECT ....');
    while ($row2=mysql_fetch_array($res)){
    }
}

Then chances are good you want to use a JOIN instead. Nested select queries like that is almost always a bad idea.

spacepoet · February 17, 2012

Hi:

OK - thanks for clarifying!

I wanted to make sure I wasn't doing something "wrong" that would cause a problem, or drain the DB resources.

Thanks again!

Sign In

"Better" or more proper way to select data on a page

Recommended Posts

spacepoet

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information