I am trying to perform this query in PHP however it keeps returning false. I have tried the query in phpMyAdmin and it works fine so if anyone can spot what is wrong that would be great. Also how can I get some better error messages for problems like this so I can try and solve the problem?
$stmt = $conn->prepare("SELECT * FROM artist WHERE artID != ?;");
if(!$stmt)
{
echo "Error creating SQL statement";
return 1;
}
I have already used $stmt = $conn->prepare(query); for a different query in the same block of PHP code which runs fine so I don't know if that is anything to do with it.
Thanks in advance :)
EDIT: I was asked where I bind the '?' used in the query. $stmt->bind_param('i', $albumArtID); I didn't include it in the question originally because the echo in the if statement runs so I presumed it was encountering an error before the bind_param.
EDIT 2: As requested here is the code used to make the connection:
<?php
error_reporting(-1);
ini_set('display_errors', 'On');
$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$db_name = 'psyjb6';
$conn = new mysqli('localhost', 'root', '', 'psyjb6');
if ($conn->connect_errno)
echo"<p>failed to connect to database</p>";
?>
EDIT 3: Here is the entire main section of code from that page, hopefully we can figure this out:
<form name="editAlbum" method="get" onsubmit="return validateForm(this)">
<div class="row">
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
include 'connection.php';
if(isset($_GET["album"]))
{
/* If album was passed in the URL then get current values
for that album */
$stmt = $conn->prepare("SELECT cd.artID, artName, cdTitle, cdPrice, cdGenre, cdTracks FROM cd INNER JOIN artist ON (cd.artID = artist.artID AND cdID = ?);");
if(!$stmt)
{
echo "Error creating SQL statement";
exit;
}
$albumID = htmlspecialchars($_GET["album"]);
$stmt->bind_param('i', $albumID);
$stmt->execute();
$stmt->bind_result($albumArtID, $albumArtName, $albumTitle,
$albumPrice, $albumGenre, $numTracks);
$stmt->fetch();
/* Create input fields */
// Album Title
echo "<div class=\"row horizontal-center\">" .
"<input type=\"text\" value=\"" . htmlspecialchars($albumTitle) . "\" name=\"albumTitle\"/>" .
"</div>";
// Artist Name
echo "<div class=\"row horizontal-center\">" .
"<h6>By Artist:</h6>" .
"</div>";
echo "<div class=\"row horizontal-center\">" .
"<select name=\"artID\">";
/* Create option for current artist so it will be first in list */
echo "<option value=\"$albumArtID\">$albumArtName</option>
";
/* Generate list of artists except artist currently associated with the album */
$stmt = $conn->prepare("SELECT * FROM artist WHERE artID != ?");
if($stmt === false)
{
echo $conn->error;
echo "hi";
exit;
}
$stmt->bind_param('i', $albumArtID);
$stmt->execute();
$stmt->bind_result($artID, $artName);
/* Check if no artists were found */
if(!$stmt->fetch())
echo "<p>No artists were found!</p>";
else
{
/* Create options for artists that were found */
do
{
echo "<option value=\"$artID\">$artName</option>
";
}while($stmt->fetch());
}
echo "</select>" .
"</div>";
// Album Price
echo "<div class=\"row horizontal-center\">" .
"<input type=\"number\" step=\"0.01\" value=\"" . htmlspecialchars($albumPrice) . "\" name=\"albumPrice\"/>" .
"</div>";
// Album Genre
echo "<div class=\"row horizontal-center\">" .
"<input type=\"text\" value=\"" . htmlspecialchars($albumGenre) . "\" name=\"albumGenre\"/>" .
"</div>";
// Number of Tracks
echo "<div class=\"row horizontal-center\">" .
"<input type=\"number\" value=\"" . htmlspecialchars($numTracks) . "\" name=\"numTracks\"
/>" .
"</div>";
// Delete checkbox
echo "<div class=\"row\">" .
"<div class=\"col-2\">" .
"<h6>Delete:</h6>" .
"</div>" .
"<div class=\"col-1\">" .
"<input type=\"checkbox\" name=\"delete\" value=\"Delete\"/>" .
"</div>" .
"</div>";
/* Create hidden field to submit the album ID with the form */
echo "<input type=\"hidden\" value=\"" . htmlspecialchars($albumID) . "\" name=\"albumID\"
/>";
}
else
{
/* Send browser back to artists page if they somehow accessed
the edit page without going through the "Edit" link next
to an artist in the table. This would be the artName variable
would not be sent via the URL.*/
header("Location: artists.php");
}
?>
</div>
<div class="row">
<div class="col-2">
<h6>Delete:</h6>
</div>
<div class="col-1">
<input type="checkbox" name="delete" value="Delete"/>
</div>
</div>
<div class="row">
<input type="submit" name="submit" value="Update"/>
</div>
<!-- PHP to edit album data -->
<?php
include 'connection.php';
if(isset($_GET["delete"]))
{
$albumID = $_GET["albumID"];
/* Create DELETE query */
$stmt = $conn->prepare("DELETE FROM cd WHERE cdID = ?;");
if(!$stmt)
{
echo "Error creating SQL statement";
exit;
}
$stmt->bind_param('i', $albumID);
$stmt->execute();
}
else if(isset($_GET["albumTitle"]) && isset($_GET["albumGenre"])
&& isset($_GET["albumPrice"]) && isset($_GET["numTracks"]))
{
$albumTitle = htmlspecialchars($_GET["albumTitle"]);
$artID = htmlspecialchars($_GET["artID"]);
$albumGenre = htmlspecialchars($_GET["albumGenre"]);
$albumPrice = htmlspecialchars($_GET["albumPrice"]);
$numTracks = htmlspecialchars($_GET["numTracks"]);
/* Create INSERT query */
$stmt = $conn->prepare("UPDATE cd SET (cdTitle = ?, artID = ?,
cdGenre = ?, cdPrice = ?, cdTracks = ?) WHERE cdID = ?;");
if(!$stmt)
{
echo "Error creating SQL statement";
exit;
}
$stmt->bind_param('sisdi', $albumTitle, $artID, $albumGenre,
$albumPrice, $numTracks);
$stmt->execute();
}
?>
</form>
