Why doesnt my 2nd SQL query run like the first?

Beginners - Learning PHP
#1

Hi All,

I know the code is poorly written, I am new and im trying to learn the basics first before I can understand what is actually bad and what i acceptable. I do appreciate all help though.

I am trying to run two sql queries in a PHP page but when i copy and past the ‘working’ code from the first part it errors on the second…

I recognise that result and row become a variable so tried to wipe those variables as it reaches the next instance of the code but still get an error on the second part. Surely I can run multiple queries, just why doesnt it like it?

I know in my past posts im told to turn on errors which i thought I had, but the result saying "error: " doesnt help me. So while I wait for help on here, i will be proactive and try and google errors and how to display errors further.

I am trying to learn.

TIA
Lee

<!-- #####################STEAKS######################### -->        

        <hr>
        <h5><u>BUTCHERS CORNER:</u></h5>
        <hr>

<?php
// get the records from the database
if ($result = $mysqli->query("SELECT * FROM products WHERE catagory = 'Steaks' ORDER BY item"))
{
// display records if there are records to display
if ($result->num_rows > 0)
{
// display records in a table
echo "<table class='table table-striped'>";

// set table headers
echo "<tr>
<th></th><th>ITEM</th><th>COST</th><th>SIZE</th><th>CATAGORY</th><th>QTY</th></tr>";

while ($row = $result->fetch_object())
{
// set up a row for each record
echo "<tr>";
// echo "<td>" . $row->id . "</td>";
echo "<td></td>";
echo "<td>" . $row->item . "</td>";
echo "<td>£ " . $row->cost . "</td>";
echo "<td>" . $row->size . "</td>";
echo "<td>" . $row->catagory . "</td>";
echo "<td><input type='number' min='0' max='99' value='0' name='".$row->id."'></td>";
echo "</tr>";
}
echo "</table>";
}
// if there are no records in the database, display an alert message
else
{
echo "No results to display!";
}
}
// show an error if there is an issue with the database query
else
{
echo "Error: " . $mysqli->error;
}

// close database connection
$mysqli->close();
// $result='';
// $row='';
?>

            
</table>
        

        
<!-- ######################MORE MEAT######################## -->
        
        <hr>
        <h5><u>MORE MEAT:</u></h5>
        <hr>

<?php
// get the records from the database
if ($result = $mysqli->query("SELECT * FROM products WHERE catagory = 'Snacks' ORDER BY item"))
{
// display records if there are records to display
if ($result->num_rows > 0)
{
// display records in a table
echo "<table class='table table-striped'>";

// set table headers
echo "<tr>
<th></th><th>ITEM</th><th>COST</th><th>SIZE</th><th>CATAGORY</th><th>QTY</th></tr>";

while ($row = $result->fetch_object())
{
// set up a row for each record
echo "<tr>";
// echo "<td>" . $row->id . "</td>";
echo "<td></td>";
echo "<td>" . $row->item . "</td>";
echo "<td>£ " . $row->cost . "</td>";
echo "<td>" . $row->size . "</td>";
echo "<td>" . $row->catagory . "</td>";
echo "<td><input type='number' min='0' max='99' value='0' name='".$row->id."'></td>";
echo "</tr>";
}
echo "</table>";
}
// if there are no records in the database, display an alert message
else
{
echo "No results to display!";
}
}
// show an error if there is an issue with the database query
else
{
echo "Error: " . $mysqli->error;
}
// close database connection
$mysqli->close();
?>
</table>

Capture
Capture1522×380 10.7 KB

#2

I found it, the first instance closes the DB connection meaning that the second instance was actually connected. Slowly getting there. :slight_smile: feel a sence of achievement haha

#3

Your php’s error related settings are not setup so that php is helping you. You would have been getting a php error at each reference to $mysqli after the point where the connection was closed. Find the php.ini that php is using and set error_reporting to E_ALL and display_errors to ON. You may need to restart your web server to get any change made to the php.ini to take effect.

As to the database statement error handling logic you have now, see this from your previous thread -

The reason for NOT outputting the raw database errors onto a live/public web page is because they only help hackers when they intentionally do things that trigger errors.

A value added feature of using exceptions for errors is that your main code only has to deal with error free execution, since execution transfers to the nearest exception handling, php in this case, upon an error.

To enable exceptions for errors for the mysqli extension, add the following line of code before the point where you make the database connection, then remove the error handling logic you have now, simplifying the code -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Also, since php automatically destroys all resources used on a page when the script ends, let it close the database connection for you, further simplifying the code you have to write. This alone would have prevented the problem.

When the false conditional logic statements are much shorter than the true logic statements, your code will be clearer if you invert the logic test, then put the false logic statements first, followed by the true logic statements.

The database specific code, that knows how to query for and fetch data, should NOT be inside the html document. Put it before the start of the html document, fetch all the data from the query into an appropriately named php variable, then test/loop over this variable at the appropriate point in the html document. This makes writing, testing, debugging, and maintaining your code easier. It also simplifies the type of php code inside the html document, reducing the amount of coding errors.

#4

Hi Phdr,
I appriciate your message and have read it a few times. Some parts are a little past where my level is at just now but i do grasp what you are saying and understand the logic you put forward. I am not sure I am at the level to put into action yet but I sure aim to be.

Sponsor our Newsletter | Privacy Policy | Terms of Service