Hi,

I am trying to make the following script work a little bit differently. The code below will output a picture from mysql db. It does work, but...I need to be able to access the 'catalog' table as well as the 'products' table in mysql db. How can I accomplish this? The 'products' table is currently being used in the following script, but I need to also be able to access a row in the 'catalog' table in the script below. How can this be done?

P.s: Today IS MY BIRTHDAY

Here's the code:

<?

// Connect to the database
mysql_connect ('localhost', 'user', 'pass') ;
mysql_select_db ('db_name'); 

// Edit this number to however many links you want displaying
$num_displayed = 1 ;
$category = '165' ;

// Select random rows from the database
$result = mysql_query ("SELECT * FROM products WHERE cid=$category ORDER BY RAND() LIMIT $num_displayed"); 

// For all the rows that you selected
while ($row = mysql_fetch_array($result)) 

{
// Display them to the screen...

echo "<a href=\"http://www.sample.com/index.php?p=product&id=" . $row["pid"] ."&parent=" . $row["cid"] . "\"> <img src=\"displaythumb.php?image=" . $row["image_url"] . "\" border=1 alt=\"" . $row["title"] . "\">
<br>
$row[title]
</a>" ;
}

?>

Thanks again for your help!
-Brian

Happy Birthday Brian.

What are you trying to do with the catalog table? Is there a relation between the two tables?

you will need to use a "LEFT JOIN" but without knowing the structures and PK/FK details it's difficult to be more specific.

Hi,

Thank you both for both replies... (Chrishirst & Someguy)

Let me first reply to Someguy since I can add more to his post...

Happy Birthday Brian

Thank you!

What are you trying to do with the catalog table? 
Is there a relation between the two tables?

Yes, there is a relation between the two, and that is they are in the same database. What I am trying to do with the catalog table is, I am trying use a row from that table called 'cid' from the catalog table in the same script that originally only uses the products table. Here's a diagram that I hope will explain a little better. ( PNG Format )

Thanks you for your time!
-Brian

something like this (just guessing the catalog fieldnames)
You are only using 4 fields from the products table and I have no idea what fields are required from catalog so

"SELECT pid,cid,title,image_url,catalog.fieldname FROM products LEFT JOIN catalog ON products.cid = catalog.id WHERE products.cid=$category ORDER BY RAND() LIMIT $num_displayed;"

add in more catalog.fieldname if required and I assumed that products.cid would be PK and catalog.id will be FK

BTW

PK = Primary Key
FK = Foreign Key

( Not tested 'cos I couldn't be bothered to recreate the db structure )

Hi Chrishirst,

I've tried what you supplied me with but I couldn't get it working so, I've done some checking in my database at the structure, and this is what I can tell you.

All I need to do is SELECT a ROW (fieldname) from two different tables and use them both in the following script. One ROW (fieldname) from the catalog table is called: cid and one from ROW from the products table is called: pid

Here's the original script again:

<?

// Connect to the database
mysql_connect ('localhost', 'user', 'pass') ;
mysql_select_db ('db_name'); 

// Edit this number to however many links you want displaying
$num_displayed = 1 ;
$category = '165' ;

// Select random rows from the database
$result = mysql_query ("SELECT * FROM products WHERE cid=$category ORDER BY RAND() LIMIT $num_displayed"); 

// For all the rows that you selected
while ($row = mysql_fetch_array($result)) 

{
// Display them to the screen...

echo "<a href=\"http://www.sample.com/index.php?p=product&id=" . $row["pid"] ."&parent=" . $row["cid"] . "\"> <img src=\"displaythumb.php?image=" . $row["image_url"] . "\" border=1 alt=\"" . $row["title"] . "\">
<br>
$row[title]
</a>" ;
}

?>

One thing I forget to mention was that in the above SELECT statement from where the WHERE statement begins, I need that as is so that it reads cid=$category, but it doesn't seem to work as is because the cid ROW (fieldname) doesn't exist in the products table as the script currently states. The cid ROW (fieldname) exists in the catalog table NOT the products table. But I DO need the products table to get the actual image, and the catalog table to get the category in which that product exists in.

The url part of the script (at the very bottom of the above code) needs to be changed so that the part that says: . $row["pid"] is from the products table & . $row["cid"] is from the catalog table.

Hope you understand this time.
Thanks again!
-Brian

Ok we are a bit adrift on terminology due to the way the script is coded.
a row in SQL terminology is the entire record (all fields), what you need to retrieve is one field from the row (known as a column). It's the much loved use of $row["column"] with php programmers that throws it into confusion.

so you need the products.pid column and the category.cid column along with products.title and products.image_url

to be able to accomplish this there needs to be a column that exists in the products table that can be used as a relational key to the category table. This is the PK/FK thing.
In a well designed normalised table structure for a product catalog system this should exist otherwise you would not be able to show your products in categorised groups.

BTW your output buffering is messed up and the gzip handler is throwing an error

Warning: ob_start(): output handler 'ob_gzhandler' conflicts with 'zlib output compression' in [include file removed] on line 1

and
HAPPY BIRTHDAY Brian

Hi Chrishirst,

I am certain about what I am seeing, it's just how I am explaining it...Let me try to explain this better...

Ok, there's a product.pid column -- there's also a category.cid column ...You have it correct...But how do I connect these two together?

Edit: Chrishirst, Did you notice if that error with the Gzip Handler is still showing up?

EDIT: THANKS ALOT CHRISHIRST!

-Brian

there will be a column in the products table that tells the system which category the product is in. That's the one needed.

Hi Chirshirst,

That would be

pid

so the link would be products.pid correct?

-Brian

Hi Chrishirst,

Let me ask you this...

How should the SELECT STATEMENT be defined? Could you just explain with the changeable information in brackets like so?

"SELECT [table] FROM [column] LEFT JOIN [column.id] 
ON products.pid =  WHERE cid=$category ORDER BY 
RAND() LIMIT $num_displayed;"

Thank you
-Brian

sounds like that might be the product ID.

If you email your structures of the products and category tables I'll see if I can figure out what is what . forumusername at candsdesign.co.uk
It's getting near the wrong side of 1am here so the brain is fading somewhat

I sent you png's and the actual exports...Dis-regard the png's I know they're no help, but I thought you might like the screens n-e-way.

-Brian

$sql = "SELECT pid,title,image_url,catalog.cid,catalog.name FROM products LEFT JOIN catalog ON products.cid = catalog.cid WHERE products.cid=$category ORDER BY RAND() LIMIT $num_displayed;"

This works,
You can add in other columns into the fieldlist from either table using the tablename.column syntax. If the column is from the primary table (products here) you can just use column provided the column name does not exist in both tables.
The display code is the same syntax ($row["columnname"])

BTW if you want to test in the phpadmin GUI just replace the variables with hardcoded values.