Insert array values into MySQL

[sanjeevcis]

Hi,
Below code is regarding to insert an array values into mysql db using php.
i dont know why the code is not inserting array values into database properly.
If is there any thing wrong with the code, Please let me know what is that!.
Here $final is an array containg the values of "ID,Name,Ph one" one by one.
array will be like $final=array(id 1,name1,ph1,id2 ,name2,ph2..... ........)

[PHP]
for($i=0;$i<cou nt($final);$i++ )
{
if($i%3==0)
{
$query="insert into cane (ID) values('$final[$i]')";
$v=$final[$i];
}
elseif($i%3==1)
$query="update cane set Name='$final[$i]' where ID='$v'";
else
$query="update cane set phone='$final[$i]' where ID='$v'";

$result=mysql_q uery($query)or die("Query failed : " . mysql_error());
}
[/PHP]

Sanjeev

[ak1dnar]

I entered those array values to my table successfully.Se e the changes and alter it against to your table.

[PHP]<?
include 'dbcon.php';

$final=array(id 1,name1,ph1,id2 ,name2,ph2,id3, name3,ph3);

for($i=0;$i<cou nt($final);$i++ )
{
if($i%3==0)
{
$query='insert into products (p_id2) values("'.$fina l[$i].'")';
$v=$final[$i];
}
elseif($i%3==1)
$query='update products set p_name="'.$fina l[$i].'" where p_id2="'.$v.'"' ;
else
$query='update products set p_image="'.$fin al[$i].'" where p_id2="'.$v.'"' ;

$result=mysql_q uery($query)or die("Query failed : " . mysql_error());
}
?>[/PHP]

[Atli]

Hi,

Please follow this site's Posting Guidelines when posting in this forum

That said, the problem might be that the fields 'Name' and 'Phone' do not have a default value, therefore you can not insert into the table without giving the values.

Also, you are manually inserting into a 'ID' field, which I'm assuming is the table's primary key? In which case you can not insert the same value twice in a table.

Besides that, your code is querying the database for each field in each row. This is not a good way to insert data into a database.
A better way would be to insert all your data into a single query.

Consider this alternetive method to your code.
[PHP]
// Set up the array
$final = array(1, "Atli", "5555555", 2, "John", "5885522", 3, "Doe", "6669999");

// Connect to the database
$DB = mysql_connect(" localhost", "<user>", "<pw>");
mysql_select_db ("myDB", $DB);

// Create the qyery string
$query = "INSERT INTO cane(ID, Name, Phone) VALUES";

// Loop through the array
for($i = 0, $c = count($final); $i < $c; $i += 3) {
// Add the next batch of values to the query string
$query .= "({$final[$i]}, '{$final[$i + 1]}', '{$final[$i + 2]}')";

// Add a comma is this is not the last batch
if($i + 3 < $c) {
$query .= ", ";
}
}

// Execute the query
$RESULT = mysql_query($qu ery, $DB);

// Check the results
if(!!$RESULT) {
echo "Query was successfull";
}
else {
echo "Query failed
<blockquote>" . $query ."</blockquote>
<blockquote>" . mysql_error($DB ) ."</blockquote>";
}
[/PHP]

This method creates a query string with all the data and executes only the one query on the database.
This is how the query would look like:

INSERT INTO table(ID, Name, Phone) VALUES
(1, "Atli", "5555555"),
(2, "John", "5885522"),
(3, "Doe", "6669999")

I hope this helps.

[sanjeevcis]

Hi,

Please follow this site's Posting Guidelines when posting in this forum

That said, the problem might be that the fields 'Name' and 'Phone' do not have a default value, therefore you can not insert into the table without giving the values.

Also, you are manually inserting into a 'ID' field, which I'm assuming is the table's primary key? In which case you can not insert the same value twice in a table.

Besides that, your code is querying the database for each field in each row. This is not a good way to insert data into a database.
A better way would be to insert all your data into a single query.

Consider this alternetive method to your code.
[PHP]
// Set up the array
$final = array(1, "Atli", "5555555", 2, "John", "5885522", 3, "Doe", "6669999");

// Connect to the database
$DB = mysql_connect(" localhost", "<user>", "<pw>");
mysql_select_db ("myDB", $DB);

// Create the qyery string
$query = "INSERT INTO cane(ID, Name, Phone) VALUES";

// Loop through the array
for($i = 0, $c = count($final); $i < $c; $i += 3) {
// Add the next batch of values to the query string
$query .= "({$final[$i]}, '{$final[$i + 1]}', '{$final[$i + 2]}')";

// Add a comma is this is not the last batch
if($i + 3 < $c) {
$query .= ", ";
}
}

// Execute the query
$RESULT = mysql_query($qu ery, $DB);

// Check the results
if(!!$RESULT) {
echo "Query was successfull";
}
else {
echo "Query failed
<blockquote>" . $query ."</blockquote>
<blockquote>" . mysql_error($DB ) ."</blockquote>";
}
[/PHP]

This method creates a query string with all the data and executes only the one query on the database.
This is how the query would look like:

INSERT INTO table(ID, Name, Phone) VALUES
(1, "Atli", "5555555"),
(2, "John", "5885522"),
(3, "Doe", "6669999")

I hope this helps.

Hi,Thanks for ur code ,,Really it is very helpful.All was good,but,Your code is not working when i build an array by splitting a string.n It is working when we define the array in code itself.....
Sanjeev

[ak1dnar]

Hi,
Below code is regarding to insert an array values into mysql db using php.
i dont know why the code is not inserting array values into database properly.
If is there any thing wrong with the code, Please let me know what is that!.
Here $final is an array containg the values of "ID,Name,Ph one" one by one.
array will be like $final=array(id 1,name1,ph1,id2 ,name2,ph2..... ..... ...)

Please refer to the last line of your Original post, you didn't mention about splitting the array.

[Atli]

Hi,Thanks for ur code ,,Really it is very helpful.All was good,but,Your code is not working when i build an array by splitting a string.n It is working when we define the array in code itself.....
Sanjeev

Then there must be a problem with the code you use to split your string. If you post the code thats giving you trouble and any error messages we can try to help you spot the problem.

[Atli]

I have edited the theads title, to better descripe the topic of this thread.
Guidlines on how to correctly name you threads can be found in the Posting Guidlines

MODERATOR

[spky]

Thanx a lot Atli & Sanjeevcis your codes were very helpful.
thanks again.