Relational tables: a 3rd table with relations

I'm building a php page that will show an specific banner when the user enters his/her phone number in a form field.

So here's my logic for the database:

Table phones with fields: id, name of user and phone number. Table banners with fields: id, banner's name/title and banner (path to the image). Table relation with fields: here's where the phone number should be related to a banner and where I need your help :)

And here's my logic for the php page:

-form gets the phone number -I query the data base -I show the banner related to the phone number entered in the form.

Below is开发者_开发百科 the code for the table creation so far .. as you'll see don't know how to advance.

Thanks a million

    CREATE TABLE phones(

id_phone INT NOT NULL AUTO_INCREMENT,

nombre VARCHAR(30),

number INT (9),

PRIMARY KEY (id_phone)

) TYPE = INNODB;


CREATE TABLE banners (

id_banners INT NOT NULL AUTO_INCREMENT,

id_phone INT NOT NULL,

name VARCHAR(250),

banner VARCHAR(250),

PRIMARY KEY(id_phone),

INDEX (id_phone),

FOREIGN KEY (id_phone) REFERENCES clientes(id_phone)

) TYPE = INNODB;

From your question, it seems that each phone number has only one banner associated with it. Therefore, delete the banners.id_banners field and add a phones.id_banners

Then to select the data, you can do a JOIN:

SELECT phones.id_phone, phones.nombre, phones.number, 
banners.id_banners, banners.name, banners.banner
JOIN banners ON phones.id_banners = banners.id_banners
WHERE phone = '123-456-789'

First of all what's the question? What can I mention at this step is, does the relation between this entities is one to one? If so, you'd better put all this data into one single table.

++

CREATE TABLE phones(
phone_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
number INT (9),
PRIMARY KEY (id_phone)
) TYPE = INNODB;

CREATE TABLE banners (
banner_id INT NOT NULL AUTO_INCREMENT,
phone_id INT NOT NULL,
name VARCHAR(250),
banner VARCHAR(250),
PRIMARY KEY(id_phone),
INDEX (id_phone),
FOREIGN KEY (phone_id) REFERENCES clientes(phone_id)
) TYPE = INNODB;

and the query would be:

$query = "SELECT * FROM clients LEFT JOIN banners USING(phone_id) WHERE clients.number='.$number.'";