[Resolved] Multiple (dependant) queries

[ElaineB]

n00b al3rt! 😕

I am not even sure if I am going to be able to describe what I am trying to figure out but I will try my best.

INFO

1. I have two tables in my db, one for PRODUCTS, and one for ADs. One of the fields in the PRODUCTS table is called

   sponsor

2. The AD table contains the information for each sponsor. It contains 3 fields. They are sponsorimage, sponsorname, and sponsorurl .

3. Each product has a different sponsor.

I would like to somehow pull the appropriate information for each sponsor into the correct place on each product page.

So for example ...

I have a product like "Cool Cellphone" and it is sponsored by "Wicked Phones LLC". So the code would be something like:

echo "<img align=\"left\" src=\"$sponsorimage.jpg\">Thank you to <a href=\"$sponsorurl\">$sponsorname</a> for donating this product!>";

So in the PRODUCTS table, if the sponsor field says WickedPhones then I'd like to pull the relevant sponsor info for WickedPhones out of the AD table and insert it in the page.

I am getting mixed up on the necessary code to call these two queries together and pull from the appropriate table.

I hope any of this has made some sense.

I am just learning PHP and I have read a lot of stuff but then I try to apply what I have read in a book or seen in a tutorial to my specific needs and I can't seem to tweak what I have learned to do what I specifically need. I mean if I was building an address book or any of the other tutorials I have read I'd be golden 😉 but for what I'm doing I can't seem to find reference material. Hence I'm asking a lot of questions here and I apologize.

Thank you in advance for any help.

Elaine

[Pig]

You need to do a join. To do this, you must have a common field between the 2 tables. It doesn't look like you have primary keys on your table, which is very bad. Your ads table should look like this:

ads

sponsorid
sponsorimage
sponsorname
sponsorurl
where sponsorid is an autonumber int.

Actually, if the table is about sponsors, why is it named ads? If it were me, I would use a solid naming convention like:

sponsor

sponsor_id
sponsor_image
sponsor_name
sponsor_url

On your product table, you need to have the ID of the sponsor (NOT the name) ie: products_sponsor. Then you just join the tables in your query.

$result = mysql_query("SELECT * FROM sponsor, products WHERE products_sponsor = sponsor_id");

[njm]

Elaine,

More of an SQL query than strictly a PHP one. 😉
You only need one query, but need to understand how to join tables in a query.

What do you want to see in your results: all the products for a particular sponsor, or all the products and who sponsors them or something else?

hth, njm

[ElaineB]

Ok now I feel stupider than ever ... let me try and follow this ...

Pig ::
I do have the product and sponsorids that are autoincrement. I just didn't include that information in my original post. I know that I need to have those unique id#s for all the tables and they are set to primary.

So if I am understanding correctly, both the PRODUCTS table and the (now renamed) SPONSORS table must share a common field name. I am not clearly understanding what you mean and I apologize. Let's see for example ..

PRODUCTS

prod_id
prod_name
prod_description
prod_image
prod_mfg
prod_sponsor

SPONSORS

sponsor_id
sponsor_name
sponsor_image
sponsor_url

So one of the field names in both of these tables has to be the same? Or would prod_sponsor hold a number corresponding to the sponsor_id in the SPONSORS table?

I think I'm confused ...

I think I understand the JOIN command but I don't think I understand about the common field name.

Elaine

[maxpup979]

ITs not that the field name needs to be the same...there needs to be a relationship between the two...for example, lets assume I have a table:
t1:id autoincrement,name text

my PK is id for t1

then there is another table
t2: addid autoincrement,t1id integer,address text

my PK for this table is addid, and it relates to nothing but the records in t2. However,
the field t1id contains the id from t1 for the appropriate records. So I have a relationship between t1, and t2: The record with id=1 and the record with t1id=1 are related. So then I can use a join to brin (in this case) name and address together.

[ElaineB]

Ok so ... if I have

PRODUCTS

prod_id = 1
prod_name = Cool Cellphone
prod_description = A cool cellphone!
prod_image = someimage.jpg
prod_mfg = Phone Builders Inc
prod_sponsor = 1

SPONSORS

sponsor_id = 1
sponsor_name = Wicked Phones LLC
sponsor_image = wickedphonelogo.jpg
sponsor_url = wickedphones.com

That means that I can pull in all the information from sponsor_id 1 into prod_sponsor?

Elaine

[Pig]

Correct.

$result = mysql_query("SELECT * FROM products, sponsors WHERE sponsors_id=prod_sponsor");

The magic behind using the ID's, is that they will always stay the same. If the sponor name changes, you don't have to change any code. sponsors_id and prod_sponsor are one-in-the-same. Does that make sense?

[ElaineB]

Ok I understand the join command but I don't understand how to implement the code into the page so that all the information is pulled into the page? To me it looks as though it would just pull in the number and not all the field? Does that make sense?

Elaine

[Pig]

$result is the identifier for the result set of your query. To get the data out for a single entry, you would do this:

$row = mysql_fetch_assoc($result);

Now $row is an array with the field names as the keys.
$row[prod_name], $row[sponsor_name], etc.

If you were going to do this for a series of results (multiple rows) it would be like this:

while( $row = mysql_fetch_array($result) )
{
     echo("$row[prod_name], $row[sponsor_name]");
}

[ElaineB]

GOT IT!!! smile Thank you everyone!

I actually understand how this works!

Elaine