Adding Data and Join Tables

[johnnys]

I have two tables 'book' and 'category'. They look like the following in phpmyadmin;

 

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

5    The Twite           Roald Dahl   Fiction   87873366

 

category

 

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

2        Kids

3        Fiction

4        Science

 

Users have the option of adding books into the library via an online form, or via a Google Booka api method (user enters isbn, searches, is presented with book info and then clicks 'add to library', done.). This is handled via ajax.

 

The online form works fine, and successfully adds the book info.

However my problem is with the Google Books method, it successfully adds the data to the db however the category column is in text format (i.e 'Juvenile Science' or 'Scary Fiction') as opposed to the manual form which adds categories as 1, 2 or 3 (1 =Horror, 2 = Kids, 3 = Fiction).

 

Is there any way I can add the Google Book category data to my table and convert it to an integer or similar? Not sure what I need to do. Suggestions appreciated!

Should I add the Google entries to another table (i.e googleCategory)?

 

My HTML only outputs the numbered category entries and ignored the text format entries.

 

my php

$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['cat_name'] . '</td>';
    echo '<td>' . $row['isbn'] . '</td>';
    echo '<td>' . $row['publicationYear'] . '</td>';
    echo '</tr>';
    echo '</tbody>';
};

Apologies if this is all a bit confusing I am very new to php and mysql.

Thanks,

 

J

Edited December 4, 2014 by johnnys

[Barand]

Don't double post !

 

This has already been answered in the MySQL forum.

[johnnys]

@Barand if you read the question carefully you will see that it's actually different. The post in the MySql forum has been answered.

 

However if you feel this is a double post then feel free to delete it.

[Ch0cu3r]

You need to first insert the new category into the categories table. You then use mysql_insert_id (provided the id column in the category table is auto increment) to get the id of the new category.

 

When you insert the book details into the book table you insert the category id into the category column.

 

The cat_name column in the category table should also be set to unique to prevent duplicate entries from occurring.

Edited December 4, 2014 by Ch0cu3r

[Barand]

Look up the category_id in your category table if a name is entered instead of an id.

SELECT category_id FROM category WHERE cat_name = '$googleCategory'

[johnnys]

Thanks @Ch0cu3r and @Barand - perfect