Php code not displaying the data right from database

Question

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 = ?;

Please format your query

Strawberry
– Strawberry

2016-08-02 02:31:16 +00:00
Commented Aug 2, 2016 at 2:31 — Strawberry
– Strawberry, Commented Aug 2, 2016 at 2:31

Community · Accepted Answer · 2017-05-23 11:51:32Z

0

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

Have a look at this answer https://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

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Aug 2, 2016 at 3:19

Joundill

7,82113 gold badges40 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

J Doe Over a year ago

I thought that was what I was doing with foreach($q as $data){ echo", "; echo'<a href="../list/genre.php?GenreCode='. $data['GenreCode'] .'&Genre='. $data['Genre'] .'">'. $data['Genre'] .'</a>'; }

Joundill Over a year ago

No, you're looping over the one result, which is why you only receive one line. $q->fetch only retrieves one row. I suggest removing that line and putting in a while loop. I'll update my answer to contain a code fragment

J Doe Over a year ago

Thanks that helped a lot and was what I was looking for! I really appreciate the help. Would I be right in understanding for any other sql query I could use the while loop above with $z = $pdo->prepare($sql3); $z->execute(array($Code)); @Joundill

J Doe Over a year ago

What about the other stuff like title and plot? Wouldn't I need that first fetch to get it?

Joundill Over a year ago

Yeah, essentially. And no, there's pretty much never a reason to use fetch() outside of a loop unless you're only retrieving one row.

|

Collectives™ on Stack Overflow

Php code not displaying the data right from database

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related