Basic Help With Join Tables

[johnnys]

I am creating a library app for personal development and would like to further my knowledge of MySql.

 

I currently have two tables 'book' and 'category'

 

book

 

id   title                    author      category     isbn
---- -------                 ----------   ----------  ------- 
1    Treasure Chest     Jim Jones     1           14252637

2    Pirates Boat         Sue Smith    2           88447737

3    Adventure Land   Harry Jo      3           01918273

4    Winter Week       Sam Dill      3           00999337

 

category

 

id       cat_name   
----     -------         
1        Horror

2        Kids

3        Fiction

4        Science

 

I am doing a simple search where I want to display all books (select * from book) and I would like to display all the books with their corresponding categories. This works but displays the category number instead of the category name.

 

How can I alter these tables in such a way that the cat_name and category are joined?

 

I tried to run a command in phpmyadmin as below however it returned an error;

ALTER TABLE book
ADD FOREIGN KEY (category) REFERENCES category(cat_name);

error

#1452 - Cannot add or update a child row: a foreign key constraint fails (`sslib`.`#sql-1ab4_1dae`, CONSTRAINT `#sql-1ab4_1dae_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`cat_name`))

I have been looking at foreign keys and indexes however I can't seem to make any sense of them. I am quite new to this so any help is much appreciated.

 

Regards,

J

[Ch0cu3r]

You need to use a join to get the category name. Example query

#Query
SELECT b.id, b.title, b.author, b.isbn,			# return the id, title, author and isbn columns from the book table
       c.cat_name					# return the cat_name column from the category table
FROM book AS b
INNER JOIN category AS c ON b.category = c.id		# join the row where the category column in the books table matches the id column in the category table

#Result
+----+-----------------+-----------+----------+----------+
| id |      title      |  author   |   isbn   | cat_name |
+----+-----------------+-----------+----------+----------+
| 1  | Treasure Chest  | Jim Jones | 14252637 | Horror   |
| 2  | Pirates Boat    | Sue Smith | 88447737 | Kids     |
| 3  | Adventure Land  | Harry Jo  | 191873   | Fiction  |
| 4  | Winter Week     | Sam Dill  | 99337    | Fiction  |
+----+-----------------+-----------+----------+----------+

The foreign key constrain only applies to insert, update or delete queries. 

[johnnys]

Thanks @Ch0cu3r - I can now see a full table of info however my 'Category' field in the table is completely empty.

 

I'm getting a notice regarding line 104 (marked below).

Notice: Undefined index: category in C:\xampp\htdocs\sslib\books.php on line 104

I have provided my complete code below, thanks.

 

$sql = "SELECT b.id, b.title, b.author, b.isbn, b.publicationYear,
c.cat_name
FROM book AS b
INNER JOIN category AS c ON b.category = c.id
WHERE status != 'Archive' ORDER BY id DESC LIMIT $startrow, 15 ";


$res = $conn->query($sql) or trigger_error($conn->error."[$sql]");
while($row = $res->fetch_array()) {


    echo '<tbody>';
    echo '<tr>';
    echo '<td>' . $row['id'] . '</td>';
    echo '<td>' . $row['title'] . '</td>';
    echo '<td>' . $row['author'] . '</td>';
    echo '<td>' . $row['category'] . '</td>'; // <--line 104 
    echo '<td>' . $row['isbn'] . '</td>';
    echo '<td>' . $row['publicationYear'] . '</td>';
    echo '</tr>';
    echo '</tbody>';
};

Any ideas why? 

[johnnys]

I figured it out I should have had

echo '<td>' . $row['cat_name'] . '</td>';

Instead of

echo '<td>' . $row['category'] . '</td>';

Thanks again @Ch0cu3r for the excellent answer!

[Ch0cu3r]

Either change   c.cat_name   to    c.cat_name AS category    within your query

 

OR

 

change   $row['category']   to   $row['cat_name']   in your PHP code.