[SOLVED] problematic querry

[Derleek]

$query = "SELECT placed FROM race_Racer WHERE race_id = '$race_id' && racer_id = '$rid'";
		$result = mysql_query($query) or die(mysql_error());

 

for some reason this times out when i execute it. any syntax errors in this?

[The Little Guy]

Try this:

 

$query = "SELECT placed FROM race_Racer WHERE race_id = '$race_id' AND racer_id = '$rid'";

[Derleek]

it sends me into an infinite loop for some reason.. hmm

[The Little Guy]

could you show more of the code?

[Derleek]

sure...

 

<?php
if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0)
{
	dbConnect('thethrgu_moto');
	//retrieves file name and size form the form
	$fileName = $_FILES['userfile']['name'];
	$content = file($fileName);
	$race_id = $_POST['race_id'];
	// count how many lines in the file
	$numLines = count($content);
	$rstore = array();//array to store the $line variable
	$count = 0;
	for ($i = 0; $i < $numLines; $i++) 
	{
		$count++;
		$line = trim($content[$count]);
	    $rstore[$i] = trim($line);
		$sql = "UPDATE race_Racer SET placed='$count' WHERE racer_ID='$rstore[$count]' && race_ID ='$race_id'";
		mysql_query($sql) or die(mysql_error());

	}
	$query = "SELECT * FROM benchracers WHERE race_id = '$race_id'"; 
	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$rid = $row['racer_ID'];
		$query = "SELECT placed FROM race_Racer WHERE race_id = '$race_id' AND racer_id = '$rid'";
		$result = mysql_query($query) or die(mysql_error());
	}

}
?>

 

Ok, i have created several tables that i am using to make a game for a moto cross fantasy league kinda thingy.  I take the order in which the moto cross riders (Racer_ID) finish. and store it into a table along with the race_ID.  What i am trying to do here is match up a users picks so i can see if they guessed anyone in the top 10.  Their picks are stored in the table benchracers.

 

uhh... let me know if anything else needs to be made clear.  I have several scripts that are going on here.

 

everything works fine except for the end of the script where i am trying to match the users choices with the actual race results.  hence: $query = "SELECT placed FROM race_Racer WHERE race_id = '$race_id' AND racer_id = '$rid'";  which is supposed to point to the position a given rider finished in (which the user chose). 

[Derleek]

what i don't understand is that when i take out the query it loops fine, only once for how ever many entries there are in the table.

 

this is the error I get when i echo $query

 

'SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = '24'SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id = ''SELECT placed FROM race_Racer WHERE race_id = '1' AND racer_id =" etc...

 

it seems like there might be some issue with the quotes or something

[Barand]

If you have the structure I suggested for the users' selection (http://www.phpfreaks.com/forums/index.php/topic,198355.msg895759.html#msg895759)

 

CREATE TABLE A_user_picks (
pickID int not null auto_increment primary key,
UserID  int,
raceID  int,
riderID int);

INSERT INTO A_user_picks (userID,raceID,riderID) VALUES
(1 ,         10 ,          2 ),
(1 ,         10 ,          4 ),
(1 ,         10 ,          6 ),
(1 ,         10 ,          8 ),
(1 ,         10 ,         10 ),
(2 ,         10 ,          2 ),
(2 ,         10 ,          4 ),
(2 ,         10 ,          1 ),
(2 ,         10 ,          3 ),
(2 ,         10 ,          5 );

 

and this for the race results

CREATE TABLE A_results (
id int not null auto_increment primary key,
raceID int,
riderID int,
place  INT);

INSERT INTO A_results (raceID, riderID, place) VALUES
(10 , 1, 1),
(10 , 3, 2),
(10 , 5, 3),
(10 , 7, 4),
(10 , 8, 5),
(20 , 6, 1),
(20 , 7, 2),
(20 , 8, 3),
(20 , 9, 4),
(20 , 10, 5);

 

then to match selections with actual results

 

mysql> SELECT p.userID, COUNT(*) as total
    -> FROM A_user_picks p
    -> INNER JOIN A_results r ON p.raceID = r.raceID AND p.riderID = r.riderID
    -> GROUP BY p.userID
    -> ORDER BY total DESC;
+--------+-------+
| userID | total |
+--------+-------+
|      2 |     3 |
|      1 |     1 |
+--------+-------+

[Barand]

or, if you want the riderIDs too

 

mysql> SELECT p.userID, COUNT(*) as total, GROUP_CONCAT(p.riderID SEPARATOR ', ') as riders
    -> FROM A_user_picks p
    -> INNER JOIN A_results r ON p.raceID = r.raceID AND p.riderID = r.riderID
    -> GROUP BY p.userID
    -> ORDER BY total DESC;
+--------+-------+---------+
| userID | total | riders  |
+--------+-------+---------+
|      2 |     3 | 1, 3, 5 |
|      1 |     1 | 8       |
+--------+-------+---------+

[Derleek]

i figured out it was a capitalization issue (i think)

 

either way i just ended up redoing the entire thing and it worked.

 

Is MySQL caps sensitive?

[Psycho]

This was the problem:

<?php

$query = "SELECT * FROM benchracers WHERE race_id = '$race_id'"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$rid = $row['racer_ID'];
$query = "SELECT placed FROM race_Racer WHERE race_id = '$race_id' AND racer_id = '$rid'";
$result = mysql_query($query) or die(mysql_error());
}

?>

 

You should NEVER do looping queries like that. It is extremely taxing on the server. What really doesn't make sense is you didn't do anything with the results. In any event, you can do the same thing with a single query:

 

SELECT *
FROM benchracers br
JOIN race_Racer rr
     ON br.race_id = rr.race_id
     AND br.racer_id = rr.racer_id
WHERE br.race_id = '$race_id'