PHP Mysql select is only showing one row

Question

I am currently trying to make a page of a TV series. Somehow the page only shows one row for seasons, which are 5 in my database.

$sql = "SELECT * FROM `shows` WHERE url='".dburl($_GET["url"])."'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {

    // General info about the TV show here

        $sql = "SELECT * FROM seasons WHERE `show`='".$row["id"]."' ORDER BY number ASC";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
            while($seasons = $result->fetch_assoc()) {

            // The seasons

                $sql= "SELECT * FROM episodes WHERE `show`='".$row["id"]."' AND `season`='".$seasons["number"]."' ORDER BY number DESC";
                $result = $conn->query($sql);

                if ($result->num_rows > 0) {
                    while($episodes = $result->fetch_assoc()) {

                        // The episodes, sorted by season

                    }
                }

            }
        }

    }
}

Is there any change I overlooked something? The episodes part works just perfectly fine, it shows all the episodes that are in a season.

Please this code is very very bad, try not to do queries inside queries and inside queries and loops like you are doing, that is real poor performance, If you are getting information from different tables that are related by some field use JOIN that is a native SQL operator dev.mysql.com/doc/refman/5.0/en/join.html and please try not to use mysql but mysqli and prepared queries php.net/manual/en/mysqli-stmt.bind-param.php your queries are easy hackeable as you are using a url parameter without proper validation — sandino
– sandino, Commented Jul 18, 2015 at 5:16

sandino · Accepted Answer · 2015-07-18 21:43:08Z

I suggest you to get all the data in one mysql query, use proper variable escaping and the code will be simpler and more readable than the one you got. Then maybe you are gonna make fewer mistakes but having better coding:

$mysqli = new mysqli("localhost", "my_user", "my_password", "my_db");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

  $sql = "SELECT * FROM `shows` sh
         JOIN `seasons` se.show ON sh.id
         JOIN `episodes` e ON e.show = se.id AND e.season = e.number
         WHERE url=?
         ORDER BY se.number, e.number";

  /* Prepare statement */
  $stmt = $conn->prepare($sql);
  if($stmt === false) {
    trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->errno . ' ' .$conn->error, E_USER_ERROR);
  }

   /* Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
   $stmt->bind_param('s', dburl($_GET["url"]));

  /* Execute statement */
  $stmt->execute();

  /* Fetch result to array */
  $res = $stmt->get_result();
  while($row = $res->fetch_array(MYSQLI_ASSOC)) {
     array_push($a_data, $row);
  }

  /* close statement */
  $stmt->close();
}

/* close connection */
$mysqli->close();

Justification or explanation:

Never concatenate or interpolate data in SQL

Never build up a string of SQL that includes user data, either by concatenation:

$sql = "SELECT * FROM users WHERE username = '" . $username . "';";

or interpolation, which is essentially the same:

$sql = "SELECT * FROM users WHERE username = '$username';";

If '$username' has come from an untrusted source (and you must assume it has, since you cannot easily see that in source code), it could contain characters such as ' that will allow an attacker to execute very different queries than the one intended, including deleting your entire database etc. Using prepared statements and bound parameters is a much better solution. PHP's mysqli and PDO functionality includes this feature (see below).

From OWASP: PHP Security Sheet

Mureinik · Accepted Answer · 2015-07-18 05:19:00Z

1

You are overwriting the $result variable used by the outer loop. Just use a new variable:

$sql = "SELECT * FROM seasons WHERE `show`='".$row["id"]."' ORDER BY number ASC";
$seasons_result = $conn->query($sql);

if ($seasons_result->num_rows > 0) {
    while($seasons = $seasons_result->fetch_assoc()) {

answered Jul 18, 2015 at 5:19

Mureinik

316k54 gold badges400 silver badges405 bronze badges

2 Comments

sandino Over a year ago

Altough it answer the question and that is the real problem I suggest to penalize this kind of bad coding so no other copy this kind of idea. One mysql query is needed to get all the information then just looping through an array not nested looping with queries, I do not even mention he is not escaping variables, this is really bad programming

sandino Over a year ago

It is not personal Hugo I just want you to know how the best approach will be and I belive you should learn the best practices first so you do not get used to bad practices

Collectives™ on Stack Overflow

PHP Mysql select is only showing one row

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related