Using PHP to query a database and return results to display on a webpage

Question

I'm trying to select some simple information from a database and display it on a webpage. I can't work out for the life of me why if ($result->num_rows > 0) from the code below evaluates to false. Most of this is copied directly from the W3Schools site from: http://www.w3schools.com/php/php_mysql_select.asp So it really shouldn't have anything wrong with it..

<html>
 <div class="panel"> 
 <!--this should print out atleast something from the database, but it's printing 0 results. -->
<?php include("populate_feature_list.php"); ?>
 </div>
 </html>

This is the contents of the included PHP file:

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


$sql = mysql_query($conn,"SELECT name, rating FROM table ORDER BY rating DESC LIMIT 5;");

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Provider: " . $row["name"]. " - Rating: " . $row["rating"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();

?>

This is what returns on the SQL server when executing the query:

    +----------------------+--------+
    | name                 | rating |
    +----------------------+--------+
    | persona              | 4.8000 |
    | personb              | 4.7500 |
    | personc              | 4.6500 |
    | persond              | 4.1500 |
    | persone              | 2.4000 |
    +----------------------+--------+

And description of table:

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | YES  | UNI | NULL    |       |
| rating | float(8,4)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

Because you are mixing `mysqli with mysql` Check `mysql_query` part — Saty, May 30 '16 at 13:51

score 2 · Answer 1 · answered May 30 '16 at 13:51

You are mixing two MySQL apis within PHP.

$sql = mysql_query($conn,"SELECT name, rating FROM table ORDER BY rating DESC LIMIT 5;");

Then you are using the return value of the mysql_query call in mysqli:

$result = $conn->query($sql);

This should be:

$sql = "SELECT name, rating FROM table ORDER BY rating DESC LIMIT 5;";

An easy way to learn this in the future is by looking what each argument accepts. By looking at http://php.net/manual/en/mysqli.query.php, you can see that $conn->query() accepts a string as the first argument.

Using PHP to query a database and return results to display on a webpage

1 Answers1