trouble with mysql insert statement

[Elmserv]

I was a foxpro AP for 12 years and you get very confident when writing the select or insert statements you have to be.

I can't find any consistancy with mysql 3.23.58


Lets take an example

INSERT INTO `sledgerdet`(cno, invno, row, unit, desc, unitprice)VALUES ($mast_cno, $invno, $invrow, $unit, $description, $unitprice)

Sometimes it needs quotes does it require ` or ' or "

Thanks in advance for your help


Richard

 

[vacunita]

It is recommended you use: [red]`[/red] For table names.

' or " can be used interchangeably for strings. and numbers don't need any.

Third it seems you are using PHP in your query in which case

' and " are not interchangeable when dealing with variables.

$myquery=[red]'[/red]INSERT INTO `sledgerdet`(cno, invno, row, unit, desc, unitprice)VALUES ($mast_cno, $invno, $invrow, $unit, $description, $unitprice)[red]'[/red];

This will not work as you expected because variables in PHP arent automatically interpeted inside single quotes. They need to be inside double quotes. however you still need to single or double quote the values if the are strings, so this is where it becomes complex.

$description is a string and as such requires some form of quoting.

So what you would from the PHP stand point would be:

$myquery=[red]"[/red]INSERT INTO `sledgerdet`(cno, invno, row, unit, desc, unitprice)VALUES ($mast_cno, $invno, $invrow, $unit,[blue]'[/blue][red]"[/red] . $description . [red]"[/red][blue]'[/blue], $unitprice)[red]"[/red];

This would produce a query similar to :
INSERT INTO `sledgerdet`(cno, invno, row, unit, desc, unitprice)VALUES (1, 5, 3, 8, [blue]'[/blue]Some description[blue]'[/blue], 5)

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Elmserv]

Yes it is PHP a lot of people in the PHP forum have helped me.

I am afraid to say that it doesn't work.

If it doesn't work I have a die(mysql_error(mysql));

The error is:-

You have an error in your SQL syntax near 'desc, unitprice)VALUES (3, 25, 1, 10,' gggggggggggggg', 10)'

I have removed the column "desc" altogether and it works, I have checked that desc is in the table, it is spelt correctly & it is a text field.

I have another field in the table called description that might be relevant

 

[vacunita]

Why don't you echo out your query, and check that its well formed. Once all variables have been replaced by their values.

So if you have something like:

$myqry="INSERT INTO...";
echo $myqry;



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Elmserv]

INSERT INTO `sledgerdet`(cno, invno, row, unit, desc, unitprice)VALUES (3, 25, 1, 10,' dffcffghhjjkkjuhkuu', 10)

 

[Elmserv]

So that you can see the whole picture

while ($x <= $mast_page)
		{
			if ($_SESSION['units'.$x] * $_SESSION['price'.$x] != 0)
				{
					$unit = $_SESSION['units'.$x];
					$unitprice = $_SESSION['price'.$x];
					$desc = $_SESSION['desc'.$x];
								
					
					$query="INSERT INTO `sledgerdet`(cno, invno, row, unit, desc, unitprice)VALUES ($mast_cno, $invno, $invrow, $unit,'".$desc."', $unitprice)";
				
					$res2 = mysql_query($query,$mysql);
			
					if (mysql_errno() >0)
						{print($query.'<br/>');
						die(mysql_error($mysql).'L69');}	
			
					$totalprice+= $unitprice * $unit;
					$invrow++ ;
				}
			$x++ ;
			
		}

 

[johnwm]

The word desc is a reserved word in MySQL - if you want to use it as a field name you should delimit it with backticks. See documentation at

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[Elmserv]

Thank you, I didn't even know that a list of reserved words existed. Now I have a link.


Its quite satisfying when you get to the bottom of this sort of problem.

Richard

PS it does work