Welcome to WebmasterWorld Guest from 54.196.232.162

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL update from array

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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.

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


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']

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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.
2:57 pm on Oct 17, 2007 (gmt 0)

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


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

can you paste what my checkamabob output

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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.

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


why are there 2 values for each item?
3:29 pm on Oct 17, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


because there are two rows in the table...?
3:38 pm on Oct 17, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:Mar 9, 2006
posts: 75
votes: 0


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.

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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.

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


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 ;)

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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.

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


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

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


output looked like this...

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

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


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 ;)

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


Hmm... what was the change? Looks muchos similaros to me!
1:51 pm on Oct 18, 2007 (gmt 0)

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


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);
}

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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.

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


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

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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...
4:09 pm on Oct 18, 2007 (gmt 0)

Preferred Member

5+ Year Member

joined:July 31, 2006
posts:629
votes: 0


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"
4:12 pm on Oct 18, 2007 (gmt 0)

Administrator

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

joined:July 31, 2003
posts:12533
votes: 0


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.
5:47 pm on Oct 18, 2007 (gmt 0)

Senior Member

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

joined:Dec 9, 2003
posts:3416
votes: 0


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 />';
}
}
?>
8:43 am on Oct 19, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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')

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


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

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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!

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


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

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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.

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

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


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 ());

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


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.