Jump to content

Basic Help With Join Tables


johnnys
Go to solution Solved by Ch0cu3r,

Recommended Posts

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

Edited by johnnys
Link to comment
Share on other sites

  • Solution

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. 

  • Like 1
Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.