Strange difference between what PHP tells me is a error and my code Insert into Mysql Table?

Question

Hello I am doing some simple inserting into a table from my <php> and it doesn't work let me start off with the code:

<?php
include_once "connect.php";
$db_host="localhost";
$user_name="root";
$pass="";
$db_name="knight orders";
$con = mysql_connect("$db_host","$user_name","$pass") or die("There is a problem with the connection");
mysql_select_db("$db_name",$con) or die("There is a problem with the database");
$name="Default";
$rank=3;
//$name=$_POST['name'];
//$rank=$_POST['rank'];
$table_name="ordertemp";
$query="INSERT INTO '$table_name' ('Code','Name')VALUES ('$rank','$name')";
mysql_query($query,$con) or die("Problems!" . mysql_error());
mysql_close($con);
?>

I'm working with some default values now but I will be reading from a form later, the strange thing is when I check out the mysql_error() result in Firefox it tells me:

Problems!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 ''ordertemp' ('Code','Name')VALUES ('3','Default')' at line 1

It's changing some of the semicolons, could this be what causes my error, or if you've seen other faults please point them out.

Sidenote: It's not PHP, that tells you, that there is an error, it's the mysql-server. — KingCrunch, Jan 19 '12 at 11:34
Thank you and dully noted I was in a bit of a hurry tipping this out. — George Bora, Jan 19 '12 at 11:46

Mark Baker · Accepted Answer · 2012-01-19T11:33:43.360

4

'$table_name' should not be in quotes: it's a table name, not a string column value,

use backticks if you have to, but they aren't necessary

edited Jan 19 '12 at 11:33

answered Jan 19 '12 at 11:32

Mark Baker

209,507
32
346
385

Only $table_name or all the others? – George Bora Jan 19 '12 at 11:34
The column names 'Code' and 'Name' shouldn't be in quotes either, they're column names... you can use backticks ` if you need to for table/column names, but it's only necessary if your table/column names contain special characters such as spaces, or are MySQL reserved words. '$rank' and '$name' are string values, so they do need to be quoted – Mark Baker Jan 19 '12 at 11:36
thank you this has worked I was so afraid of messing up the SQL synthax I copied exactly how phpmyadmin did it. – George Bora Jan 19 '12 at 11:39
@GeorgeBora phpmyadmin uses backticks, indeed – Damien Pirsy Jan 19 '12 at 11:44

score 3 · Answer 2 · answered Jan 19 '12 at 11:35

3

In MySQL quotes (') are used to denote a string literal.

Backticks (`) are used to denote MySQL 'objects' such as database names, table names and column names.

answered Jan 19 '12 at 11:35

Matt

9,068
12
64
84

score 2 · Answer 3 · edited May 23 '17 at 10:24

2

Don't use quotation marks for table and column names. You can use backticks "`" instead or just leave the quotes out:

$query = "INSERT INTO `{$table_name}` (`Code`, `Name`) VALUES ('{$rank}', '{$name}')";

PS: Never ever insert an unsafe string variable like $name=$_POST['name']; directly into your SQL statements. This makes your application vulnerable against SQL injections. See here for more information: How can I prevent SQL injection in PHP?

edited May 23 '17 at 10:24

Community

1
1

answered Jan 19 '12 at 11:34

DerVO

3,679
1
23
27

Using mysqli or pdo prepared statements would be even better – Mark Baker Jan 19 '12 at 11:48

Strange difference between what PHP tells me is a error and my code Insert into Mysql Table?

3 Answers3