PHP mySQLi query returning data but not displaying it

Question

Good day, I have done extensive research on this issue but unfortunately none of the related issues solved my problem.

Here I have a very basic PHP mySQLi db connection. The connection succeeds and so does the query that is run on the table. The issue is that the the result set will not display. All of my references are correct and when I check to see if the result set is populated, it is. I believe the issue is with my while block but no errors are returned when this is run. Thank you for your time

<?php
$db = mysqli_connect('localhost','root','','securitour') //connection to the         database
or die('Error connecting to MySQL server.');
?>

<html>
<head>
</head>
<body>
<?php

$query = "SELECT * FROM location"; //The SQL query
mysqli_query($db, $query) or die('Error querying database.'); 
$result = mysqli_query($db, $query); //query the table an store the result set in a     variable
$row = mysqli_fetch_array($result); //create an array and store the records of the     result set in it

if (mysqli_num_rows($result) != 0) //to check if the result set contains data
{
    echo "results found"; //THIS is what is returned.
} 
else 
{
    echo "results not found";
}
while ($row = $result->fetch_assoc()) //itterate through the array and display the         name column of each record
    {
    echo $row['name'];
    }
    mysqli_close($db);
?>
</body>
</html>

WHY are you doing mysqli_query($db, $query) TWICE one after another — RiggsFolly
– RiggsFolly, Commented Nov 2, 2018 at 10:44
@MasivuyeCokile Yes sir, that is checking if the number of rows in the result set storred in $result is not equal to 0 — MarkusMulholland
– MarkusMulholland, Commented Nov 2, 2018 at 10:44
You are switching between the procedural and class-based versions of mysqli, stick to one or the other, preferably class-based. — Andy G
– Andy G, Commented Nov 2, 2018 at 10:45
THen you fetch a row... and do nothing with it, before moving on to the while loop. So you will loose row 1 completely — RiggsFolly
– RiggsFolly, Commented Nov 2, 2018 at 10:46
@RiggsFolly, the first is to check if the query succeeds. That is not the issue here though. I did that to isolate the code to see what was going wrong. — MarkusMulholland
– MarkusMulholland, Commented Nov 2, 2018 at 10:46

RiggsFolly · Accepted Answer · 2018-11-02 10:55:38Z

Lots of things not right here.

You are processing the mysqli_query() function twice - there is no need.
You are selecting all fields in your SQL query (SELECT *). You should select fields by name.
You're interchanging between procedure and class-based MySQLi - You should stick to one or the other.

Try this instead:

    <?php
    $db = mysqli_connect('localhost','root','','securitour') //connection to the         database
    or die('Error connecting to MySQL server.');
    ?>

    <html>
    <head>
    </head>
    <body>
    <?php

    $query = "SELECT name FROM location"; //The SQL query
    $result = mysqli_query($db, $query) or die('Error querying database'); //query the table an store the result set in a     variable
    if(mysqli_num_rows($result) > 0){
        echo "Results found!";
        while($row = mysqli_fetch_array($result)){ //create an array and store the records of the     result set in it
            echo $row['name'];
        }
    } else {
        echo "results not found";
    }
        mysqli_close($db);
    ?>
    </body>
    </html>

re: mysqIi_query - Originally I did not run it twice but when this error occurred i attempted to isolate code to try and pin point the issue, i obviously did not do that correctly. That sql query is just for test purpose. Thank you so much for your answer this is exactly what i needed
No problem - glad to help. Don't forget to mark the answer as correct if it solved your problem :D

Muhammad Saqlain · Accepted Answer · 2018-11-02 10:54:52Z

You don't need to run mysqli_query() twice. and you need to use mysqli_fetch_assoc for associative array

<?php
$db = mysqli_connect('localhost','root','','securitour') or die('Error connecting to MySQL server.');
?>

<html>
<head>
</head>
<body>
<?php
$query = "SELECT * FROM location"; //The SQL query
$result = mysqli_query($db, $query) or die('Error querying database.'); //query the table an store the result set in a     variable
$row = mysqli_fetch_assoc($result); //create an array and store the records of the     result set in it

if (mysqli_num_rows($result) != 0) //to check if the result set contains data
{
    echo "results found"; //THIS is what is returned.
} else {
    echo "results not found";
}

foreach ( $row as $name=>$val) {
    echo $name . ':' . $val . '<br>';
}
mysqli_close($db);
?>
</body>
</html>

I think your While loop should be a foreach over the $row array
@RiggsFolly you are right although while loop is also working.
@RiggsFolly its fine. its all about the result display Thanks :)

Collectives™ on Stack Overflow

PHP mySQLi query returning data but not displaying it

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related