php mysql using nested query

Question

I am trying to find if two runners have ever ran in the same race. The two runners are Peter Smith and Diane Peters.

$resultRaceType = mysqli_query($db,"SELECT  DISTINCT date,time FROM results where runner = 'Peter, Smith' ");   
while($row = mysqli_fetch_array( $resultRaceType )) 
{
    $resultRaceType1 = mysqli_query($db,"SELECT  * FROM results where date = ' " . $row['date'] . " ' and time = ' " . $row['time'] . " ' and runner = 'Diane, Peters'");   
    while($row1 = mysqli_fetch_array( $resultRaceType1 )) 
    {
        echo "<tr >";   
        echo "<td>"; 
        echo $row1['date'];
        echo " - " . $row1['time'];
        echo "</td>";   
        echo "<tr>";    
    }    
}

The above code works, but only if I limit the first select to LIMIT 50. So I can see that it is timing out. My table has over 100K rows. I know I am doing something wrong but cant see what it is. Thanks for any help you guy's can give me.

You should probably join the table on itself using the different conditions. — jeroen
– jeroen, Commented Apr 10, 2018 at 12:42
It's a bad idea to load a 100k rows table on dom.. You should consider server side pagination and fetch results in batches. — Tania Petsouka
– Tania Petsouka, Commented Apr 10, 2018 at 12:43
You are wide open for SQL injection. Since you're using mysqli, take advantage of prepared statements and bind_param. This will take care of any pesky quoting issues that may occur. Such as the extra spaces as where you're quoting your variables. — aynber
– aynber, Commented Apr 10, 2018 at 12:48
@aynber Depends... he might aswell not be vulnerable to SQL injection, we can't know that from his code. — xate
– xate, Commented Apr 10, 2018 at 13:14
This could be acheived using only one query with joins and ordering. You could try that. — Mathieu de Lorimier
– Mathieu de Lorimier, Commented Apr 10, 2018 at 13:15

wordragon · Accepted Answer · 2018-04-10 13:00:40Z

1

Try:

SELECT a.date, a.time FROM results a
    JOIN results b ON (a.date = b.date AND a.time = b.time)
    WHERE a.runner='Peter, Smith' AND b.runner='Diane, Peters';

edited Apr 10, 2018 at 13:00

answered Apr 10, 2018 at 12:43

wordragon

1,3579 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

php mysql using nested query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related