Is there any way to write this code using prepared statements?

$sql = "SELECT * 
    FROM exercises 
    WHERE exercise_id IN (
        SELECT DISTINCT e.exercise_id 
        FROM users u,users_subjects us, exercises e 
        WHERE u.username='".$_SESSION['username']."' AND us.user_id_fk=u.id AND e.subjects=us.subject_id_fk
    );";
$result = $conn->query($sql);

I'm trying this way, but with the sencente ‘IN’, I’m not sure how to do it:

$stmt = $mysqli->prepare("SELECT * FROM exercises where exercise_id in (select distinct e.exercise_id from users u,users_subjects us, exercises e where u.username='".$_SESSION['username']."' and us.user_id_fk=u.id and e.subjects=us.subject_id_fk");
$stmt->bind_param("" );
$stmt->execute();
$stmt->close();

1 Answers

0
jtylerm On
$username = $_SESSION['username'];

// use ?'s in prepare
$stmt = $mysqli->prepare("SELECT * FROM exercises where exercise_id in (select distinct e.exercise_id from users u,users_subjects us, exercises e where u.username=? and us.user_id_fk=u.id and e.subjects=us.subject_id_fk");

// then pass $username to bind_param()
$stmt->bind_param("s", $username);

Then the rest of your code should work. I'd also recommend testing your stmt commands for error handling. They all return booleans.

// use ?'s in prepare
$stmt = $mysqli->prepare("SELECT * FROM exercises where exercise_id in (select distinct e.exercise_id from users u,users_subjects us, exercises e where u.username='?' and us.user_id_fk=u.id and e.subjects=us.subject_id_fk");

if(!$stmt) echo "prepare failed: " . mysqli_error($conn);

Same can be done for bind_param() and execute().