Need help creating a loop... Will pay $10.

Hello, I really need help making this loop. It's way over my head, my brain crashes every time I think about it. There are multiple loops needed I think.

OK, let me try to explain this as best as I can. I'll change the table names and take off unnecessary fields etc to make it more understandable for you.



The tables:

Table WINNERS with fields ID, WINNERID, ROUNDNR
example info:
1,130,5
2,340,6
3,340,6
4,130,6


Table NAMES with fields ID,NAME,USER
example info:
130,John,john22
340,Mac,admin

Table FINAL with fields ID,THENAME,USERNAME,ROUNDID, WINS


Connections between tables:

Table WINNERS field WINNERID is same as table NAMES field ID.


The mission:

Take everyones ID from table WINNERS (from field WINNERID), count the wins (as in first example WINNERID 340 has two wins in the given $round), select their matching info from table NAMES (WINNERID - ID), from NAMES table take from the previously selected winners info for the FINAL table. The fields needed to be taken and inserted to table FINAL:

THENAME = field NAME from table NAMES
USERNAME = field USER from table NAMES
ROUNDID = *read the bottom, will be a variable like $round*, but is the same as table WINNERS field ROUNDNR
WINS = count how many times the person won, and insert to the table. In the example WINNERID 340 won two times, so the number to be submitted to the WINS field would be 2.

IMPORTANT NOTE: ROUNDID/ROUNDNR will be given to the script by me. It's a variable... One ROUNDID/ROUNDNR will be used at a time. So if I give the script 5 as ROUNDID/ROUNDNR it will only select winners with ROUNDID 5 from the WINNERS table etc.


Example of completed task (used the info I put under the tables as examples) The ROUNDID/ROUNDNR to run the script for was 5 and 6:

Table FINAL with fields ID,THENAME,USERNAME,ROUNDID, WINS.
Would contain the following info:
ID, THENAME, USERNAME, ROUNDID, WINS
-------------------------------------------
1,John,john22,5,1
2,Mac,admin,6,2
3,John,john22,6,1


I will throw in $10 for the person who can make it. Pretty impossible for me.

 

If you're doing a count of all the wins, which ROUNDID should be put into the final table? There could potentially be multiple for a single user...

 

$row = mysql_fetch_assoc(mysql_query("SELECT count(`WINNERS`.`WINNERID`) AS Total, `WINNERS`.`WINNERID`, `NAMES`.`ID`, `NAMES`.`NAME`, `NAMES`.`USER` FROM `NAMES`, `WINNERS` WHERE `WINNERS`.`ROUNDNR` = '" . $rooundid/nr . "' AND `NAMES`.`ID` = `WINNERS`.`WINNERID`");
mysql_query("INSERT INTO `FINAL` (`THENAME`, `USERNAME`, `ROUNDID`, `WINS`) VALUES('" . $row['NAME'] . "', '" . $row['USER'] . "','" . $rooundid/nr . "', '" . $row['Total'] . "'");

Code (markup):

make sure you set $rooundid/nr to be the roundid/nr that you specify & remember to run it through some sort of cleaning to stop sql injections

 

The ROUNDID will be specified by me.

It's an admin file, so only me will be having access to it. I will test the code and get back to you.

 

Parse error: syntax error, unexpected ';'

 

$row = mysql_fetch_assoc(mysql_query("SELECT count(`WINNERS`.`WINNERID`) AS Total, `WINNERS`.`WINNERID`, `NAMES`.`ID`, `NAMES`.`NAME`, `NAMES`.`USER` FROM `NAMES`, `WINNERS` WHERE `WINNERS`.`ROUNDNR` = '" . $rooundid/nr . "' AND `NAMES`.`ID` = `WINNERS`.`WINNERID`");
mysql_query("INSERT INTO `FINAL` (`THENAME`, `USERNAME`, `ROUNDID`, `WINS`) VALUES('" . $row['NAME'] . "', '" . $row['USER'] . "','" . $rooundid/nr . "', '" . $row['Total'] . "')");

Code (markup):

My Apologies.

 

$sql = mysql_query("SELECT `NAMES`.`NAME` , `NAMES`.`USER` , COUNT( `WINNERS`.`WINNERID` ) , `WINNERS`.`ROUNDNR` FROM `NAMES` , `WINNERS` WHERE `WINNERS`.`WINNERID` = `NAMES`.`ID` GROUP BY `WINNERS`.`ROUNDNR`");

while ($row = mysql_fetch_array($sql))
{
$name = $row['NAME'];
$user = $row['USER'];
$wins = $row[COUNT(`WINNERS`.`WINNERID`)];
$round = $row['ROUNDNR'];
mysql_query("INSERT INTO FINAL (THENAME, THEUSER, ROUNDID, WINS) VALUES ('$name', '$user', '$wins', '$round')");
}

PHP:

 

$dbuser="username";
$dbpass="password";
$dbname="mydata";  //the name of the database
$chandle = mysql_connect("localhost", $dbuser, $dbpass) 
    or die("Connection Failure to Database");
mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found. " . $dbuser);



$query1="select * from winners "; 
$result = mysql_db_query($dbname, $query1) or die("Failed Query of " . $query1);  //do the query
while ($row = mysql_fetch_assoc($result)) {

{
  $i=0;
$query2="select * from names where id=".$row['winnerid']; 
$result2 = mysql_db_query($dbname, $query2) or die("Failed Query of " . $query2); 
$thisrow=mysql_fetch_row($result);
 

$query3="select * from final where thename='".$thisrow['name']."'"; 
$result3 = mysql_db_query($dbname, $query3) or die("Failed Query of " . $query3); 
$thisrow2=mysql_fetch_row($result);
if ($thisrow2)  //if the results of the query are not null
{
$wins=thisrow2["wins"];
$wins++;
$query3="update final set wins="$wins."where thename='".$thisrow['thename']."' and roundid=".$row['roundnr']; 
$result3 = mysql_db_query($dbname, $query3) or die("Failed Query of " . $query3); 
}
else
{
$query3="insert into  final (id,thename,username,roundid,wins) values($i,$thisrow["name"],$thisrow["user"],1)"; 
$result3 = mysql_db_query($dbname, $query3) or die("Failed Query of " . $query3); 
}


echo <br>";  //put a break after each database entry
}

PHP:

 

Hi,

SQL is not procedural language, so that you need is one line query!
You didn't provide table structure ( I mean CREATE TABLE script ) , but I'll make these and will PM you when ready, or if you wish contact me via Skype in my profile.

Regards
Edit: SQL Query Code,example and info sent via PM.