Inserting data into 3 tables at same time.

[knoxinator]

Greetings, I have this website http://www.heresourwedding.com

It is a poker league website just sitting on a spare domain at the moment.

Anyway each day after a poker league game is played I will add scores into the leaderboards.

For example if the game was played at Durack Tavern I would put the name and score in the Durack Tavern page and then the overall page and then the player of the week page.

 

What I am trying to figure out is how to put the scores in just the Durack Tavern page and the website automatically puts the information into the other 2 pages.

 

If you want to have a look go to leaderboard/Durack tavern, then click on the red spade at bottom of page.

Username is: user5

Password is : password

 

Here is the code for durplayers.php

 

 

<?php

session_start();

if(!isset($_SESSION["sess_user"])){

header("location:index.php");

}

include "dbconnect.php";

if(isset($_POST["submit"]))

{

if(!empty($_POST['username']) && !empty($_POST['points']))

{

$user = $_POST['username'];

$points = $_POST['points'];

$sql = "SELECT username,points FROM durack WHERE username='".$user."'";

$result = mysqli_query($conn, $sql);

$numrows = mysqli_num_rows($result);

 

//update the points

if($numrows!=0)

{

while($row=mysqli_fetch_assoc($result))

{

$username=$row['username'];

$oldpoints=$row['points'];

}

 

$totalPoints = $oldpoints + $points;

 

$sql1 = "UPDATE durack SET points='".$totalPoints."' WHERE username='".$user."'";

if(mysqli_query($conn, $sql1))

{

header("Location: AddDurack.php?added=Points");

 

}

}

// Add player and Points

else

{

$sql2 = "INSERT INTO durack(`username`,`points`) values ('$user',$points)";

if(mysqli_query($conn, $sql2))

{

header("Location: AddDurack.php?added=PlayerAndPoints");

 

}

 

}

 

}

else

{

header("Location: AddDurack.php?added=mandatory");

 

}

mysqli_close($conn);

}

?>

 

 

The Durack Tavern table is called Durack, the player of the week table is called potw and the overall table is called players if that helps.

I tried duplicating the INSERT command for potw and players but I just got errors.

Cheers.

[mac_gyver]

1) you need to review the last post in your previous thread on this forum.

 

2) you should NOT have separate database tables for each location/venue. you should have one result table with a column that holds a location id that the data belongs to, with a separate table that defines the locations and assigns the id value via an autoincrement column.

 

3) once you have the raw data stored properly, you can query for anything you need anytime you need it.

[mac_gyver]

further to the above, the data you 'collect' each week is what all other results are 'derived' from. by storing the source data, you can query for any of the derived information. you don't need to store the derived information itself, as this results in redundant and wasteful data storage.

 

to query for the top player in any week, you just query the source data to get the highest score for any date/week. to query for the overall leaders, just group by the player_id, sum() the score values, order by the sum'ed score, and retrieve the top n records.

 

---

 

whatever you do, you need to fix the security in the code you posted above.

 

the login check needs an exit; statement after the header() redirect to prevent the rest of the code from running. anyone, logged in or not, can just ignore the redirect and post anything they want to your code and your code will process the submitted data.

 

if that particulate session variable is the same one that is set when anyone logs into your site, anyone who is logged in can submit data to that code, not just you as the administrator of the site. you need to enforce permissions to limit who can submit data. currently, a logged in user can easily add any amount he wants to his or anyone else's points and because you are not storing the raw data as separate rows in a results table, you don't even know if this is happening.

 

you are not doing anything to prevent sql injection, so until you fix the above two items, so that only you can submit data, anyone can mess with your database tables any way they want.

 

lastly, if you were doing something where you need to maintain a point value for each individual user, you don't need all that code. you can use one INSERT ... ON DUPLICATE KEY UPDATE query to replace the three individual select, update, and insert queries.