php - Retrieving a category name from database

378

I have two tables in my database. One namedjokes and the other namedcategory.

jokes

CREATE TABLE `jokes` (
  `joke_id` int(11) NOT NULL AUTO_INCREMENT,
  `joke` varchar(1024) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`joke_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

category

CREATE TABLE `category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(51) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

My PHP which displays the jokes in a table

<?php
include 'database.php';
$pdo = Database::connect();
$sql = 'SELECT * FROM jokes ORDER BY joke_id DESC';
foreach ($pdo->query($sql) as $row) {
  echo '';
  echo '<table >';
  echo '<thead>';
  echo '<tr>';
  echo '<h3><th>Category Name</th></h3>';
  echo '<h3><th>Action</th></h3>';
  echo '<tbody>';
  echo '<tr>';
  echo '<thead>';
  echo '<tbody>';
  echo '<tr>';
  echo '<td >' . $row['joke'] . '</td>';
  echo '<td width=250>';
  echo '<a href="read.php?id=' . $row['joke_id'] . '">Read</a>';
  echo '&nbsp;';
  echo '<a href="update.php?id=' . $row['joke_id'] . '">Update</a>';
  echo '&nbsp;';
  echo '<a href="delete.php?id=' . $row['joke_id'] . '">Delete</a>';
  echo '</td>';
  echo '</tr>';

}
Database::disconnect();
?>

My question is how would I get the joke'scategory_name to be displayed in the echo'<h3><th>Category name</th></h3>'; with PHP? Because at the moment, the table just displays a joke, without the category name of the joke. For example: Celebrity Joke: this is a celebrity joke.

152

Answer

Solution:

You should alter your query to include the category name.

SELECT j.joke AS joke, j.joke_id AS joke_id, c.name AS category 
FROM jokes j, category c 
WHERE j.category_id = c.category_id

After that, you can get the category by calling$row['category']

816

Answer

Solution:

Use this query:

$sql = 'SELECT j.*, c.name 
        FROM jokes j 
         LEFT JOIN category c ON c.category_id = j.category_id 
         ORDER BY joke_id DESC';

And to echo the category name use:$row['name']

People are also looking for solutions to the problem: PHP-Mysql WHERE is empty AND returns results

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.