[RESOLVED] MySQL 1064 error on Insert into DB

[dmacman1962]

Hi All,

[EDIT] I resolved the 1064 error, but have a new issue, see my 2nd post below[/EDIT]

I am a little stumped here.

I have a form that I am trying to insert the values into my db, and I keep getting an error...

1064
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 '2006-03-31', '9:34 AM')' at line 1

I searched the forums and none of the resolved messages applied to this??

Here is my process code to insert into the db..

//Login DB
$conn = mysql_connect("x", "x", "x") 
  OR DIE (mysql_error());
@mysql_select_db ("inthecla_Misc", $conn) OR DIE (mysql_error());

// Select the DB
$db_selected=mysql_select_db("inthecla_Misc");
if(!$db_selected){
die(mysql_error());
} 

//Get data from form and date - time
$DOCUMENT_ROOT = $_SERVER['DOCUMENT_ROOT'];
$QRatingID = ($_POST['QRatingID']);
$Date = date('Y\-m\-d');
$Time = date('g\:i A', time() + 10800); 

//Loop thru the answers and make them an array to insert into the db
foreach ($_POST['N'] as $n) { //Set the elements in the array
	$Name0 = $n[0];
	$Name1 = $n[1];
	$Name2 = $n[2];
	$Name3 = $n[3];
	$Name4 = $n[4];
	$Name5 = $n[5];
	$Name6 = $n[6];
	$Name7 = $n[7];
	$Name8 = $n[8];
	$Name9 = $n[9];
	$Name10 = $n[10];
	$Name11 = $n[11];
	$Name12 = $n[12];
	$Name13 = $n[13];
	$Name14 = $n[14];
	$Name15 = $n[15];
	$Name16 = $n[16];
	$Name17 = $n[17];
	$Name18 = $n[18];
	$Name19 = $n[19];
	$Name20 = $n[20];
	$Name21 = $n[21];
	$Name22 = $n[22];
	$Name23 = $n[23];
	$Name24 = $n[24];
	$Name25 = $n[25];
	$Name26 = $n[26];
	$Name27 = $n[27];
	$Name28 = $n[28];
	$Name29 = $n[29];
	$Name30 = $n[30];
	$Name31 = $n[31];
	$Name32 = $n[32];
	$Name33 = $n[33];
	$Name34 = $n[34];
	$Name35 = $n[35];
	$Name36 = $n[36];
	$Name37 = $n[37];
	$Name38 = $n[38];
	$Name39 = $n[39];
	$Name40 = $n[40];
	$Name41 = $n[41];
	$Name42 = $n[42];
	$Name43 = $n[43];
	$Name44 = $n[44];
	$Name45 = $n[45];
}

//Insert data into db
$sql ="insert into QRatingVotes";
$sql .="(QRatingID, Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10, Name11, Name12, Name13, Name14, Name15, Name16, Name17, Name18, Name19, Name20, Name21, Name22, Name23, Name24, Name25, Name26, Name27, Name28, Name29, Name30, Name31, Name32, Name33, Name34, Name35, Name36, Name37, Name38, Name39, Name40, Name41, Name42, Name43, Name44, Date, Time)";
$sql .="values('$QRatingID', '$Name1', '$Name2', '$Name3', '$Name4', '$Name5', '$Name6', '$Name7', '$Name8', '$Name9', '$Name10', '$Name11', '$Name12', '$Name13', '$Name14', '$Name15', '$Name16', '$Name17', '$Name18', '$Name19, '$Name20', '$Name21', '$Name22', '$Name23', '$Name24', '$Name25', '$Name26', '$Name27', '$Name28', '$Name29', '$Name30', '$Name31', '$Name32', '$Name33', '$Name34', '$Name35', '$Name36', '$Name37', '$Name38', '$Name39', '$Name40', '$Name41', '$Name42', '$Name43', '$Name44', '$Date', '$Time')";
if(!mysql_query($sql)){
    echo mysql_errno().'<br>'.mysql_error().'<br><br>';
}

I don't see the issue. I am doing exactly what I did with my last form and this code is nearly identical, except for fieldnames and variable names.

Thanks in advance,

Don

[dmacman1962]

I resolved the 1064 error,.

The 1064 error was i the insert values (missing ')...

'$Name19,

But, after this processed (semmingly, OK) I checked the DB and it did not insert any values intot he data base for the $Names ??

The QRatingID and Date - Time inserted OK, so I have a flaw in my array and how I am inserting it.

Anyone see where I messed up?

Thanks,

Don

[Oersoep]

First of all: Show the query.
If you see the query, you'll probably see what mysql doesn't swallow immediately.

Second, try this:

foreach ($_POST['N'] as $n) { //Set the elements in the array
    for( $c = 0; $c <= 45; $c++ ){
        ${'Name'.$c} = $n[$c];
    }
}

Third: normalise your database.

That much identical fields in one table is not done. As soon as you try the normal way to do things you'll understand why.

Make a table like this:
name:QRating
fields: QRatingID
(primary key: QRatingID)

And a table like this:
name: QRatingVotes
fields: VoteID, QRatingID, Name
(primary key: VoteID)

Use the field QRatingID in table QRatingVotes to save which QRating the Vote belongs to.

[dmacman1962]

Hi Oersoep,

Thanks for the help.

I used your loop, works great (Saved me LOT of time!)

also, here is the values of the array, QRatingID, Date and Time...

2006-03-31

10:04 AM

The QRating ID is : 1

Array
(
[0] => 1
[1] => 2
[2] => 3
[3] => 4
[4] => 5
[5] => 4
[6] => 3
[7] => 2
[8] => 1
[9] => 2
[10] => 3
[11] => 4
[12] => 5
[13] => 4
[14] => 3
[15] => 2
[16] => 1
[17] => 2
[18] => 3
[19] => 4
[20] => 5
[21] => 4
[22] => 3
[23] => 2
[24] => 1
[25] => 2
[26] => 3
[27] => 4
[28] => 5
[29] => 4
[30] => 3
[31] => 2
[32] => 1
[33] => 2
[34] => 3
[35] => 4
[36] => 5
[37] => 4
[38] => 3
[39] => 2
[40] => 1
[41] => 2
[42] => 3
[43] => 4
[44] => 5
)

And I am trying to follow your normization suggestions.

Your suggestions seem logical, but let me add this.

The form is a QRating (basically a popularity poll) and in the end, I need to take all the votes for each person, and tally them as such)

Example:

Persons Name |my favorite|Like a lot|Like a little|Fair|
Adam Sandler | 1 | 2 | 3 | 4 |

Then I need to total all the 'my favorite' and total votes for that person.

Example: Adam Sandler got a 28 in 'my favorite' [ 28 people voted column 'my favorite' (28 x 1) and there was 100 total votes. His QRating would be 28% (28/100).

So my logic was to keep all votes in a table, with a record per person, so I can do the math.

Wouldn't your normalization disrupt this??

Thanks,

Don