CODEWITHSUNDEEP
phpmysql
Hugo
Hugo

Reputation: 15

PHP Mysql select is only showing one row

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.

Upvotes: 1

Views: 1379

Answers (2)

sandino
sandino

Reputation: 3842

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

Upvotes: 1

Mureinik
Mureinik

Reputation: 311018

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()) {

Upvotes: 1

Related Questions