Solved

Updating Records In MySQL With PHP

Posted on 2000-04-04
39
202 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

911 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

20 Experts available now in Live!

Get 1:1 Help Now