SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrieve content from database - nested query problem

    Hi all,

    I'm retrieving some records from a MySQL database using a query (a while loop) in another query. Everything works fine except that the while loop prints out the same row twice, instead of two separate rows (I've only got 2 rows in that particular table at the moment for testing purposes).

    Could there be some conflict between the first

    $row = mysqli_fetch_array($r, MYSQLI_ASSOC); {

    and the nested

    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) ?

    For my second query, I've changed some variables in my actual code so that they don't conflict ($row, $num etc).


    The following code is just some sample code I created for demonstration purposes, so my query code isn't accurate but the focus is on how the second query (with while loop) is nested.

    Could someone please have a look at this?

    Thank you in advance.




    PHP Code:

    <?php

    $q 
    "SELECT name, address, books_read,  FROM table1 INNER JOIN table2 WHERE some_id = 5";        

    $r = @mysqli_query ($dbc$q);     
    $num mysqli_num_rows($r);
    if (
    $num 0) {
    $row mysqli_fetch_array($rMYSQLI_ASSOC); {
     
    Print 
    '<div>
    <p class="name">'
    .$row['name'] . ' </p>
    <p class="address">'
    .$row['address'].'</p>
    </div>'
    ;


    $q "SELECT books_read FROM table1 INNER JOIN table2 WHERE some_id = 5";        

    $r = @mysqli_query ($dbc$q);     
    $num mysqli_num_rows($r);
    if (
    $num 0) {
    while (
    $row mysqli_fetch_array($rMYSQLI_ASSOC)) {
     
    Print 
    '<div><p class="books">'.$row['books_read'] . ' </p></div>';
    }
    }

    Print 
    '<div>
    <p>other content</p>
    </div>'
    ;

     }
    }
    ?>

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You're using $r and $row in the second process so therefore you will lose your first process' results. No?

  3. #3
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Nope, I've used different variables in my actual code.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Since I was guessing at what your code looked like, I took my best guess. If you want more help you'll have to show us the relevant part that is mis-behaving.

  5. #5
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    742
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Can I assume that "books_read" from table2 probably has two records? Let's do a little test. Comment out that second query.
    PHP Code:
    <?php

    $q 
    "SELECT name, address, books_read,  FROM table1 INNER JOIN table2 WHERE some_id = 5";        

    $r = @mysqli_query ($dbc$q);     
    $num mysqli_num_rows($r);
    if (
    $num 0) {
    $row mysqli_fetch_array($rMYSQLI_ASSOC); {
     
    Print 
    '<div>
    <p class="name">'
    .$row['name'] . ' </p>
    <p class="address">'
    .$row['address'].'</p>
    </div>'
    ;

    /*
    $q = "SELECT books_read FROM table1 INNER JOIN table2 WHERE some_id = 5";        

    $r = @mysqli_query ($dbc, $q);     
    $num = mysqli_num_rows($r);
    if ($num > 0) {
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
     
    Print '<div><p class="books">'.$row['books_read'] . ' </p></div>';
    }
    }
    */
    Print '<div>
    <p>other content</p>
    </div>'
    ;

     }
    }
    ?>
    Does the name loop twice?

    And what about if you did two separate queries for table1 and table2.
    PHP Code:
    <?php

    $q 
    "SELECT name, address FROM table1 WHERE some_id = 5";        

    $r = @mysqli_query ($dbc$q);     
    $num mysqli_num_rows($r);
    if (
    $num 0) {
    $row mysqli_fetch_array($rMYSQLI_ASSOC); {
     
    Print 
    '<div>
    <p class="name">'
    .$row['name'] . ' </p>
    <p class="address">'
    .$row['address'].'</p>
    </div>'
    ;


    $q "SELECT books_read FROM table2 WHERE some_id = 5";        

    $r = @mysqli_query ($dbc$q);     
    $num mysqli_num_rows($r);
    if (
    $num 0) {
    while (
    $row mysqli_fetch_array($rMYSQLI_ASSOC)) {
     
    Print 
    '<div><p class="books">'.$row['books_read'] . ' </p></div>';
    }
    }

    Print 
    '<div>
    <p>other content</p>
    </div>'
    ;

     }
    }
    ?>
    Assuming some_id is a unique identifier in both tables.

    Then you can simply do one query and check if heading (name etc) has been displayed and only show it once.
    PHP Code:
    <?php

    $q 
    "SELECT name, address FROM table1 INNER JOIN table2 WHERE some_id = 5";        

    $r = @mysqli_query ($dbc$q);     
    $num mysqli_num_rows($r);
    if (
    $num 0) {
        
    $heading 0;
        while (
    $row mysqli_fetch_array($rMYSQLI_ASSOC)) {
            if(
    $heading == 0){ 
                Print 
    '<div>
                <p class="name">'
    .$row['name'] . ' </p>
                <p class="address">'
    .$row['address'].'</p>
                </div>'
    ;
            }
             
    $heading++;
            Print 
    '<div><p class="books">'.$row['books_read'] . ' </p></div>';
        }
        Print 
    '<div>
        <p>other content</p>
        </div>'
    ;
    }
    ?>
    Rough draft anyway.

  6. #6
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Drummin and others,

    thank you for your help. Everything is working fine. For my second query I did change the $row variable in

    PHP Code:
    $row mysqli_fetch_array($rMYSQLI_ASSOC); { 
    but somehow forgot to change it in

    PHP Code:
    <class="address">'.$row['address'].'</p
    Thanks

    Ciao


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
  •