MySQL taking variable from a 'while' and using it to update

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Deaglex
Forum Newbie
Posts: 24
Joined: Thu Feb 24, 2005 10:15 am

MySQL taking variable from a 'while' and using it to update

Post by Deaglex »

I would like to run the while and take the 'number' field to correlate with the points in that row of the table to UPDATE the database with the corresponding record.

Code: Select all

 
<?php 
 
 
require_once 'dblogin.php';
 
$result = mysql_query("SELECT * FROM drivers");
            
if (!isset($_POST['submit']))   {
         
 
         echo "<table class=\"sortable\" align='center' preserve_style=\"cell\" border='0' width='100%'>
         <tr bgcolor=\"fff978\">
         <th>Car:</th>
         <th>Driver:</th>
         <th>Points:</th>
         </tr><form action\"" . $_SERVER['PHP_SELF']  .    "\" method=\"post\">";
         
         $color1=1;
         while($row = mysql_fetch_array($result))
        {
           
           if ($color==1){
                
//Change color of rows
           echo "<tr bgcolor=\"ffee55\">";
          echo "<td align=\"center\">" . '#' . $row['number'] . "</td>";
           echo "<td>" . $row['first_name'] . ' ' . $row['last_name'] . "</td>";
           echo "<td align=\"center\">". $row['points'] . "</td>";
           echo "<td><input type=\"text\" name= \"" .  $row['number'] . "\" size=\"3\" maxlength=\"3\"/></td>";
           echo "</tr>";
           
           $color=2;
           }        
         else {
            echo "<tr bgcolor=\"cccccc\">";
          echo "<td align=\"center\">" . '#' . $row['number'] . "</td>";
           echo "<td>" . $row['first_name'] . ' ' . $row['last_name'] . "</td>";
           echo "<td align=\"center\">" . $row['points'] . "</td>";
           echo "<td><input type=\"text\" name= \"" .  $row['number'] . "\" size=\"3\" maxlength=\"3\"/></td>";
           echo "</tr>";
         $color=1;
         }
               
        }
        echo "</table> <input type=\"submit\" name=\"submit\" value=\"\Submit\"/></form>";
}
?>
Thanks for any help!
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MySQL taking variable from a 'while' and using it to update

Post by jaoudestudios »

Not sure what you mean there is no update in your code.

But some coding suggestions/improvements:
1. Be consistent, in this case with your quotes...echo "<table class=\"sortable\" align='center' preserve_style=\"cell\" border='0' width='100%'>
2. Your query is outside your IF but your WHILE is inside. So to improve efficiency you could move your mysql_query into the IF statement
3. Your zebra stripping, nice work around, but there is a much easier way that also will reduce your redundant code...just have an IF on the class not on the whole row. Also try this if($i%2) for even numbers :)
Deaglex
Forum Newbie
Posts: 24
Joined: Thu Feb 24, 2005 10:15 am

Re: MySQL taking variable from a 'while' and using it to update

Post by Deaglex »

What i want to do is for each row of the table to generate an input field associated with the corresponding row. ie:



Driver # : Driver Name : Points: new data
Driver # : Driver Name : Points: new data
Driver # : Driver Name : Points: new data
Driver # : Driver Name : Points: new data
Submit

I don't know where to put the UPDATE in code, that's where I'm stuck :oops:

http://nascar.net23.net/stats.php table with an input field on the end and one submit to input all the data. :)
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MySQL taking variable from a 'while' and using it to update

Post by jaoudestudios »

Will each cell be replaced with an input field and one submit button at the bottom that will submit all rows and columns?
Deaglex
Forum Newbie
Posts: 24
Joined: Thu Feb 24, 2005 10:15 am

Re: MySQL taking variable from a 'while' and using it to update

Post by Deaglex »

Yes. that's what i want to do. With on click the data for each row can be submitted at once.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MySQL taking variable from a 'while' and using it to update

Post by jaoudestudios »

Deaglex wrote:With on click the data for each row can be submitted at once.
Please elaborate on this.
Deaglex
Forum Newbie
Posts: 24
Joined: Thu Feb 24, 2005 10:15 am

Re: MySQL taking variable from a 'while' and using it to update

Post by Deaglex »

http://nascar.net23.net/pointsdata.php

here is what the table will look like. I want each row to submit it's own value for the points input: field. Then input the points in to MySQL database.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MySQL taking variable from a 'while' and using it to update

Post by jaoudestudios »

ok. Can you post the table schema?
Deaglex
Forum Newbie
Posts: 24
Joined: Thu Feb 24, 2005 10:15 am

Re: MySQL taking variable from a 'while' and using it to update

Post by Deaglex »

Code: Select all

drivers
(`driver_id` smallint unsigned NOT NULL auto_increment,
  `last_name` varchar collate latin1_general_ci NOT NULL,
  `first_name` varchar collate latin1_general_ci NOT NULL,
  `number` varcharcollate latin1_general_ci NOT NULL,
  `manufacturer` varchar collate latin1_general_ci NOT NULL,
  `team` varchar collate latin1_general_ci NOT NULL,
  `sponser` varchar collate latin1_general_ci NOT NULL,
  `points` mediumint unsigned NOT NULL,
  PRIMARY KEY  (`driver_id`),
  UNIQUE KEY `number` (`number`)
)
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MySQL taking variable from a 'while' and using it to update

Post by jaoudestudios »

Does $row['number'] reference to driver_id in the table?

Something like this...

Code: Select all

 
<?php
 
 
require_once 'dblogin.php';
 
$result = mysql_query("SELECT * FROM drivers");
           
if (!empty($_POST))   {
        foreach ($_POST AS $k=>$v) {
           if ($k != 'submit') {
              $q = "INSERT INTO drivers SET points = '".$v."' WHERE driver_id = '".$k."'"; // requires database protection
              $sql = mysql_query($q);
           }
        }
} 
else {
         echo "<table class=\"sortable\" align='center' preserve_style=\"cell\" border='0' width='100%'>
         <tr bgcolor=\"fff978\">
         <th>Car:</th>
         <th>Driver:</th>
         <th>Points:</th>
         </tr><form action\"" . $_SERVER['PHP_SELF']  .    "\" method=\"post\">";
         
         $color1=1;
           $i=0;
         while($row = mysql_fetch_array($result))
        {
           
           if ($color==1){
               
           //Change color of rows
           echo "<tr bgcolor=\"ffee55\">";
          echo "<td align=\"center\">" . '#' . $row['number'] . "</td>";
           echo "<td>" . $row['first_name'] . ' ' . $row['last_name'] . "</td>";
           echo "<td align=\"center\">". $row['points'] . "</td>";
           echo "<td><input type=\"text\" name= \"" .  $row['number'] . "\" size=\"3\" maxlength=\"3\"/></td>";
           echo "</tr>";
           
           $color=2;
           }        
         else {
            echo "<tr bgcolor=\"cccccc\">";
          echo "<td align=\"center\">" . '#' . $row['number'] . "</td>";
           echo "<td>" . $row['first_name'] . ' ' . $row['last_name'] . "</td>";
           echo "<td align=\"center\">" . $row['points'] . "</td>";
           echo "<td><input type=\"text\" name= \"" .  $row['number'] . "\" size=\"3\" maxlength=\"3\"/></td>";
           echo "</tr>";
         $color=1;
         }
            $i++;   
        }
        echo "</table> <input type='hidden' value='".$i."' name='total' /><input type=\"submit\" name=\"submit\" value=\"\Submit\"/></form>";
}
?>
 
Deaglex
Forum Newbie
Posts: 24
Joined: Thu Feb 24, 2005 10:15 am

Re: MySQL taking variable from a 'while' and using it to update

Post by Deaglex »

cool I'm going to try that.. I will let you know

Thanks.
Post Reply