Better way to loop sql data?

[shortyb]

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.

PHP Code:

               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]

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]

Thanks that is a bit faster.

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.

PHP Code:

              $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>";
            } 


PHP Code:

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]

try changing this:

PHP Code:

$schedule = get_scday(Sunday); 


To:

PHP Code:

$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]

That will correct a PHP notice, but that will not fix the MySQL error.
Change this line:

PHP Code:

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


...to this:

PHP Code:

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


[shortyb]

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.

PHP Code:

               echo "<p class='headline'>Sunday</p>";
            $schedule = get_schedule();        
            while ($post = mysql_fetch_assoc($schedule)) {
                $starttime = strftime("%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>";    
            }