[Resolved] Multiple Updates to MySQL

[socont]

Greetings,

I've read what I could find on this subject but still am not getting it.

I have a four-field MySQL database. Field1 is text, the other fields are numeric.

I have a form that pulls all the data from the database into text boxes.

I'd like to be able to edit any or all of fields2,3,and 4 on the form (field1 is the primary key) and use a submit button to make all the updates at once.

Questions:

1. Is the output of the form (below) in the correct format for updating to a MySQL database?

2. Do I need field1 in the array since I'm not editing it? For some reason I suspect I do.

3. How on earth do I build a loop (foreach? while? or either?) that integrates an
   <?php mysql_query("UPDATE....." ?> query. I've tried but a bunch of different things but I either get parse errors (because I have no clue what I'm doing) or an unformated version of the array shown below (which I find odd?).

If somebody could point me in the right direction, I'd appreciate it.

#my form:

<?php

mysql_pconnect("localhost","user","password")
or die ("MySQL Connection Failure");

mysql_select_db("database")
or die ("Can't Connect to Database");

$result = mysql_query("SELECT field1, field2, field3, field4 from database")
or die ("Database Query Failed!");

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))

{

extract($row);

echo "<tr>
<td width=100 align=left>
<input type='text' name=field1[] maxlength='10 size='12' value='$field1'</td>\n
<td width=85 align=right>
<input type='text' name=field2[] maxlength='6' size='8' value='$field2'></td>\n
<td width=85 align=right>
<input type='text' name=field3[] maxlength='4' size='8' value='$field3'></td>\n
<td width=85 align=right>
<input type='text' name=field4[] maxlength='6' size='8' value='$field4'></td>\n
</tr>\n";

}

?>

#Which is outputing like this:

Array
(
[field1] => Array
(
[0] => text
[1] => text
[2] => text
[3] => text

    )

[field2] => Array
    (
        [0] => numeric
        [1] => numeric
        [2] => numeric
        [3] => numeric

    )

[field3] => Array
    (
        [0] => numeric
        [1] => numeric
        [2] => numeric
        [3] => numeric

    )

[field4] => Array
    (
        [0] => numeric
        [1] => numeric
        [2] => numeric
        [3] => numeric

    )

)

[dnast]

#my form:

<?php

mysql_pconnect("localhost","user","password")
	or die ("MySQL Connection Failure");

mysql_select_db("database")
	or die ("Can't Connect to Database");

$result = mysql_query("SELECT field1, field2, field3, field4 from database")
	or die ("Database Query Failed!");


while ($row = mysql_fetch_array($result, MYSQL_ASSOC))

{

extract($row);

echo "<tr>
<td width=100 align=left>
		<input type='text' name=field1[] maxlength='10 size='12' value='$field1'</td>\n		
<td width=85 align=right>
		<input type='text' name=field2[] maxlength='6' size='8' value='$field2'></td>\n
<td width=85 align=right>
		<input type='text' name=field3[] maxlength='4' size='8' value='$field3'></td>\n
<td width=85 align=right>
		<input type='text' name=field4[] maxlength='6' size='8' value='$field4'></td>\n
	</tr>\n";

}


?>

#Which is outputing like this:

Array
(
    [field1] => Array
        (
            [0] => text
            [1] => text
            [2] => text
            [3] => text

    )

[field2] => Array
    (
        [0] => numeric
        [1] => numeric
        [2] => numeric
        [3] => numeric

    )

[field3] => Array
    (
        [0] => numeric
        [1] => numeric
        [2] => numeric
        [3] => numeric

    )

[field4] => Array
    (
        [0] => numeric
        [1] => numeric
        [2] => numeric
        [3] => numeric

    )

)

^ I just did that so I could see it better.

1. Field1 should be invisible to the user.
2. Yes, Field1 would be used to determine which record to update where.
3. Please show the code that you were using to update the table. You were probably very close with just a parse error. I have no idea how the code you posted could print an array layout like that, however.

[socont]

I need Field1 visible for reference, but it doesn't have to be in a text box. Does it need to be in the array for comaprison?

The code below gives:

Parse error: parse error, unexpected $end in....

Which has been my most common parse error, I worked through the others. I think this means it can't figure out the comparison.

I am missing something fundamental. I don't understand how to do the comparison of field1 between the array and the db.
I'm not getting any connection errors.

I know this is way off base....be kind...

mysql_pconnect("localhost","user","password")
or die ("MySQL Connection Failure");

mysql_select_db("database")
or die ("Can't Connect to Forms Database");

$dbtype = mysql_query("SELECT field1 from database")
or die ("<br><br><h4>Paper List Query Failed!</h4>");

#this probably looks stupid, but hey, I was getting desperate

foreach ($_POST[type] as $dbtype):

{
mysql_query("UPDATE database SET field1='$POST[field1]',
field2='$POST[filed2]', field3='$_POST[field3]'")

or die ("<h2>Didn't work DOH!</h2>");
}

echo "<h3>Update complete</h3>";
echo "Number of items changed:", mysql_affected_rows();

?>
</body>
</html>

I apologize for the format of my posts. Another thing to figure out.

[socont]

I somehow missed my starting tag when I copied that. Sorry.

[dnast]

No need to apologize. 🙂 Let's see what's wrong.

You need quotes when refering to associative keys in an array i.e. $_POST['type'];

You have a colon ( : ) after your foreach statement which, combined with the curly brackets, is probably doing some weird things.

Plus I don't think that foreach statement will work in this situation.

Lastly I think you were typing too fast on the line
field2='$POST_[filed2]', :p

So instead of

foreach ($_POST[type] as $dbtype):

{
mysql_query("UPDATE database SET field1='$_POST[field1]',
field2='$POST_[filed2]', field3='$_POST[field3]'")

or die ("<h2>Didn't work DOH!</h2>");
}

Try this

//Since each field is an array from the form, I'll use
//a for loop, which will make it easy to track the
//index number
for ($i = 0; $i < count($_POST['field1']); $i++) //
{
    //I took the $_POST variables out of the string because arrays
    //usually cause parse errors when put directly in a strings
    $query = "UPDATE database SET
    field2='".$_POST['field2'][$i]."',
    field3='".$_POST['field3'][$i]."',
    field4='".$_POST['field4'][$i]."'
    WHERE field1 = '".$_POST['field1'][$i]."'";
    //"WHERE field1 =" lets mysql know exactly which record to replace

mysql_query($query)
    or die ("<h2>Didn't work DOH!</h2>");
}

By the way, this isn't tested so let me know if I made any errors.

[socont]

HAHAHA!!!! Like I would know!

Thank you very much for your help! I'll give it a try and get back to you.

[socont]

Drat. I'm getting an unformated version of the array again. I really don't understand why it would just print out the array.

Output:

Array ( [field1] => Array ( [0] => field1 [1] => field1 [2] => field1 [3] => field1)[field2] => Array ( [0] => field2 [1] => field2 [2] => field2 [3] => field2) [field3] => Array ( [0] => field3 [1] => field3 [2] => field3 [3] => field3 ) [field4] => Array ( [0] => field4 [1] => field4 [2] => field4 [3] => field4 ) )

It's a lot longer in reality (there's 54 rows in the db and it's printing them all out).

My code now:

<?php

mysql_pconnect("localhost","user","password")
or die ("MySQL Connection Failure");

mysql_select_db("database")
or die ("Can't Connect to Database");

for ($i = 0; $i < count($POST['field1']); $i++)
{
$query = "UPDATE database SET
field1='".$POST['field1'][$i]."',
field2= '".$POST['field2'][$i]."',
field3= '".$POST['field3'][$i]."'
WHERE type = '".$_POST['field1'][$i]."'";

mysql_query($query)
or die ("<h2>Didn't work DOH!,/h2>");
}

echo "<h3>Update complete</h3>";
echo "Number of items changed:", mysql_affected_rows();

?>
</body>
</html>

[dnast]

I'm baffled as to why it's printing that out. Is there more code that you've taken out to display on the forum or are there any includes or requires that aren't shown?

error_reporting(E_ALL);

^Put this on the top of your pages and see if you're getting any error messages.

Even though you got this output, was the table updated correctly?

[socont]

Well, color me embarrassed.

I’ve got to fess up: I had a block of php code at the top that I used to test the output. The same code that output the array in my first post. I improperly commented it with HTML comment tags.

This works! the db is being updated with any data I edit in any boxes. The only thing is the affected_rows is returning “0” even though rows are being updated. I’ll see if I can figure out what’s wrong there.

Thank You! You’re invited to my next barbecue.
😃

[dnast]

Mmm....BBQ. 😃

I thought something wierd was going on. Glad it's working. I'm not sure about your other problem. All I know is [man]mysql_affected_rows[/man] doesn't count rows that have been updated with the same values that were already there. I haven't really had a reason to use it yet.