homepage Welcome to WebmasterWorld Guest from 54.237.213.31
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
MySQL update from array
Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 1:40 pm on Oct 17, 2007 (gmt 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.

 

jatar_k

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



 
Msg#: 3479761 posted 2:11 pm on Oct 17, 2007 (gmt 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']

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 2:48 pm on Oct 17, 2007 (gmt 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.

jatar_k

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



 
Msg#: 3479761 posted 2:57 pm on Oct 17, 2007 (gmt 0)

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

can you paste what my checkamabob output

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 3:03 pm on Oct 17, 2007 (gmt 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.

jatar_k

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



 
Msg#: 3479761 posted 3:27 pm on Oct 17, 2007 (gmt 0)

why are there 2 values for each item?

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 3:29 pm on Oct 17, 2007 (gmt 0)

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

mjwalshe

5+ Year Member



 
Msg#: 3479761 posted 3:38 pm on Oct 17, 2007 (gmt 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.

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 3:42 pm on Oct 17, 2007 (gmt 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.

jatar_k

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



 
Msg#: 3479761 posted 3:43 pm on Oct 17, 2007 (gmt 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 ;)

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 3:47 pm on Oct 17, 2007 (gmt 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.

jatar_k

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



 
Msg#: 3479761 posted 4:03 pm on Oct 17, 2007 (gmt 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

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 4:08 pm on Oct 17, 2007 (gmt 0)

output looked like this...

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

jatar_k

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



 
Msg#: 3479761 posted 5:16 pm on Oct 17, 2007 (gmt 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 ;)

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 8:28 am on Oct 18, 2007 (gmt 0)

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

jatar_k

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



 
Msg#: 3479761 posted 1:51 pm on Oct 18, 2007 (gmt 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);
}

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 3:00 pm on Oct 18, 2007 (gmt 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.

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 3:07 pm on Oct 18, 2007 (gmt 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

jatar_k

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



 
Msg#: 3479761 posted 3:08 pm on Oct 18, 2007 (gmt 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

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 3:19 pm on Oct 18, 2007 (gmt 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...

joelgreen

5+ Year Member



 
Msg#: 3479761 posted 4:09 pm on Oct 18, 2007 (gmt 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"

coopster

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



 
Msg#: 3479761 posted 4:12 pm on Oct 18, 2007 (gmt 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.

whoisgregg

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



 
Msg#: 3479761 posted 5:47 pm on Oct 18, 2007 (gmt 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 />';
}
}
?>

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 8:43 am on Oct 19, 2007 (gmt 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')

jatar_k

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



 
Msg#: 3479761 posted 12:23 pm on Oct 19, 2007 (gmt 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

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 1:33 pm on Oct 19, 2007 (gmt 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!

jatar_k

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



 
Msg#: 3479761 posted 1:49 pm on Oct 19, 2007 (gmt 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

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 2:18 pm on Oct 19, 2007 (gmt 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.

Robert Poole

5+ Year Member



 
Msg#: 3479761 posted 2:19 pm on Oct 19, 2007 (gmt 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 ());

jatar_k

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



 
Msg#: 3479761 posted 2:24 pm on Oct 19, 2007 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved