Solved

Updating Records In MySQL With PHP

Posted on 2000-04-04
39
200 Views
Last Modified: 2006-11-17
I am going to try and explain this in the best way I know how.

I have several users that have a set amount of points in a mySQL database. The field that I want to update is called bonuspts.

I use a query to select all users that have entered the required info asked.

I want to be able to check a select box to basically approve them getting their points.

What I need to do next is the problem. I want to update all records that I have selected and have 200 points added to each one. The problem I have been having is that if the same user has 5 records, it only updates that persons record with 200 points instead of the 1000 they should get. Is there a way to update them all regardless of how many times?

Here is the meat and potato part of my query, you can view the results at http://www.level40.com/list.php3   -

echo ("<TABLE WIDTH=100% ALIGN=CENTER><TR>");
echo ("<TD WIDTH=20%>");
echo ("<FONT FACE=VERDANA SIZE=1>");echo $data["username"];
echo ("</TD>");
echo ("<TD WIDTH=20%>");
echo ("<FONT FACE=VERDANA SIZE=1>");echo $data["song"];
echo ("</TD>");
echo ("<TD WIDTH=20%>");
echo ("<FONT FACE=VERDANA SIZE=1>");echo $data["date"];
echo ("</TD>");
echo ("<TD WIDTH=20%>");
echo ("<FONT FACE=VERDANA SIZE=1>");echo $data["time"];
echo ("</TD>");
echo ("<TD WIDTH=20%>");
echo ("<input type=checkbox name=users value=ON>");
echo ("</TD>");
echo ("</TR></TABLE>");

I want to select check each name that I want updated and the update all that are selected.

I can't even decipher what I am trying to say so if you need more details then let me know.
0
Comment
Question by:phreakin
  • 15
  • 13
  • 11
39 Comments
 
LVL 1

Expert Comment

by:mafweb
ID: 2686373
how about trying the following:
in the form, you use a field like
<input type=whatever name=<?echo $data[username]."[]";?>
this will give you an array for every user containing as many entries as the user has fields.
then you use a loop (let the username be "foo"):

$count=0;
for ($i=0; $i<sizeOf($foo); $i++)
  if ($foo[$i]=="on")
    $count++;

then you can add $count*200 to bonuspts

hope this is what you need ;-)

maf
0
 
LVL 8

Expert Comment

by:us111
ID: 2686605
I didn't try. You can the get the idea
It should work but you need to add connection to database


<?
if ($send <> "")
{ while (list($var, $value) = each($HTTP_POST_VARS))
   { $person_num = split("-", $var);
      // if checkbox selected and user not updated
      if ($person_num[0] == "users" && $already_updated["$person_num[1]"] <> "updated")
      {      // array for remember if the person have been already updated
      $already_updated["$person_num[1]"] = "updated";            
      $query = "UPDATE table SET bonuspts=bonuspts+200 WHERE person_num = $person_num[1]";
      mysql_query($query, $conn);
      }
   }
}
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF">
<form action="index.php3" method="post">
<input type="hidden" value="send" name="send">
<?
// pseudo code
while (next_person)      {      ?>
<TABLE WIDTH=100% ALIGN=CENTER>
<TR>
    <TD WIDTH=20%><?echo $data["username"]?></TD>
    <TD WIDTH=20%><?echo $data["song"]?></TD>
    <TD WIDTH=20%><?echo $data["date"]?></TD>
    <TD WIDTH=20%><?echo $data["time"]?></TD>
      <TD WIDTH=20%><input type=checkbox name="users-<?print $data["person_number"]?>"></TD>
</TR>
</TABLE>
<?      }      ?>
</form>

</body>
</html>
0
 

Author Comment

by:phreakin
ID: 2686661
Hmm, maybe I am illiterate, but what is wrong with this?


<?

      
if ($send <> "")
{ while (list($var, $value) = each($HTTP_POST_VARS))
   { $person_num = split("-", $var);
      // if checkbox selected and user not updated
      if ($person_num[0] == "users" && $already_updated["$person_num[1]"] <> "updated")
      { // array for remember if the person have been already updated
$already_updated["$person_num[1]"] = "updated";
$query = "UPDATE table SET bonuspts=bonuspts+200 WHERE person_num = $person_num[1]";
mysql_query($query, $conn);
      }
   }
}

$conn = mysql_connect(server,username,password);

$result = mysql_db_query("database","select * from bonus order by username ASC")
      or die
      ("No Information");

while ($data=mysql_fetch_array($result)) {

?>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF">
<form action="index.php3" method="post">
<input type="hidden" value="send" name="send">
<?
// pseudo code
while (next_person) { ?>
<TABLE WIDTH=100% ALIGN=CENTER>
<TR>
    <TD WIDTH=20%><?echo $data["username"]?></TD>
    <TD WIDTH=20%><?echo $data["song"]?></TD>
    <TD WIDTH=20%><?echo $data["date"]?></TD>
    <TD WIDTH=20%><?echo $data["time"]?></TD>
<TD WIDTH=20%><input type=checkbox name="users-<?print $data["person_number"]?>"></TD>
</TR>
</TABLE>
<? } ?>
</form>

</body>
</html>


0
 
LVL 8

Expert Comment

by:us111
ID: 2686717
Try,

<?
$server="ddhjkhkas";
$username="kékk";
$password="kjjl";
$conn = mysql_connect($server,$username,$password);

if ($send <> "")
{ while (list($var, $value) = each($HTTP_POST_VARS))
  { $person_num = split("-", $var);
    // if checkbox selected and user not updated
    if ($person_num[0] == "users" && $already_updated["$person_num[1]"] <> "updated")
    { // array for remember if the person have been already updated
      $already_updated["$person_num[1]"] = "updated";
      $query = "UPDATE bonus SET bonuspts=bonuspts+200 WHERE person_num = $person_num[1]";
      mysql_db_query("database", $query)
    }
  }
}

$result = mysql_db_query("database","select * from bonus order by username ASC") or die ("No Information");
?>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

 
<body bgcolor="#FFFFFF">
<form action="index.php3" method="post">
  <input type="hidden" value="send" name="send">
  <? while ($data=mysql_fetch_array($result)) { ?>
  <TABLE WIDTH=100% ALIGN=CENTER>
    <TR>
      <TD WIDTH=20%><?echo $data["username"]?></TD>
      <TD WIDTH=20%><?echo $data["song"]?></TD>
      <TD WIDTH=20%><?echo $data["date"]?></TD>
      <TD WIDTH=20%><?echo $data["time"]?></TD>
      <TD WIDTH=20%>
        <input type=checkbox name="users-<?print $data["person_number"]?>">
      </TD>
    </TR>
  </TABLE>
  <? } ?>
</form>
</body>
</html>
0
 

Author Comment

by:phreakin
ID: 2686767
us111:

The query works fine except for the <input type=checkbox name="users-"> area. The above is actually what gets printed. And when I try and update a record, it obviously doesn't update.
0
 
LVL 8

Expert Comment

by:us111
ID: 2686939
<input type=checkbox name="users-<?print $data["person_number"]?>">

$data["person_number"] is from your database, I've put person_number (for example) but it's the key (field) of your table bonus.

otherwise put your code here? Is it the same page
http://www.level40.com/list.php3

0
 
LVL 1

Expert Comment

by:mafweb
ID: 2688811
hi phreakin, have you tried my code sample?
the important part, imho, is <?echo $data[username]."[]";?> because here you create an array containig every field of your form exactly once, so you can see exactly which checkboxes have been selected

maf
0
 

Author Comment

by:phreakin
ID: 2688821
mafweb:

I am new to PHP, so is there a way to give an example script like the other so I can try? I am willing to give all 1400 of my points for this question
0
 
LVL 1

Expert Comment

by:mafweb
ID: 2688881
of course,

as it seems, the first part of your program (pulling user info out of the db) works, so here's just the one line to be changed:

echo ("<input type=checkbox name=".$username."[] value=ON>");

where $username is the account name for this user.

you can evaluate the form data with:

$db=mysql_connect("server", "username","pwd")
$rs=mysql_query("select * from users");
for ($i=0; $i<mysql_num_rows(); $i++) {
  $username=mysql_result($rs, $i, "username");
  $count=0;
  for ($i=0; $i<sizeOf($$username); $i++)
     if ($$username[$i]=="on")
        $count++;
  $bonus=mysql_result($rs, $i, "bonuspts");
  $bonus+=$count*200;
  $rs=mysql_query("update users set bonuspts=$bonus where      username=$username");
}

hope this works, as I don't have a working mysql sys here at work

maf
0
 
LVL 8

Expert Comment

by:us111
ID: 2688948
I have an idea !!.
Could you dump your bonus table , put here or send me (rtom@free.fr) the database and the php file
0
 

Author Comment

by:phreakin
ID: 2692650
Ok, still not working. I have the source at http://www.level40.com/list.phps.
0
 
LVL 1

Expert Comment

by:mafweb
ID: 2692703
what's wrong with this code is, that you get only one var per user, whether you have one or hundred checkboces for him.
Try this:

{ while (list($var, $value) = each($HTTP_POST_VARS))  
  { $person_num = split( "-", $var);  
     // if checkbox selected and user not updated  
    if ($person_num[0] ==  "users" && $already_updated[ "$username[1]"] <>  "updated")  
    {  // array for remember if the person have been already updated  
      $already_updated[ "$username[1]"] =  "updated";  

        for ($i=0; $i<sizeOf($$username[1]); $i++)
           if ($$username[1][$i]=="ON")
              $count++;
         $bonus=mysql_result($rs, $i, "bonuspts");
         $bonus+=$count*200;
         $query =  "UPDATE users SET bonuspts=bonuspts+$bonus WHERE username = $username[1]";  

      mysql_db_query( "flpkpty", $query);
    }  
  }  
}  

....

      <TD WIDTH=20%> <?echo $data[ "date"]?></TD>  
      <TD WIDTH=20%> <?echo $data[ "time"]?></TD>  
      <TD WIDTH=20%>  

        <input type=checkbox name="users- <?print $data[ "username"]."[]"?>">  

      </TD>  
....

the rest is like in your script. the brackets [] are very important in the input tag

maf
0
 

Author Comment

by:phreakin
ID: 2692707
Ok, still not working. I have the source at http://www.level40.com/list.phps.
0
 
LVL 1

Expert Comment

by:mafweb
ID: 2692732
is there an errormessage or does the script simply not output the rigth data? And does it increment any values?

maf
0
 

Author Comment

by:phreakin
ID: 2692915
Btw, the source I currently have running is at:

http://www.level40.com/list.phps
0
 
LVL 8

Expert Comment

by:us111
ID: 2692916
ok it means you don't have enable http_track_var, if you use winnt
check this line :
track_vars                  =      On      

it should be on.

If you use php as an apache module, you need to recompile php module, not really easy.

Otherwise I've got your script, I've made a small databse, and
I'm working on your stuff.

Wait for my answer :))
0
 
LVL 8

Expert Comment

by:us111
ID: 2692922
mysql_num_rows($rs);
0
 

Author Comment

by:phreakin
ID: 2692924
Btw, the source I currently have running is at:

http://www.level40.com/list.phps
0
 
LVL 1

Expert Comment

by:mafweb
ID: 2692948
sorry, the line should be:
for ($i=0; $i<mysql_num_rows($rs); $i++) {

maf
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:mafweb
ID: 2692958
sorry,

should be:

for ($i=0; $i<mysql_num_rows($rs); $i++) {

maf
0
 
LVL 1

Expert Comment

by:mafweb
ID: 2692961
sorry,

should be:

for ($i=0; $i<mysql_num_rows($rs); $i++) {

maf
0
 

Author Comment

by:phreakin
ID: 2692841
It shows the following error:

Warning: Variable passed to each() is not an array or object in /var/www/virtual/level40.com/www/list.php3 on line 7


If I take the =each out, it doesn't show an error. But it doesn't update records either way either.
0
 

Author Comment

by:phreakin
ID: 2692843
Adjusted points from 500 to 750
0
 
LVL 1

Expert Comment

by:mafweb
ID: 2692998
sorry, try

for ($i=0; $i<mysql_num_rows($rs); $i++) {

maf
0
 
LVL 1

Expert Comment

by:mafweb
ID: 2693000
sorry, try

for ($i=0; $i<mysql_num_rows($rs); $i++) {

maf
0
 
LVL 8

Expert Comment

by:us111
ID: 2692866
ok it means you don't have enable http_track_var, if you use winnt
check this line :
track_vars                  =      On      

it should be on.

If you use php as an apache module, you need to recompile php module, not really easy.

Otherwise I've got your script, I've made a small databse, and
I'm working on your stuff.

Wait for my answer :))
0
 

Author Comment

by:phreakin
ID: 2692868
As long as I can get it working and updating, I'll give you all my points :)
0
 
LVL 1

Expert Comment

by:mafweb
ID: 2692876
first, delete the whitespace from the name=users-<?echo... part

then try this instead of the while (list)...block

$rs=mysql_query("select * from users");
for ($i=0; $i<mysql_num_rows(); $i++) {
                           $username=mysql_result($rs, $i, "username");
                           $count=0;
                           for ($i=0; $i<sizeOf(${"users-".$username); $i++)
                              if (${"users-".$username[$i]}=="on")
                                 $count++;
                           $bonus=mysql_result($rs, $i, "bonuspts");
                           $bonus+=$count*200;
                           $rs=mysql_query("update users set bonuspts=$bonus where username=$username");
}

maf
0
 

Author Comment

by:phreakin
ID: 2692886
mafweb:

errors in the code, I get the following:

Warning: Wrong parameter count for mysql_num_rows() in /var/www/virtual/level40.com/www/list.php3 on line 8
0
 

Author Comment

by:phreakin
ID: 2692896
Btw, the source I currently have running is at:

http://www.level40.com/list.phps
0
 
LVL 8

Expert Comment

by:us111
ID: 2693146
ok I've got it. the following code works for me.
Just add the field 'n' in your table bonus
'n' will be an automatic index (key).


<?  
$server= "localhost";  
$username= "";  
$password= "";  
$conn = mysql_connect($server,$username,$password);  
$DATABASE = "jde";

if ($send <>  "")  
{  for($i=1;$i<=$number;$i++)
   {  $userfield = "users-".$i;
             if ($$userfield == "on")
          { $query =  "select bonus,username from bonus WHERE n = $i";
              $r = mysql($DATABASE, $query,$conn);

              $bonus = mysql_result($r, 0, "bonus");
              $bonus = $bonus + 200;
              $username = mysql_result($r, 0, "username");

                $query =  "UPDATE bonus SET bonus=$bonus WHERE username = '$username'";  
              $r = mysql($DATABASE, $query, $conn);
                  print "<h4>done</h4>";
    }
  }  
}  

$result = mysql_db_query( "jde", "select * from bonus order by username ASC") or die ( "No Information");  
?>  

<html>  
<head>  
<title>Untitled Document</title>  
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  
</head>  

   
<body bgcolor="#FFFFFF">  
<form action="list.php3" method="post">  
  <input type="hidden" value="send" name="send">  
   <? while ($data=mysql_fetch_array($result)) { ?>  
  <TABLE WIDTH=100% ALIGN=CENTER>  
    <TR>  
      <TD WIDTH=20%> <?echo $data[ "username"]?></TD>  
      <TD WIDTH=20%> <?echo $data[ "song"]?></TD>  
      <TD WIDTH=20%> <?echo $data[ "date"]?></TD>  
      <TD WIDTH=20%> <?echo $data[ "time"]?></TD>  
      <TD WIDTH=20%>  
        <input type=checkbox name="users-<?print $data["n"]?>">  
      </TD>  
    </TR>  
  </TABLE>  
   <? } ?>  
       <input type=hidden name="number" value="<?print mysql_num_rows($result)?>">  
     <input type=submit name="submit">  
</form>  
</body>  
</html>  
0
 
LVL 8

Expert Comment

by:us111
ID: 2693314
forget my last version, bugs if you delete a user.
use the following, you don't need the field 'n'

<?  
$server= "localhost";  
$username= "";  
$password= "";  
$conn = mysql_connect($server,$username,$password);  
$DATABASE = "jde";

if ($send <>  "")  
{        while (list($index, $value)=each($users))
      {      if ($value <> "")
            {      $query =  "select bonus from bonus WHERE username = '$value'";
                  $r = mysql($DATABASE, $query,$conn);

                  $bonus = mysql_result($r, 0, "bonus");
                  $bonus = $bonus + 200;

                    $query =  "UPDATE bonus SET bonus=$bonus WHERE username = '$value'";  
                  $r = mysql($DATABASE, $query, $conn);
                  print "<h4>done</h4>";
            }
      }  
}

$result = mysql_db_query( "jde", "select * from bonus order by username ASC") or die ( "No Information");  
?>  

<html>  
<head>  
<title>Untitled Document</title>  
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  
</head>  

   
<body bgcolor="#FFFFFF">  
<form action="list.php3" method="post">  
  <input type="hidden" value="send" name="send">  
   <? while ($data=mysql_fetch_array($result)) { ?>  
  <TABLE WIDTH=100% ALIGN=CENTER>  
    <TR>  
      <TD WIDTH=20%> <?echo $data[ "username"]?></TD>  
      <TD WIDTH=20%> <?echo $data[ "song"]?></TD>  
      <TD WIDTH=20%> <?echo $data[ "date"]?></TD>  
      <TD WIDTH=20%> <?echo $data[ "time"]?></TD>  
      <TD WIDTH=20%>  
        <input type=checkbox name="users[]" value="<?echo $data["username"]?>">  
      </TD>  
    </TR>  
  </TABLE>  
   <? } ?>  
     <input type=submit name="submit">  
</form>  
</body>  
</html>
0
 

Author Comment

by:phreakin
ID: 2694444
Btw, the source I currently have running is at:

http://www.level40.com/list.phps
0
 

Author Comment

by:phreakin
ID: 2694565
ok, I got the update to work with a couple of hacks. The only thing I need to know now is how to surpress the error message when a user doesn't exist. It currently says the following:

Warning: Unable to jump to row 0 on MySQL result index 2 in /var/www/virtual/level40.com/www/list.php3 on line 14



Can't I just add a @ to suppress the query errors?

Btw, here is the code I used:

<?  
$server= "localhost";  
$username= "";  
$password= "";  
$conn = mysql_connect($server,$username,$password);  
$DATABASE = "flpkpty";

if ($send <>  "")  
{   while (list($index, $value)=each($users))
{ if ($value <> "")
{ $query =  "select bonuspts from users WHERE username = '$value'";
$r = mysql($DATABASE, $query,$conn);

$bonuspts = mysql_result($r, 0, "bonuspts");
$bonus = $bonuspts + 200;

@$query =  "UPDATE users SET bonuspts=$bonus WHERE username = '$value'";  
$r = mysql($DATABASE, $query, $conn);
print "<h4>done</h4>";
}
}  
}

$result = mysql_db_query( "flpkpty", "select * from bonus order by username ASC") or die ( "No Information");  
?>  

<html>  
<head>  
<title>Untitled Document</title>  
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  
</head>  

     
<body bgcolor="#FFFFFF">  
<form action="list.php3" method="post">  
  <input type="hidden" value="send" name="send">  
   <? while ($data=mysql_fetch_array($result)) { ?>  
  <TABLE WIDTH=100% ALIGN=CENTER>  
    <TR>  
      <TD WIDTH=20%> <?echo $data[ "username"]?></TD>  
      <TD WIDTH=20%> <?echo $data[ "song"]?></TD>  
      <TD WIDTH=20%> <?echo $data[ "date"]?></TD>  
      <TD WIDTH=20%> <?echo $data[ "time"]?></TD>  
      <TD WIDTH=20%>  
        <input type=checkbox checked name="users[]" value="<?echo $data["username"]?>">  
      </TD>  
    </TR>  
  </TABLE>  
   <? } ?>  
     <input type=submit name="submit">  
</form>  
</body>  
</html>
0
 
LVL 8

Expert Comment

by:us111
ID: 2694930
yep you can add @ before a function to avoid the warning.

try that (with a test)

if ($send <>  "")    
{   while (list($index, $value)=each($users))
{ if ($value <> "")
{ $query =  "select bonuspts from users WHERE username = '$value'";
$r = mysql($DATABASE, $query,$conn);
if ($r <> "")
{
$bonuspts = @mysql_result($r, 0, "bonuspts");
$bonus = $bonuspts + 200;

$query =  "UPDATE users SET bonuspts=$bonus WHERE username = '$value'";    
$r = @mysql($DATABASE, $query, $conn);
print "<h4>done</h4>";
}
}
}    
}

or try to use the following

if ($send <>  "")    
{   while (list($index, $value)=each($users))
{ if ($value <> "")
{ $query =  "select bonuspts from users WHERE username = '$value'";
$r = mysql($DATABASE, $query,$conn);

$bonuspts = @mysql_result($r, 0, "bonuspts");
$bonus = $bonuspts + 200;

$query =  "UPDATE users SET bonuspts=$bonus WHERE username = '$value'";    
$r = @mysql($DATABASE, $query, $conn);
print "<h4>done</h4>";
}
}    
}
0
 
LVL 8

Accepted Solution

by:
us111 earned 750 total points
ID: 2694947
I hope it's what you need and wish:)

David
0
 

Author Comment

by:phreakin
ID: 2695321
Excellent help with this, I appreciate it alot.
0
 
LVL 8

Expert Comment

by:us111
ID: 2695694
sorry maxweb

phreakin, if you have other short questions, you can put it in this question. I think for 750 points (for me 3000), I can help you more.
0
 
LVL 8

Expert Comment

by:us111
ID: 2695698
oops mafweb not maxweb :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now