J Doe J Doe - 4 months ago 4
SQL Question

Php code not displaying the data right from database

I'm working on a movie database project and I got my database the way it needs to be, but having issues with the front-end part of it. I finally got it to show all the information and have it be clickable, but I'm pretty sure this isn't the way to do it.

I'm having trouble displaying the genres. I either can groupconcat and display all of them like that, but then I can't have them be links or I could do them individual and have the links, but not get them all. I finally got them all, but then when I try it on the phone or tablet I only see the last genre and not the others. So I did another database and pull request to get all the data over again, but I'm pretty sure that isn't right.

<?php
require '../database.php';
$Code = null;
if ( !empty($_GET['Code'])) {
$Code = $_REQUEST['Code'];
}

if ( null==$Code ) {
header("Location: index.php");
} else {
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT Movies.Code, Title, Plot, Movies.Type, Movies.Category, Image, Score, Rated, Alt, Status, YearReleased, Duration, SUBSTRING(Duration, 1, CHAR_LENGTH(Duration) - 3) AS Duration2, TotalEps, Types.code as tcode, Types.Type as ttype, Categories.Code as ccode, Categories.Category as ca, Ratings.Code as rc, Ratings.Rating as rr, Genre, GenreCode FROM Movies, Types, Categories, Ratings, MovieGenres, Genres WHERE Movies.Type=Types.Code AND Movies.Rated=Ratings.Code AND Movies.Category=Categories.Code AND Movies.Code=MovieGenres.MovieCode AND MovieGenres.GenreCode=Genres.Code AND Movies.Code = ?";
$q = $pdo->prepare($sql);
$q->execute(array($Code));
$data = $q->fetch(PDO::FETCH_ASSOC);
Database::disconnect();
}
?>

<!DOCTYPE html>
<html lang="en">

<head>

<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="description" content="">
<meta name="author" content="">

<title>MovieDB - <?php echo $data['Title'];?></title>

<!-- Bootstrap Core CSS -->
<link href="../css/bootstrap.min.css" rel="stylesheet">

<!-- Custom CSS -->
<link href="../css/modern-business.css" rel="stylesheet">
<link href="../css/custom.css" rel="stylesheet">
<!-- Custom Fonts -->
<link href="../font-awesome/css/font-awesome.min.css" rel="stylesheet" type="text/css">

<!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->

</head>

<body>

<!-- Navigation -->
<?php include '../include/nav.php';?>

<!-- Page Content -->
<div class="container">

<!-- Page Heading/Breadcrumbs -->
<div class="row">
<div class="col-lg-12">
<h2 class="page-header"><?php echo $data['Title'];?>
</h2>
</div>
</div>
<!-- /.row -->

<!-- Portfolio Item Row -->
<div class="row">
<div class="col-md-4">
<img class="img-responsive" src="../Images/<?php echo $data['Image'];?>" alt="">
</div>


<div class="col-md-8 hidden-sm hidden-xs">
<ul class="nav nav-tabs">
<li class="active"><a data-toggle="tab" href="#Plot">Plot</a></li>
<li><a data-toggle="tab" href="#Details">Details</a></li>
</ul>
<div class="tab-content">
<div id="Plot" class="tab-pane fade in active">
<p><?php echo $data['Plot'];?></p>
</div>
<div id="Details" class="tab-pane fade">
<?php
echo'<li>Alternative Title: '. $data['Alt'] .'</li>';
echo'<li>Type: <a href="../list/type.php?Code='. $data['tcode'] .'&Type='. $data['ttype'] .'">'. $data['ttype'] .'</a></li>';
echo'<li>Rated: <a href="../list/rating.php?Code='. $data['rc'] .'&Rating='. $data['rr'] .'">'. $data['rr'] .'</a></li>';
echo'<li>Episodes: '. $data['TotalEps'] .'</li>';
echo'<li>Duration: '. $data['Duration2'] .'</li>';
echo'<li>Genre: ';
echo'<a href="../list/genre.php?GenreCode='. $data['GenreCode'] .'&Genre='. $data['Genre'] .'">'. $data['Genre'] .'</a>';
foreach($q as $data){
echo", ";
echo'<a href="../list/genre.php?GenreCode='. $data['GenreCode'] .'&Genre='. $data['Genre'] .'">'. $data['Genre'] .'</a>';
}
echo '</li>';
echo'<li>Status: '. $data['Status'] .'</li>';
echo'<li>Category: <a href="../list/categories.php?Code='. $data['ccode'] .'&Category='. $data['ca'] .'">'. $data['ca'] .'</a></li>';
echo'<li>Year Released: <a href="../list/year.php?Year='. $data['YearReleased'] .'">'. $data['YearReleased'] .'</a></li>';
echo'<li>Score: '. $data['Score'] .'</li>';
?>
</div>
</div>
</div>

<div class="col-md-4 hidden-md hidden=-lg">
<h3>Plot</h3>
<p><?php echo $data['Plot'];?></p>
</div>
<div class="col-md-4 hidden-md hidden=-lg ">
<h3>Details</h3>
<ul>
<?php
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT Movies.Code, Title, Plot, Movies.Type, Movies.Category, Image, Score, Rated, Alt, Status, YearReleased, Duration, SUBSTRING(Duration, 1, CHAR_LENGTH(Duration) - 3) AS Duration2, TotalEps, Types.code as tcode, Types.Type as ttype, Categories.Code as ccode, Categories.Category as ca, Ratings.Code as rc, Ratings.Rating as rr, Genre, GenreCode FROM Movies, Types, Categories, Ratings, MovieGenres, Genres WHERE Movies.Type=Types.Code AND Movies.Rated=Ratings.Code AND Movies.Category=Categories.Code AND Movies.Code=MovieGenres.MovieCode AND MovieGenres.GenreCode=Genres.Code AND Movies.Code = ?";
$q = $pdo->prepare($sql);
$q->execute(array($Code));
$data = $q->fetch(PDO::FETCH_ASSOC);
Database::disconnect();

echo'<li>Alternative Title: '. $data['Alt'] .'</li>';
echo'<li>Type: <a href="../list/type.php?Code='. $data['tcode'] .'&Type='. $data['ttype'] .'">'. $data['ttype'] .'</a></li>';
echo'<li>Rated: <a href="../list/rating.php?Code='. $data['rc'] .'&Rating='. $data['rr'] .'">'. $data['rr'] .'</a></li>';
echo'<li>Episodes: '. $data['TotalEps'] .'</li>';
echo'<li>Duration: '. $data['Duration2'] .'</li>';
echo'<li>Genre: ';
echo'<a href="../list/genre.php?GenreCode='. $data['GenreCode'] .'&Genre='. $data['Genre'] .'">'. $data['Genre'] .'</a>';
foreach($q as $data){
echo", ";
echo'<a href="../list/genre.php?GenreCode='. $data['GenreCode'] .'&Genre='. $data['Genre'] .'">'. $data['Genre'] .'</a>';
}
echo '</li>';
echo'<li>Status: '. $data['Status'] .'</li>';
echo'<li>Category: <a href="../list/categories.php?Code='. $data['ccode'] .'&Category='. $data['ca'] .'">'. $data['ca'] .'</a></li>';
echo'<li>Year Released: <a href="../list/year.php?Year='. $data['YearReleased'] .'">'. $data['YearReleased'] .'</a></li>';
echo'<li>Score: '. $data['Score'] .'</li>';
?>
</ul>
</div>
</div>

<!-- /.row -->



<hr>

<!-- Footer -->
<?php include '../include/footer.php';?>

<!-- /.container -->

<!-- jQuery -->
<script src="../js/jquery.js"></script>

<!-- Bootstrap Core JavaScript -->
<script src="../js/bootstrap.min.js"></script>
</body>

</html>


formated:

SELECT Movies.Code, Title, Plot, Movies.Type, Movies.Category, Image, Score, Rated, Alt, Status, YearReleased, Duration, SUBSTRING(Duration, 1, CHAR_LENGTH(Duration) - 3) AS Duration2, TotalEps, Types.code as tcode, Types.Type as ttype, Categories.Code as ccode, Categories.Category as ca, Ratings.Code as rc, Ratings.Rating as rr, Genre, GenreCode
FROM Movies, Types, Categories, Ratings, MovieGenres, Genres
WHERE Movies.Type=Types.Code
AND Movies.Rated=Ratings.Code
AND Movies.Category=Categories.Code
AND Movies.Code=MovieGenres.MovieCode
AND MovieGenres.GenreCode=Genres.Code
AND Movies.Code = ?;

SELECT Genre, GenreCode
FROM MovieGenres, Genres
WHERE MovieGenres.GenreCode=Genres.Code
AND MovieCode = ?;

Answer

You aren't looping over the rows returned by the query.

Have a look at this answer http://stackoverflow.com/a/160365/6632744

You'll want something like this:

while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
    echo'<a href="../list/genre.php?GenreCode='. $row['GenreCode'] .'&Genre='. $row['Genre'] .'">'. $row['Genre'] .'</a>';
}

And you'll want to strip away your initial fetch.

$data = $q->fetch(PDO::FETCH_ASSOC); // remove this line
Comments