Changing Mysql functions to Mysqli breaks the script

Question

I have this script that deletes a certain picture from the website. It's written with mysql functions so i wanted to update it to mysqli but doing so makes the script stop working. No die message from the script are shown no php errors and adding error_reporting(E_ALL); doesn't show any errors either.

Original script:

    if(isset($_POST['F3Verwijderen']))
    try 
    {                           
        //delete the file
        $sql = "SELECT PandFoto3 FROM tblpand WHERE `PK_Pand` = '".$pandid."'";

        $con = mysql_connect('WEBSITE.mysql', 'WEBSITE', 'PASS');
        if (!$con) {
        die('Could not connect: ' . mysql_error());
        }
        mysql_select_db("WEBSITE");
        $result = mysql_query($sql, $con);
        while ($row = mysql_fetch_array($result)) {                     
            if(file_exists($_SERVER['DOCUMENT_ROOT'].'/'.$row['PandFoto3'])) {
                unlink($_SERVER['DOCUMENT_ROOT'].'/'.$row['PandFoto3']);
            } else {
            echo $row['PandFoto3'];
            }
        }
        //delete the path url from the database field
        mysql_query("UPDATE tblpand SET PandFoto3 = NULL WHERE `PK_Pand` = '".$pandid."'");
        mysql_close($con);          


        header('Location: ../admin/pand-aanpassen.php?id='.$pandid);
    }

Updated to mysqli:

try 
    {                           
        //delete the file
        $sql = "SELECT PandFoto3 FROM tblpand WHERE `PK_Pand` = '".$pandid."'";

        $con = mysqli_connect('WEBSITE.mysql', 'WEBSITE', 'PASS');
        if (!$con) {
        die('Could not connect: ' . mysqli_error());
        }
        mysqli_select_db("WEBSITE");
        $result = mysqli_query($sql, $con);
        while ($row = mysqli_fetch_array($result)) {                        
            if(file_exists($_SERVER['DOCUMENT_ROOT'].'/'.$row['PandFoto3'])) {
                unlink($_SERVER['DOCUMENT_ROOT'].'/'.$row['PandFoto3']);
            } else {
            echo $row['PandFoto3'];
            }
        }
        //delete the path url from the database field
        mysqli_query("UPDATE tblpand SET PandFoto3 = NULL WHERE `PK_Pand` = '".$pandid."'");
        mysqli_close($con);         


        header('Location: ../admin/pand-aanpassen.php?id='.$pandid);
    }

Answer 1

Edit:

"no php errors and adding error_reporting(E_ALL); doesn't show any errors either."

• That's because it isn't a PHP issue, it's a MySQL issue.
  • Those are two different animals altogether.

As I said in commments, you need to switch these variables ($sql, $con) around ($con, $sql).

Then this:

$con = mysqli_connect('WEBSITE.mysql', 'WEBSITE', 'PASS');

Just use the 4th parameter instead of mysqli_select_db("WEBSITE"); where you didn't pass the connection variable to.

$con = mysqli_connect('WEBSITE.mysql', 'WEBSITE', 'PASS', 'WEBSITE');

The syntax is:

• host
• username
• password (if any)
• database

You also could have done mysqli_select_db($con, "WEBSITE");

Sidenote: In mysql_ (see footnotes), the connection comes last, unlike in mysqli_ which comes first.

Do the same for your UPDATE and pass the connection parameter first.

mysqli_query($con, "UPDATE...

Sidenote: To verify that the update truly was successful, use affected_rows()

• http://php.net/manual/en/mysqli.affected-rows.php.

Another thing, mysqli_error() requires a connection to it mysqli_error($con) and check for errors for your queries.

I.e.:

$result = mysqli_query($con, $sql) or die(mysqli_error($con));

References:

• http://php.net/manual/en/mysqli.query.php
• http://php.net/manual/en/mysqli.error.php
• http://php.net/manual/en/mysqli.select-db.php

Sidenote:

You're using try() but no catch(). Either remove it, or consult the manual:

• http://php.net/manual/en/language.exceptions.php

Example #4 pulled from the manual:

<?php
function inverse($x) {
    if (!$x) {
        throw new Exception('Division by zero.');
    }
    return 1/$x;
}

try {
    echo inverse(5) . "\n";
} catch (Exception $e) {
    echo 'Caught exception: ',  $e->getMessage(), "\n";
} finally {
    echo "First finally.\n";
}

try {
    echo inverse(0) . "\n";
} catch (Exception $e) {
    echo 'Caught exception: ',  $e->getMessage(), "\n";
} finally {
    echo "Second finally.\n";
}

// Continue execution
echo "Hello World\n";
?>

---

Final notes:

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements, they're much safer.

---

Footnotes: (MySQL and MySQLi comparison)

In regards to mysql_query():

mixed mysql_query ( string $query [, resource $link_identifier = NULL ]

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

For mysqli_query():

mixed mysqli_query ( mysqli $link , string $query [, int $resultmode = MYSQLI_STORE_RESULT ] )

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