Welcome to WebmasterWorld Guest from 54.227.1.130

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL update from array

     

Robert Poole

1:40 pm on Oct 17, 2007 (gmt 0)

5+ Year Member



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

2:11 pm on Oct 17, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

2:48 pm on Oct 17, 2007 (gmt 0)

5+ Year Member



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

2:57 pm on Oct 17, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

can you paste what my checkamabob output

Robert Poole

3:03 pm on Oct 17, 2007 (gmt 0)

5+ Year Member



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

3:27 pm on Oct 17, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



why are there 2 values for each item?

Robert Poole

3:29 pm on Oct 17, 2007 (gmt 0)

5+ Year Member



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

mjwalshe

3:38 pm on Oct 17, 2007 (gmt 0)

5+ Year Member



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

3:42 pm on Oct 17, 2007 (gmt 0)

5+ Year Member



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

3:43 pm on Oct 17, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

3:47 pm on Oct 17, 2007 (gmt 0)

5+ Year Member



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

4:03 pm on Oct 17, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

4:08 pm on Oct 17, 2007 (gmt 0)

5+ Year Member



output looked like this...

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

jatar_k

5:16 pm on Oct 17, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

8:28 am on Oct 18, 2007 (gmt 0)

5+ Year Member



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

jatar_k

1:51 pm on Oct 18, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

3:00 pm on Oct 18, 2007 (gmt 0)

5+ Year Member



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

3:07 pm on Oct 18, 2007 (gmt 0)

5+ Year Member



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

3:08 pm on Oct 18, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

3:19 pm on Oct 18, 2007 (gmt 0)

5+ Year Member



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

4:09 pm on Oct 18, 2007 (gmt 0)

5+ Year Member



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

4:12 pm on Oct 18, 2007 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

5:47 pm on Oct 18, 2007 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

8:43 am on Oct 19, 2007 (gmt 0)

5+ Year Member



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

12:23 pm on Oct 19, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

1:33 pm on Oct 19, 2007 (gmt 0)

5+ Year Member



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

1:49 pm on Oct 19, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

2:18 pm on Oct 19, 2007 (gmt 0)

5+ Year Member



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

2:19 pm on Oct 19, 2007 (gmt 0)

5+ Year Member



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

2:24 pm on Oct 19, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month