updating multiple rows in a single column

[jktress]

I'm very new to php and attempting to build a formula 1 fantasy site.

I have created an admin page, and would like to be able to update the amount of points each driver has accumulated during the race via a form. There are 23 drivers, and therefore I need to update 23 rows in a single column each weekend.

I have tried to save the $_POST method and update my table that way, however I don't think a single variable can hold all the data...I believe I need to use an array (in addition to the $_POST array) however I'm not sure how to implement this. Can anyone help me out?

[sanfly]

Are you using a mysql database? have you set up any tables yet? If so, what tables and structure have you used so far?

[jktress]

[!--quoteo(post=369532:date=Apr 28 2006, 06:30 AM:name=sanfly)--][div class=\'quotetop\']QUOTE(sanfly @ Apr 28 2006, 06:30 AM) [snapback]369532[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Are you using a mysql database? have you set up any tables yet? If so, what tables and structure have you used so far?
[/quote]

I have set up a users table, drivers table, driverPoints table. I was hoping to relate the driverPoints table to the drivers table

structure of the driver points table is as follows (driver ID(Primary Key), race1, race2...etc)

[!--quoteo(post=369535:date=Apr 28 2006, 06:41 AM:name=jktress)--][div class=\'quotetop\']QUOTE(jktress @ Apr 28 2006, 06:41 AM) [snapback]369535[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I have set up a users table, drivers table, driverPoints table. I was hoping to relate the driverPoints table to the drivers table

structure of the driver points table is as follows (driver ID(Primary Key), race1, race2...etc)
[/quote]

I'm using a MySQL database, with phpmyadmin

[sanfly]

Hmm, I would set things up a bit differently if I were writing this, not saying its the best way, just how i would do it (PS: looking at what i now have below here, i think ive gone a bit overboard!!)

[b]table: drivers[/b]

driver_id (primary key)
driver_name

etc....

Make a form to add the drivers details

[b]table: races[/b]

race_id (primary_key)
race_date
race_location

etc...

Make a form to add each race details

[b]table: race_points[/b]

rp_id (primary Key)
rp_driver (drivers id inserted here)
rp_race (race id inserted here)
rp_points

Make a form to list the races, and have a link to add race points:

[code]// insert database connection details here

$q = "SELECT * FROM races ORDER BY race_date";
$r = mysql_query($q) or die(mysql_error());
   while($row = mysql_fetch_array($r)){
      $race_id = $row['race_id'];
      $race_date = $row['race_date'];
         echo "$race_date (<a href=\"races.php?action=addpoints&rid=$race_id\">Add Points</a>)<br>";
   }[/code]



then in races.php

[code]<?
    $rp_driver = array("1","2","3");
    $rp_points = array("111", "222", "333");
    
    $dp = array();
    $dp['driver'] = $rp_driver;
    $dp['points'] = $rp_points;
    
    print_r($rp_driver);
    
    echo "<br><br>";
    
    $num = count($dp);
    for($i = 0; $i < $num; $i ++){
        $dp_driver = $dp['driver'][$i];
        $dp_points = $dp['points'][$i];
        
        echo "$dp_driver, $dp_points<br>";
    }




$rId = $_GET['rid'];
if($_GET['action'] = "addpoints" && $_GET['rid'] == $rId){ ?>

    <form method="post" action="race.php?action=addpoints2">
        <table>
            <tr>
                <td>Driver name</td>
                    <td>Points</td>
            </tr>
        
<?    $q = "SELECT * FROM drivers";
    $r = mysql_query($q) or die(mysql_error());
           while($row = mysql_fetch_array($r)){
              $driver_id = $row['driver_id'];
              $driver_name = $row['driver_name']; ?>
             <tr>
                <td><?=$driver_name?> <input type="hidden" name="rp_driver[]" value="<?=$driver_id?>"></td>
                    <td><input type="text" name="rp_points[]" size="10"></td>
            </tr>
       <?    } ?>
            <tr>
                <td colspan="2">
                    <input type="hidden" name="rp_race" value="<?=$rId?>">
                    <input type="submit" value="Submit Results">
                </td>
            </tr>
        
        </table>
    </form>
<?    
}
if($_GET['action'] = "addpoints2"){
    // Get Posted Variables
    $rp_race = $_POST['rp_race'];
    $rp_driver = $_POST['rp_driver'];
    $rp_points = $_POST['rp_points'];
    
    $dp = array();
    $dp['driver'] = $rp_driver;
    $dp['points'] = $rp_points;
    $num = count($dp);
    for($i = 0; $i < $num; $i ++){
        $dp_driver = $dp['driver'][$i];
        $dp_points = $dp['points'][$i];
        
        // Insert database connection info here
        
        $q = "INSERT INTO race_points (rp_race, rp_driver, rp_points) VALUES ('$rp_race', '$rp_driver', '$rp_points')";
        $r = mysql_query($q) or die(mysql_error());
    }
    
    echo "done, link to whatever here";
}
[/code]

This should make it easier to calculate total points etc too

eg:
[code]$total_points = 0;
    // Insert database connection info here
    $q = "SELECT * FROM race_points WHERE rp_driver = '$some_drivers_id'";
    $r = mysql_query($q) or die(mysql_error());
        while($row = mysql_fetch_array($r)){
            $rp_points = $row['rp_points'];
            $total_points = $total_points + $rp_points;
        }
        echo "$total_points";[/code]

[jktress]

great, thanks very much for the help.. It's going to take me a while to wrap my brain around this! I'll go through the code and try to figure out each piece.

Now I also have to calculate points for engines and chassis's. The users get 2 drivers, 1 engine, and 1 chassis. I have tables set up exactly like the drivers table above, (ie. Engine_ID, Engine_Name, Engine_Price). I need to be able to update the points for those as well. Scores from drivers, engine, and chassis are combined for a total.
Would you recommend a similar solution as in your above post?

[sanfly]

Yeah, think so, its kinda hard to know because im not sure of the aim of your site. Im assuming that the users pick a different combo of drivers, chassis and engine on a race by race basis? Do the number of points awarded for engine and chassis change per race? if so their values can be added to the race_points table above

Each of the elements (engine, chassis etc) should have its own table with appropriate values, then have a table that stores what the users pick each time, like a cross referencing table eg:

[b]table: user_race[/b]

ur_id (primary Key)
ur_user (users id inserted here)
ur_race = (race id inserted here)
ur_driver1 (driver id inserted here)
ur_driver2 (driver id inserted here)
ur_engine (engine id inserted here)
ur_chassis (chassis id inserted here)

[b]table: race_points[/b]

rp_id (primary Key)
rp_driver (drivers id inserted here)
rp_race (race id inserted here)
rp_driver_points
rp_engine_points
rp_chassis_points

(PS: I just noticed that in the code i gave you below, i still had some of my testing stuff, anything in that script above $rId = $_GET['rid']; should be deleted)

[code]$rId = $_GET['rid'];
if($_GET['action'] = "addpoints" && $_GET['rid'] == $rId){ ?>

    <form method="post" action="race.php?action=addpoints2">
        <table>
            <tr>
                <td>Driver name</td>
                    <td>Driver Points</td>
                        <td>Engine Points</td>
                            <td>Chassis Points</td>
            </tr>
        
<?    $q = "SELECT * FROM drivers";
    $r = mysql_query($q) or die(mysql_error());
           while($row = mysql_fetch_array($r)){
              $driver_id = $row['driver_id'];
              $driver_name = $row['driver_name']; ?>
             <tr>
                <td><?=$driver_name?> <input type="hidden" name="rp_driver[]" value="<?=$driver_id?>"></td>
                    <td><input type="text" name="rp_driver_points[]" size="10"></td>
                        <td><input type="text" name="rp_engine_points[]" size="10"></td>
                            <td><input type="text" name="rp_chassis_points[]" size="10"></td>
                        
            </tr>
       <?    } ?>
            <tr>
                <td colspan="4">
                    <input type="hidden" name="rp_race" value="<?=$rId?>">
                    <input type="submit" value="Submit Results">
                </td>
            </tr>
        
        </table>
    </form>
<?    
}
if($_GET['action'] = "addpoints2"){
    // Get Posted Variables
    $rp_race = $_POST['rp_race'];
    $rp_driver = $_POST['rp_driver'];
    $rp_driver_points = $_POST['rp_driver_points'];
    $rp_engine_points = $_POST['rp_engine_points'];
    $rp_chassis_points = $_POST['rp_chassis_points'];
    
    $dp = array();
    $dp['driver'] = $rp_driver;
    $dp['driver_points'] = $rp_driver_points;
    $dp['engine_points'] = $rp_engine_points;
    $dp['chassis_points'] = $rp_chassis_points;
    $num = count($dp);
    for($i = 0; $i < $num; $i ++){
        $dp_driver = $dp['driver'][$i];
        $dp_driver_points = $dp['driver_points'][$i];
        $dp_engine_points = $dp['engine_points'][$i];
        $dp_chassis_points = $dp['chassis_points'][$i];
        
        // Insert database connection info here
        
        $q = "INSERT INTO race_points (rp_race, rp_driver, rp_driver_points, rp_engine_points, rp_chassis_points)
            VALUES ('$rp_race', '$rp_driver', '$rp_driver_points', '$rp_engine_points', '$rp_chassis_points')";
        $r = mysql_query($q) or die(mysql_error());
    }
    
    echo "done, link to whatever here";
}[/code]

[jktress]

Ok I have the pages up, I believe I understand the code pretty well. I'm having some trouble getting the values inserted into my driverPoints table however, and I can't find the problem. It is inserting 10 blank rows.

[code]
<?php
$rId = $_GET['rId'];
if($_GET['action'] = "addpoints" && $_GET['rId'] == $rId){ ?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Untitled Document</title>
</head>

<body>

    <form method="post" action="races.php?action=addpoints2">
        <table>
            <tr>
                <td>Driver name</td>
                    <td>Points</td>
            </tr>
<? require('Connections/formula1.php');
@mysql_select_db($database_formula1) or die( "Unable to select database");
$q = "SELECT * FROM drivers";
    $r = mysql_query($q) or die(mysql_error());
           while($row = mysql_fetch_array($r)){
              $driver_id = $row['driver_id'];
              $driver_name = $row['nameD']; ?>
             <tr>
              
                <td><?=$driver_name?> <input type="hidden" name="rp_driver[]" value="<?=$driver_id?>"></td>
                    <td><input type="text" name="rp_points[]" size="10"></td>
            </tr>
       <?    } ?>
            <tr>
                <td colspan="2">
                    <input type="hidden" name="rp_race" value="<?=$rId?>">
                    <input type="submit" value="Update the Points">
                </td>
            </tr>
        
        </table>
    </form>

<?php    
}
if($_GET['action'] = "addpoints2"){
    // Get Posted Variables
    $rp_race = $_POST['rp_race'];
    $rp_driver = $_POST['rp_driver'];
    $rp_points = $_POST['rp_points'];
    
    $dp = array();
    $dp['driver'] = $rp_driver;
    $dp['points'] = $rp_points;
    $num = count($dp);
    for($i = 0; $i < $num; $i ++){
        $dp_driver = $dp['driver'][$i];
        $dp_points = $dp['points'][$i];
        
        require('Connections/formula1.php');
        @mysql_select_db($database_formula1) or die( "Unable to select database");
        $q = "INSERT INTO driverPoints (rp_race, rp_driver, rp_points) VALUES ('$rp_race', '$rp_driver', '$rp_points')";
        $r = mysql_query($q) or die(mysql_error());
    }
    
    echo "done, link to whatever here";
}?>
[/code]

yes, the users pick a team, but they stick with that team throughout the entire season and earn points based on driver, chassis, and engine performance.