I'm creating a page for my shop but I was using get_result, and after I finished everything I realised that HostGator couldn't allow me to have mysqlnd running. So I had to convert everything to bind_result instead but I'm in a dead end here probably from being frustrated that I have to change everything...
Anyway my problem is that I call some queries to read tables but the whole code stops after returning 1 result. In this code for example I call for a list of Appointments, although I have like 5 appointments set in the database it only returns the first one. It works as it is supposed to and reads from multiple tables but stops at 1 loop. It wouldn't let me to continue reading the 2nd table if I didn't do a $listappointments_stmt->free_result(); first before the next queries so I think that's my problem but I have no idea how to work arround it as it gives me a boolean error if I don't have it there. Any ideas welcome!
Thank you in advance, Xenos K.
<?php
$query="SELECT * FROM appointments";
$listappointments_stmt=$mysqli->prepare($query);
$listappointments_stmt->execute();
$listappointments_stmt->bind_result($AppId, $AppDate, $AppTime, $AppType, $AppArtist, $AppTitle, $AppClient, $AppPrice, $AppNotes);
while ($listappointments_stmt->fetch())
{
$theDate=date("d-M-Y", strtotime($AppDate));
echo "<tr><td>".$theDate."</td>";
echo "<td>".$AppTime."</td>";
$listappointments_stmt->free_result();
$tempappointmentType=$AppType;
$query2="SELECT * FROM appointmenttypes WHERE ID=?";
$listappointmentsTypes_stmt=$mysqli->prepare($query2);
$listappointmentsTypes_stmt->bind_param("s", $tempappointmentType);
$listappointmentsTypes_stmt->execute();
$listappointmentsTypes_stmt->bind_result($AppTypeId, $AppTypeName, $AppTypeColor);
while ($listappointmentsTypes_stmt->fetch())
{
echo "<td><span class=\"label\" style=\"background-color:".$AppTypeColor."\">".$AppTypeName."</span></td>";
}
$listappointmentsTypes_stmt->free_result();
$listappointmentsTypes_stmt->close();
$tempappointmentArtist=$AppArtist;
$query3="SELECT * FROM staff WHERE ID=?";
$listappointmentsArtist_stmt=$mysqli->prepare($query3);
$listappointmentsArtist_stmt->bind_param("s", $tempappointmentArtist);
$listappointmentsArtist_stmt->execute();
$listappointmentsArtist_stmt->bind_result($ArtId, $ArtName, $ArtNickName, $ArtSurname, $ArtPhone, $ArtBirthDate, $ArtIdentificationNumber, $ArtStreetName, $ArtStreetNumber, $ArtPostalCode, $ArtCity, $ArtCountry, $ArtPosition, $ArtEmail, $ArtFacebook, $ArtInstagram);
while ($listappointmentsArtist_stmt->fetch())
{
echo "<td>".$ArtName." ".$ArtNickName." ".$ArtSurname."</td>";
}
$listappointmentsArtist_stmt->free_result();
$listappointmentsArtist_stmt->close();
echo "<td>".$AppTitle."</td>";
$tempappointmentClient=$AppClient;
$query4="SELECT * FROM clients WHERE ID=?";
$listappointmentsClient_stmt=$mysqli->prepare($query4);
$listappointmentsClient_stmt->bind_param("s", $tempappointmentClient);
$listappointmentsClient_stmt->execute();
$listappointmentsClient_stmt->bind_result($CliId, $CliName, $CliSurname, $CliPhone, $CliBirthDate, $CliIdentificationNumber, $CliStreetName, $CliStreetNumber, $CliPostalCode, $CliCity, $CliCountry, $CliFathersFullName, $CliMothersFullName, $CliEmail, $CliFacebook, $CliInstagram, $CliNotes);
while ($listappointmentsClient_stmt->fetch())
{
echo "<td>".$CliName." ".$CliSurname."</td>";
echo "<td>".$CliPhone."</td>";
}
$listappointmentsClient_stmt->free_result();
$listappointmentsClient_stmt->close();
echo "<td>".$AppPrice."</td>";
echo "<td><a href=\"appointmentsedit.php?appointmentStatus=view&appointmentId=".$AppId."\" title=\"".$lang['view']."\"><i class=\"text-green fa fa-eye\"></i></a></td>";
echo "<td><a href=\"appointmentsedit.php?appointmentStatus=edit&appointmentId=".$AppId."\" title=\"".$lang['edit']."\"><i class=\"text-blue fa fa-edit\"></i></a></td>";
echo "<td><a href=\"appointmentsedit.php?appointmentStatus=delete&appointmentId=".$AppId."\" title=\"".$lang['delete']."\"><i class=\"text-red fa fa-trash-o\"></i></a></td></tr>";
}
$listappointments_stmt->close();
?>
You'll be wise to learn to use JOIN statements in SQL. This will allow you to use just one SQL query to fetch all the results you need.
In the meantime, if you are nesting some SQL queries inside others, the outside query (in your case SELECT * FROM appointments) needs a separate database connection (in your case $mysqli) from the rest of the queries. Issuing a new query from the connection resets the rest of the queries.
Pro tip: Avoid using SELECT * in production software. Instead give a list of columns you need. Your software will be more robust and easier to understand if you do that.