Error: You have an error in your SQL

Question

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''violation','officer','date','time') values('2101093','100','2','Daryl','Coronad' at line 1

<?php
date_default_timezone_set('Asia/Manila');
$conn=mysql_connect("localhost","root","");
    mysql_select_db("dbposo",$conn);

if(isset($_POST['add']))
{
    $number=$_POST['id'];
    $fname=$_POST['fname'];
    $lname=$_POST['lname'];
    $age=$_POST['age'];
    $gender=$_POST['gender'];
    $violation=$_POST['violation'];
    $officer=$_POST['officer'];
    $date=date("y-m-d");
    $time=date("H:i:s");

    $violation=mysql_query("select count(number) as offensecount from tblcitizen where number='$number'",$conn);
    if($data=mysql_fetch_array($violation))
    {
        $offensecount=$data['offensecount'];

        if($offensecount==1)
        {
            mysql_query("insert into tblcitizen(number, price, offenses, fname, lname, age, gender,'violation','officer','date','time') values('$number','100','2','$fname','$lname','$age','$gender','$violation','$officer','$date','$time')",$conn) or die(mysql_error());
            echo "<script>alert('2nd Offense: 300php Penalty!');</script>";
            header('Refresh: 0; URL=admin_publicviolation.php');
        }
        else if($offensecount==2)
        {
            mysql_query("insert into tblcitizen(number, price, offenses, fname, lname,'age','gender','violation','officer','date','time') values('$number','500','3','$fname','$lname','$age','$gender','$violation','$officer','$date','$time')",$conn) or die(mysql_error());
            echo "<script>alert('3rd Offense: 200php Penalty!');</script>";
            header('Refresh: 0; URL=admin_publicviolation.php');
        }
        else if($offensecount==3)
        {
            mysql_query("insert into tblcitizen(number, price, offenses, fname, lname,'age','gender','violation','officer','date','time') values('$number','1000','4','$fname','$lname','$age','$gender','$violation','$officer','$date','$time')",$conn) or die(mysql_error());
            echo "<script>alert('4th Offense: 1,000php Penalty!');</script>";
            header('Refresh: 0; URL=admin_publicviolation.php');
        }
        else
        {
            echo "<script>alert('KUKULONG NA PO KAU SORRY PO!');</script>";
            header('Refresh: 0; URL=admin_publicviolation.php');
        }
    }
}
else
{
    header('location:admin_publicviolation.php');
}
?>

WHAT IS MY ERROR IN THERE?

Column identifiers belong into backticks, not single quotes. Also read up on SQL escaping, or parameterised queries if versed. — mario, Feb 17 '14 at 01:38
[http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Phil, Feb 17 '14 at 01:53

John Conde · Accepted Answer · 2014-02-18T13:44:45.187

0

You can't wrap column names in quotes. Either use ticks or nothing at all (when allowed):

mysql_query("insert into tblcitizen(number, price, offenses, fname, lname, age, 
            gender,'violation','officer','date','time')  
            values('$number','100','2','$fname','$lname','$age','$gender','$violation','$officer','$date','$time')",$conn) or die(mysql_error());

should be

mysql_query("insert into tblcitizen(number, price, offenses, fname, lname, age, 
            gender,`violation`,`officer`,`date`,`time`) 
            values('$number','100','2','$fname','$lname','$age','$gender','$violation','$officer','$date','$time')",$conn) or die(mysql_error());

edited Feb 18 '14 at 13:44

answered Feb 17 '14 at 01:38

John Conde

217,595
99
455
496

wait sir, i'll try. and thank for your help :) – Daryl Feb 17 '14 at 01:40
Make sure you fix it in *all* of your queries. – John Conde Feb 17 '14 at 01:42
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''`date`,`time`) values('2101094','100','2','Daryl','Coronado','17','male','Resou' at line 1 – Daryl Feb 17 '14 at 01:42
I recommend wrapping those column names in ticks because they are reserved words (although date can be left without ticks it is still a good diea). – John Conde Feb 17 '14 at 01:43
I see the error sir :) thank you :) can i add you in your Yahoo Messenger, Skype or facebook for more questions sir? :) – Daryl Feb 17 '14 at 01:45
sir how about i have a table, and i fetch all the data in there, how can i get the latest id to fetch in my table? – Daryl Feb 17 '14 at 01:47
I'm not sure what you mean by your last question. Do you mean get the latest record in the database? – John Conde Feb 17 '14 at 01:53
i have a table, Name, Bank#, Penalty, Cash-in, Cash-out Daryl, 123, 5000, 5000, 5000 and if i update that i add another row, what am i going to do if i want to make bank # not repeat in the table, just 1 row per user – Daryl Feb 17 '14 at 01:54
Make that column a unique key or primary key. That will only allow it to be in the database once. – John Conde Feb 17 '14 at 01:56
http://stackoverflow.com/questions/21819491/fetch-only-latest-id-or-distinct here is my thread – Daryl Feb 17 '14 at 01:58
what am i suppose to do if i want to display the latest bank # only – Daryl Feb 17 '14 at 01:59
Just `ORDER BY date DESC LIMIT 1` in your query – John Conde Feb 17 '14 at 02:00
$news=mysql_query("select *,date_format(time,'%h:%i %p') as timed from tblcitizen"); while($data=mysql_fetch_array($news)) { $number=$data['number']; $link=str_replace(" ","-",$number); $fname=$data['fname']; $lname=$data['lname']; $offenses=$data['offenses']; $officer=$data['officer']; $violation=$data['violation']; $date=$data['date']; $time=$data['time']; – Daryl Feb 17 '14 at 02:32
print " $lname,$fname $number $offenses $officer $violation $date $time "; } ?> – Daryl Feb 17 '14 at 02:32
You left one of those `'` in there, right before ``date``. That's why he still had an error. – scenia Feb 18 '14 at 09:16

Error: You have an error in your SQL

1 Answers1