SQL Join Two Tables

[johnnys]

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 December 5, 2014 by johnnys

[Barand]

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.

[johnnys]

Good advice @Barand I was hoping for an easier solution however your suggestion makes sense for future amendments. 

 

Thanks

[Psycho]

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 December 5, 2014 by Psycho

[Barand]

IF they are consistent, yes.

 

I'd advise running

SELECT DISTINCT category FROM book WHERE ABS(category) = 0 ORDER BY category

and checking the list before going ahead