fetch_array errors when querying data using mysqli_query

Question

If I try to create a sql query such as this:

$sql2 = mysqli_query($connection,"SELECT * FROM CHILD_IMG WHERE PROD_ID='$delID'") or die(mysqli_error());
$getQuery = $connection->query($sql2);
while($row = $getQuery->fetch_array()){
    $childID = $row['ID'];
    $parentID = $row['PROD_ID'];
    $childName = '../ProductImages/ChildImages/'.$parentID . "_".$childID.".jpg";
    unlink($childName);
}

I get the following error:

Fatal error: Call to a member function fetch_array() on null in

If I run and store the query to $sql like this:

$sql2 = ("SELECT * FROM CHILD_IMG WHERE PROD_ID = '$delID'") or die(mysqli_error());
$getQuery = $connection->query($sql2);
while($row = $getQuery->fetch_array()){
    $childID = $row['ID'];
    $parentID = $row['PROD_ID'];
    $childName = '../ProductImages/ChildImages/'.$parentID . "_".$childID.".jpg";
    unlink($childName);
}

The query run smoothly without any issues.

What is the problem why doesn't the first option works?

Answer 1

See this bit of code?

$sql2 = mysqli_query($connection,"SELECT * FROM CHILD_IMG WHERE PROD_ID='$delID'") or die(mysqli_error());
        ^^^^^^^^^^^^
$getQuery = $connection->query($sql2);
                         ^^^^^

You're actually querying twice, that's why you're getting the error.

Plus, the or die(mysqli_error()) belongs after the query call and it requires a db connection as the argument.

I.e.: or die(mysqli_error($connection)).

So you'd do the following to check if the query failed:

if(!$getQuery){

    echo "Error: " . die(mysqli_error($connection));

}

Rewrite:

$sql2 = "SELECT * FROM CHILD_IMG WHERE PROD_ID='$delID'";
$getQuery = $connection->query($sql2);
while($row = $getQuery->fetch_array()){
    $childID = $row['ID'];
    $parentID = $row['PROD_ID'];
    $childName = '../ProductImages/ChildImages/'.$parentID . "_".$childID.".jpg";
    unlink($childName);
}

You're also open to an SQL injection; use a prepared statement.

References:

• https://en.wikipedia.org/wiki/Prepared_statement
• http://php.net/manual/en/mysqli.prepare.php (mysqli)
• http://php.net/manual/en/pdo.prepared-statements.php (PDO)

Note: If you intend on going with PDO, remember to not mix the different MySQL APIs.