Page 1 of 1

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

Posted: Mon Jan 12, 2009 10:15 pm
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!

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

Posted: Tue Jan 13, 2009 1:52 am
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 :)

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

Posted: Tue Jan 13, 2009 1:12 pm
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. :)

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

Posted: Tue Jan 13, 2009 2:19 pm
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?

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

Posted: Tue Jan 13, 2009 3:01 pm
by Deaglex
Yes. that's what i want to do. With on click the data for each row can be submitted at once.

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

Posted: Tue Jan 13, 2009 3:04 pm
by jaoudestudios
Deaglex wrote:With on click the data for each row can be submitted at once.
Please elaborate on this.

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

Posted: Tue Jan 13, 2009 3:15 pm
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.

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

Posted: Tue Jan 13, 2009 3:22 pm
by jaoudestudios
ok. Can you post the table schema?

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

Posted: Tue Jan 13, 2009 3:42 pm
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`)
)

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

Posted: Tue Jan 13, 2009 3:54 pm
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>";
}
?>
 

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

Posted: Tue Jan 13, 2009 4:15 pm
by Deaglex
cool I'm going to try that.. I will let you know

Thanks.