Applying functions to every line of a database...

[Seaholme]

Hey all,

 

This isn't so much a problem with code I already have as a request for some help as to what code to use in the first place!

 

What I have are two tables, one with information about Events -- the ID of the event and the prize you get if you win it -- and another with information about the individuals entered into this event -- their ID, the ID of the event they were entered into, and their score.

 

What I want to do is create a script which will allow me to take every single row of the table with the Events information in it and do the following:

1. gather all the information relating to a Event

2. gather the information about the individuals entered in an Event

3. list the individuals entered in the event by their score

4. award prizes to those with the 1st, 2nd and 3rd highest scores

5. delete the event

 

My problem is not so much how to complete stages 1-5, but more how to make a script which will run through the database of Events and complete stages 1-5 on each event individually. The only way I know how to do it would be for me to manually enter all the event IDs and do it for each event separately, but I'm sure there must be some sort of PHP which will complete these events for each row in turn.

 

Can anybody help me work out how to make this happen for every line of the database? I don't just want to gather information about each line, but to have functions 1-4 happen on it!

 

Thanks in advance for any ideas!

[Kevin.Arvixe]

The only way I know how to do it would be for me to manually enter all the event IDs and do it for each event separately, but I'm sure there must be some sort of PHP which will complete these events for each row in turn.

 

The only way I know how to do it would be for me to manually enter all the event IDs and do it for each event separately, but I'm sure there must be some sort of PHP which will complete these events for each row in turn.

 

for each

 

foreach();

 

http://us.php.net/manual/en/control-structures.foreach.php

 

Very well said You actually SAID the name of the function that will save you IN the post haha

 

To elaborate... A foreach statement runs through each of the members of an array. Mysql returns results in an array. Happiness achieved.

[Seaholme]

Thanks!

 

I'm trying to use it, but I'm still quite new to PHP and am finding this function extremely counterintuitive. This is what I have so far:

 

<?php
// Make a MySQL Connection
.....

$date = date("d/m/y");

$result = mysql_query("SELECT * FROM training
              WHERE rundate='$date') or die(mysql_error());
while($row = mysql_fetch_array( $result )) {

foreach ($row as $value) {

  $entrants = mysql_query("SELECT * FROM trainingentrants
              WHERE eventid='$row[\'id\']' ORDER BY entrantscore ASC") or die(mysql_error());
              while($entrantinfo = mysql_fetch_array( $entrants )) {
              
              $first = $row['gold'];
              $entrantid = $entrantinfo['entrantid'];
              
              mysql_query("UPDATE dogs SET TFA = TFA + '10' WHERE id = '$entrantid'");
              
              }
              

}

?>

 

I can't work out what $value should be! Which part should I call "value"? :/

 

[wildteen88]

1, 2, and 4 can be achieved by MySQL from a single query, look into using SQL Joins and the ORDER BY clause

3 is easily done by loop through the results and outputting the information you want from your query.

5 is again another simple query which deletes the event from the events table, eg DELETE FROM events WHERE event_id = EVENT_ID_HERE

[Seaholme]

Thanks so much for mentioning MySQL joins, I hadn't heard of them before.

 

I hope you guys don't mind, but I'm finding it quite difficult to stick all this stuff together -- this is my SQL join, it's giving me the following error

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'trainingentrants, entrantsWHERE trainingentrants.eventid = training.idGROUP BY t' at line 1

 

... and I'm not sure why, as I'm not familiar with this function at all! I followed a tutorial I found to piece it together.

 

// Make a MySQL Connection
// Construct our join query
$query = "SELECT trainingentrants.entrantscore, trainingentrants.entrantid, trainingentrants.eventid, training.gold, training.silver, training.run AS entrantstuff".
"FROM trainingentrants, entrants".
"WHERE trainingentrants.eventid = training.id".
"GROUP BY traininentrants.entrantscore";

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
echo $row['entrantscore']. " - ". $row['entrantid'];
echo "<br />";}

 

Can anybody help me sort this out? :[

[wildteen88]

Its to do with how you're concatenating your strings.

$query = "SELECT trainingentrants.entrantscore, trainingentrants.entrantid, trainingentrants.eventid, training.gold, training.silver, training.run AS entrantstuff".
"FROM trainingentrants, entrants".
"WHERE trainingentrants.eventid = training.id".
"GROUP BY traininentrants.entrantscore";

You need to add a space before FROM, WHERE and GROUP BY