MySQL update from array

[Robert Poole]

Okay, another day another dollar. Here's my problem...

I generate a form using a while statement like so...

$rs = mysql_query ("SELECT * FROM table");
while ($row = mysql_fetch_array($rs))
{
echo "<tr>
<input type='hidden' name='id[]' value='".$row['id']."'>
<td><input type='text' name='field1[]' value='".$row['field1']."'></td>
<td><input type='text' name='field2[]' value='".$row['field2']."'></td>... etc etc

And then there is one submit button at the bottom which needs to update the whole table.

The closest I've come so far is with this:

$id_req = $_REQUEST['id'];
if (!empty($id_req))
{
foreach($id_req as $id_rows)
{
$query = ("UPDATE allinfo SET
field1 = '".$_POST['field1']."', etc etc

But that just enters "Array" into all of the fields. I've tried a variation of things but can't get it to work. Any help would be appreciated.

[jatar_k]

the best way to start is to take a look at what your script is actually getting from the form and the format

echo '<pre>';
print_r($_POST);
echo '</pre>';

that will show you the whole post array

also, don't use $_REQUEST, stick to specifying the proper method, you should be able to get the same thing from $_POST['id']

[Robert Poole]

I used that little checkamabob and it appears that the arrays are correct, I just need to know what the proper method is for running the mysql statement with these values.

[jatar_k]

if it is inputing 'Array' then you aren't accessing the elements properly

can you paste what my checkamabob output

[Robert Poole]

Otay, it outputs something like...

Array
(
[id] => Array
(
[0] => 2
[1] => 1
)

[field1] => Array
(
[0] => hello
[1] => my
)

[field2] => Array
(
[0] => there
[1] => name
)

[field3] => Array
(
[0] => mr
[1] => is
)

[field4] => Array
(
[0] => world
[1] => rob
)
...

[Update] => Update
)

So this is all looks hunky dory to me.

[jatar_k]

why are there 2 values for each item?

[Robert Poole]

because there are two rows in the table...?

[mjwalshe]

your trying to use the entire array as a value from what i can see btw "select *" is the work of the devil and should be avoided :-)

$rs = mysql_query ("SELECT foo as name, bar as value FROM table");
while ($row = mysql_fetch_array($rs))
{
echo "<tr>
<input type='hidden' name='.$row['name'].' value='".$row['value']."'>
...

checking what that sql is returning using a SQL devenvironment would help to in case your mysql database has problems.

[Robert Poole]

so how do I not use the entire array as a value?

all I'm really looking for is what to put in the insert statement and how to use that in conjunction with the loop.

[jatar_k]

ok, but then you have to build your update differently

you aren't referencing the elements correctly

field1 will contain an element from each row, as will all of the following elements

so do you want to issue an update query for each individual row? That way you would, in this case, be sending 2 update queries because there are 2 rows.

I want to be sure before I write any code ;)

[Robert Poole]

that is correct j. i guess it's going to have as many update statements as there are rows in the table, how ever many that will be.

i guess it could get a bit shiddy as this table will probably end up 100+ rows, but there you go.

[jatar_k]

my suggestion for the future is to output a list with links beside like

example.com/editrow.php?id=98798798

and then load the data for only that specific row and do a single update but we can get this working now.

maybe see if this works, it will just output the constructed queries so we can look at them

$outer = 0; 
$query = ''; 
while (isset($_POST['id'][$outer])) { 
 $query = "UPDATE allinfo SET "; 
 $inner = 1; 
 while (isset($_POST['field' . $inner])) { 
 $query .= "field" . $inner . " = '" . $_POST['field' . $inner] . "',"; 
 $inner++; 
 } 
$query = substr($query,0,strlen($query)-1); 
$query .= ' where id=' . $_POST['id'][$outer]; 
$outer++; 
echo '<br>',$query; 
//mysql_query($query); 
}

that's off the top of my head so just post what the output is, not sure if I got it all right

[Robert Poole]

output looked like this...

UPDATE allinfo SET where id=2
UPDATE allinfo SET where id=1

[jatar_k]

hmm, made a change, try this

$outer = 0; 
$query = ''; 
while (isset($_POST['id'][$outer])) { 
 $query = "UPDATE allinfo SET "; 
 $inner = 1; 
 while (isset($_POST['field' . $inner])) { 
 $query .= "field" . $inner . " = '" . $_POST['field' . $inner][$outer] . "',"; 
 $inner++; 
 } 
$query = substr($query,0,strlen($query)-1); 
$query .= ' where id=' . $_POST['id'][$outer]; 
$outer++; 
echo '<br>',$query; 
//mysql_query($query); 
}

and check your sticky ;)

[Robert Poole]

Hmm... what was the change? Looks muchos similaros to me!

[jatar_k]

it was similar but the internal loop was wrong the first time

now that I have seen the actual field names try this one

$thefields = array('orderdate', 'address', 'client', 'processdate', 'deaused', 'searchcoused', 'dateepcreceived', 'dateepcgraphsent', 'datelrreceived', 'datelandreceived', 'datedrainagereceived', 'datepackassembled', 'turnaround', 'clientnotified', 'solicitornotified', 'number', 'description', 'notes');

$outer = 0;
$query = '';
while (isset($_POST['id'][$outer])) {
$query = "UPDATE allinfo SET ";
$inner = 1;
while (isset($_POST[$thefields[$inner]])) {
$query .= $thefields[$inner] . " = '" . $_POST[$thefields[$inner]][$outer] . "',";
$inner++;
}
$query = substr($query,0,strlen($query)-1);
$query .= ' where id=' . $_POST['id'][$outer];
$outer++;
echo '<br>',$query;
//mysql_query($query);
}

[Robert Poole]

it appears that it's still not getting in the inner loop, as it still results in...

UPDATE allinfo SET where id=3
UPDATE allinfo SET where id=2
UPDATE allinfo SET where id=1

The closest I've come so far is with this...

$outer = 0;
$query = '';

while (isset($_POST['id'][$outer]))
{
$query = "UPDATE allinfo SET
orderdate = '".$_POST['orderdate'][$outer]."',
address = '".$_POST['address'][$outer]."',
client = '".$_POST['client'][$outer]."', ...

and I thought that was working but it turned out however many columns you updated (say two) it would apply the changes to entries with id's 1 and 2, even if you applied the changes to say rows 8 and 13.

[Robert Poole]

Okay, no my bad, I was missing a line.

I've tried your new version and the SQL and at first glance it looks like it's outputting the right stuff but then I ran an error check on it and tried to execute it and got this:

UPDATE allinfo SET address = 'Auriol (NR99 9RA)', client = 'Company Name', processdate = '', deaused = '', searchcoused = '', dateepcreceived = '', dateepcgraphsent = '', datelrreceived = '', datelandreceived = '', datedrainagereceived = '', datepackassembled = '', turnaround = '', clientnotified = 'on', solicitornotified = 'on', number = '', description = '', notes = '', where id='123')An error has ocured: 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 'where id='123')' at line 1:1064

[jatar_k]

hmmm, what I posted worked for me, I will take another look but have to head out in a few

if anyone else has an idea just jump on in

[Robert Poole]

Hmm, a couple of tweaks and now I've got it to execute okay, and apparently all of the textboxes are okay. The only problem now seems to be with the checkboxes...

[joelgreen]

Checkbox value is not passed to the server if checkbox not marked. Whn marked it will pass "on" or another value specified as value="blah-blah"

[coopster]

You can use isset() [php.net] to determine whether or not a checkbox value was submitted via your form by checking for it's index in your $_POST superglobal array.

[whoisgregg]

Glad to see you got it working. I use the bracket field name notation all the time. Just in case you wanted to see a different way of going about it, some code follows. :D

<?php
$theFields = array('orderdate', 'address', 'client', 'processdate', 'deaused', 'searchcoused', 'dateepcreceived', 'dateepcgraphsent', 'datelrreceived', 'datelandreceived', 'datedrainagereceived', 'datepackassembled', 'turnaround', 'clientnotified', 'solicitornotified', 'number', 'description', 'notes');
if(isset($_POST[$theFields[0]]) && is_array($_POST[$theFields[0]])){
$totalRows = count($_POST[$theFields[0]]);
for($i=0; $i<$totalRows; $i++){
$insertFields = array(); // clear the array
foreach($theFields as $key){
if(isset($_POST[$key][$i])){
$insertFields[] = "`".$key."`='".mysql_real_escape_string($_POST[$key][$i])."'";
}
}
echo "UPDATE `allinfo` SET ".implode(', ', $insertFields)." WHERE `id`='".$_POST['id'][$i]."' LIMIT 1;".'<br />';
}
}
?>

[Robert Poole]

So my first problem is going to be how to do this...

"You can use isset() to determine whether or not a checkbox value was submitted via your form by checking for it's index in your $_POST superglobal array. "

I guess I need to pick out the two that are checkboxes and assign them blank values if they are not set, and then use this value in the mysql statement.

The second problem is that when you check some boxes they don't get applied to the right rows in the sql statement, so if I checked the checkboxes in say rows 8 and 10 then the sql statement will apply the value "on" to rows 1 and 2. I'm not sure what this is all about.

Just to confirm, this is where I'm at right now:

$thefields = array('orderdate', 'address', 'client', 'processdate', 'deaused', 'searchcoused', 'dateepcreceived', 'dateepcgraphsent', 'datelrreceived', 'datelandreceived', 'datedrainagereceived', 'datepackassembled', 'turnaround', 'clientnotified', 'solicitornotified', 'number', 'description', 'notes');

$outer = 0;
$query = '';
while (isset($_POST['id'][$outer]))
{
$query = "UPDATE allinfo SET ";
$inner = 1;
while (isset($_POST[$thefields[$inner]]))
{

$query .= $thefields[$inner] . " = '" . $_POST[$thefields[$inner]][$outer] . "', ";
$inner++;
}
$query = substr($query,0,strlen($query)-2);
$query .= " where id=" . $_POST['id'][$outer] . "";
$outer++;
echo '<br>',$query;
mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());

(The checkboxes are 'clientnotified' and 'solicitornotified')

[jatar_k]

you could test the checkboxes individually and take them out of the control array

you could put the tests between these lines

$query = "UPDATE allinfo SET ";
// right here
$inner = 1;

and concatenate their values into the query before the inner loop starts

[Robert Poole]

Okay jk I'll give that a try later. Unfortunately the servers down right now which is a bit shid when you're trying to test out your PHP! Do you know if it matters if the SQL elements are in order? Because I'm thinking now that I just tack these two checkbox elements on the end of the SQL statement, even though they're not the last two elements in the table.

Then after getting them to apply to the correct column in the table there will just be the matter of getting them to apply to the correct row in the table!

[jatar_k]

order doesn't matter

for the appropriate row, is the id in the update query wrong?
check it against the row in the html form and then check it against the $_POST array

[Robert Poole]

well i'm guessing it's a similar problem of that when the checkboxes aren't checked they don't go into the array, so even if you check boxes 5 and 9 then they still go into the array as 0 and 1 or 1 and 2 or whatever. I can't really tell why it's doing this... I'm not even reading the checkboxes into the array, or at least I don't think I am.

Take look and see what you think... This is where I'm at right now.

[Robert Poole]

Sorry, would have helped if I would have actually pressed paste before posting

$thefields = array('orderdate', 'address', 'client', 'processdate', 'deaused', 'searchcoused', 'dateepcreceived', 'dateepcgraphsent', 'datelrreceived', 'datelandreceived', 'datedrainagereceived', 'datepackassembled', 'turnaround', 'number', 'description', 'notes');

$outer = 0;
$query = '';
while (isset($_POST['id'][$outer]))
{
$query = "UPDATE allinfo SET ";
// Test to see if checkboxes are checked
if(isset($_POST['clientnotified'][$outer]))
{
$clientnotified = $_POST['clientnotified'][$outer];
}
else
{
$clientnotified = "";
}

if(isset($_POST['solicitornotified'][$outer]))
{
$solicitornotified = $_POST['solicitornotified'][$outer];
}
else
{
$solicitornotified = "";
}

$inner = 1;
while (isset($_POST[$thefields[$inner]]))
{

$query .= $thefields[$inner] . " = '" . $_POST[$thefields[$inner]][$outer] . "', ";
$inner++;
}
//$query = substr($query,0,strlen($query)-2);
$query .= "clientnotified = '".$clientnotified."', solicitornotified = '".$solicitornotified."' where id=" . $_POST['id'][$outer] . "";
$outer++;
echo '<br>',$query;
mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());

[jatar_k]

I wonder if the post array collapses, which makes those values appear in the wrong position, I am betting that if you have 4 records and only 2 have checkboxes checked then you only get 2 values in the POST array which blows the logic

try switching those checkboxes to yes/no dropdowns, this way they will always have a value sent and won't screw up the order of values.

then you could put them back in the control array and not worry about special cases.