0

the following commands work if I run them directly in phpmyadmin as SQL

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM De_Lijst WHERE Nieuw = "Ja";
UPDATE tmptable_1 SET DB_ID = NULL, Nieuw = "", Positie =0, Datum_Af = CURRENT_DATE;
INSERT INTO De_Lijst SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;
update De_Lijst set Info =if(Nieuw ="Ja","",Info);
update De_Lijst set Datum_Af =if(Nieuw ="Ja","",Datum_Af);
update De_Lijst set Datum_Op =if(Nieuw ="Ja","",Datum_Op);
update De_Lijst set Titel =if(Nieuw ="Ja","",Titel);
update De_Lijst set Artiest =if(Nieuw ="Ja","",Artiest);
update De_Lijst set Nieuw =if(Nieuw ="Ja","",Nieuw);
update De_Lijst set Nieuw =if(Nieuw ="Nieuw","",Nieuw);

I created a page called reset.php that looks this

<?php

require 'database.php';
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'update De_Lijst set Info =if(Nieuw ="Ja","nee",Info)';
$q = $pdo->prepare($sql);

$q->execute(array($Nieuw,$Positie,$Artiest,$Titel,$Info,$Datum_Op,$Datum_Af)); Database::disconnect();

?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
    <link rel="stylesheet" href="./bootstrap-4.0.0/dist   /css/bootstrap.min.css">
    <link rel="stylesheet" href="./css/custom.css">
    <script src="./bootstrap-4.0.0/dist/js/bootstrap.bundle.min.js">     </script>
</head>
<body>
 Hello<br>

<form method="POST" action="reset.php">
    <input type="submit" name="reset" value="reset"/>
</form>

</body>
</html>

The php file is just a test with one query but I need all the sql inserts. but results in a 500 error. Any info on what I am doing wrong?

Thanks, Jacques

Revdutchie
  • 41
  • 5
  • 1
    One thing that is wrong are the unescaped double quotes in the string literal in the line `$sql = "...`. You shouldn't use double quotes in SQL for string literals (caution, this is now on the SQL level, not PHP anymore) anyway. Use single quotes: `$sql = "update De_Lijst set Info =if(Nieuw ='Ja','nee',Info)";`. – sticky bit Jan 08 '21 at 20:45
  • Where is your `catch() { }` block ? – Syscall Jan 08 '21 at 20:47
  • 1
    Note : a "500 error" probably means that there are some debug informations in the webserver logs. – Syscall Jan 08 '21 at 20:47
  • Like Syscall said, there are multiple PHP syntax errors in this code, unrated to whatever PDO or mysql stuff. However, he SQL is *also* wrong because you don't run a dozen update queries but a single query. You are also dropping the table before updating it. Overall, there are so much issues with this code that it's hardly salvageable. Learn step by step: connecting, running queries in PDO in general, then update queries, then run three queries, the create table, the insert and the update – Your Common Sense Jan 08 '21 at 21:05
  • I removed the try it is already in my database.php including the catch block, Made it single quotes at least the error is gone. The table is a tempory table and that is for a reason. About the multiple update that was my next fix, first I need to get it working, – Revdutchie Jan 08 '21 at 21:23
  • @YourCommonSense I create a Temp table insert data copy it to the real table then drop the temp table. Most php is directly from w3schools so I wonder how there can be so many errors like you state. – Revdutchie Jan 08 '21 at 21:39
  • coming from w3schools is the exact reason for so many errors. this infamous resource is dubbed w3fools for a reason. better see here: https://phpdelusions.net/pdo_examples – Your Common Sense Jan 08 '21 at 21:45
  • @YourCommonSense Intresting, the first thing I see when I got to that page and the update function is the use of double quotes while I was told to use single here. Anyway, I will give it a try and see if that can fix my problem. Sadly here is aperently not the right place. – Revdutchie Jan 08 '21 at 21:53

0 Answers0