insert values syntax error:

[rawbgnr]

i'm getting a check syntax for mysql version with the following code. it seems to be located in the insert into table statement:

<?php  

//htmlentities() replaces all HTML charcters, like <, >, etc. with their code names (ampersands and numbers).
//strip_tags() gets rid of any HTML and PHP tags that have been entered.
//addslashes() adds an escaping slash before and quotes entered in a form. Otherwise, the user could use an SQL injection on your table, which is bad.
if($_POST){
	$space = addslashes(strip_tags(htmlentities($_POST['space'])));  

	$space2 = addslashes(strip_tags(htmlentities($_POST['space2'])));  

	$music = addslashes(strip_tags(htmlentities($_POST['music'])));  

	$music2 = addslashes(strip_tags(htmlentities($_POST['music2'])));  

	$slichot = addslashes(strip_tags(htmlentities($_POST['slichot'])));  

	$rh = addslashes(strip_tags(htmlentities($_POST['rh'])));  

	$tash = addslashes(strip_tags(htmlentities($_POST['tash'])));  

	$yomk = addslashes(strip_tags(htmlentities($_POST['yomk'])));  

	$succot = addslashes(strip_tags(htmlentities($_POST['succot'])));  

	$simhat = addslashes(strip_tags(htmlentities($_POST['simhat'])));  

	$hhbest = addslashes(strip_tags(htmlentities($_POST['hhbest'])));  

	$least = addslashes(strip_tags(htmlentities($_POST['least'])));  

	$attend = addslashes(strip_tags(htmlentities($_POST['attend'])));  

	$shabbatattend = addslashes(strip_tags(htmlentities($_POST['shabbatattend'])));  

	$membership = addslashes(strip_tags(htmlentities($_POST['membership'])));  

	$username = ''; 
	$password = ''; 
	$database = ''; 
	mysql_connect('server.com', $username, $password) or die(mysql_error()); 
	@mysql_select_db($database) or die('Unable to select database'); 
	$query = "INSERT INTO `space`('id',`space`,`space2`,`music`,`music2`,`slichot`,`rh`,`tash`,`yomk`,`succot`,`simhat`,`hhbest`,`least`,`attend`,`shabbatattend`,`membership`) 
		  VALUES('','{$space}','{$space2}','{$music}''{$music2}','{$slichot}','{$rh}','{$tash}','{$yomk}','{$succot}','{$simhat}','{$hhbest}','{$least}','{$attend}',' {$shabbatattend}','{$membership}')";
	mysql_query($query) or die(mysql_error()); 
	echo '<p align="center">Your responses have been added to our Database...Thank You.</p>'; 
}

the id column in mysql is primary and auto-increment. i also have a entryDateTime column that should be set by server.
if someone could help me i'd be very grateful. only been using php for two weeks so be gentle .. i'm set up for php 5 and mysql 5.0.27 could switch to php4 and mysql 4.0.27 if that might work.

[bpat1434]

Well, rather than using stripslashes or addslashes, try using [man]mysql_real_escape_string/man 😉

I can see the error from here, and it's not a version 😉 You just made a minor "flub" when typing this all out.

'{$music}''{$music2}'

I'm guessing those are meant to be 2 separate fields, not one field with two strings 😉 You need a comma in between them for this to work.

Also, next time you post an error like this, it's extremely more helpful if you actually post the error, not just the error type. Typically you can get better, or faster replies 😉

[rawbgnr]

bpat1434 wrote:

Well, rather than using stripslashes or addslashes, try using [man]mysql_real_escape_string/man 😉

I can see the error from here, and it's not a version 😉 You just made a minor "flub" when typing this all out.

'{$music}''{$music2}'

I'm guessing those are meant to be 2 separate fields, not one field with two strings 😉 You need a comma in between them for this to work.

Also, next time you post an error like this, it's extremely more helpful if you actually post the error, not just the error type. Typically you can get better, or faster replies 😉

thanks. i corrected typo and still getting the following trunkated error message:
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 ''id',space,space2,music,music2,slichot,rh,tash,`yo
looked up what u said about mysql real_escape_string .. thanks again.

[bpat1434]

'id'
Try changing the ' to a back-tick: `

[rawbgnr]

thanks bpat.. but do u mean the whole line?😕 i changed the whole line to back ticks and got a blank screen .. so i guess that's not what u meant. so do i just change id and nothing else?

[bpat1434]

No, column names should be in back-ticks (so all the words in between the first set of parenthesis). The second set of parenthesis should be variables (or whatever) encapsulated in single-quotes (unless they're integers, in which case they don't need quotes).

Now, before we go any further, before you run that query, echo it out on your screen and post it here.

[rawbgnr]

bpat1434 wrote:

No, column names should be in back-ticks (so all the words in between the first set of parenthesis). The second set of parenthesis should be variables (or whatever) encapsulated in single-quotes (unless they're integers, in which case they don't need quotes).

Now, before we go any further, before you run that query, echo it out on your screen and post it here.

so how do i do an echo😕

[bpat1434]

    $query = "INSERT INTO `space`('id',`space`,`space2`,`music`,`music2`,`slichot`,`rh`,`tash`,`yomk`,`succot`,`simhat`,`hhbest`,`least`,`attend`,`shabbatattend`,`membership`)
          VALUES('','{$space}','{$space2}','{$music}''{$music2}','{$slichot}','{$rh}','{$tash}','{$yomk}','{$succot}','{$simhat}','{$hhbest}','{$least}','{$attend}',' {$shabbatattend}','{$membership}')";

echo $query;
    // mysql_query($query) or die(mysql_error()); 

Just put the word echo on a new line, and the variable $query after it. Then comment out the mysql_query() call.... like above (obviously using the proper query).

[rawbgnr]

bpat1434 wrote:

    $query = "INSERT INTO `space`('id',`space`,`space2`,`music`,`music2`,`slichot`,`rh`,`tash`,`yomk`,`succot`,`simhat`,`hhbest`,`least`,`attend`,`shabbatattend`,`membership`)
          VALUES('','{$space}','{$space2}','{$music}''{$music2}','{$slichot}','{$rh}','{$tash}','{$yomk}','{$succot}','{$simhat}','{$hhbest}','{$least}','{$attend}',' {$shabbatattend}','{$membership}')";

echo $query;
    // mysql_query($query) or die(mysql_error()); 

Just put the word echo on a new line, and the variable $query after it. Then comment out the mysql_query() call.... like above (obviously using the proper query).

well it seemed to work after a lot of searching and learning to get proper query
here's what it printed out ..
worshipspace(id,'space','space2','music','music2','slichot','rh','tash','yomk','succot','simhat','hhbest','least','attend','shabbatattend','membership') VALUES('','1','i liked the chairs for sleeping','1','the singing woke me up','2','4','1','5','3','6','the noise kept me up','the whole thing','1','2','3')

Your responses have been added to our Database...Thank You.
here's the code i used:

echo $query; 
"('','{$space}','{$space2}','{$music}','{$music2}','{$slichot}','{$rh}','{$tash}','{$yomk}','{$succot}','{$simhat}','{$hhbest}','{$least}','{$attend}','{$shabbatattend}','{$membership}')";

now what?

[bpat1434]

Well, the next step is to capture the result of the mysql_query() call:

$result = mysql_query($query) or die(mysql_error());

Then check the result, make sure it inserted...

if(mysql_affected_rows() >= 1)
     echo '<p align="center">Your responses have been added to our Database...Thank You.</p>';
else
    echo '<p align="center">There was an error inserting your responses...Sorry.</p>';

[rawbgnr]

bpat1434 wrote:

Well, the next step is to capture the result of the mysql_query() call:

$result = mysql_query($query) or die(mysql_error());

Then check the result, make sure it inserted...

if(mysql_affected_rows() >= 1)
     echo '<p align="center">Your responses have been added to our Database...Thank You.</p>';
else
    echo '<p align="center">There was an error inserting your responses...Sorry.</p>';

well, i think, and i'm not sure but it seems you've and i have narrowed the problem down.
here's the error:
There was an error inserting your responses...Sorry.
its the id or the time stamp field. right?

[chasiv]

CODE VALUES('','1','i liked the chairs for sleeping','1','the singing woke me up','2','4','1','5','3','6','the noise kept me up','the whole thing','1','2','3')[/CODE]

It could also be that you're putting numbers inside single quotes. If the datatypes are numeric datatypes, they will not accept the '1' value. So, if space is a numeric datatype in your database - - '1' is an invalid value.

[bpat1434]

if the id field is an auto-increment field, you can drop that from the insert statement all-together since it is auto-generated.

I don't see a time-stamp field. And if it's defined as "NOT NULL" in the table, then you need to include it in the INSERT statement. I don't see a column named timestamp, so I can't say why that would be interfering.

[rawbgnr]

still getting error:
There was an error inserting your responses...Sorry.
dropped id from insert and values:
added $TimeDate (that's the type of field) and added EntryDateTime to values:

$query = "INSERT INTO `worshipspace`('space','space2','music','music2','slichot','rh','tash','yomk','succot','simhat','hhbest','least','attend','shabbatattend','membership','DateTime') VALUES('{$space}','{$space2}','{$music}','{$music2}','{$slichot}','{$rh}','{$tash}','{$yomk}','{$succot}','{$simhat}','{$hhbest}','{$least}','{$attend}','{$shabbatattend}','{$membership}','{$EntryDateTime}')";

so i'm getting frustrated. what next .. do i have the field and post variable confused?

[bradgrafelman]

Your problem is with the backticks and quotes still.

1. Table and column names don't need any delimiters at all unless you used a reserved name/word. In this case, MySQL's method of delimiting column/table names is by using backticks ( ` ). If you want to enclose your table names and column names with anything, it must be a backtick.

2. Values, unless numeric, should be quoted with single quotes ( ' ). They should also be escape with [man]mysql_real_escape_string/man (not [man]stripslashes/man) if they were user-supplied.

[rawbgnr]

i hate to be dumb but i guess i am. i'm not sure what line/s are supposed to have back ticks? i changed the values to back ticks and that's not correct .. still get there is an error. message so do i use back ticks also for insert into
which are my fields i think they're the $post space and the other $space are what in the array?
so

$space = ($_POST['space2']   

which is what

i guess i was in over my head when i started this. but i'm doing this as a volunteer.

[bpat1434]

Your query should be:

INSERT INTO `worshipspace` (`space`, `space2`, `music`, `music2`, `slichot`, `rh`, `tash`, `yomk`, `succot`, `simhat`, `hhbest`, `least`, `attend`, `shabbatattend`, `membership`, `DateTime`)
VALUES ({$space}, '{$space2}', {$music}, '{$music2}', {$slichot}, {$rh}, {$tash}, {$yomk}, {$succot}, {$simhat}, '{$hhbest}', '{$least}', '{$attend}', {$shabbatattend}, {$membership}, {$EntryDateTime})

Notice the use of back-ticks for column names (i.e. the set of names between the first set of parenthesis), and the use of single-quotes for values. Also note that the values that translated to numbers (space, music, etc.) are without quotes because they're numeric.

[rawbgnr]

are the values numeric even though they're defined as type varchar? or because the value in the html field is a number? if i can get that straight i'll have learned something.
thanks for all your help.

[bpat1434]

If the database type is INT then you want can insert numbers without quotes (like above); however, if it's varchar, text, or whatever, you might be better off using quotes, but it's optional.

[rawbgnr]

well, i'm worse off. now i'm not even getting the echo .. sorry there was an error inserting your responses:
i think that i may have confused you all. the column name's i have are listed in the post on the right side of the following code:

$space = addslashes(strip_tags(htmlentities($_POST['space'])));  

$space2 = addslashes(strip_tags(htmlentities($_POST['space2'])));  

$music = addslashes(strip_tags(htmlentities($_POST['music'])));  

$music2 = addslashes(strip_tags(htmlentities($_POST['music2'])));  

$slichot = addslashes(strip_tags(htmlentities($_POST['slichot'])));  

$rh = addslashes(strip_tags(htmlentities($_POST['rh'])));  

$tash = addslashes(strip_tags(htmlentities($_POST['tash'])));  

$yomk = addslashes(strip_tags(htmlentities($_POST['yomk'])));  

$succot = addslashes(strip_tags(htmlentities($_POST['succot'])));  

$simhat = addslashes(strip_tags(htmlentities($_POST['simhat'])));  

$hhbest = addslashes(strip_tags(htmlentities($_POST['hhbest'])));  

$least = addslashes(strip_tags(htmlentities($_POST['least'])));  

$attend = addslashes(strip_tags(htmlentities($_POST['attend'])));  

$shabbatattend = addslashes(strip_tags(htmlentities($_POST['shabbatattend'])));  

$membership = addslashes(strip_tags(htmlentities($_POST['membership'])));  

$DateTime= addslashes(strip_tags(htmlentities($_POST['EntryDateTime'])));
 

so EntryDateTime is my column heading in mysql db.:
Date time is the type.
my html form has option values ranging from 1-6 for most questions however they're for drop down boxes.
in mysql db four columns are strings and type is text: space2; music2; hhbest; and least. the rest are strings of a varchar type.
i will get to adding security later once i get the data to be entered into mysql db.