Query returning nothing

[Martsteel]

Hello, I’m trying to query a database that is hosted by Bluehost. I can connect to the database successfully but when I run a query it returns nothing. As a complete novice I’m oblivious to why this is the case. There are over two hundred records in the table which I expected to be displayed but the web page is blank with no errors displayed or in the php log on the server. Here is my code:

    <?php
    $dbhost = 'localhost';
    $dbuser = 'martstee_Guest';
    $dbpass = '*************';
    $dbname = 'martstee_WPSPT';
    // Create connection
    $conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $squery = "SELECT * FROM data products";
    if ($result = mysqli_query($conn, $squery)) {
        while ($row = mysqli_fetch_assoc($result)) {
            echo "<tr>";
            echo "<td>" .  $row['Code'] . "</td>";
            echo "<td>" .  $row['Qty'] . "</td>";
            echo "<td>" .  $row['Loc'] . "</td>";
            echo "</tr>";   
        }
        mysqli_free_result($result);
    }
    ?>

Thank you in advance for taking the time to read this and hopefully advise. Any help is gratefully appreciated.

Cheers Martin

[phdr]

The most immediate problem is you have an error in your sql query syntax at the two word table name. This requires special handling in a query and should be avoided. You should use an under score _, rather than a space, in any multiple word database, table, or column names. Read on to see how to get error handling that would have alerted you to this issue.

When learning, developing, and debugging code/query(ies), you should set php’s error_reporting to E_ALL (it should always be this value) and set display_errors to ON. This will give you immediate feedback as to any problems. These settings should be in the php.ini on your system. You should also be using a localhost development system and only put the final, tested, debugged, and secure code onto a live/public server. Continually uploading each change you make to a remote server to see the result of that change is a huge waste of time. On a live/public server, you would set display_errors to OFF and set log_errors to ON, in the php.ini, to cause all php errors to be logged instead of displayed.

Next, you should always have error handling for statements that can fail. For database statements, you should simply use exceptions for errors and in most cases let php catch and handle the exception, where php will use its error related settings (see above) to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) The exception to this rule are for errors that the visitor to your site can correct, such as when inserting/updating duplicate or out of range data values. In this case, your code should catch the exception, detect if the error number is for something your code is designed to handle, then setup and output a message telling the user exactly what was wrong with the data that they submitted. For all other error numbers, just re-throw the exception and let php handle it. A neat point of using exceptions for errors, is your main code only has to deal with error free execution. You can therefor remove any existing error handling conditional logic, to simplify your code.

Lastly, if you are just starting out, you should forget about the mysqli extension and use the much simpler and more consistent PDO extension. You should also put your database connection code into a separate .php file and require it when needed, so that you don’t have to spend time redacting information in it when posting your main code.

[ErnieAlex]

Yes, do not use spaces in table names.

FROM data products

Rename the table to data_products and should be fixed.

[Martsteel]

Thank you all so much for the replies which contained helpful and useful information

The code is working now as expected. After changing the table name to include no spaces I was still having issues. There must must be a problem with the table as I created a new database and table and everything is now OK.

Thank you all.