Using query function in loop php

Question

Can I use a query in PHP for loop to get right rows from DB? I wrote this code and it's working, but I don't know if this is correct.

if(isset($_GET['group'])){
    $group = $_GET['group'];
    $group_exp = explode('/', $group);
    $dayofweek = array('Poniedziałek', 'Wtorek', 'Środa', 'Czwartek', 'Piątek');
    
    echo '<table>';
    for($i=0; $i<5; $i++){
        $sql_activities = "SELECT teacher.first_name, teacher.last_name, activities.name, room.no_room, reservation.date, reservation.date_end, groups.profile, groups.semester, groups.type, groups.number 
            FROM reservation 
            JOIN room ON room.id_room = reservation.id_room 
           JOIN teacher ON reservation.id_teacher = teacher.id_teacher 
            JOIN activities ON reservation.id_activities = activities.id_activities 
            JOIN groups ON groups.id_group = reservation.id_group 
            WHERE (WEEKDAY(reservation.date) = '$i') AND (groups.profile = '$group_exp[0]' AND groups.semester = '$group_exp[1]') AND (reservation.date > '$mon' AND reservation.date < '$fri') 
            ORDER BY reservation.date";
        $result = $conn -> query($sql_activities);
        echo '<tr>';
        echo '<td>'.$dayofweek[$i].'</td>';
        while($row = $result -> fetch_assoc()){
            echo '<td>'.$row['name'].'<br>'.$row['first_name'].' '.$row['last_name'].'<br>'.'Sala '.$row['no_room'].'<br>'.'od godz. '.date("H:i", strtotime($row['date'])).'-'.date("H:i", strtotime($row['date_end'])).'</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}

You are open to SQL injections, parameterize and use prepared statements. — user3783243, Dec 24 '20 at 23:06
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 24 '20 at 23:06
Yes, you can. But you should still use a prepared statement instead of substituting variables into the query. You can prepare the statement and bind the parameters once before the loop. Then when you execute it, it will use the current value of the variables. — Barmar, Dec 24 '20 at 23:06
@user3783243 To be equivalent it should be `between 0 and 4`. — Barmar, Dec 24 '20 at 23:11
But if you do it that way, you need to include `WEEKDAY(reservation.date)` in the `SELECT` list, so that when looping over the results you can start a new row whenever the weekday changes. — Barmar, Dec 24 '20 at 23:15
And also change it to `ORDER BY WEEKDAY(reservation.date), reservation.date` — Barmar, Dec 24 '20 at 23:15
Application is still in progress and I know it is are open to SQL Injections, anyway thanks for tips. — Marcin Łukasiewicz, Dec 24 '20 at 23:47

score 1 · Accepted Answer · edited Dec 25 '20 at 11:42

If I understand correctly you could maybe do something like this instead?

if( isset($_GET['group']) ){
    $group = $_GET['group'];
    $group_exp = explode('/', $group);
    $dayofweek = array('Poniedziałek', 'Wtorek', 'Środa', 'Czwartek', 'Piątek');
    
    
    $sql_activities = "
        SELECT 
            teacher.first_name, teacher.last_name,
            activities.name,
            room.no_room,
            reservation.date,   reservation.date_end,
            groups.profile,     groups.semester,      groups.type, groups.number,
            WEEKDAY(reservation.date) AS dayofweek
        FROM reservation 
            JOIN room       ON room.id_room              = reservation.id_room 
            JOIN teacher    ON reservation.id_teacher    = teacher.id_teacher 
            JOIN activities ON reservation.id_activities = activities.id_activities 
            JOIN groups     ON groups.id_group           = reservation.id_group 
        WHERE ( WEEKDAY(reservation.date) BETWEEN 0 AND 5 )
            AND ( groups.profile     = ? 
                AND groups.semester  = ? ) 
            AND ( reservation.date   > ? 
                AND reservation.date < ? ) 
        ORDER BY reservation.date
    ";
    $result = $conn->prepare($sql_activities);
    $result->bind_param("ssss", $group_exp[0], $group_exp[1], $mon, $fri);
    $result->execute();
    $result = $result->get_result();
    
    $last_day = FALSE;
    echo '<table>';

    while( $row = $result->fetch_assoc() ){
        if( $last_day == FALSE || $last_day <> $row['dayofweek'] ){
            echo ( $last_day ) ? '</tr><tr>' : '<tr>';
            $last_day = $row['dayofweek'];
            echo "<td>{$dayofweek[$last_day]}</td>";
        }
        echo "<td>
            {$row['name']}<br>
            {$row['first_name']} {$row['last_name']}<br>
            Sala {$row['no_room']}<br>
            od godz ".
            date("H:i", strtotime($row['date'])).'-'.
            date("H:i", strtotime($row['date_end']))
            .'</td>';
    }
    echo '</tr></table>';
}

Using query function in loop php

1 Answers1