Click here to Skip to main content
14,486,704 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a php code that needs to update the data from the database, but it gives me this error: Error description:You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

This is my code:
<pre><?php
//initialize variables
$Pand =" ";
$Naam =" ";
$Email =" ";
$Huisnummer =" ";
$Deel =" ";
$id = 0;

// connect to database
$db = mysqli_connect("xxx","xxx","xxx","xxx");
//update records
if (isset($_POST['aanpassen'])) {
    $Naam = mysqli_real_escape_string($_POST["Naam"]);
    $Email = mysqli_real_escape_string($_POST["Email"]);
    $Pand = mysqli_real_escape_string($_POST["Pand"]);
    $Huisnummer = mysqli_real_escape_string($_POST["Huisnummer"]);
    $Deel = mysqli_real_escape_string($_POST["Deel"]);
    $id = mysqli_real_escape_string($_POST["id"]);
    
    if (!mysqli_query($db,"UPDATE Info SET Naam= '$Naam' , Email= '$Email' , Pand= '$Pand' , Huisnummer= '$Huisnummer' , Deel= '$Deel' WHERE id=$id")){
    echo("Error description:". mysqli_error($db));
    }
    header('location: overzichtlocatie.php');
}
// retrieve records
$results = mysqli_query($db, "SELECT *  FROM Info");
?>


What I have tried:

I have tried the code like this:
<pre>
<?php
// connect to database
$db = mysqli_connect("xxx","xxx","xxx","xxx");
//update records
if (isset($_POST['aanpassen'])) {
    $Naam = mysqli_real_escape_string($_POST["Naam"]);
    $Email = mysqli_real_escape_string($_POST["Email"]);
    $Pand = mysqli_real_escape_string($_POST["Pand"]);
    $Huisnummer = mysqli_real_escape_string($_POST["Huisnummer"]);
    $Deel = mysqli_real_escape_string($_POST["Deel"]);
    $id = mysqli_real_escape_string($_POST["id"]);

    if (!mysqli_query($db,"UPDATE Info SET Naam= '$Naam' , Email= '$Email' , Pand= '$Pand' , Huisnummer= '$Huisnummer' , Deel= '$Deel' WHERE id=$id")){
    echo("Error description:". mysqli_error($db));
    }
    header('location: overzichtlocatie.php');
}
// retrieve records

?>
Posted
Updated 17-Mar-20 10:45am
Comments
Richard Deeming 17-Mar-20 13:34pm
   
Firstly, the "escape string" functions aren't really an adequate defence against SQL Injection. You should be using prepared statements with parameters instead.

PHP: SQL Injection - Manual[^]
533578 17-Mar-20 13:56pm
   
I have tried multiple things, and it gives me this error still.. Even with prepared statements.
phil.o 17-Mar-20 14:13pm
   
Please use the green "Improve question" widget and qualify it with the prepared-statement version.
Richard Deeming 17-Mar-20 13:35pm
   
Secondly, you're storing passwords in plain text. Don't do that.
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

PHP even has built-in functions to help you do the right thing:
PHP: password_hash[^]
PHP: password_verify[^]
533578 17-Mar-20 13:45pm
   
It is only made for myself and my teacher. Because it is for a school project ;)
phil.o 17-Mar-20 13:48pm
   
This does not prevent you from doing it right :)
533578 17-Mar-20 13:54pm
   
True, but I am just getting started with php so I need to learn a lot of things. And school gave me this order, and no one can help me because all the schools are closed. Now I need to ask people on sites like this. And I am 2 days stuck with this error :(
ZurdoDev 17-Mar-20 15:05pm
   
You likely have a ' in one of your fields causing you sql statement to break. I don't do php and mysql but w3schools has a good article on how to do it.

https://www.w3schools.com/php/php_mysql_prepared_statements.asp

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 2

Quote:
I need help with SQL syntax error.

1 of your problems is that you can't know what is the query.
I would start by replace
if (!mysqli_query($db,"UPDATE Info SET Naam= '$Naam' , Email= '$Email' , Pand= '$Pand' , Huisnummer= '$Huisnummer' , Deel= '$Deel' WHERE id=$id")){

by something like
$Query= "UPDATE Info SET Naam= '$Naam' , Email= '$Email' , Pand= '$Pand' , Huisnummer= '$Huisnummer' , Deel= '$Deel' WHERE id=$id";
if (!mysqli_query($db,$Query)){

This little change allow you to print $Query or inspect it with debugger. This way, you can know what was your real query.
-----
Your code do not behave the way you expect, or you don't understand why !

There is an almost universal solution: Run your code on debugger step by step, inspect variables.
The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't know what your code is supposed to do, it don't find bugs, it just help you to by showing you what is going on. When the code don't do what is expected, you are close to a bug.
To see what your code is doing: Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute.

The downside of this solution:
Debugger - Wikipedia, the free encyclopedia[^]

Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]
Basic Debugging with Visual Studio 2010 - YouTube[^]

phpdbg | php debugger[^]
Debugging techniques for PHP programmers[^]

The debugger is here to only show you what your code is doing and your task is to compare with what it should do.
   
Comments
533578 18-Mar-20 7:19am
   
I have tried your code, and it is still giving me the same error.
Patrice T 18-Mar-20 7:42am
   
It don't correct the error, it only allow you to see what is the real query if you print it or use the debugger.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100