SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Addict lespaulsf's Avatar
    Join Date
    Dec 2006
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    display PHP SQL query

    Hi

    I'm in the process of creating a newsletter script for a school that allows the users to upload a pdf document into a specific grade folder. I have a db that stores the location and filename and grade of the newsletter. The thing I'm trying to figure out is how to do a SELECT statement and only return 1 record at a time without doing 6-7 different SELECTS on the same page with the only difference is the school grade. The reason I ask is because there will also be static text in between the link for the news letter.

    For example

    2nd Grade

    This is the second grade and their various text info.
    Here is the newsletter "link" (this link will be dynamic from DB)

    3rd Grade

    This is the third grade and their various text info.
    Here is the newsletter "link" (this link will be dynamic from DB)


    I hope this example makes since. I guess I'm asking should I just do 6-7 single SELECTS for each link or how would I loop through this if there is static text within the page like.

    If you notice I use the WHERE clause to find the particular newletter I want to pull. Keep in mind only the newest newletter should be displayed for each class.

    PHP Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html>

    <head>
        <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
        <meta name="author" content="Shane Ferguson" />

        <title>Untitled 1</title>
    </head>

    <body>

    <?php

    /**
     * @author Shane Ferguson
     * @copyright 2009
     */
        //require db connection
        
    require 'config.php';
              
        
    //connect and select db 
        
    $dbcnxmysql_connect($db_host,$db_userS,$db_passS) or die (mysql_error());
                
    mysql_select_db($db_name,$dbcnx) or die (mysql_error());

        
    $sql "SELECT ID, grade, filefolder, filename, timestamp 
                FROM newsletter WHERE grade='rainbow_dreamers'
                ORDER BY timestamp DESC"
    ;

        
    $rs mysql_query($sql) or die("Unable to process query: " mysql_error());
        
            
    // if the category is empty display as message
              
    if(mysql_num_rows($rs) == 0) {
            echo 
    "<p class='validate_error'><b>There are currenlty no news letters</a>.</p></b>";
            } else {
            
    //echo "<h5>Here is the <i>$category</i> category</h5>";    
            
    }

        while (
    $row mysql_fetch_array($rs)) {
            
    $filefolder $row['filefolder'];
            
    $fileName $row['filename'];
            
    $newsID $row['ID'];
            
    //$timestamp = $row['timestamp'];
            
            //clean grade for display
            
    $grade $row['grade'];
            
    $grade str_replace('_',' ',$grade);
            
    $grade ucwords($grade);
            
          
           
    //display newletter links
               
              
    echo "<div class=viewposts>";
           echo 
    "<p>Here is the $grade monthly news letter.</p>"//$timestamp";
           
    echo "<a href=\"pdf/$filefolder$fileName\">$fileName</a>";
           echo 
    "</div>";
                 
        }

    ?>
        <div id="pods">
        <h3>The Pods</h3>
         <h4>Sunshine Kids</h4>
         <p>Here is the Sunshine Kids monthly news letter. <a href="#"> April 2009</a></p>
         <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Phasellus iaculis vestibulum sem. Aenean sollicitudin, ligula vitae interdum vulputate, purus tortor feugiat dui, at commodo eros sapien consequat dui. Sed accumsan porttitor mauris. Donec aliquet, lorem vitae varius mollis, nulla tortor tristique augue, quis hendrerit turpis massa sed eros. Aenean nec ligula. Vivamus nibh. Donec id augue. Suspendisse potenti. Morbi velit. Sed felis. </p>
         <h4>Clover Club</h4>
         <p>Here is the Clover Club monthly news letter. <a href="#"> April 2009</a></p>
         <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Phasellus iaculis vestibulum sem. Aenean sollicitudin, ligula vitae interdum vulputate, purus tortor feugiat dui, at commodo eros sapien consequat dui. Sed accumsan porttitor mauris. Donec aliquet, lorem vitae varius mollis, nulla tortor tristique augue, quis hendrerit turpis massa sed eros. Aenean nec ligula. Vivamus nibh. Donec id augue. Suspendisse potenti. Morbi velit. Sed felis. </p>
         <h4>Rainbow Dreamers</h4>
         <p>Here is the Rainbow Dreamers monthly news letter. <a href="#"> April 2009</a></p>
         <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Phasellus iaculis vestibulum sem. Aenean sollicitudin, ligula vitae interdum vulputate, purus tortor feugiat dui, at commodo eros sapien consequat dui. Sed accumsan porttitor mauris. Donec aliquet, lorem vitae varius mollis, nulla tortor tristique augue, quis hendrerit turpis massa sed eros. Aenean nec ligula. Vivamus nibh. Donec id augue. Suspendisse potenti. Morbi velit. Sed felis. </p>
         <h4>Kindergarten</h4>
         <p>Here is the Kindergarten monthly news letter. <a href="#"> April 2009</a></p>
         <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Phasellus iaculis vestibulum sem. Aenean sollicitudin, ligula vitae interdum vulputate, purus tortor feugiat dui, at commodo eros sapien consequat dui. Sed accumsan porttitor mauris. Donec aliquet, lorem vitae varius mollis, nulla tortor tristique augue, quis hendrerit turpis massa sed eros. Aenean nec ligula. Vivamus nibh. Donec id augue. Suspendisse potenti. Morbi velit. Sed felis. </p>
         <h4>Elementary Kids</h4>
         <p>Here is the Elementary Kids monthly news letter. <a href="#"> April 2009</a></p>
         <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Phasellus iaculis vestibulum sem. Aenean sollicitudin, ligula vitae interdum vulputate, purus tortor feugiat dui, at commodo eros sapien consequat dui. Sed accumsan porttitor mauris. Donec aliquet, lorem vitae varius mollis, nulla tortor tristique augue, quis hendrerit turpis massa sed eros. Aenean nec ligula. Vivamus nibh. Donec id augue. Suspendisse potenti. Morbi velit. Sed felis. </p>
        </div>

    </body>
    </html>
    If you notice where is has a link and then April 2009. That link is only thing that needs to be dynamic and the text needs to be static.
    Last edited by lespaulsf; Apr 7, 2009 at 08:05. Reason: explaination

  2. #2
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you familiar with parametrized statements? Basically, you prepare a single SQL statement with a variable (written as a '?') in the SQL statement, and then bind parameters (in this case, your grade) and execute the statement. So, you'd only need to write the SQL once, and then you could simply have a loop which binds and executes (and fetches and displays data) for each of your grades.

    Does that make sense?

  3. #3
    SitePoint Addict lespaulsf's Avatar
    Join Date
    Dec 2006
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That sounds like what I need to do. Although I have never used parametrized statements and not for sure how to do this. Would you mind showing me how?

  4. #4
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure. Please note, I'm going to use the mysqli interface, not the mysql one you're probably used to. This is simply a matter of API comfort for me. You can read about the mysqli interface here: http://us2.php.net/mysqli

    PHP Code:
    $DBLink mysqli->connect("username""password""server");
    $grade = array(12345);

    $sql "SELECT ID, grade, filefolder, filename, timestamp 
                FROM newsletter WHERE grade = ?
                ORDER BY timestamp DESC"
                
    //note the ? instead of a field value

    $DBstmt $DBLink->prepare($sql);
    foreach(
    $grade as $key => $grade)
    {
         
    $DBstmt->bind_param('i'$grade); //this is telling the SQL server what information you want to fill the ? with for each execution.
         
    $DBstmt->execute();
         
    $DBstmt->bind_result($id$class$filefolder$filename$timestamp); // this assigns the values of the fields you selected
         
    while($DBstmt->fetch()) //mysqli->fetch() returns true for every row you selected. This while() loop runs over that and provides processing on the values.
         
    {
              
    //all of your processing for links and the like go here
         
    }
    }
    $DBstmt->close(); 
    Note that this won't work fully for you, I've made some changes for the sake of not copying your code verbatim, but that's the basic outline of how you'd do a parametrized statement for this information. Does that help?

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could do it in a single query.

    Code:
    (SELECT ID
          , grade
          , filefolder
          , filename
          , timestamp
       FROM newsletter
      WHERE grade = 'A'
     ORDER 
         BY timestamp DESC
      LIMIT 1)
      
    UNION
    
    (SELECT ID
          , grade
          , filefolder
          , filename
          , timestamp
       FROM newsletter
      WHERE grade = 'B'
     ORDER 
         BY timestamp DESC
      LIMIT 1)
      
    UNION
    
    (SELECT ID
          , grade
          , filefolder
          , filename
          , timestamp
       FROM newsletter
      WHERE grade = 'C'
     ORDER 
         BY timestamp DESC
      LIMIT 1)

  6. #6
    SitePoint Addict lespaulsf's Avatar
    Join Date
    Dec 2006
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SituationSoap View Post
    Sure. Please note, I'm going to use the mysqli interface, not the mysql one you're probably used to.

    Note that this won't work fully for you, I've made some changes for the sake of not copying your code verbatim, but that's the basic outline of how you'd do a parametrized statement for this information. Does that help?
    Wow I haven't learned about that yet. using the mysqli way seems very confusing. I appreciate the example although.

  7. #7
    SitePoint Addict lespaulsf's Avatar
    Join Date
    Dec 2006
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI crmalibu

    That makes since. Although how would I make sure and get the correct grade to display? Would I us something like the following? Could I do it this way for all of the news letters. There should only be 6-7 at the most.

    Code:
    SELECT ID
          , grade
          AS B
          , filefolder
          , filename
          , timestamp
       FROM newsletter
      WHERE grade = 'B'
     ORDER 
         BY timestamp DESC
      LIMIT 1

  8. #8
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lespaulsf View Post
    Wow I haven't learned about that yet. using the mysqli way seems very confusing. I appreciate the example although.
    mysqli is simply a different API to perform the same function. I know, it seems a little daunting at first, but really, any API should be able to do parametrized queries.

    I would definitely recommend that you do some reading on them, they're invaluable, especially when you're dealing with things like user input, or unknown values. They also provide an additional layer of security against things like SQL Injection.

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $grades = array();
    while (
    $row mysql_fetch_assoc($result)) {
        
    $grades$row['grade'] ] = $row;
    }
    print_r($grades);
    print_r($grades['A']);
    echo 
    $grades['A']['filename']; 

  10. #10
    SitePoint Addict lespaulsf's Avatar
    Join Date
    Dec 2006
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmm don't know if I totally understand how to use the above code.

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by lespaulsf View Post
    hmmm don't know if I totally understand how to use the above code.
    First you run the query crmalibu posted. Then you load the result of that query in an array, where each row of the array contains the info of a grade, and the key of that row is the grade itself (this is the piece of code he posted).

    Just try it, and look at the result of the print_r commands. It should become clear how it works

    You then can display the data in the array in any order you want, by specifying the first index (the grade) and the second (filefolder, filename, timestamp). The echo statement gives an example.

  12. #12
    SitePoint Addict lespaulsf's Avatar
    Join Date
    Dec 2006
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dude that is amazing. I understand how. Very awesome. There have been times in the past I have wanted to do this but didn't know how to.

    Is there a way to print out the array without it being all smashed togather? When I have done a print_r before it puts it in a table sort of view breaking it down.

    I'm not familar with the following peice of code. What is it doing here?
    PHP Code:
    $grades[$row['grade']] = $row
    So the empty array $grades is first created and then filled with the contents of the query?

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Easiest way to see the print_r() output formatted is to just view the html source. You could also output an html <pre> tag right before it.


    On each iteration of the loop, the value of $row['grade'] changes. For example, the first iteration, it probably has a value of A, then B on the next iteration etc....

    So it's basically doing this
    PHP Code:
    // first loop
    $grades['A'] = $row;


    // second loop
    $grades['B'] = $row
    and so on until the looping is done, storing each successive $row from the database result.

  14. #14
    SitePoint Addict lespaulsf's Avatar
    Join Date
    Dec 2006
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is to cool man. Thanks alot. I must be doing something wrong because I'm trying to get the foldername and it will just display something like 's'. For example

    PHP Code:
    echo $grades['sunshine_kids']['filefolder']['filename']; 
    prints out 's'.

    Here is print_r($grades['sunshine_kids'])

    returns

    Array
    (
    [ID] => 2
    [grade] => sunshine_kids
    [filefolder] => sunshine_kids/
    [filename] => Take_it_All_04-7-2009.pdf
    [timestamp] => 2009-04-07 15:37:35
    )
    s

    print_r($grades); It returns this

    Array
    (
    [sunshine_kids] => Array
    (
    [ID] => 2
    [grade] => sunshine_kids
    [filefolder] => sunshine_kids/
    [filename] => Take_it_All_04-7-2009.pdf
    [timestamp] => 2009-04-07 15:37:35
    )

    [clover_club] => Array
    (
    [ID] => 3
    [grade] => clover_club
    [filefolder] => clover_club/
    [filename] => Jesus_Paid_it_All_04-7-2009.pdf
    [timestamp] => 2009-04-07 15:38:59
    )

    [rainbow_dreamers] => Array
    (
    [ID] => 5
    [grade] => rainbow_dreamers
    [filefolder] => rainbow_dreamers/
    [filename] => 780i_UserGuide_V2_04-7-2009.pdf
    [timestamp] => 2009-04-07 15:40:57
    )

    [kindergarten] => Array
    (
    [ID] => 7
    [grade] => kindergarten
    [filefolder] => kindergarten/
    [filename] => Salvation_is_Here_04-8-2009.pdf
    [timestamp] => 2009-04-08 08:59:34
    )

    [elementary_kids] => Array
    (
    [ID] => 4
    [grade] => elementary_kids
    [filefolder] => elementary_kids/
    [filename] => IPphonesetup_04-7-2009.pdf
    [timestamp] => 2009-04-07 15:39:21
    )

    )
    s

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $grades['sunshine_kids']['filefolder']['filename'] doesn't exist.

    You need
    $grades['sunshine_kids']['filefolder']
    and
    $grades['sunshine_kids']['filename']

  16. #16
    SitePoint Addict lespaulsf's Avatar
    Join Date
    Dec 2006
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah ha I see now. Sorry I don't have that much experience with these types of nested arrays yet but now I'm staring to understand them more. Thanks very much.


Bookmarks

Posting Permissions

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