Small section of PHP/mysql code dies. Not sure why

Question

I rarely do programming. I only know enough to be dangerous as they say and I simply assemble bits of code to get what I want. My code below seems to die at the $sql query statement. It never returns any data. It should show the 13 records that are present, but it says there is none to return. I'm guessing this is some kind of syntax error?

<?php
$host = 'myipaddress';
$user = 'myuser';
$pass = 'mypass';
$db = 'mydatabase';

$conn = mysql_connect($host, $user, $pass, $db) or die("Can not connect." . mysql_error());

// Create connection
//$conn = mysqli_connect($host, $user, $pass, $db);
// Check connection
if (!$conn) {
die("Connection failed: ");
}

$sql = "SELECT * FROM pages WHERE pid > '5'";
$result = mysql_query($conn, $sql);

if (mysql_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["pid"]. " - Name: " . $row["title"]. "<br>";
}
} else {
echo "0 results";
}

mysql_close($conn);
?>

Answer 1

Your using the mysql_ API right up until you try to fetch rows here, where you're using mysqli_. That will not work.

while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["pid"]. " - Name: " . $row["title"]. "<br>";
}

Your script is at risk for SQL Injection Attacks. Please stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it's really pretty easy.

EDIT: Your connection (Good Eyes Ralph!) string will not work because mysql_connect() doesn't accept the database as part of the connection. You must use the additional function mysql_select_db() to choose your database.

In addition, it is not necessary to specify the connection link in mysql_query() but if you do it should be the second argument:

$result = mysql_query($sql, $conn);

Answer 2

You need to use mysql_fetch_assoc() in place of mysqli_fetch_assoc(), because your previous functions are based on mysql_*, not mysqli_*

if (mysql_num_rows($result) > 0) {
// output data of each row
while($row = mysql_fetch_assoc($result)) {
    echo "id: " . $row["pid"]. " - Name: " . $row["title"]. "<br>";
}
} else {
echo "0 results";
}

Answer 3

There is quite a bit wrong with your code.

mysql_connect($host, $user, $pass, $db)

mysql_connect() uses 3 parameters, the 4th doesn't do what you think it does.

• http://php.net/manual/en/function.mysql-connect.php

You need to use mysql_select_db() http://php.net/manual/en/function.mysql-select-db.php

Then,

$result = mysql_query($conn, $sql);

The connection comes second in mysql_.

• http://php.net/manual/en/function.mysql-query.php

Then you're mixing a MySQLi function mysqli_fetch_assoc which doesn't intermix with the mysql_ library.

• Read: Can I mix MySQL APIs in PHP?

So, just use the full MySQLi library

• http://php.net/manual/en/book.mysqli.php

or PDO:

• http://php.net/manual/en/book.pdo.php

Along with a prepared statement:

• https://en.wikipedia.org/wiki/Prepared_statement

---

Check for the real errors, should your query fail:

• http://php.net/manual/en/function.mysql-error.php

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

---

As you can see, I did not provide you with a full rewrite, as I feel that in "Teaching a person how to fish...", will feed them for life, rather than "Throwing them a fish...", and only feed them for a day (wink).