PDO "Query was empty" [branched from: Can't insert data with PDO]

Report · Oct 17, 2015

Now trying to DELETE a record. I select it from a list, and go to the delete routine. The record is displayed, but when I confirm the delete, I get an error saying

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty' in /home1/sainttim/public_html/DeleteRec.php:53 Stack trace: #0 /home1/sainttim/public_html/DeleteRec.php(53): PDOStatement->execute(Array) #1 {main} thrown in /home1/sainttim/public_html/DeleteRec.php on line 53

I don't understand why the query is empty since the data is displayed on the screen, but assume that what I'm displaying on the screen is not what's in array($_POST['delete key']). I'm stuck!

My code:

$OK = false;

$deleted = false;

if (isset($_GET['varpage'])) {

$varpageSend = $_GET['varpage'];

$NextPage = "DisplayText.php?varpage=".$varpageSend;

}

if ((isset($_GET['recid'])) && ($_GET['recid'] != "")) {

$delrec = $_GET['recid'];

}

else

{

$error = "Record does not exist!";

}

if (isset($_GET['recid']) && !$_POST)

{

// prepare SQL query to display record

$sql = "SELECT home_key, textpage, h_date, h_seq, h_col, p_heading, p_text, h_hide FROM Homepage_text WHERE home_key = ?";

$rs1=$sainttim->prepare($sql);

$OK = $rs1->execute(array($_GET['recid']));

$row=$rs1->fetch();

$home_key=$row['home_key'];

$textpage=$row['textpage'];

$h_date=$row['h_date'];

$h_seq=$row['h_seq'];

$h_col=$row['h_col'];

$p_heading=$row['p_heading'];

$p_text=$row['p_text'];

$h_hide=$row['h_hide'];

if ($h_hide==0) {

$h_hide='N';

}

else {

$h_hide="y";

}

if (isset($rs1) && !$OK) {

$error = $rs1->errorInfo();

if (isset($error[2])) {

// store error message if query fails

$error = $error[2];

}

if (isset($_POST['delete']))

{

// NEW code

$deletesql = 'DELETE FROM Homepage_text WHERE home_key=?';

$stmt=$sainttim->prepare($deleteSQL);

$deleted = $stmt->execute($row);

if (!$deleted)

{

$error = 'There was a problem deleting the record.';

}

else {

header('Location: '.$deleteGoTo);

exit;

}

The form:

<h1>Delete Entry from <?php echo $varpageSend; ?> Page</h1>

<?php if (isset($error)) {

echo "<p> class='errormsg'>Error: ".$error."</p>";

} ?>

<tr>

</tr>

</tr>

<tr>

<td align="right">Sequence:</td>

</tr>

<tr>

</tr>

<tr>

<td align="right" >Heading:</td>

</tr>

<tr>

<td align="right">Text Content:</td>

</tr>

<tr>

<td align="right" >Hide Content?:</td>

</tr>

<tr>

<td display="hidden"><input value=<?php echo $delrec?> name="deletekey" id="deletekey" /></td>

</tr>

<tr>

<td align="right"><a href="DisplayText.php?varpage=<?php echo $Thistextpage; ?>"><span class="RedButton"> CANCEL </span></a></td>

</tr>

</table>

</form>

Report · Oct 18, 2015

You're using a prepared statement to delete the record, but you never bind the parameter for home_key:

$deletesql = 'DELETE FROM Homepage_text WHERE home_key=?';

$stmt=$sainttim->prepare($deleteSQL);

$deleted = $stmt->execute($row);

You're passing $row as the argument to the execute() method, but $row is created only when the form is first submitted, so you're passing a null value to the method. Even if $row did exist, it contains the whole record, not just the home_key.

When you click the button to confirm the deletion, the value of home_key is contained in $_POST['deletekey'], so this is the value that needs to be passed to the execute() method. Even if there is only one anonymous placeholder in a PDO prepared statement, the value passed to execute() must be an array:

$deleted = $stmt->execute(array($_POST['deletekey']));

Report · Oct 18, 2015

Thanks. You covered the execute value as an array, and it just didn't sink in. I still have at least 2 problems.

I now get:

Fatal error: Call to undefined method PDOStatement::bind_Param() in /home1/sainttim/public_html/DeleteRec.php on line 53

Code:

if (isset($_GET['recid']) && !$_POST)

{

// prepare SQL query to display record

$sql = "SELECT home_key, textpage, h_date, h_seq, h_col, p_heading, p_text, h_hide FROM Homepage_text WHERE home_key = ?";

$rs1=$sainttim->prepare($sql);

$rs1->bindColumn(1,$home_key);

$rs1->bindColumn(2,$textpage);

$rs1->bindColumn(3,$h_date);

$rs1->bindColumn(4,$h_seq);

$rs1->bindColumn(5,$h_col);

$rs1->bindColumn(6,$p_heading);

$rs1->bindColumn(7,$p_text);

$rs1->bindColumn(8,$h_hide);

$rs1->execute(array($_GET['recid']));

$rs1->fetch();

$error = $rs1->errorInfo();

if (isset($error)) {

$error="Read: ".$error;

if (isset($error[2])) {

// store error message if query fails

$error = $error." ".$error[2];

}

if (isset($_POST['delete']))

{

$deletesql = "DELETE FROM Homepage_text WHERE home_key=?";

$stmt=$sainttim->prepare($deleteSQL);

$stmt->bind_Param('?', $_POST['deletekey'], PDO::PARAM_STR); <------ line 53

$stmt->execute(array($_POST['deletekey']));

$deleted = $stmt->rowCount();

if (!$deleted)

{

#$error = 'There was a problem deleting the record.';

$error = $stmt->errorInfo();

if (isset($error[2]))

{

// store error message if query fails

$error = $error." ".$error[2];

}

echo "Delete error ".$error."<br /><br />";

}

else {

header('Location: '.$deleteGoTo);

exit;

}

However, there is also an error in the code in which I get the values to display, because the following

Error: Read: Array a

is displayed at the top of my confirmation page. The data displayed is correct. I've omitted the type=hidden from home_key, and that is also correct.

Again, thanks for your responses.

Report · Oct 18, 2015

Forgot to say.. If I comment out the bind_Param statement, I'm back to my original error query was empty.

Report · Oct 18, 2015

The error message is pretty clear. You're calling an undefined method. It's bindParam(), not bind_Param(). However, you don't need to call bindParam() if you're passing an array as an argument to the execute() method.

The reason you're probably getting an empty query error even when you comment out the undefined bind_Param() is because you're using the hidden attribute on the table cell that contains the deletekey input element.

<tr>

<td display="hidden"><input value=<?php echo $delrec?> name="deletekey" id="deletekey" /></td>

</tr>

The deletekey input element should be in a hidden form element, not in a hidden table cell. Replace the preceding code with the following:

<input value="<?php echo $delrec; ?>" name="deletekey" id="deletekey" type="hidden">

You get the error message when displaying the values to delete because of this code:

$error = $rs1->errorInfo();

if (isset($error)) {

$error="Read: ".$error;

if (isset($error[2])) {

// store error message if query fails

$error = $error." ".$error[2];

}

It should be this:

$errorInfo = $rs1->errorInfo();

if (isset($errorInfo[2])) {

// store error message if query fails

$error = $errorInfo[2];

}

Report · Oct 19, 2015

My read error is gone - thanks!

However, my code:

if (isset($_POST['delete']))

{

$deletesql = "DELETE FROM Homepage_text WHERE home_key=?";

$stmt=$sainttim->prepare($deleteSQL);

$stmt->execute(array($_POST['deletekey']));

$deleted = $stmt->rowCount();

if (!$deleted)

{

$error = 'There was a problem deleting the record.';

} <------- line 59 !!!!!

else {

header('Location: '.$deleteGoTo);

exit;

}

still results in the message

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty' in /home1/sainttim/public_html/DeleteRec.php:59 Stack trace: #0 /home1/sainttim/public_html/DeleteRec.php(59): PDOStatement->execute(Array) #1 {main} thrown in /home1/sainttim/public_html/DeleteRec.php on line 59

My form has a table which displays the record content.

Form definition is

The final lines of the table are

<tr>

<td><input value="<?php echo $delrec; ?>" name="deletekey" id="deletekey" type="hidden"></td>

</tr>

<tr>

<td align="right"><a href="DisplayText.php?varpage=<?php echo $Thistextpage; ?>"><span class="RedButton"> CANCEL </span></a></td>

</tr>

Thanks again for being so patient with me!

Report · Oct 19, 2015

Sorry - error occurs at execute statement, not where I indicated...

Report · Oct 19, 2015

Right-click the form before clicking Confirm Delete. What is the value of the deletekey hidden input element?

Report · Oct 19, 2015

342 - which is correct.

Report · Oct 19, 2015

Would it have anything to do with the fact that the key is an auto increment and therefore integer, and delete key is "342" as string?

Report · Oct 19, 2015

The reason the query is empty lies in the lack of consistency in spelling of your variable for the prepared statement:

$deletesql = "DELETE FROM Homepage_text WHERE home_key=?";

$stmt=$sainttim->prepare($deleteSQL);

The query is stored as $deletesql, but the value you're passing to the prepare() method is $deleteSQL. PHP variables are case sensitive. Use either $deletesql in both cases, or store the query as $deleteSQL.

Report · Oct 19, 2015

A-a-a-r-g-h! I can't believe the amount of time spent on a stupid mistake! I am aware of the case sensitivity, and I'm usually very careful about that. Your Eagle eye caught it - I had looked at every single line of code so many times, but did not spot it.

When I corrected that, the delete worked. I then had the header problem, and finally solved it by deleting a comment line at the top of the file in which I specify the file name which was before the opening php tag. I didn't know that nothing should precede the php tag.

Again, thanks for your patience and for your very helpful books.

PDO "Query was empty" [branched from: Can't insert data with PDO]

1 Correct answer