Need help with a Query!

[esbenp]

Hi Forum!

 

I'm doing a webpage for a client. It's basicly a webshop selling car tires.

Now i have a table in my db, called products (manufactor, tiretype, speedindex)

 

Each field contain a id which refers to 3 other tables (manufactors, tiretypes, speedindexes). Each of theese tables only got a id and a name,

now after i've done my search i need to pull out the names according to the id's from the first table.

 

I'm pretty sure this should be done with JOIN, but im really clueless about join, so could any of you help me? :-)

[fareedreg]

Please make ur query clear..

 

by the way the answer can be

 

go  to the first  table and got 1st id.. now search this 1st id  in second  or third table  with the help of loop in php and get records.

[esbenp]

PRODUCTS TABLE
----------------------
id - Id of the product
manufactor - The manufactor
speedindex - -
tiretype - -

Example:
ID: 1
Manufactor: 1
Speedindex: 2
Tiretype: 2


--------------------------
MANUFACTORS TABLE
--------------------------
id 
name - name of the manufactor

--------------------------
SPEEDINDEXES TABLE
--------------------------
id
name - name of the speedindex

--------------------------
TIRETYPES TABLE
--------------------------
id
name - name of the tiretype

------------------------

 

Thats my tables, now i searched the products table for a product with theese values (manufactor=1,speedindex=2,tiretype=2)

and i need to show the name of theese 3 id's.

I could do this if i did multiple queries, but i would like to do it in 1.

 

Was that a bit easier? :-)

[jl5501]

You have an id for each of the tables which is fine for indexing those tables alone, but for the single query you want, you will need to link the tables using foreign keys.

 

So for each entry in the products table you would have a manufacturerid, which would link to the id of the manufacturers table and a typeid and a speedindexid which would link to the ids of the relevant table. Then you could do a join query which would extract all the relevant information in one query

[esbenp]

Okay,

so manufactors, tiretypes and speedindex id's need to be foreign keys, to connect to the primary key in the products table?

 

And then when i've done that, what should my sql query look like?

 

 

[jl5501]

the foreign keys are in the product table and connect to primary keys in the other tables.

 

so using aliases to save typing table names too often

 

select * from products p,manufacturers m,speedindexes s,tiretypes t where p.man_id=m.id and p.speedid=s.id and p.tire_id=t.id and p.id=$product_id

[esbenp]

ah great got it working! :-)

thx alot!

 

now the next problem, i couldn't find any info regarding how to use this sql sentence?

id	manufactor	tiretype	dimensions	speedindex	price	stock	id	name	id	name	id	name
1	1	       1	    1	                1	        525.25	  5     1	Bridg... 1	H: asldksaj... 1	Sommerdæk

 

so how do i select the names of each table?

like $result["m.name"], $result["s.name"] ???

 

[jl5501]

Those values you have extracted, are now simply variables in your script, and you can echo them in your page just like you would any other variable

[esbenp]

yeah what i meant was how,

but nevermind i found out that if i use MYSQLI_ASSOC (as i prefer), you cannot use multiple columns with the same name.

So i've used MYSQLI_NUM in this case and now it works like a charm :-)

 

thanks alot for all your help