...

View Full Version : multiple insert with one query problem, new here , need help



awong82
06-18-2010, 05:28 PM
Hello, I am currently having some issue on inserting in a database.
my idea is to have a couple of input boxes to allow user to enter data and then submit, where this data will be inserted into the corresponding table. some of the code i did is by referring to other person coding and understand how it works.

for example in my html
i have 3 input text has name tag with has array elements
<form action =" somescript.php">
<input type="text" name="id[]"/>
<input type="text" name="name[]"/>
<input type="text" name="date[]"/>
</form>

i then have a for loop when the condition is true
i call the sql insert statement by assigning to a variable
$sql = ' insert into (id, name, date) values ($id[$i],$name[$i],$date[$i])';
before that in order to check whether the insert works i did like
$sql = ' insert into id,name, date) values ("2","ben","1999-1-1"); and it works fine, but when assign to an array element it fails to insert data.

Any advise or is it my understanding is wrong ?

Thank you.

mlseim
06-18-2010, 09:45 PM
Show us your whole PHP script (as you have it now).
The script that processes the form.

xGIHavoc
06-19-2010, 09:40 AM
I suggest simply echoing what is in those arrays, it could be inserting an empty row (nothing) because it isn't retrieving any real values out of those keys.

awong82
06-19-2010, 06:34 PM
Here is the code mlseim,

There are two parts
the enter data in the input box will be insert into the table

// here is the testconnection.php
-----------------------------------
// here to let you know the table has 3 data field id, name and date
$sql = 'create TABLE if not exists test (id INT NOT NULL AUTO_INCREMENT primary key, name TEXT, mydate DATE NOT NULL)DEFAULT CHARACTER SET UTF8';
// check if create table works
if(!mysqli_query($link,$sql)){
$output = " cannot create table";
include 'output.html.php';
exit();
}
$output = "successfully created table";
include 'output.html.php';

// looping
for($i=0;$i<count($name);$i++){
if($name=="")
continue;
// i did a print out out the data to check the values as XGIHavoc says and
//they show the correct values i enter in the input box
echo "<br>";
echo $id[$i];
echo "<br>";
echo $name[$i];
echo "<br>";
echo $date[$i];
echo "<br>";
echo count($name);
echo "<br>";

// here is the statement where i insert the data in the the test table
$sql = 'insert into test(id, name, mydate) values($id[$i],$name[$i],$date[$i])';
// i did a testing with the below code to check whether my insert code is working and it works.
//$sql = 'insert into test(id, name, mydate) values("2","drew","2002-10-2")';

// check if insert works
if (!mysqli_query($link,$sql)){
$output = " cannot insert data table";
include 'output.html.php';
exit();
}

// here is the html part

<form action="testconnection.php">
<input type="text" name="id[]" />
<input type="text" name="name[]" />
<input type="text" name="date[]"/>
<input type="submit" value="enter"/>
</form>


}

//for some reason it keeps on goes to handle error and display the cannot insert data data output.

xGIHavoc
06-19-2010, 09:08 PM
I believe it is because of this line:

$sql = 'insert into test(id, name, mydate) values($id[$i],$name[$i],$date[$i])';
PHP does not process strings inside of single quotes, but it does inside of double quotes. Change occurrences of ' to ".

So,
$sql = "insert into test(id, name, mydate) values($id[$i],$name[$i],$date[$i])";

awong82
06-19-2010, 10:34 PM
message deleted

awong82
06-20-2010, 05:18 PM
i tried switching the double quote and the single quote but it is still the same, cannot insert data in the table

DJCMBear
06-20-2010, 06:11 PM
the reason its not working is because you have it checking $name when $name hasn't even been set.

awong82
06-20-2010, 06:16 PM
hello again, i manage to debug the code and found out that the problem was the insert statement, where instead of using

insert into tablename(field1, field2, field3) values (array[],array[],array[]);

it is suppose to be

insert into tablename(field1,field2,field3) values("'.array[].'","'.array[].'","'array[].'");

even so i m still trying to understand what does "'.'" means?

Thank you for your help again.

DJCMBear
06-20-2010, 06:49 PM
Why not just do this



$sql = mysql_query("INSERT INTO test(id,name,mydate) VALUES('{$id[$i]}','{$name[$i]}','{$date[$i]}')");


And the '..' is only used to pass php vars into a string when using single quotes around the string but if your using double quotes you dont need to do that.

for example.
print 'hello $name'; Wrong.
print 'hello '.$name; Right.

and with double quotes and array values.
print "hello $items['name']"; Wrong.
print "hello $items[name]"; Right.
or
print "hello {$items['name']}"; Right.

awong82
06-22-2010, 04:02 PM
thanks djmbear



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum