Newbie SQL Querie help 1

[ghvn]

Hi Folks,

i've bitten the bullet and started learning MySQL. As part of this I am setting up a DB to store references for my studies. You know the thing, you have a paper to write and need to have a stock of references to 'use'. I've used forums, web articles and 'PHP and MySQL web development' by Luke welling for my learning.

I set up a DB called 'refs' with 2 tables (so far)'books' and 'publisher'.

The SQL i used is
>>start SQL<<

create table books
( isbn char(13) not null primary key,
author char (60) not null,
title char (120) not null,
pubdate char (15),
edition char (4),
pub char (60)
);

create table publisher
( id int unsigned not null auto_increment primary key,
publisher char (60) not null,
address char (120) not null,
city char (20) not null
);

<<<end SQL>>>

Could anyone help me get my schema in order? As many books share the same publisher (especially in my profession(medical)) I would like an easy way to enter the publisher into the books table.

As an example, I would like to enter say 'SAMS' in books.pub and have this look up SAMS in the publisher table so that I can join them later to give a full academic (Harvard) reference with the Address etc included, formatted with PHP.

Eventually I will turn this into a webapp driven by PHP, so when entering a book into the DB the user will either be able to type just the publisher name or select from a list if the publisher is already in the DB - if it is not then the user will have to enter the full publisher details. I am sure there is an easier way to explain this and I trust you guru's will understand me

Any help greatly appreciated,

best wishes

Mike

 

[Broccoli2]

What I think you are asking is a convenient way to join your two tables. You are almost there! I think the best way would be to modify your tables using something like this:

create table books (
    isbn char(13) not null primary key,
    author char (60) not null,
    title char (120) not null,
    pubdate char (15),
    edition char (4),

publisher_id int

);
create table publishers (

publisher_id int not null auto_increment primary key

,
    publisher char (60) not null,
    address char (120) not null,
    city char (20) not null
);

Now the

publisher_id

field in

books

is a 'foreign key' to the

publisher

table. By using your primary key for the link (rather than the publisher's name) your queries will run faster and will be less error prone. You can easily set up a web application which will fill your

books.publisher_id

field according to a user's choice of the publisher's name.

You can retrieve details of all books and their publishers using

SELECT
books.isbn
,books.author
,books.title
,books.pubdate
,books.edition
,publishers.publisher
,publishers.address
,publishers.city
FROM books
LEFT JOIN publishers ON books.publisher_id=publishers.publisher_id

-Rob

 

[ghvn]

Hi Rob,

Thanks for the excellent explaination, it has been a huge help. can I ask a couple of questions ...

create table books (
    isbn char(13) not null primary key,
    author char (60) not null,
    title char (120) not null,
    pubdate char (15),
    edition char (4),
    publisher_id int
);
create table publishers (
    publisher_id int not null auto_increment primary key,
    publisher char (60) not null,
    address char (120) not null,
    city char (20) not null
);

How does MySQL know that    ' publisher_id int' (from books) is a forieng key? Does it automatically know because it is a primary key to another table (I assumed I would have to mark it as a Forieng Key but it looks like I am wrong.)

As for 'Left Join' I read the chapter on that the other day, got confussed and comforted myself by saying 'never have to use that anyway'!! Wrong again, am I right in saying that this simply joins the tables at the specified columb?

Thanks again for your help Rob, it is very much appreciated,

Cheers
Mike

 

[Broccoli2]

At least in the standard release, MySQL doesn't 'know' about foreign keys - you have to handle this yourself. It's not that much hassle though.

The way that

INNER JOIN

works is as follows:
eg

t1 INNER JOIN t2 ON condition

For each row in

t1

, check each row in

t2

to see if

condition

is true. If it is, add the row from t1 and t2 to the result set.

LEFT JOIN

is similar, but includes all the rows from

t1

, even if there is no match from

t2

.

e.g.

+-------+---------+
| t1.id | t1.name |
+-------+---------+
|     1 |   apple |
|     2 |  orange |
|     3 |  banana |
+-------+---------+
+-------+-----------+
| t2.id | t2.colour |
+-------+-----------+
|     1 |     green |
|     1 |       red |
|     4 |    purple |
|     3 |    yellow |
+-------+-----------+

(apples can be green or red, bananas are yellow)

SELECT t1.name, t2.colour
FROM t1

INNER

 JOIN t2 ON t1.id=t2.id;

+---------+-----------+
| t1.name | t2.colour |
+---------+-----------+
|   apple |     green |
|   apple |       red |
|  banana |    yellow |
+---------+-----------+

no match for orange - so it doesn't appear in the result set

SELECT t1.name, t2.colour
FROM t1

LEFT

 JOIN t2 ON t1.id=t2.id;

+---------+-----------+
| t1.name | t2.colour |
+---------+-----------+
|   apple |     green |
|   apple |       red |
|  orange |           |
|  banana |    yellow |
+---------+-----------+

orange is included in result set, but with

Null

in

t2.colour

Hope this makes things clearer

-Rob

 

[ghvn]

Rob,





Great explanation again Rob, the book I have been using gave a complex example, had me completely lost!





The good news is that I now have a 'prototype' referencing database set up and working. I can add, edit, delete and search for books (with the pulishers details included). Just have to make a PHP front end to the publishers table (add, edit etc) and it will prove a great time saving tool.





I am starting to get the hang of PHP/MySQL now, I've even found it good fun so am wondering about expanding my idea and making it into a web app for give away.





Do you think it would be reasonable to make another table which stores quotes (and page numbers and a explaination of what the reference is good for) from a book. The idea being when you are studying and use a reference you store it for a rainy day. when you are stuck for a quote you search your DB for something that will pass and it spits back a formatted academic reference (so all you have to do is copy into your paper, or make a script to do some clever formatting Could be a handy tool.





would a table like the following be OK as a start point ...





CREATE TABLE quote (


quote_id int(4) unsigned NOT NULL auto_increment,


book_isbn char(13) NOT NULL ,


book_page char(20) NOT NULL ,


quote char(250) NOT NULL ,


quote_about char(250) ,


quote_spare char(250) ,


PRIMARY KEY (quote_id)


)





quote_about would hold keywords and notes about the quote and quote_spare for future use.





Any opinion would be welcomed, thanks again for the explaination about joins



Best wishes



Mike

 

[Broccoli2]

Your idea seems fine - you could use

book_isbn

to join your

quotes

table to your

books

table.

Now that you've got the hang of it, can I suggest a couple of improvements?

1. Create an additional field

book_id int not null auto_increment

in

books

and use that as your primary key instead of

book_isbn

. This has the advantage of
a) giving you more flexibility (books without or unknown ISBNs, different copies of same book)
b) database would be more efficient and queries run faster
Obviously you would need to use

book_id

instead of

book_isbn

in your new

quotes

table as well

2. Change your

char

columns to

varchar

or

text

(for anything over 150 characters). Again, this is for efficiency and speed.

3. Add an index to your foreign keys - eg:

ALTER TABLE books ADD INDEX(publisher_id);

This will enable the server to join and search the tables more easily.

These steps aren't crucial to your database, but they will make things better in the long run.

-Rob

 

[ghvn]

Hi Rob,



Sorry for not getting back sooner. I have made the changed to the DB as you suggested and it works a treat .



I have one more question though, How do I join multiple tables. I have tried to follow the syntax from examples but it does not seem to work.

SELECT

*

FROM books

LEFT JOIN publishers ON books.publisher_id=publishers.publisher_id,

LEFT JOIN quote ON

quote.book_id=books.book_id

What I want to do is join all three tables [books, publisher and quote]. This is so I can extract a reference with all the book and publisher details as well.
First I would select the quote and this would add the book details from the book_id. Then publisher details would be joined (as before) on the book_id from both tables.


Any further help is appreciated as always.



Best Wishes Mike

 

[Broccoli2]

Try joining the tables in the order you mention, ie

SELECT quote.*, books.*, publishers.*
FROM quotes
LEFT JOIN books ON quotes.book_id=books.book_id
LEFT JOIN publishers ON books.publisher_id=publishers.publisher_id

If you get an error, post it here.

-Rob

 

[ghvn]

Thanks again Rob!

that worked just fine. i think I am getting more used to te syntax now.

Now I just need to tidy up the PHP! Do you recomend seperate scripts per function (add, edit, delete, search)
or would it be better to use a self referencing script?

cheers
Mike

 

[Broccoli2]

I prefer to use self-referencing forms, then if the sections of code get too large and unmanageable, separate them into separate

include

d scripts.

My normal script structure runs something like:
- clean and check any user submitted variables
- valid input for delete?
y: do
n: - valid input for add?
y: do
n: - valid input for edit?
y: do
- valid input for search?
y: set search params
n: set default search params
- do search/display

However, you may want to think about form posting methods (ie

POST

v

GET

) as there are various ways of handling these which might influence how you structure your scripts. If you want more info, post here.

-Rob