Query works in phpmyadmin but same query won't return in PHP script-CodePudding

This is NOT a duplicate. None of the already existing threads have the same problem as me.

I have a database that stores athlete performances. It contains sessions, each session has sets, each set has "tables" (such as 4x100m, 12x50m and so on), and each table has times. I also have a table for athletes. Each athlete has an ID, each time links with the athlete through the AthleteID. Every session, set, timetable and time also have each unique IDs, used to link them with each other.

I want to make it so that when passing a session ID, it will return all the athletes that have at least 1 time in that session. I made a page that gets requests and the session ID is passed as GET search data (will make it POST later on). The request system works fine, but the problem is in the query. To do it I used inner joins to connect each table. This is my query (it is not the fastest method, but that's for another thread):

$q = "SET @evID = " . $method['sessID'] . ";";
$q .= "SELECT `athletes`.* FROM `events` 
INNER JOIN `sets` ON `sets`.`EventID` = `events`.`EventID`
INNER JOIN `timetables` ON `timetables`.`SetID` = `sets`.`SetID`
INNER JOIN `times` ON `times`.`TableID` = `timetables`.`TableID`
INNER JOIN `athletes` ON `athletes`.`ID` = `times`.`AthleteID`
WHERE `events`.`EventID` = @evID
AND `times`.`TimeID` IN(
    SELECT MIN(`TimeID`)
    FROM `times`
    WHERE `TableID` IN(
        SELECT `TableID`
        FROM `timetables`
        WHERE `SetID` IN(
            SELECT `SetID`
            FROM `sets`
            WHERE `EventID` = @evID
        )
    )
    GROUP BY `AthleteID`
)";

Every single time I ran that in phpmyadmin it returned all the athletes, and the data was correct. However, when I run it in my script, the query value is false (such as if there is an error). I tried debugging like this:

$r = $db -> query($q);

var_dump($q);
var_dump($r);
var_dump($db->error);

The query is returned just fine (only difference is lack of newline characters), and when I copy what's returned in phpmyadmin the data is just the same. The rest however:

bool(false)

string(228) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT `athletes`.* FROM `events` INNER JOIN `sets` ON `sets`.`EventID` = `...' at line 1"

Other users with the same problem have not really gone that far to find out if they're wrong, but I have. This post is not a duplicate, and I didn't find any solutions online. Could this be a problem with the amount of queries in a single string? (There is one for setting @evID and one for the actual selection). Please explain the solution and methods kindly as I'm only 13 and still learning...

CodePudding user response：

As @NigelRen has suggested, please use parameterized prepared statement.

Assuming that

$sessionid is storing the value for EventID, and assuming that this variable is of integer type; and
$conn is the connection

Then for Mysqli, you can use:

//$q = "SET @evID = " . $method['sessID'] . ";";

$sql = "SELECT `athletes`.* FROM `events` 
INNER JOIN `sets` ON `sets`.`EventID` = `events`.`EventID`
INNER JOIN `timetables` ON `timetables`.`SetID` = `sets`.`SetID`
INNER JOIN `times` ON `times`.`TableID` = `timetables`.`TableID`
INNER JOIN `athletes` ON `athletes`.`ID` = `times`.`AthleteID`
WHERE `events`.`EventID` = ?
AND `times`.`TimeID` IN(
    SELECT MIN(`TimeID`)
    FROM `times`
    WHERE `TableID` IN(
        SELECT `TableID`
        FROM `timetables`
        WHERE `SetID` IN(
            SELECT `SetID`
            FROM `sets`
            WHERE `EventID` = ?
        )
    )
    GROUP BY `AthleteID`
)";

$stmt = $conn->prepare($sql); 
$stmt->bind_param("ii", $sessionid, $sessionid);
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$row = $result->fetch_assoc(); // fetch data  

// do other things you want , such as echo $row['fieldname1'];