Skip to main content
ElizabethGailLittle
ElizabethGailLittle
Inspiring
October 17, 2015
Answered

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

  • October 17, 2015
  • 1 reply
  • 3761 views

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>";

} ?>

<form method="POST" name="form1" id="form1">

  <table class="DisplayTable" align="center">

    <tr>

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

      <td><?php echo $textpage?></td>

    </tr>

    <tr valign="baseline">

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

      <td><?php echo $h_date?></td>

    </tr>

    <tr>

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

      <td><?php echo $h_seq?></td>

    </tr>

    <tr>

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

      <td><?php echo $h_col?></td>

    </tr>

    <tr>

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

      <td><?php echo $p_heading?></td>

    </tr>

    <tr>

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

      <td><?php echo $p_text?></td>

    </tr>

    <tr>

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

      <td><?php echo $h_hide?></td>

    </tr>

   <tr>

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

      <td display="hidden"></td>

   </tr>

    <tr>

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

      <td align="left"><input name="delete" id="delete" type="submit" class="GreenButton" value="Confirm Delete" /></td>

   </tr>

</table>

</form>

This topic has been closed for replies.
Correct answer David_Powers

342 - which is correct.


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.

1 reply

David_Powers
David_Powers
Inspiring
October 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']));

ElizabethGailLittle
ElizabethGailLittleAuthor
Inspiring
October 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.

David_Powers
David_PowersCorrect answer
Inspiring
October 19, 2015

342 - which is correct.


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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices