PHP code returns mysql_fetch_assoc() expects parameter 1 to be resource

Question

I'm using this PHP code to learn databases and PHP, though I'm receiving the error message:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /Applications/XAMPP/xamppfiles/htdocs/index.php on line 20

At first, the SQL query turned out to contain I spelling error which I have recently fixed, and I've ran the SQL query directly in PHPMyAdmin - where it works as it should. I can't find any reasonable hints using the mysql_error() line.

Here's the rest of the code:

<!DOCTYPE html>
<html>
<body>

    <?php

$user_name = "root";
$password = "";
$database = "addressbook";
$server = "localhost";

$db_handle = new mysqli($server, $user_name, $password);
$db_found = $db_handle->select_db($database);

    if ($db_found) {

        $SQL = "SELECT * FROM tb_address_book";
        $result = mysql_query($SQL);

        while ($db_field = mysql_fetch_assoc($result)) {
            print $db_field['ID'] . "<BR>";
            print $db_field['First_Name'] . "<BR>";
            print $db_field['Surname'] . "<BR>";
            print $db_field['Address'] . "<BR>";
        }

        $db_handle->close();

    } else {
        print "Database NOT found";
        $db_handle->close();
    }

?>

</body>
</html>

Doing

        if ($result) {

    }

returns false

What am I doing wrong?

Your query is returning no rows. That's because you're creating a `mysqli` connection and then attempting to use `mysql_*` functions with it. — Geoff Atkins, Jan 08 '16 at 10:10
@GeoffAtkins changing `mysql_query()` to `$db_handle->query()` changes the error message from `boolean given` to `object given`. Hmmm — Erik, Jan 08 '16 at 10:12
@Geoffatkins yup, that was the flaw indeed. Printing the rows correcty now — Erik, Jan 08 '16 at 10:14

score 1 · Answer 1 · answered Jan 08 '16 at 10:17

You are using out dated function mysql_query. Use mysqli_query, mysqli_fetch_assoc and your code should look like this

<?php

$user_name = "root";
$password = "";
$database = "addressbook";
$server = "localhost";

$db_handle = new mysqli($server, $user_name, $password);
$db_found = $db_handle->select_db($database);

if ($db_found) {

    $SQL = "SELECT * FROM tb_address_book";
    $result = mysqli_query($db_found,$SQL);

    while ($db_field = mysql_fetch_assoc($result)) {
        print $db_field['ID'] . "<BR>";
        print $db_field['First_Name'] . "<BR>";
        print $db_field['Surname'] . "<BR>";
        print $db_field['Address'] . "<BR>";
    }

    $db_handle->close();

} else {
    print "Database NOT found";
    $db_handle->close();
}

?>

score 1 · Answer 2 · answered Jan 08 '16 at 10:23

<!DOCTYPE html>
<html>
<body>

<?php
class Connection {

    protected $host = "localhost";
    protected $dbname = "addressbook";
    protected $user = "root";
    protected $pass = "";
    protected $conn;

    function __construct() {

        try {
            $this->conn = new PDO("mysql:host=$this->host;dbname=$this->dbname", $this->user, $this->pass);
        }
        catch (PDOException $e) {

            echo $e->getMessage();
        }
    }

    public function closeConnection() {

        $this->conn = null;
    }
}

class querys extends Connection{
   public function adres_book{
    $sql = "SELECT * FROM tb_address_book";
    $sql = $this->conn->prepare($sql);
    $sql->execute();
    return $sql->fetchAll();
   }
}

$conn    = new Connection();
$query  = new querys();

?>


On the php file:
<?php
Foreach($query->adres_book() as $db_field){
            print $db_field['ID'] . "<BR>";
            print $db_field['First_Name'] . "<BR>";
            print $db_field['Surname'] . "<BR>";
            print $db_field['Address'] . "<BR>";

}
?>

</body>
</html>

This should work. If not report back to me

I changed `mysql_query()` to `$db_handle->query()` as well as changed `mysql_fetch_assoc` to `mysqli_fetch_assoc`, which made it work. Haven't tried adding your code, but it looks good and appreciate your help! Thanks! — Erik, Jan 08 '16 at 11:05

score 1 · Answer 3 · answered Jan 08 '16 at 10:27

1

You are mixing Mysqli with Mysql.

Use Mysqli methods to query:

$res = $db_handle->query($SQL);

while ($row = $res->fetch_assoc()) {
    // do stuff
}

Here the docs: http://php.net/manual/en/mysqli.quickstart.statements.php

answered Jan 08 '16 at 10:27

Yuri Blanc

636
10
24

PHP code returns mysql_fetch_assoc() expects parameter 1 to be resource

3 Answers3