How to UPDATE table without WHERE clause in php?

Question

I would like to update table data on user input without a where clause. I tried using the code below, but it shows "500 Internal server error"

form code (test1.php):

<html>
<form action="anja.php" method="get">
 <p>Essen: <input type="text" name="ess1" /></p>
 <p>Essen: <input type="text" name="ess2" /></p>
 <p><input type="submit" value="update" /></p>
</form>
</html>

update data code (anja.php):

</html>
<?php
require 'inc/db.php';


$ess1 = $_GET['ess1'];
$ess2 = $_GET['ess2'];

$data = [
    'ess1' => $ess1,
    'ess2' => $ess2
        
];

{
    $sql ="UPDATE essenplan SET ess1='$ess1'";"UPDATE essenplan SET ess2='$ess2'";
    $stmt= $pdo->prepare($sql);
    $stmt->execute($data);
    
}
?>
</html>

database connection code (db.php) :

<?php
$pdo = new PDO('mysql:host=hfpsql.bandstahl.local;dbname=essenmenue', 'essen', 'c1AzGm4oMqO0RiB7');
?>

Thanks in advance!

If it shows a 500 page then you would need to check your error logs or [enable proper error reporting](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) — DarkBee, Mar 14 '23 at 10:20
You generally can't write two queries in the same execute() command. Use separate prepare() and execute()s for each one. — ADyson, Mar 14 '23 at 10:23
Make sure you enable [PDO error handling](https://phpdelusions.net/pdo#errors) so you get a sensible exception, and then read [How can I get useful error messages in PHP?](https://stackoverflow.com/questions/845021/how-can-i-get-useful-error-messages-in-php) to understand about displaying and/or logging errors in PHP — ADyson, Mar 14 '23 at 10:23
Also, you haven't properly understood how to use prepared statements...whilst you're building a parameter array in the right way, you forgot to convert the other PHP variables inside the $sql variable into _placeholders_ (to match the parameters). There are some nice, simple examples of the correct approach here: https://phpdelusions.net/pdo#prepared — ADyson, Mar 14 '23 at 10:25
_NOTE_: The `{` and `}` around the 3 query lines are completely unnecessary — RiggsFolly, Mar 14 '23 at 10:31
I think you have to learn the basics of database usage first (manly SQL language). Otherwise you will run in many other problems (syntax and semantic). — Wiimm, Mar 14 '23 at 15:15

score 1 · Answer 1 · answered Mar 14 '23 at 10:43

The proper way to create a prepared, parameterised query. Also you cannot and in this case do not need to do 2 queries in one call in PDO. You are allowed to update as many columns as you like as long as they are all in the same row all in one query.

$ess1 = $_GET['ess1'];
$ess2 = $_GET['ess2'];

$data = [
    'ess1' => $ess1,
    'ess2' => $ess2
        
];

//{ not necessary

$sql ="UPDATE essenplan SET ess1= ?, ess2= ?";
$stmt= $pdo->prepare($sql);
$stmt->execute($data);
    
//} not necessary

NOTE: I hope you realise this will update EVERY row in the essenplan table.

How to UPDATE table without WHERE clause in php?

1 Answers1