Select statement HELP!

[TCovert]

Shouldn't the following code work for a multi-table search? Please bare with me...there is a lot of code here. Any help would be much appreciated:

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

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

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

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

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

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

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

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

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


if ($photo_credit != "") {
if ($photo_credit != "") {
   $where .= $sep . "stories.story_id = photo.story_id ";
   $sep = " AND "; 
}
}

   $where .= $sep . "stories.page LIKE '%" . $page . "%' ";
   $sep = " AND ";
}

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

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



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

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



if ($photo_caption != "") {
if ($photo_credit != "") {
   $where .= $sep . "stories.story_id = photo.story_id ";
   $sep = " AND ";
}
}




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


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

[sarahk]

did you ever echo this monster out and have a look at it?

You always query from both tables therefore the join:

if ($photo_caption != "") 
{
   if ($photo_credit != "") 
   {
      $where .= $sep . "stories.story_id = photo.story_id ";
      $sep = " AND ";
   }
}

should happen every time and not just be dependant on the caption and credit being given.

You must be getting alot of data back.

I'd also change the query on stories.page to an equals, rather than a like.

[TCovert]

The reason I was only joining them when there was a search done on photo_credit or photo_caption, is that is the only time that I need data from the photo table. Otherwise, the data all comes from the stories table.

But, are you saying that I need to join them all the time, regardless of if I am searching for data in the oother table or not?

Why would you change the stories.page to an equals?

I tried echoing it back, but I could never get it to show up on the page anywhere. And when I tried to write the final query to a file, the file was empty.

Any more suggestions?