insert into table using array

[nel]

hi..
i want to know how to insert data using array in mysql where the data stored in field given? this field_name i taken from database....
thanks a lot..... ;)

[zq29]

Sorry, I didn't understand any of that. Could you maybe give us some more information?

[Chetan]

U want to select data from 1 table and insert it into the other?
first select the data
and use foreach to keep executing the query using mysql_query

[nel]

it's not true...i don't want to insert into other table but use this table to determine which table and field i want to insert


i hope u will understand..

[Chetan]

Ok understood, quick thing
us a where clause
eg: I cant understand wat u say so eg
retreive id from table 1
update where id=wat got from table 1

[nel]

i'm not understand yet...
so can u give me example coding in insert this data?

[kenrbnsn]

There are two ways of doing what you want to do, assuming I understand your question.

First way:

If your array consist of the field names as indices and the data as data, like:
[code]
<?php
$data = array('field1' => 'data1', 'field2'=>'data2', 'field3'=>'data3');
?>[/code]
then you can use a temporary array and the alternate format for the insert function:
[code]<?php
$tmp = array();
foreach($data as $fld=>$val)
  $tmp[] = $fld . " = '" . mysql_real_escape_string($val) . "'";
$query = "insert into yourtable set " . implode(', ',$tmp);
echo $query;
?>[/code]
The second method is similar, except that the field names are in a separate array:
[code]<?php
$fields = array('field1','field2','field3');
$data = array($data1,$data2,$data3);
$tmp = array();
for ($i=0;$i<count($data);$i++)
  $tmp[] = $fields[$i] . " = '" . mysql_real_escape_string($data[$i]) . "'";
$query = "insert into yourtable set " . implode(', ',$tmp);
echo $query;
?>[/code]

Ken

[nel]

thanks....

[B]i'm 50% understand..[/B]

now, the problem is i take the data by request from previous page by using array(for) and  how i can determine which field i can insert in selected table.....can u give me others sample code...

thanks...

[tomfmason]

ok this may also help.

[code]
<?php
$array= array('field1' => 'data1', 'field2' => 'data2');
$data= serialize($array);
$sql= "INSERT INTO sometable (data_column) VALUES ('{$data}')";
?>[/code]

Also, I didn't realy understand what you meant by Your question. Can you post your code so that I may better understand what you mean or explain it better.

[nel]

<?php
$array= array('field1' => 'data1', 'field2' => 'data2');
$data= serialize($array);
$sql= "INSERT INTO sometable (data_column) VALUES ('{$data}')";
?>

i not undersatand yet how to determine "(data_column)" in this query;
and how can i determine when have NULL value?

[Chetan]

You are saying there is information in one table about X and there is another table with info for X so you want to do something to those specific X column.

Well thats all I understand so, srry I cant help if I dun understand...
~.~

[nel]

$fields = array('field1','field2','field3');

$data = array($data1,$data2,$data3);


so how i can call this $field and $data because i use array (for) and field(field1, field2,...) i get from database (table 1) while $data1,$data2,.. , i get from post function.


* i don't know how to use array in one query for example field1, field2  !
thanks....

[Chetan]

I think it was olved but still have it

[code]
<?php
// MySQL
$conn=mysql_connect('localhost', 'root', '');
$db   =mysql_select_database('database');
$table='table';

// Declare feilds and data
$fields = array('field1','field2','field3');
$data = array($data1,$data2,$data3);

// The script
$num = count($field);

for($i=0; $i<$num; $i++)
{
$exec = '$sql = \'INSERT INTO '. $table.'($field['.$i.']) values($data['.$i.']);';

// For debugging, you may uncomment this code
// echo($exec);

eval($exec);
mysql_query($sql);
}
?>
[/code]

This script is actually commplex but still Ill tell you what you have to do
in
$conn set your host, user and pass
$db set your database
$table set the table

for data and feilds you can use a script but make sure you you set $feilds to the feilds array and $data to the data

The for() is not that difficult but I prefer you dont touch it. You can put it anywhere in your script but make sure $table is set and $feilds and $data arrys are set.

I hope this is what you want

[nel]

[move] now i have 75% understanding...[/move]
this is for my knowledge. so i want to know :
1. why i must declare field and data in array()
2. what is the eval() function?
3. what is the code when i have more than one table, so how i can determine what is the field for this table. it because i want to insert the data in field that i had determined.

thanks a lot..... :D

[Chetan]

1. Uh because, if its not an array you would need to think of the names again and again which is not quite impossible.

2. (think about php.net) eval() function takes some php code and executes it. for egample you have some Php code in mysql db you can retreive it and execute it using eval()

3. So you want to put this stuff in not only 1 table but 2, ok.
Use this code

[code]
<?php
// MySQL
$conn=mysql_connect('localhost', 'root', '');
$db  =mysql_select_database('database');
$table=array('table1', 'table2', 'table3');

// Declare feilds and data
$fields = array('field1','field2','field3');
$data = array($data1,$data2,$data3);

// The script
$num2= count($table);
$num = count($field);

for($u=0; $u<$num2; $u++)
{
for($i=0; $i<$num; $i++)
{
$exec = '$sql = \'INSERT INTO $table['.$u.']($field['.$i.']) values($data['.$i.']);';

// For debugging, you may uncomment this code
// echo($exec);

eval($exec);
mysql_query($sql);
}
}
?>
[/code]

In this case we are repeating the code by counting the number of tables. The names of tables are in $table as array

[Chetan]

Lemme check

[Chetan]

[code]
<?php
// MySQL
$conn=mysql_connect('localhost', 'root', '');
$db   =mysql_select_database('database');
$table=array('table1', 'table2', 'table3');

// Declare feilds and data
$fields = array('field1','field2','field3');
$data = array($data1,$data2,$data3);

// The script
$num2= count($table);
$num = count($field);

for($u=0; $u<$num2; $u++)
{
for($i=0; $i<$num; $i++)
{
$exec = '$sql = \'INSERT INTO $table['.$u.']($fields['.$i.']) values($data['.$i.']);';

// For debugging, you may uncomment this code
// echo($exec);

eval($exec);
mysql_query($sql);
}
}
?>
[/code]

In case this dosent work uncomment the commented debugging line

[nel]

actually i want to insert data for example :

"insert in $table_name1 f$ield_name1, $field_name2, $field_name3 values $data1, $data2, $data3";

all this variable i get from select statement for example table & field
and i get from $_Request for example data.

all this variable, i also use array(for statement).

2. $fields = array($field1,$field2,$field3);
$data = array($data1,$data2,$data3);

how i can use array(for statement) in this array ?

can u give me sample code..

[Chetan]

Well i dont understand any of that.
What i understand is that you want to use for inside an array, put the code in a variable and eval() it

[nel]

this is sample inserting that i want to make :

"insert into table_name ($field[i], $field[i+1], $field[i+2]) values $data[i], $data[i+1];"

so can u give me sample code to in this example.

2. in the array(*,*,*)
* use for, for example : array(data[i], $data[i+1])

3. can you give me sample code, what is yout understand

thanks...

[Chetan]

Sorry for big delay, ill just do what you want

[Chetan]

The for loops
change em

[code]
for($u=0; $u<=$num2; $u++)
{
$flds='';
$dat='';
for($i=0; $i<=$num; $i++)
{
$flds.='$fields['.$i'.]';
$dat.='$data['.$i.']';
}
$exec = '$sql = \'INSERT INTO $table['.$u.']($flds) values($dat]);';

// For debugging, you may uncomment this code
// echo($exec);

eval($exec);
mysql_query($sql);
}
[/code]

[nel]

thanks foe the code..
but this code make m ucclear for example :

$flds.='$fields['.$i'.]';
when i make echo"$flds"; the output contain all of the field and not have comma(,) between field.

$dat.='$data['.$i.']';
when i make echo"$dat"; the output contain all of the data and not have comma(,) between data.

this all cause the data cannot be inserted.

please give me another code...thanks

[kenrbnsn]

The easiest way to do this is with the implode() function. See http://www.php.net/implode
[code]<?php
$query = "insert into table_name (" . implode(',',$fields) . ") values '" . implode("','",$data) . "'";
?>[/code]

Ken

[Chetan]

Uh sorry, Another way
[code]
for($u=0; $u<=$num2; $u++)
{
$flds='';
$dat='';
for($i=0; $i<=$num; $i++)
{
$flds.='$fields['.$i'.],';
$dat.='$data['.$i.'],';
}
$flds=substr($flds, 0, -1);
$dat=substr($dat, 0, -1);
$exec = '$sql = \'INSERT INTO $table['.$u.']($flds) values($dat]);';

// For debugging, you may uncomment this code
// echo($exec);

eval($exec);
mysql_query($sql);
}
[/code]

This should do it