[Resolved] trouble inserting data into table

smapdi

I have a large form that will be filled out and I need that data entered into a mysql database. To keep things managable and let php do all the work I'm taking the defined POST variables and splitting the arrays into their keys and values setting a variable for each. Then I'm trying to use those variables in a loop, running a query inserting each value into its proper field. This is what I'm using.

$arr = get_defined_vars();
//print_r($arr['POST']);
$red = array_keys($arr['POST']);
$blue = array_values($arr['POST']);
//print_r (array_keys($arr['POST']));
for ($i = 5; $i < 31; $i ++)
{
mysql_query ("insert into table1 ($red[$i]) values ('$blue[$i]')");
echo $insert;
}
mysql_close();

The sections that are commented out are for my own test. So is the echo $insert; The results of the echo insert look good. I get all the sentences with variables and their correct values inserted but the database gets populated with all null values. I've tried so many thing and have been looking at this for so long I can't see what's missing.
Thanks in advance for the help.

mattvenables

i don't see off hand whats wrong, but try making the following change just to see what will happen:

change:

mysql_query ("insert into table1 ($red[$i]) values ('$blue[$i]')");

to:

mysql_query ("insert into table1 (" . $red[$i] . ") values ('" . $blue[$i] . "')");

smapdi

no, that didn't work either
still getting null values

mattvenables

i see in your debugging that you printed the variable: array_keys($arr['POST']), but did you try printing array_values($arr['POST']) ?

smapdi

yes, I did. I just tried it again to double check myself. The two arrays match up. ??

herve

You can't use variable inside strings that way.

For instance you can use :

$cmd="SELECT * FROM mytable WHERE id='$id'";

but you can't use :

$cmd="SELECT * FROM mytable WHERE id='$tab[$id]'";

what you have to do is a concatenation like this :

$cmd="SELECT * FROM mytable WHERE id='".$tab[$id]."'";

So for your code :

mysql_query ("insert into table1 ($red[$i]) values ('$blue[$i]')");

will become :

$cmd = "insert into table1 (".$red[$i])." values ('".$blue[$i]."')"

mysql_query ($cmd);

If it still doesn't work, check the value of the $cmd variable , with a

print $cmd

just before executing the query, to check that you have good values inside your array.

tekky

try this...

$query = "insert into table1 (" . $red[$i] . ") values ('" . $blue[$i] . "')";
echo $query;
mysql_query ($query) OR die('error: '. mysql_error()); //update this so you can see the error, otherwise your just chasing shadows

I bet your query statement isnt coming out correctly

nemonoman

but you can't use : use :
$cmd="SELECT * FROM mytable WHERE id='$tab[$id]'";

You sure can; I do this all the time...if $id is a number, not a string, it works fine.

smapdi

It actually worked once with the suggestions above but that was at work while I was publishing to a web server to test pages. At home I use IIS on my box so I can test locally and it's not working there.

I've seen the use of . to concatenate but I don't understand it in the examples above. If the explanation isn't to long would someone enlighten me.

And it still frustrates me that when I print the query loop as a string I get the exact syntax that I want for the query but it always dies on execution. Very frustrating. Thank you everyone for the help.

nemonoman

To answer your concat question:

here are 3 variables:

$var1=1;
$array[1]=1;
$array['id']=1;

This works:

mysql_query("SELECT from table where id=$var1");
mysql_query("SELECT from table where id=$array[1]");

This fails and throws a PHP error;
mysql_query("SELECT * from table where id=$array['id']");

For array elements identified by string constants, you must concatenate:

mysql_query("SELECT * from table where id=".$array['id']);

tekky

cant you enclose arrays in {} inside a string to avoid concatenation also?

mysql_query("SELECT * FROM {$tables['mytable']}");

I never use it but I recall that being an option...

smapdi

It finally worked. But what I didn't think of before hand is that as it loops every entry goes into its own row. Not good.
So I had to alter things to insert the first row and loop with an update query. This was also a hassle because I couldn't update a row with a string unless it was quoted. It was updating twice with integers and then it would fail.

For those interested, this is what I'm using. Thanks again all.

$arr = get_defined_vars();
$keys = array_keys($arr['POST']);
$values = array_values($arr['POST']);
$u_id = $_GET['u'];
echo $u_id;

//enters the entry info database
mysql_query ("INSERT INTO entry ($keys[0]) VALUES ('$values[0]')");
$e_id = mysql_insert_id();
$_SESSION['e_id'] = $e_id;
mysql_query ("UPDATE entry SET user_id = $u_id WHERE entry_id = '$e_id'");
for ($i = 1; $i < 5; $i ++)
{
$heading = "UPDATE entry SET $keys[$i] = '$values[$i]' WHERE entry_id = '$e_id'";
mysql_query ($heading) or die();
}

//enters the remaining form results into database
mysql_query ("INSERT INTO maintenance (entry_id) VALUES ('$e_id')");
for ($i = 5; $i < 13; $i ++)
{
$cmd = "UPDATE maintenance SET $keys[$i] = '$values[$i]' WHERE entry_id = '$e_id'";
mysql_query ($cmd) or die("There was an error during processing");
}
mysql_close();

}