...

View Full Version : Inserting arrays into table using php



Jenny Dithe
11-12-2010, 05:52 AM
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.

dniwebdesign
11-12-2010, 06:12 AM
$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. :)

Jenny Dithe
11-12-2010, 07:06 AM
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.

poyzn
11-12-2010, 07:19 AM
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);

Jenny Dithe
11-12-2010, 09:04 AM
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.

poyzn
11-12-2010, 09:17 AM
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)

dniwebdesign
11-12-2010, 09:51 AM
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.

Jenny Dithe
11-12-2010, 10:04 AM
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

Jenny Dithe
11-12-2010, 10:08 AM
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

poyzn
11-12-2010, 10:12 AM
Sorry I am tired which is why some silly mistakes are being made.

My code again using the PHP ...

and post echo $sql result

Jenny Dithe
11-12-2010, 11:02 AM
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

poyzn
11-12-2010, 11:13 AM
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

Jenny Dithe
11-12-2010, 03:13 PM
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.

dniwebdesign
11-12-2010, 07:32 PM
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?

Jenny Dithe
11-13-2010, 07:21 AM
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.

poyzn
11-13-2010, 07:23 AM
do you have autoincremented field in your base?

Jenny Dithe
11-13-2010, 08:53 AM
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

poyzn
11-13-2010, 09:14 AM
ok, what if you try inserting with defined IDs

Jenny Dithe
11-13-2010, 11:03 AM
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.

poyzn
11-13-2010, 11:40 AM
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);
}
}

Jenny Dithe
11-13-2010, 12:42 PM
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.

poyzn
11-13-2010, 01:27 PM
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

Jenny Dithe
11-14-2010, 06:08 AM
Works. Thank you so much. The if($names[$i]) was the key.

Thank you.

Jenny Dithe
11-14-2010, 06:08 AM
And thank you for time you put into this for helping me to get it.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum