Page 1 of 1

If/Else Across Two Tables

Posted: Mon Jan 19, 2009 12:24 pm
by itsinmyhead
I have two tables in my database. One holds information for classified ads while the other holds information for advertiser information. I would like to run an If/Else statement across these two tables.

In the table "classifieds," the fields are ID, Category and Description. Here's what I would like to do:

If the category is 'real estate', then I would like to display the advertiser information for that corresponding table ('links_rea').

If the category is 'automotive', then I would like to display the advertiser information for that corresponding table ('links_auto').

etc.

I've been looking around for a while on this - looking at joins and unions and whatnot. I can't get anything to work this way, though, and I'm not really sure what else I can search to find what I need.

Thanks!

Re: If/Else Across Two Tables

Posted: Mon Jan 19, 2009 2:33 pm
by jaoudestudios
Can you post your database schema?

Re: If/Else Across Two Tables

Posted: Tue Jan 20, 2009 11:27 am
by itsinmyhead
I've never exported a schema before, so if this isn't correct, just let me know... thanks for your help!

Code: Select all

 
-- Table structure for table `classifieds`
-- 
 
CREATE TABLE `classifieds` (
  `ID` int(4) NOT NULL auto_increment,
  `Category` varchar(25) NOT NULL,
  `Description` text NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `links_rea`
-- 
 
CREATE TABLE `links_rea` (
  `id` int(3) NOT NULL auto_increment,
  `name` text NOT NULL,
  `street` text NOT NULL,
  `city` text NOT NULL,
  `phone` text NOT NULL,
  `web` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
 

Re: If/Else Across Two Tables

Posted: Tue Jan 20, 2009 12:57 pm
by Shiki
What's the foreign key that connects a links_rea record to a classifieds record?

Re: If/Else Across Two Tables

Posted: Tue Jan 20, 2009 3:00 pm
by itsinmyhead
What do you mean by foreign key?

What I want is this... if the term "Real Estate" is found in the "Category" field of the table "classifieds", I want to display the data from the "links_rea" table.

Re: If/Else Across Two Tables

Posted: Tue Jan 20, 2009 4:32 pm
by Shiki
You mean there's an initial SELECT

SELECT Category FROM classifieds

But depending on the value of 'Category', you want to include additional data either from links_rea or links_auto in the returned resultset?

Re: If/Else Across Two Tables

Posted: Wed Jan 21, 2009 8:09 am
by itsinmyhead
Shiki wrote:You mean there's an initial SELECT

SELECT Category FROM classifieds

But depending on the value of 'Category', you want to include additional data either from links_rea or links_auto in the returned resultset?
Right - depending on the value of 'Category', I want to include the data from its corresponding links table (links_rea, links_auto, etc.).

Re: If/Else Across Two Tables

Posted: Wed Jan 21, 2009 12:26 pm
by Shiki
Ok. I think there should be a way to link the tables in the first place. And, you could opt to use two queries. First query for:

SELECT Category FROM classifieds

Then the second query for the specific table (links_rea, links_auto, etc.) depending on the value of Category. I guess this means you can do your "ifs" in PHP instead of in SQL. I believe this is more faster than joining tables. OR not, but it's worth a try. Another alternative I could think of right away is using CASE:

Code: Select all

SELECT Category,
   CASE classifieds.Category
      WHEN 'auto' THEN links_auto.field_val
      WHEN 'rea' THEN links_rea.field_val
      ELSE 'unknown'
   END AS specific_field_value 
FROM classifieds
LEFT JOIN links_rea ON (classifieds.id = links_rea.classifieds_id) /* links_rea.classifieds_id is the foreign key that points to a classifieds record (what I was asking about earlier) */
LEFT JOIN links_auto ON (classifieds.id = links_auto.classifieds_id)

Re: If/Else Across Two Tables

Posted: Fri Jan 23, 2009 10:55 am
by itsinmyhead
Alright, so I've got the data displaying - sort of. Here's the code:

Code: Select all

$query = "SELECT classifieds.Category, links_auto.* ".
 "FROM classifieds, links_auto ".
    "WHERE classifieds.Category = 'Automotive'";
 
$result = mysql_query($query) or die(mysql_error());
 
// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
    echo " <b> ". $row['name']. " </b><br /> ". $row['street']. " <br /> ". $row['city']. " <br /> ". $row['phone']. " <br /><a href=\" ". $row['web']. " \"> ". $row['web']. " </a><br /><br /> ";
}
There are a few problems, though. Say there are 10 entries in the "links_auto" table, but 20 entries in the "classifieds" table with the category "automotive". This will loop the 10 entries in the "links_auto" table until they fill out those 20 entries in the "classifieds" table.

Second, this also displays the "links_auto" table based on the information that is in the "classifieds" table, which is sort of what I want. Have a look here: http://www.adkpennysaver.com/search.php

If you search "car" then you will receive several entries in the "Automotive" section. I want the code to read like an "if/else" statement - if the information displayed on the page is from a certain category, it will display the links for that category. This way, if there are several categories displayed (as there are when you search the word "car), then the data from all of those link tables will be displayed.

If this doesn't make any sense, let me know... I'm probably jumbling some stuff around - I know what I want to do, but I'm probably not very good at explaining it.

Thanks so much for your help!

Re: If/Else Across Two Tables

Posted: Mon Jan 26, 2009 11:19 am
by itsinmyhead
Alrighty, I've come across something completely different that I've never used before - preg_match. Here's my code:

Code: Select all

while($row = mysql_fetch_array($result2)) {
if (preg_match("/Real Estate/i", $query1))
{
    echo " <b> ". $row['name']. " </b><br /> ". $row['street']. " <br /> ". $row['city']. " <br /> ". $row['phone']. " <br /><a href=\" ". $row['web']. " \"> ". $row['web']. " </a><br /><br /> ";
}
elseif (preg_match("/Real Estate/i", $query))
{
    echo " <b> ". $row['name']. " </b><br /> ". $row['street']. " <br /> ". $row['city']. " <br /> ". $row['phone']. " <br /><a href=\" ". $row['web']. " \"> ". $row['web']. " </a><br /><br /> ";
}
 else {
    echo "A match was not found.";
}
}
And it works! For the most part. Go here: http://adkpennysaver.com/search_test.php

If you click on "Real Estate" then you'll see the Real Estate links. If you search "Real Estate" then you'll see the Real Estate links. But if you search for "home" or "rent" or anything else that will bring up Real Estate classifieds, you do not get the links. Is there a way to get around this? So that, if the results contain "real estate" regardless of what the specific search term is, the links will still display?