Running multiple loops based on mysql query

Question

As I'm learning PHP I'm making a personal episode tracker for tv shows. My issue right now, is that I don't know how to print out a "pretty list" separated by season. Right now every single episode of a series is printed with a loop from the mysql query. There's also a "watched" function in here that I need to use to check if a user has marked the episode in the loop.

How can I loop one season at a time instead of every single episode? I got the amount of seasons as and int in another "Series" table if that makes it any easyer.

// Get episodes from database
$list_sql = "SELECT * FROM episodes WHERE series_id = '".$_GET['id']."' ORDER BY `episodes`.`season` ASC, `episodes`.`season_episode` ASC LIMIT 2000";
$list_query = mysqli_query($con, $list_sql) or die(mysqli_errno($con));



while ($row = mysqli_fetch_assoc($list_query)) {

    // GET INFO ON WHEN EPISODE WAS VIEWED
    $when_watched_sql = "SELECT date_watched FROM watched WHERE episode_id = '".$row['id']."'";
    $when_watched_query = mysqli_query($con, $when_watched_sql);
    $when_watched_result = mysqli_fetch_assoc($when_watched_query);

// IS EPISODE WATCHED? If yes, make CHECKBOX CHECKED
    $watched_sql = "SELECT watched FROM watched WHERE episode_id = '".$row['id']."' AND watched = '1'";
    $watched = mysqli_query($con, $watched_sql);

    if(mysqli_num_rows($watched)>0) {


        // One row returned, episode is watched
        $watched_box = "checked";
    } else {
        // No rows returned, episode is not watched
        $watched_box = "";
    }


    print("Watched: <input type='checkbox' name='watched[]' value='".$row['id']."' $watched_box  />&nbsp; Season: ".$row['season']." Episode: ".$row['season_episode']." ".$row['name']." <br>");
}

I probably have quite a few wrong ways of doing things here too, I do enjoy some constructive hints and tips for learning how to do this all in a proper way.

Your query is open to sql injection.http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Loko, Mar 16 '15 at 09:39
Thank you for reminding me, I have used mysqli_real_escape_string throughout my script but seems that my late nights have made me forget this. — Messhias, Mar 16 '15 at 10:45

score 0 · Accepted Answer · answered Mar 16 '15 at 11:18

OFFTOPIC:

First of all,in your code you check when the episode is watched and after that you check if it's watched(according to your comments). Which doesnt make sense to me?

So I would recommend to use the 2 queries combined together:

$watched_sql = "SELECT watched,date_watched FROM watched WHERE episode_id = '".$row['id']."' AND watched = '1'";

If this wouldn't work in your case for whatever reason, I would still recommend to check the date watched after you're sure it has been watched:

if(mysqli_num_rows($watched)>0) {

    $when_watched_sql = "SELECT date_watched FROM watched WHERE episode_id = '".$row['id']."'";
    $when_watched_query = mysqli_query($con, $when_watched_sql);
    $when_watched_result = mysqli_fetch_assoc($when_watched_query);
    // One row returned, episode is watched
    $watched_box = "checked";
} else {
    // No rows returned, episode is not watched
    $watched_box = "";
}

ON TOPIC:

I would say you need to get the $watched_box as an array.I dont seem to understand you mysql table structure completely so I'm a bit off. I cant test this stuff out right now but I'd say you need to do it somewhat like this:

$i=0;
$watched_box=array();
while ($row = mysqli_fetch_assoc($list_query)) {

    // GET INFO ON WHEN EPISODE WAS VIEWED
$when_watched_sql = "SELECT date_watched FROM watched WHERE episode_id = '".$row['id']."'";
$when_watched_query = mysqli_query($con, $when_watched_sql);
$when_watched_result = mysqli_fetch_assoc($when_watched_query);

// IS EPISODE WATCHED? If yes, make CHECKBOX CHECKED
$watched_sql = "SELECT watched FROM watched WHERE episode_id = '".$row['id']."' AND watched = '1'";
$watched = mysqli_query($con, $watched_sql);

    if(mysqli_num_rows($watched)>0) {


        // One row returned, episode is watched
        $watched_box[$i] = "checked";
    } else {
        // No rows returned, episode is not watched
        $watched_box[$i] = "";
    }
    $i++;
    var_dump($i);

I am 99% sure this isn't the code that is going to work for you straight away since I dont completely get your database structure and I cant test this. You need to make some changes to this and this is the way you want to go I think. With a counter and an array. Try to var_dump($watched_box); after the while loop and see what it will show

Thanks for the input, you're completely right about the WHEN WATCHED thing, it's actually not used in the script I just kept the fetch there for when I get my episode list formatting up and running. Thank you, and that code makes alot more sense to me than my own. If you want I can provide the mysql structure and the full php script if this will help, because yeah, that code didn't work and as I'm a bit new i'm not quite sure how your code works, sorry about that. — Messhias, Mar 16 '15 at 11:42
@Messhias No problem. Let me know what happened when you used the **ON TOPIC** part. — Loko, Mar 16 '15 at 12:00

score 0 · Answer 2 · answered Mar 16 '15 at 12:32

With the assistance of @Loko I figured this out. My original issue was how to print one list per season with all episodes in that season, instead of one large list of just episodes.

My solution to this was a FOR loop, this looped through X amount of seasons based on how many seasons was set in the series information in the MySQL database.

    // Test new print loop (Thanks Loko)
?><form method="post" action="episode_list.php?id=<?php echo $series_id;?>"><?php
// Make a loop that iterates X amount of SEASONS based on mysql count result
$season = $season_count_array['seasons'];
// LOOP, start with season 1, if below $season (Amount of seasons), continue and add 1 to variable until loop reaches amount of seasons
for($seasons = 1; $seasons <= $season; $seasons++) {
    $season_episodes_sql = "SELECT * FROM episodes WHERE season = '".$seasons."' AND series_id = '".$_GET['id']."' ORDER BY season_episode";
    $season_episodes_query = mysqli_query($con, $season_episodes_sql);

    // Loop through episodes of chosen season
    while ($row = mysqli_fetch_assoc($season_episodes_query)) {

        // Is episode watched?
        $watched_sql = "SELECT watched FROM watched WHERE episode_id = '".$row['id']."' AND watched = '1'";
        $watched = mysqli_query($con, $watched_sql);

        if(mysqli_num_rows($watched)>0) {

            // One row returned, episode is watched
            $watched_box = "checked";
        } else {
            // No rows returned, episode is not watched
            $watched_box = "";
        }

        // Allright, let's print
        print("Sett: <input type='checkbox' name='watched[]' value='".$row['id']."' $watched_box  />&nbsp; Sesong: ".$row['season']." Episode: ".$row['season_episode']." ".$row['name']." <br>");

    }
    // Here I can add what I want as a spacer between printed seasons, just a break, for now.
    echo '<br>';

}

Yeah basically what I said with the counter in the while and the array, you can just use a for loop. Good luck with your project! — Loko, Mar 16 '15 at 12:35

Running multiple loops based on mysql query

2 Answers2