Updating multiple MySQL rows with form, problem

Beginners - Learning PHP
#1

Hello! This will be my first post to this forum but I am hoping to become one of the programmers helping others here. The reason I’m here is a home project(making a website for my game guild) has taken me into the world of both HTML, PHP and MySQL, and I’m loving it! I am mostly scavenging through all the websites and tutorials I can find to learn more, but this one has me beat for now.

I am making a user database for the website, and I’ve made an admin index page where user and guild management can be done. Here I’ve made a table that updates dynamically with set values from the MySQL table. I tested this a bit and noticed nothing happened. I did however not get an error connecting to MySQL, selecting the database, or submitting the information to the database.

So in order to debug I added an echo of the actual query submitted to the database, and found it only submitted the last row in the database back, not every record as I intended.

I was wondering if one of your bright minds could have a look at the code for this and see where I’ve gone horribly wrong :slight_smile:

Here we make the connection to the database in order to fill the variables we want to update the fields with:

[php]<?php
$username=“username”;
$password=“password”;
$database=“database”;

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die(“Unable to select database”);

$query=“SELECT * FROM Users WHERE weight > 5 ORDER BY weight”;
$result=mysql_query($query);
$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {
$ID=mysql_result($result,$i,“ID”);
$name=mysql_result($result,$i,“usrname”);
$class=mysql_result($result,$i,“usrcla”);
$title=mysql_result($result,$i,“usrtit”);
$level=mysql_result($result,$i,“usrlvl”);
$alt=mysql_result($result,$i,“usralt”);
$officer=mysql_result($result,$i,“usrofficer”);
$active=mysql_result($result,$i,“Active”);

?>[/php]

Here we display the information in a table(I’m sure there may be a way of optimizing the boolean fields but I’m not that comfortable with the possibilities yet and wanted to go with something that would return what I wanted):

[code]

<?php ++$i; }

echo “

Name

Class

Title

Level

Alt

Officer

Active
<? echo $class; ?> Powertech Mercenary Juggernaut Marauder Operative Sniper Sorcerer Assassin "><?if ($alt == 1) { echo "Yes"; } else { echo "No"; }; ?> "><?if ($alt == 1) { echo "No"; } else { echo "Yes"; }; ?> "><?if ($officer == 1) { echo "Yes"; } else { echo "No"; }; ?> "><?if ($officer == 1) { echo "No"; } else { echo "Yes"; }; ?> "><?if ($active == 1) { echo "Yes"; } else { echo "No"; }; ?> "><?if ($active == 1) { echo "No"; } else { echo "Yes"; }; ?>
”
?> [/code] (I'm aware I also cheated on the select box for the class field, but I think it works to its purpose :p)

And here’s usrupdate.php

[php]<?php

$id=$_POST[‘ud_id’];
$usrname=$_POST[‘ud_usrname’];
$usrcla=$_POST[‘ud_usrcla’];
$usrtit=$_POST[‘ud_usrtit’];
$usrlvl=$_POST[‘ud_usrlvl’];
$usralt= $_POST[‘ud_usralt’];
$usrofficer= $_POST[‘ud_usrofficer’];
$Active= $_POST[‘ud_active’];

mysql_connect (“localhost”, “username”, “password”)
or die (‘I cannot connect to the database.’);
mysql_select_db (“database”)

or die(mysql_error(‘I can not select the table.’));

$query = “Update Users SET usrname=’$usrname’, usrcla=’$usrcla’, usrtit=’$usrtit’, usrlvl=’$usrlvl’, usralt=’$usralt’, usrofficer=’$usrofficer’, Active=’$Active’ WHERE id=’$ud_id’”;

echo $query;

if ($query) {
Print “The user has been successfully updated.”;
}
else{
Print “Unable to update user.”;
}

mysql_query($query);
mysql_close();

?>[/php]

I know this is a big thing to look through but I’m close to losing my mind troubleshooting this, hope you can assist!

#2

Hello and welcome!

In this piece of code you’re missing closing } and I do not see where do you increment the $i variable within the loop?
[php]$i=0;
while ($i < $num) {
$ID=mysql_result($result,$i,“ID”);
$name=mysql_result($result,$i,“usrname”);
$class=mysql_result($result,$i,“usrcla”);
$title=mysql_result($result,$i,“usrtit”);
$level=mysql_result($result,$i,“usrlvl”);
$alt=mysql_result($result,$i,“usralt”);
$officer=mysql_result($result,$i,“usrofficer”);
$active=mysql_result($result,$i,“Active”);[/php]

Also, this order of functions calls is not correct:
[php]$query = “Update Users SET usrname=’$usrname’, usrcla=’$usrcla’, usrtit=’$usrtit’, usrlvl=’$usrlvl’, usralt=’$usralt’, usrofficer=’$usrofficer’, Active=’$Active’ WHERE id=’$ud_id’”;

echo $query;

if ($query) {
Print “The user has been successfully updated.”;
}
else{
Print “Unable to update user.”;
}

mysql_query($query);
mysql_close();
[/php]

The code above will always print: The user has been successfully updated., because this condition will always be true:
[php]if ($query) {[/php]
(you’re checking if string is not empty here, and string is not empty)

You need to do something like this:
[php]if(mysql_query($query)){
echo “The user has been successfully updated.”;
}
else{
echo "Unable to update user: ".mysql_error();
}[/php]

#3

Hey PHP help, and thank you for your pointers!

The closing } for the while is below the table, in the second code snippet:
[php]<?php
++$i;
}

echo “”
?>[/php]

The intention here is for the table to grow with the number of rows in the table.

Thank you for your help with the $query condition! :slight_smile:

#4

Oh, now I see - these 2 blocks of code are from the same script. But your code still look strange: you have these lines inside your loop:

[code]

[/code] while closing table tag
and form tag are outside the loop.

If your intention is to update one record at a time - you need to close each form inside the loop (and table too). And also store record ID in a hidden form field:
[php][/php]

And if you want to save all the records at once - you will need to move the form tag outside the loop and make each field as an array of fields, so that instead of:
[php][/php]
you will have:
[php][/php]

#5

Thank you very much for your help phphelp! You do your name justice :stuck_out_tongue:

Arrays was the answer, I’ve been able to update all records now, but the boolean ones.
As for the boolean records, it’s really strange. For every user it sets every value to 0, except for the 4th user being updated, to which it sets every value to 1. Is this something you guys have experienced before?

Attaching the updated records so you can look through it if your curiousity gets you :stuck_out_tongue:

Update form:
[php]

<?php $username="username"; $password="password"; $database="database";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die(“Unable to select database”);

$query=“SELECT * FROM Users WHERE weight > 5 ORDER BY weight”;
$result=mysql_query($query);
$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {
$ID=mysql_result($result,$i,“ID”);
$name=mysql_result($result,$i,“usrname”);
$class=mysql_result($result,$i,“usrcla”);
$title=mysql_result($result,$i,“usrtit”);
$level=mysql_result($result,$i,“usrlvl”);
$alt=mysql_result($result,$i,“usralt”);
$officer=mysql_result($result,$i,“usrofficer”);
$active=mysql_result($result,$i,“Active”);

?>





















<?php ++$i; } echo "

Name

Class

Title

Level

Alt

Officer

Active

<? echo $class; ?>
Powertech
Mercenary
Juggernaut
Marauder
Operative
Sniper
Sorcerer
Assassin
<option value="<? if ($alt == 1) {
echo “1”;
}
else {
echo “0”;
}; ?>"><?if ($alt == 1) {
echo “Yes”;
}
else {
echo “No”;
}; ?>
<option value="<? if ($alt == 0) {
echo “0”;
}
else {
echo “1”;
}; ?>"><?if ($alt == 1) {
echo “No”;
}
else {
echo “Yes”;
}; ?>
<option value="<? if ($officer == 1) {
echo “1”;
}
else {
echo “0”;
}; ?>"><?if ($officer == 1) {
echo “Yes”;
}
else {
echo “No”;
}; ?>
<option value="<? if ($officer == 0) {
echo “0”;
}
else {
echo “1”;
}; ?>"><?if ($officer == 1) {
echo “No”;
}
else {
echo “Yes”;
}; ?>
<option value="<? if ($active == 1) {
echo “1”;
}
else {
echo “0”;
}; ?>"><?if ($active == 1) {
echo “Yes”;
}
else {
echo “No”;
}; ?>
<option value="<? if ($active == 0) {
echo “0”;
}
else {
echo “1”;
}; ?>"><?if ($active == 1) {
echo “No”;
}
else {
echo “Yes”;
}; ?>
" ?> [/php]

And usrupdate.php

[php]<?php
mysql_connect (“localhost”, “username”, “password”)
or die (‘I cannot connect to the database.’);
mysql_select_db (“database”)
or die(mysql_error(‘I can not select the table.’));

$size = count($_POST[‘ud_id’]);

$i = 0;
while ($i < $size) {
$id=$_POST[‘ud_id’][$i];
$usrname=$_POST[‘ud_usrname’][$i];
$usrcla=$_POST[‘ud_usrcla’][$i];
$usrtit=$_POST[‘ud_usrtit’][$i];
$usrlvl=$_POST[‘ud_usrlvl’][$i];
$usralt= $_POST[‘ud_usralt’][$i];
$usrofficer= $_POST[‘ud_usrofficer’][$i];
$Active= $_POST[‘ud_active’][$i];

$query = “Update Users SET usrname=’$usrname’, usrcla=’$usrcla’, usrtit=’$usrtit’, usrlvl=’$usrlvl’, usralt=’$usralt’, usrofficer=’$usrofficer’, Active=’$Active’ WHERE id=’$id’ LIMIT 1”;
echo $query;
++$i;

if (mysql_query($query)) {
Print “The user has been successfully updated.”;
}
else{
Print “Unable to update user:”.mysql_error();
}
mysql_query($query);
}
mysql_close();

?>[/php]

I’ve been able to echo the command sent to MySQL and what I see is that the query doesn’t contain the values at all, it’s the query that is sending the wrong values to MySQL for update. So it’s being sent, it’s just wrong(for $usrofficer, $usralt, and $Active). The rest of the variables are working perfectly now(though I will probably have to sanitize my inputs?)

#6

The way how you generate the elements in your form is not correct. The values of options should not vary. You need to operate with the ‘selected’ attribute. Here is an example:
[php]

>Yes >No [/php]

As for sanitizing input - yes, you need to do this for text boxes, like this:
[php]$usrname=mysql_real_escape_string($_POST[‘ud_usrname’][$i]);[/php]

#7

And there we go!

Database inputs sanitized and the select options are working like a charm.
This has been a great learning experience phphelp, thank you!

Sponsor our Newsletter | Privacy Policy | Terms of Service