Trouble querying specific, related data from two different tables

[Venom Rush]

Hi all

I'm having the most frustrating time trying to select data from two different tables that relate to each other.

I have a database that has the following tables: images, gallery, categories, news (news is somewhat irrelevant to my current problem)

The gallery table has the following columns:

_____________________
| id | title | category | date |
******************************
| 1 | Party 1 | party | 0000-00-00 |
| 2 | Dinner Event 1 | dinner | 0000-00-00 |

The images table has the following columns:

_______________________________________
| id | image | thumb | category | relation_id | date |
*******************************************************
| 1 | path/party_image_1.jpg | path/party_thumb_1.jpg | party | 1 | 0000-00-00 |
| 2 | path/party_image_2.jpg | path/party_thumb_2.jpg | party | 1 | 0000-00-00 |
| 3 | path/party_image_3.jpg | path/party_thumb_3.jpg | party | 1 | 0000-00-00 |
| 4 | path/dinner_image_1.jpg | path/dinner_thumb_1.jpg | dinner | 2 | 0000-00-00 |
| 5 | path/dinner_image_2.jpg | path/dinner_thumb_2.jpg | dinner | 2 | 0000-00-00 |
| 6 | path/dinner_image_3.jpg | path/dinner_thumb_3.jpg | dinner | 2 | 0000-00-00 |
| 7 | path/dinner_image_4.jpg | path/dinner_thumb_4.jpg | dinner | 2 | 0000-00-00 |
| 8 | path/dinner_image_5.jpg | path/dinner_thumb_5.jpg | dinner | 2 | 0000-00-00 |

Now let's pretend that there are 5 entries in the gallery table that have the category 'dinner' and that there are several images that relate to each of these gallery table entries (relation_id in the images table is the id of the entry inside the gallery table).

I want to select each entry from the gallery table that belongs to the 'dinner' category, as well as the first image that relates to the entry and display it on my page.

Any help would be highly appreciated!!!

[pclfw]

Unfortunately your tables don't relate very well.

As your IMAGES table has the columns
| id | image | thumb | category | relation_id | date |

then, ideally, your GALLERY table should have
| id | title | category | relation_id | date |

Well actually the GALLERY table should be split into GALLERIES and EVENTS
GALLERIES would have
| id | title | category |
with ID as the primary key

EVENTS would have
| id | category_id | relation_id | date |
where CATEGORY_ID is a foreign key to GALLERIES.

Now you can actually JOIN these tables with SQL.

Play with these ideas for a while and see what you make of them.

[Venom Rush]

Unfortunately your tables don't relate very well.

As your IMAGES table has the columns
| id | image | thumb | category | relation_id | date |

then, ideally, your GALLERY table should have
| id | title | category | relation_id | date |

Well actually the GALLERY table should be split into GALLERIES and EVENTS
GALLERIES would have
| id | title | category |
with ID as the primary key

EVENTS would have
| id | category_id | relation_id | date |
where CATEGORY_ID is a foreign key to GALLERIES.

Now you can actually JOIN these tables with SQL.

Play with these ideas for a while and see what you make of them.

So are you saying that the tables I should have are IMAGES, EVENTS and GALLERIES?

Use the sql query to retreive the records as you want:

Select image,thumb from images where category='dinner';

retrieve the values to php:
$img[0]="path/dinner_image_1.jpg "
$img[1]="path/dinner_image_2.jpg"

use <img src=$img[0]> to show your first image.

Here I am not exatcly given the code, I just give you the logic. And I directly use the dinner as the category

[pclfw]

So are you saying that the tables I should have are IMAGES, EVENTS and GALLERIES?

I'm saying that you should have normalised your data first.

[Venom Rush]

I'm saying that you should have normalised your data first.

Thanks pclfw. The downside of learning web development on your own is that one doesn't always know everything. Time for me to learn about normalization.