[SOLVED] trying to run a query on multiple tables

[poe]

i am trying to build my own banner tracking db.

i have 5 tables:

 

#1 = user

id | name

1 | chris

2 | mike

 

 

 

#2 = banners

id | url | filename | user_id | zone_id

1 | sony.com | style.jpg | 1 | 1

2 | nike.com | swoosh.jpg | 1 | 2

3 | mazda.com | zoomzoom.jpg | 2 | 1

 

 

 

#3 = zones

id | description

1 | top of page

2 | bottom of page

 

 

 

#4 = priceplan

id | cost | zone_id | time

1 | 25.00 | 1 | 1mth

2 | 50.00 | 1 | 3mths

3 | 150.00 | 1 | 1yr

4 | 15.00 | 2 | 1mth

5 | 100.00 | 2 | 1yr

 

 

 

#5 = sales

id | zone_id | user_id | startmth | duration(mths)

1 | 1 | 1 | 5 | 1

2 | 1 | 2 | 7 | 1

3 | 2 | 1 | 5 | 1

 

 

 

 

when someone buys an ad spot

 

'sales' table is updated to record 'zone' (top of page ad, or bottom

page ad), the user, when to start showing ads, duration(in mths) to run

the ad.

 

i am stuck trying to list all sales and their asociated banner, zone info

 

the result i want is:

zone:top | user:chris | url:sony.com

zone:top | user:mike | url:mazda.com

zone:bottom | user:chris | url:nike.com

 

i have :

$qx = " SELECT z.id, z.description, s.id, s.zone_id, s.user_id, s.startmth, s.duration, b.id, b.url, b.filename, b.user_id, b.zone_id ";
$qx .= " FROM sales=s ";

$qx .= " RIGHT JOIN zones=z ON s.zone_id = z.id "; 
$qx .= " RIGHT JOIN banners=b ON s.user_id = b.user_id "; 

$qx .= " ORDER BY z.id ASC ";

 

 

but it isnt working

 

help please.

 

is there something wrong with my table logic, or am i going about this the wrong way

[artacus]

but it isnt working

 

Can you be more vague please? Do you get an error, whats the error message?

 

Use "FROM sales AS s" instead of "FROM sales=s"

Also, while left join and right join do pretty much the same thing, Left join is pretty much the industry convention.

[poe]

 

my query is:

 

$qx = " SELECT z.id, z.description, s.id, s.zone_id, s.user_id, s.startmth, s.duration, b.id, b.url, b.filename, b.user_id, b.zone_id ";

$qx .= " FROM sales AS s ";

 

$qx .= " LEFT JOIN zones=z ON s.zone_id = z.id ";

$qx .= " LEFT JOIN banners=b ON s.user_id = b.user_id ";

 

$qx .= " ORDER BY z.id ASC ";

 

 

my result should be 3 array keys, not 5 as it outputs:

Array

(

    [0] => Array

        (

            [id] => 1

            [description] => top of page

            [zone_id] => 1

            [user_id] => 1

            [startmth] => 5

            [duration] => 1

            => sony.ca

            [filename] => walkman.jpg

        )

 

    [1] => Array

        (

            [id] => 2

            [description] => top of page

            [zone_id] => 2

            [user_id] => 1

            [startmth] => 5

            [duration] => 1

            => nike.com

            [filename] => swoosh.jpg

        )

 

    [2] => Array

        (

            [id] => 3

            [description] => top of page

            [zone_id] => 1

            [user_id] => 2

            [startmth] => 7

            [duration] => 1

            => mazda.com

            [filename] => zoomzoom.jpg

        )

 

    [3] => Array

        (

            [id] => 1

            [description] => bottom of page

            [zone_id] => 1

            [user_id] => 1

            [startmth] => 5

            [duration] => 1

            => sony.ca

            [filename] => walkman.jpg

        )

 

    [4] => Array

        (

            [id] => 2

            [description] => bottom of page

            [zone_id] => 2

            [user_id] => 1

            [startmth] => 5

            [duration] => 1

            => nike.com

            [filename] => swoosh.jpg

        )

 

)

 

[artacus]

Ok, you probably need a group by.

 

If you have 1 row from table 1 and join it to another table in a 1 to many relationship, you'll end up with as many records matched in the second table.

 

So 1 row in table 1 joined w/ 3 rows in table 2 and joined with 4 rows in table 3 will give you 12 rows in your result.

[poe]

ahhhh!

 

i see, that looks better,

 

thanks