Query multiple tables using one select statement

[TCovert]

This is what I am using, but it doesn't return anything...it just runs forever and then freezes my browser:

Select stories.story_id, stories.bodytext, photo.photo_caption from stories, photo where stories.story_id = 1;

What am I doing wrong?

[rheroux]

You need to do it with aliases like this:

SELECT t1.story_id, t1.bodytext, t2.photo_caption FROM stories AS t1, photo AS t2 WHERE t1.story_id = 1;

[Lars_Berg]

What am I doing wrong?

You don't join the tables. Now you will get a crossproduct. What result do you want?

You need to do it with aliases like this:

No, where did you get that notion?

[rheroux]

That's how I've done it before and it worked for me. If there's another way to do it, I'd like to know. 🙂

[TCovert]

Here is my actual full query, it worked fine until I tried to do the multiple table search. I need to do a boolean search that contains data in both tables.

$query = "SELECT t1.story_id, DATE_FORMAT(t1.pub_date, '%Y %m-%d'), t1.pub_id, t1.section_id, t1.page, t1.series, t1.keyword_id, t1.byline, t1.headline, t1.i
ntro, t1.bodytext, t1.correction, t1.correction_date, t1.label, t2.photo_credit, t2.photo_caption FROM stories AS t1, photo AS t2 ";
$where = "";
$sep = "WHERE ";

if ($strKeyword != "") {
   $where .= $sep . "t1.bodytext LIKE '%" . $strKeyword . "%' ";
   $sep = "AND ";
}

if ($headline != "") {
   $where .= $sep . "t1.headline LIKE '%" . $headline . "%' ";
   $sep = " AND ";
}

if ($bodytext != "") {
   $where .= $sep . "t1.bodytext LIKE '%" . $bodytext . "%' ";
   $sep = " AND ";
}

if ($byline != "") {
   $where .= $sep . "t1.byline LIKE '%" . $byline . "%' ";
   $sep = " AND ";
}

if ($section_id != "") {
   $where .= $sep . "t1.section_id LIKE '" . $section_id . "' ";
   $sep = " AND ";
}

if ($page != "") {
   $where .= $sep . "t1.page LIKE '%" . $page . "%' ";
   $sep = " AND ";
}

if ($keyword_id != "") {
   $where .= $sep . "t1.keyword_id LIKE '%" . $keyword_id . "%' ";
   $sep = " AND ";
}

if ($series != "") {
   $where .= $sep . "t1.series LIKE '%" . $series . "%' ";
   $sep = " AND ";
}

if ($photo_caption != "") {
   $where .= $sep . "t2.photo_caption LIKE '%" . $photo_caption . "%' ";
   $sep = " AND ";
}

if ($photo_credit != "") {
   $where .= $sep . "t2.photo_credit LIKE '%" . $photo_credit . "%' ";
   $sep = " AND ";
}

if ($strFrom != "" || $strTo != "") {
if ($strFrom != "" && $strTo == "") {
$where .= $sep . "t1.pub_date > '%" . $strFrom . "%'";
$sep = "AND";
} 
else if ($strFrom == "" && $strTo != "") {
$where .= $sep . "t1.pub_date < '%" . $strTo . "%'";
$sep = "AND";
} 
else if ($strFrom != "" && $strTo != "") {
$where .= $sep . "t1.pub_date BETWEEN '" . $strFrom . "' AND '" . $strTo . "'";
$sep = "AND";
} 
}


$query .= $where . "ORDER BY t1.pub_date DESC";

[TCovert]

Any ideas? I could use some help ASAP. Thanks.

[TCovert]

Any ideas on this at all?

[Weedpacket]

What makes you think it's the query that's at fault? There's nothing in the code you've posted that could "run forever", because all it does it puts together a string.

echo the query before executing it, so that you can check and make sure it's correct.
A judicious use of mysql_error() might be beneficial, also. Maybe you're doing this; I can't tell.

[Rodders]

Without knowing your table definitions and the amount of data you've got in each it's difficult to know exactly what the problem is.

At a guess, I would say you need to put a JOIN in your query.

Select stories.story_id, stories.bodytext, photo.photo_caption from stories, photo where stories.story_id = 1

This will take every row on 'stories' where story_id = 1. It will then join it to every single row on 'photo'. I doubt that's what you want to happen, so make a JOIN on the common key(s).

[TCovert]

How do I do a JOIN on the tables...and what exactly does that accomplish. I am new to this concept. Thanks.

[Rodders]

JOINs are a fundamental part of using relational databases. I suggest if you are going to be using a database a lot then you should take an online course or even buy a book. You may also want to learn about SQL (Structured Query Language) which is the way of manipulating data in the database.

I will try and explain a JOIN, but I am one of those people that understands it yet finds it hard to explain.

Imagine you've got a list of your friends addresses. You know which address belongs to which friend because you've got their name written next to it. On a separate piece of paper, you've got your friends phone numbers. You know which phone number belongs to which friend because they've also got their names next to the numbers.

So, if you want your friend Dave's address and phone number you look up the address on the first list, then the phone number on the second list. Both times you will use 'Dave' when you are searching. Imagine if one or both lists didn't have names on it, you'd have possibly thousands of combinations without knowing which was correct.

In your example, you would have a list of stories and the text that goes with it. Each story has an ID. Then you would have a list of photos. The only way to know which photo goes with which story is if your photos list also has the story ID on it. Then it's easy to find related stories and photos.

The SQL would look like this:

SELECT stories.story_id, stories.bodytext, photo.photo_caption
FROM stories, photo
WHERE stories.story_id = photo.story_id
AND stories.story_id = 1;

The first line says "get these fields of data"
2nd line says "from these tables"
3rd line says "where they share the same story id"
4th line says "in this case, I am interested in story id 1"

Without the JOIN at line 3 you the database would think that you wanted every photo in the photo table listed for story id 1.

Hope that's clear (ish?)