Jump to content

SQL Join Two Tables


johnnys
Go to solution Solved by Barand,

Recommended Posts

I have two tables book and category

 

book

id   title           author   category          isbn
1    fun times       Joe         1              16161514 (new record)
2    fishing trip    Jim      Juvenile Science  88771615 (old record) 
3    beach hunt      Sam         3              81009991 (new record)
4    day out         John        3              81009991 (new record)
5    farm fun        Jim      Classic Kids      88771615 (old record)

category

id   cat_name
1    horror 
2    science
3    kids 

I use the join query below to display a list of all book info, works fine.

SELECT b.id, b.title, b.category, b.author, b.isbn, b.publicationYear
        c.cat_name
        FROM book AS b
        INNER JOIN category AS c ON b.category = c.id

The problem I am facing is that all some of the books (1000's actually) in the db have been imported from a previous db, and instead of containing an integer for the category they contain a string such as 'General Fiction' or 'Popular Classics' etc. 

 

Must I import all of these categories into the 'category' table and assign an integer to them, or is there a way of simply displaying the string that is contained within the 'book' table as it stands?

 

I use the code below to display the html table of results;

while($row = $res->fetch_array()) {
    echo '<tr>';
    echo '<td>' . $row['id'] . '</td>';
    echo '<td>' . $row['title'] . '</td>';
    echo '<td>' . $row['author'] . '</td>';
    echo '<td>' . $row['cat_name'] . '</td>';
    echo '<td>' . $row['isbn'] . '</td>';
    echo '<td>' . $row['publicationYear'] . '</td>';
    echo '</tr>';
    echo '</tbody>';    
};

Still teaching myself php/mysql so I appreciate the patience and thanks in advance.

Edited by johnnys
Link to comment
Share on other sites

  • Solution

Bite the bullet and clean up your data. Create categories in the category table for those that are not already there (such as Classic Kids) and store category id in the book table.

 

I know this this may be as easy as it sounds. I once did a similar data migration exercise where a company department had been storing supplier invoice details in a home-built Access database. One of the data fields was the company name. Some companies had hundreds of invoices stored and it was rare to find every record for a company having exactly the same spelling of the name. Some had half a dozen variations of the same name.

 

And that is a reason NOT to store names but to use the ids.

  • Like 1
Link to comment
Share on other sites

If the string values for the categories are consistent, this is a very easy operation: two queries and one alter.

 

First, run one query to populate the non-numeric categories from the book table into the category table

INSERT INTO category (cat_name) SELECT DISTINCT category FROM book WHERE ABS(category) = 0

Next update the category field in the book table to the numeric ids from the categories that were just added

UPDATE book b
JOIN category c ON b.category = c.cat_name
SET b.category = c.id

Lastly, change the category field in the book table to be an INT type field.

 

ALTER TABLE book CHANGE category category INT NOT NULLE
Edited by Psycho
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.