Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    May 2007
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Better way to loop sql data?

    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.

  • #2
    Regular Coder Iszak's Avatar
    Join Date
    Jun 2007
    Location
    Perth, Western Australia
    Posts
    332
    Thanks
    2
    Thanked 58 Times in 57 Posts
    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!

  • Users who have thanked Iszak for this post:

    shortyb (07-26-2008)

  • #3
    New to the CF scene
    Join Date
    May 2007
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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'

  • #4
    New Coder
    Join Date
    Jul 2008
    Posts
    91
    Thanks
    4
    Thanked 9 Times in 9 Posts
    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!

  • #5
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    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' 

  • #6
    New to the CF scene
    Join Date
    May 2007
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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>"
    ;    
                } 
    Last edited by shortyb; 07-26-2008 at 07:10 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •