...

View Full Version : Better way to loop sql data?



shortyb
07-26-2008, 07:53 AM
Ok currently this is what I use for each day of the week. Its queried by asc 'starttime'. It seems like I am repeating when I dont need to, I am real new to php and still learning the basics but kinda jump head first you know? I could really use the help on a better way to sort by 'day' of week and display each 'name' and info under that day.


echo "<p class='headline'>Sunday</p>";
$schedule = get_schedule();
while ($post = mysql_fetch_array($schedule)) {
if ($post["day"]=="Sunday")
echo "<div class='title'> {$post["starttime"]} - {$post["endtime"]} | <strong>{$post["name"]}</strong> </div>
<div class='tag'>with {$post["dj"]}: {$post["discription"]} </div>";
}
echo "<br /><p class='headline'>Monday</p>";
$schedule = get_schedule();
while ($post = mysql_fetch_array($schedule)) {
if ($post["day"]=="Monday")
echo "<div class='title'> {$post["starttime"]} - {$post["endtime"]} | <strong>{$post["name"]}</strong> </div>
<div class='tag'>with {$post["dj"]}: {$post["discription"]} </div>";
}

This code works fine but I can see it build the page as it pulls my function for each day seeing as it queries the mySQL each time get_schedule() is called I figured thats why I can see it build itself. This was kinda my first clue to I am doing something wrong.

Iszak
07-26-2008, 10:48 AM
Use 'mysql_fetch_assoc' compared to 'mysql_fetch_array' as by the looks of the usage of each row you're not using the numbered version that you're also called when using array. Where as you are using the associative version. This will cut down on database usage and execution time. Only slightly but why not? When you're not using someone discard it!

shortyb
07-26-2008, 11:03 AM
Thanks that is a bit faster. :thumbsup:

I have been trying to tell it to only query my day, but no clue what I am really doing. Of if this method is worth it or possible.

$schedule = get_scday(Sunday);
while ($post = mysql_fetch_array($schedule)) {
echo "<div class='title'> {$post["starttime"]} - {$post["endtime"]} | <strong>{$post["name"]}</strong> </div>
<div class='tag'>with {$post["dj"]}: {$post["discription"]} </div>";
}


function get_scday($day) {
global $connection;
$query = "SELECT * ";
$query .= "FROM schedule ";
$query .= "WHERE day=" . $day . " ";
$query .= "ORDER BY starttime ASC ";
$query .= "LIMIT 0, 30 ";
$results_set = mysql_query($query, $connection);
confirm_query($results_set);
if ($subject = mysql_fetch_array($results_set)) {
return $subject;
} else {
return NULL ;
}

OUTPUT: database query failed: Unknown column 'Sunday' in 'where clause'

scoop_987
07-26-2008, 05:46 PM
try changing this:

$schedule = get_scday(Sunday);

To:

$schedule =get_scday("Sunday");

You must quote the day rather than state it. So it has to be "Sunday" rather than Sunday

Get it? I know i made those mistakes and wondered why it wouldnt work!

kbluhm
07-26-2008, 06:56 PM
That will correct a PHP notice, but that will not fix the MySQL error.
Change this line:


$query .= "WHERE day=" . $day . " "; // WHERE day=Sunday

...to this:


$query .= "WHERE day='{$day}' "; // WHERE day='Sunday'

shortyb
07-26-2008, 07:41 PM
So when I use {$var} there is no need to break my ""'s? I notice this method a lot, like with echos. thanks for the help you two, It still seems to be giving me errors though. "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in" So this means my array is not being dumped proper? I will do a pre print to check but looks like I will not call my 'day' with my function cause it still requires me to recode for each day.


Is there a better way to be repeating this info though? I still feel like I should be able to query once then be able to sort and loop the data without repeating the code for each day. now that I have to format the time ect it looks even more repetitive.

I repeat this code for each day of the week.


echo "<p class='headline'>Sunday</p>";
$schedule = get_schedule();
while ($post = mysql_fetch_assoc($schedule)) {
$starttime = strftime("&#37;I:%M %p",strtotime($post["starttime"]));
$endtime = strftime("%I:%M %p",strtotime($post["endtime"]));
$time = $starttime . " - " . $endtime;
if ($post["day"]=="Sunday")
echo "<div class='title'> {$time} | <strong>{$post["name"]}</strong> </div>
<div class='tag'>with {$post["dj"]}: {$post["discription"]} </div>";
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum