MySQL only working on localhost

Question

For some reason this code works flawlessly on localhost (MAMP), but for some reason it does not work on my domain.

<?php

    require(ROOT."/resc/define.mysql.php");

    $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    $query = 'SELECT * FROM `Products` WHERE ID = '.mysql_real_escape_string($_GET['art']);

    $resultSet = $mysqli->query($query);

    if ($resultSet->num_rows != 0) {

        while ($row = $resultSet->fetch_assoc()) {

            foreach ($row as $key => $value) {

                $$key = $value;

            }

        }

    } else {

        echo('MySQL Error!');

    }

?>
<article class="full">
    <header><h1><?php echo($Title); ?></h1></header>
    <img style="float:right;width:60%;" src="<?php echo($ImagePath); ?>">
    <footer>
        <?php echo($Ingres); ?>
    </footer>
    <br/>
    <h4>Specs:</h4>
    <ul style="padding-left:30px;">
        <?php echo($Specs); ?>
    </ul>
    <br/>
    <p style="font-size:90%;line-height:1.5;">
        <?php echo($Article); ?>
    </p>
    <div class="clearBoth"></div>
</article>

The require() at the start gets a file that requires the script which contains the mysql login information. This is ofcourse a different file on localhost than on the webserver. This file on the webserver is located in httpd.private which is accessed like this: require($_SERVER['DOCUMENT_ROOT']."/../httpd.private/define.mysql.php");, and contains:

<?php
    define('DB_USER', 'myUsername');
    define('DB_PASS', 'myPassword');
    define('DB_HOST', 'myHostAddress');
    define('DB_NAME', 'myDatabaseName');
    define('DB_CHAR', 'utf8');
    define('DB_COLL', '');
?>

Then the script goes on to create variables for each cell in the the selected row, and then echo those variables at the apropriate place in the HTML code. The databases on my localhost and webserver are exact copies of eachother so that can't be the problem. I have tried to troubleshoot all i can think of, the login information is correct, so the conection to the database works fine, but for some reason the script does not output the information from the database when run from the webserver. However it works fine on localhost.

Thanks in advance for any help.

Answer 1

I think the mistake is the use of mysql_real_escape_string, indeed the PHP documentation is reported that the second parameter, $link_identifier, need an active link by mysql_connect():

$link_identifier: The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

I think your own server is set to not display the warning.

Try to enter the error_reporting(E_ALL) before starting connection and see if you actually get the warning.

If you actually get the error I think is better to use a statement to prevent SQL Injection in this way:

$query = "SELECT * FROM `Products` WHERE ID = ?";
$stmt = $mysqli->prepare($query);
if ($stmt === FALSE) {
    die("$mysqli->error");
}

$art = $_GET['art'];
$stmt->bind_param('i', $art);
$stmt->execute();
if ($stmt->errno != 0) {
    die("SQL Error: ({$stmt->errno}), {$stmt->error}");
}

$resultSet = $stmt->get_result();
$stmt->close();