Inserting arrays into table using php

Hi,

I have an array which I want to explode , insert each value into the table as a new row, but also insert into another column the string.

My syntax is as follows


$reap=$_POST['details'];
$steep=str_replace(";"," ",$reap);

$to=$_POST['details'];
$names = explode("; ",$to);
foreach ($names as $Invitee){
$sql="INSERT INTO event (invitee, to)
VALUES
({$Invitee},{$steep})";
}


I have some other fields, but I have removed and added all fields one by one to work out that inserting into "to" is causing a fatal syntax error and that invitee is inserting as 0. The value of $_POST['details'] is 2;4; (this is for my test it can go up and down and include any number of values when operating for real) when I echo out $names[0] and $names[1] I get 2 and 4 respectively.

Needless to say despite there being two values so I want this inserted twice once for 2 and once for 4 there is only one insertion.

I have tried inserting as '{$invitee}' and "{$invitee}" but whatever I try I get 0.

In the table invitee and to are both set to INT, which is why I tried the replace to get rid of the semi colons incase that was a problem (though I would prefer to keep them).

The table names are correct, I double checked.

I have another variable which is a word which I can quite happily insert using '{$var}'

I have run out of things to try.

$reap=$_POST['details']; //Say value is 2;4
$steep=str_replace(";"," ",$reap);

$names = explode("; ",$reap);
for($i=0;$i<count($names);$i++) {
$sql="INSERT INTO event (invitee, to)
VALUES
('".$names[$i]."','$steep')";
}

Little different, but if I understand, this should do what you want... and if not, I'll give it another shot. :)

Thank you for helping.

Unfortunately I am still getting a syntax error for $steep and if I remove that the insert is still entering only once and putting the invitee value as 0.

I tried echoing out $names[0] and $names[1] and that printed 2 4 so I am baffled as to why it isn't inserting.

print out $sql in the loop and post it here

for($i=0;$i<count($names);$i++) {
$sql="INSERT INTO event (invitee, to)
VALUES
('".$names[$i]."','$steep')";
echo $sql . '<br />';
}

and where is that part of a the code where you are inserting data to db? If you put data to the db not in the loop, you should do that before inserting:

for($i=0;$i<count($names);$i++) {
$sql[]="('".$names[$i]."','$steep')";
}
$sql = "INSERT INTO event (invitee, to) VALUES " . implode(', ', $sql);

Ok my full syntax goes:

if($_POST['view']=='1'){
$view='public';
} else {
$view='private';}

$reap=$_POST['details'];
$steep=str_replace(";"," ",$reap);

$names = explode("; ",$reap);
echo $names[0];
echo $names[1] . "<br />";
for($i=0;$i<count($names);$i++){
$sql="INSERT INTO event (host, invitee, to, state, date, view)
VALUES
('{$_SESSION['me']}','" . $names[$i] . "','$steep','pending',NOW(),'{$view}')";
echo $sql . '<br />';
}

And my printed $sql is:

2 4 //this is my echo
INSERT INTO event (host, invitee, to, state, date, view) VALUES ('1','',' 2 4 ','pending',NOW(),'private')


And I get the error message:

Error: 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 'to, state, date, view) VALUES ('1','',' at line 1


I don't have anything separately inserted into the table, but for each name I want a new row to be inserted.

TO is reserved mysql word, should be `to`, either as date -> `date`
mysql reserved words (http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html)

poyzn is right... "to" is a reserved MySQL word and need to be quotes using `... I usually find it best to do that to all of my column & table titles.

INSERT INTO `event` (`host`, `invitee`, `to`, `state`, `date`, `view`)...

And use the [ php ] tags when quoting php. ;-) Makes it easier to read, for myself at least.

Stupid mistake on my part, thank you for pointing it out.

I still have the problem though that the invitee/$names will not insert at all

Sorry I am tired which is why some silly mistakes are being made.

My code again using the PHP

if($_POST['view']=='1'){
$view='public';
} else {
$view='private';}

$reap=$_POST['details'];
$steep=str_replace(";"," ",$reap);

$names = explode("; ",$reap);
echo $names[0];
echo $names[1] . "<br />";
for($i=0;$i<count($names);$i++){
$sql="INSERT INTO `event` (`host`, `invitee`, `to`,` state`,` date`,` view`)
VALUES
('{$_SESSION['me']}','" . $names[$i] . "','$steep','pending',NOW(),'{$view}')";
echo $sql . '<br />';
}


I changed to to tom

Sorry I am tired which is why some silly mistakes are being made.

My code again using the PHP ...

and post echo $sql result

The print $sql says:

2 4
INSERT INTO event (host, invitee, tom, state, date, view) VALUES ('1',' 2',' 2; 4; ','pending',NOW(),'private')
INSERT INTO event (host, invitee, tom, state, date, view) VALUES ('1',' 4',' 2; 4; ','pending',NOW(),'private')


But what is entering my table is

1,0,2,pending, 12.11.2010 12.00,private

And this is only being entered once

what type of columns are invitee and tom? may by the type of the invitee column is INT and you're trying to put there a string

Tom was an int (I changed that to varchar and it now works) Invitee I changed to varchar and it came up blank.

So it is still not inserting into invitee and there is only one insertion which is 0 instead of two where one is 2 and one is 4.

I cannot really see any reason as to why the insert is blank for the column invitee... how many characters do you have the invitee varchar set to?

255.

But I think I know why, I just don't know what is causing it.

The echo I am getting is

INSERT INTO privateevent (host, invitee, tom, state, date, view) VALUES ('1',' 2',' 2; 4; ','pending',NOW(),'private')
INSERT INTO privateevent (host, invitee, tom, state, date, view) VALUES ('1',' 4',' 2; 4; ','pending',NOW(),'private')
INSERT INTO privateevent (host, invitee, tom, state, date, view) VALUES ('1','',' 2; 4; ','pending',NOW(),'private')


So I think the third submission is overwriting the first two. However I have no idea where the third submission is coming from. This is my code:

<?php

$con = mysql_connect("localhost","user","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mydb", $con);

function check_input($value){

if (get_magic_quotes_gpc()){
$value=stripslashes($value);
}

if (!is_numeric($value)){
$value = mysql_real_escape_string($value);
}
return $value;
}

foreach ($_POST as $key=>$value){
$_POST[$key]= check_input($value);
}

$reap=$_POST['details'];
$steep=str_replace(";"," ",$reap);

$names = explode("; ",$reap);
for($i=0;$i<count($names);$i++){
$sql="INSERT INTO privateevent (host, invitee, tom, statae, datesent, view)
VALUES
('{$_SESSION['Me']}','" . $names[$i] . "','{$_POST['details']}','pending',NOW(),'{$view}')";
echo $sql . '<br />';
}


?>


Sorry for only bringing this up now I thought it was simply generated by my

$result=mysql_query($sql) or die('Error: ' . mysql_error());

But I removed that today to check, and realised it was an actual error in the script. I tried the foreach as well but I still get three responses.

do you have autoincremented field in your base?

I have one autoincrement field which is the table ID field separate to what I am uploading.

So on the table it goes: ID, host, invitee, etc

ok, what if you try inserting with defined IDs

This is what the var dump says I don't know if that helps, it looks like a third string is being created but out of what I don't know.


string(269) "INSERT INTO event (host, invitee, tom, state, date, view) VALUES ('1',' 2',' 2; 4; ','pending',NOW(),'')"

string(269) "INSERT INTO event (host, invitee, tom, state, date, view) VALUES ('1',' 4',' 2; 4; ','pending',NOW(),'')"

string(267) "INSERT INTO event (host, invitee, tom, state, date, view) VALUES ('1','',' 2; 4; ','pending',NOW(),'')"


Poyzn, just seen your suggestion, will try that now.

and make a verification of the elements in for-loop before defining the sql variable:

for($i=0; ...) {
if($names[$i]) {
$sql = ...
mysql_query($sql);
}
}

Ok, by adding your code, a row inserts with the value 2, but not a row with value 4.

I am using firefox, I don't know if this makes a difference.

This is what the var dump says I don't know if that helps, it looks like a third string is being created but out of what I don't know.


string(269) "INSERT INTO event (host, invitee, tom, state, date, view) VALUES ('1',' 2',' 2; 4; ','pending',NOW(),'')"

string(269) "INSERT INTO event (host, invitee, tom, state, date, view) VALUES ('1',' 4',' 2; 4; ','pending',NOW(),'')"

string(267) "INSERT INTO event (host, invitee, tom, state, date, view) VALUES ('1','',' 2; 4; ','pending',NOW(),'')"


Poyzn, just seen your suggestion, will try that now.

do you backquote the date fileds?

INSERT INTO event (host, invitee, tom, state, `date`, view) VALUES ('1',' 2',' 2; 4; ','pending',NOW(),'')


lets start again.
post here your code with db connection and insertion

Works. Thank you so much. The if($names[$i]) was the key.

Thank you.

And thank you for time you put into this for helping me to get it.