1

At the moment, I have two queries. The first selects a column from a row. The second then deletes that row. As both queries deal with the same row, I was wondering if it was possible to execute both queries in one (to reduce the amount of code).

I had a look at SELECT then immediately DELETE mysql record and tried Whatever Kitchen's answer

This was my code beforehand (which works fine):

$stmt = $con->prepare("SELECT number FROM viewings WHERE username=:user");
$stmt->bindParam(':user', $user);
$stmt->execute();
$row = $stmt->fetch();
$result = $row['number'];
    
$stmt = $con->prepare("DELETE FROM viewings WHERE username=:user");
$stmt->bindParam(':user', $user);
$stmt->execute();
      
echo $result;

This was my code after trying the answer:

$stmt = $con->prepare("DELETE FROM viewings WHERE username=:user IN (SELECT number FROM viewings WHERE username=:user LIMIT 1)");
$stmt->bindParam(':user', $user);
$stmt->execute();
$row = $stmt->fetch();
$result = $row['number'];
echo $result;

However, I receive these errors:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'' in /home//public_html/page.php:47

Stack trace:

0 /home//public_html/page.php(47): PDOStatement->execute()

1 {main} thrown in /home//public_html/page.php on line 4

Community
  • 1
  • 1
The Codesee
  • 3,714
  • 5
  • 38
  • 78
  • 1
    You will not get the content or the `viewings` from that query. – RiggsFolly Jul 09 '18 at 11:34
  • 1
    It's also not how you use `IN`. The syntax is: `WHERE columnName IN ('value1', 'value2', ...)`. – M. Eriksson Jul 09 '18 at 11:38
  • 1
    You cannot select and delete at the same time. You've to use the queries above or just write stored procedure – num8er Jul 09 '18 at 11:39
  • 1
    Go back to using your original code – RiggsFolly Jul 09 '18 at 11:44
  • 1
    go back to previous code and fix first select: `SELECT number FROM viewings WHERE username=:user LIMIT 1` to avoid db engine walk through table in search of other rows. – num8er Jul 09 '18 at 11:49
  • @num8er Should that be used for all queries which are supposed to only return one row? – The Codesee Jul 09 '18 at 11:52
  • @TheCodesee I always think about data retention when user deletes something unexpectedly. So why not just have `deleted` flag-field in viewings table and just set it to 1 when You want to delete it? Also I cannot see any reason to select the data You want to delete, since when user wants to delete it, user see that HTML element and clicks delete. So what necessity makes You to select data before delete? – num8er Jul 09 '18 at 16:57
  • @TheCodesee about `LIMIT 1` if You know that such row can be only one or You're taking topmost row - YES – num8er Jul 09 '18 at 23:48

1 Answers1

1

You can try using the EXISTS condition:

DELETE FROM viewings WHERE EXISTS (SELECT * FROM viewings WHERE username=:user LIMIT 1)

Source: SQL EXISTS condition

aCarella
  • 2,369
  • 11
  • 52
  • 85