PHP + MySQL - While Loop not

Question

I'm quite new to PHP + MySQL so, please pardon my confusion — I don't understand why my code won't loop through the 'underground_name' column and echo all the rows?

Here's my code...

<?php
    include("../includes/header.php");
    require('../../mysqli_connect.php');
    include("../functions/filter_time.php");

    // query the database
    $query = "SELECT * FROM projects_underground, underground, projects
            LEFT JOIN river ON projects.river_id=river.river_id 
            LEFT JOIN dlr ON projects.dlr_id=dlr.dlr_id 
            LEFT JOIN overground ON projects.overground_id=overground.overground_id 
            LEFT JOIN natrail ON projects.natrail_id=natrail.natrail_id 
            LEFT JOIN tram ON projects.tram_id=tram.tram_id

            WHERE 
            projects_underground.underground_fk = underground.underground_id AND
            projects_underground.projects_fk = projects.projects_id AND
            name = 'Imperial War Museum'";

    $result = mysqli_query($dbc, $query); // put queried result into variable
    $row = mysqli_fetch_assoc($result); // put that into seperate arrays called $row
    $ug = mysqli_fetch_array($result);

    while ($ug = mysqli_fetch_array($result)) {
        echo $ug['underground_name'];
    }
?>

Thanks for your help in advance!

John Bupit · Accepted Answer · 2015-08-31 12:12:51Z

1

Try running your query on the SQL console, and check what results you get.
Try dumping the $ug array to see if the output is being fetched.
You're calling mysqli_fetch_*() thrice, once while initializing $row, once while initializing $ug, and once more in the while loop. This will miss the first two rows - and consequently, if you have only two rows in the query result, no output would be printed.

$row = mysqli_fetch_assoc($result); // Fetches first result row off the stack 
$ug = mysqli_fetch_array($result);  // Fetches second result row

while ($ug = mysqli_fetch_array($result)) { // Fetches third row
     // Starts printing from the third row (if there is any)
     echo $ug['underground_name'];
}

answered Aug 31, 2015 at 12:12

John Bupit

10.7k10 gold badges45 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CallMePhilip Over a year ago

1. SQL Query -

SELECT underground_name FROM projects_underground, projects, underground WHERE projects_underground.underground_fk = underground.underground_id AND projects_underground.projects_fk = projects.projects_id AND projects_id = 5

creates the expected result in PHPmyAdmin — 2. — When I (var_dump($row['underground_name']) I only get the 1st row of results? Very confused...? –

John Bupit Over a year ago

mysqli_fetch_row and mysqli_fetch_assoc functions return an individual row from the result. Each subsequent call to one of those would return the next row in the result (or NULL if there are no more rows left). Hence, $row in your current code would contain the 1st row, $ug would contain the second row (before the while loop), and so on.

Machavity · Accepted Answer · 2015-08-31 12:13:07Z

1

You need to understand that when you call mysqli_fetch_assoc($result); or mysqli_fetch_array($result); that you are fetching a row off the result stack. So before you ever reach the while statement you've already gotten two rows off. So if your query returned three results, you'd only show one in your loop.

answered Aug 31, 2015 at 12:13

Machavity♦

31.8k27 gold badges97 silver badges108 bronze badges

3 Comments

CallMePhilip Over a year ago

Thanks for your reply @Machavity.

CallMePhilip Over a year ago

SELECT underground_name FROM projects_underground, projects, underground WHERE projects_underground.underground_fk = underground.underground_id AND projects_underground.projects_fk = projects.projects_id AND projects_id = 5

creates the expected result in PHPmyAdmin — but when I (var_dump($row['underground_name']) I only get the 1st row of results? Very confused...?

Machavity Over a year ago

@CallMePhilip Remember that these functions only return one row at a time. If you want an array of arrays you will have to build your own loop that does that.

SteveW · Accepted Answer · 2015-09-02 09:53:15Z

Try running the following to see how many rows are being returned:

echo $result->num_rows().' rows have been returned.'.PHP_EOL;

Here's an update to your code that I think should do the trick (by removing the earlier calls to fetch_assoc and fetch_array and adding some basic checking that any rows have been returned):

<?php
include("../includes/header.php");
require('../../mysqli_connect.php');
include("../functions/filter_time.php");

// query the database
$query = "SELECT * FROM projects_underground, underground, projects
        LEFT JOIN river ON projects.river_id=river.river_id 
        LEFT JOIN dlr ON projects.dlr_id=dlr.dlr_id 
        LEFT JOIN overground ON projects.overground_id=overground.overground_id 
        LEFT JOIN natrail ON projects.natrail_id=natrail.natrail_id 
        LEFT JOIN tram ON projects.tram_id=tram.tram_id

        WHERE 
        projects_underground.underground_fk = underground.underground_id AND
        projects_underground.projects_fk = projects.projects_id AND
        name = 'Imperial War Museum'";

$result = mysqli_query($dbc, $query); // put queried result into variable

if (is_object($result) && $result->num_rows() > 0) {

    // This line is just for testing, delete from real code
    echo $result->num_rows().' rows have been returned.'.PHP_EOL;

    while ($ug = mysqli_fetch_array($result)) {
        echo $ug['underground_name'];
    }
} else {
    // Do something if no results were returned
}
?>

Collectives™ on Stack Overflow

PHP + MySQL - While Loop not

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related