Jump to content

Archived

This topic is now archived and is closed to further replies.

jktress

updating multiple rows in a single column

Recommended Posts

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?

Share this post


Link to post
Share on other sites
Are you using a mysql database? have you set up any tables yet? If so, what tables and structure have you used so far?

Share this post


Link to post
Share on other sites
[!--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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.