If/Else Across Two Tables
Moderator: General Moderators
-
itsinmyhead
- Forum Commoner
- Posts: 25
- Joined: Wed Aug 13, 2008 11:33 am
If/Else Across Two Tables
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!
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!
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: If/Else Across Two Tables
Can you post your database schema?
-
itsinmyhead
- Forum Commoner
- Posts: 25
- Joined: Wed Aug 13, 2008 11:33 am
Re: If/Else Across Two Tables
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
What's the foreign key that connects a links_rea record to a classifieds record?
-
itsinmyhead
- Forum Commoner
- Posts: 25
- Joined: Wed Aug 13, 2008 11:33 am
Re: If/Else Across Two Tables
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.
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
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?
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?
-
itsinmyhead
- Forum Commoner
- Posts: 25
- Joined: Wed Aug 13, 2008 11:33 am
Re: If/Else Across Two Tables
Right - depending on the value of 'Category', I want to include the data from its corresponding links table (links_rea, links_auto, etc.).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?
Re: If/Else Across Two Tables
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:
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)-
itsinmyhead
- Forum Commoner
- Posts: 25
- Joined: Wed Aug 13, 2008 11:33 am
Re: If/Else Across Two Tables
Alright, so I've got the data displaying - sort of. Here's the code:
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!
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 /> ";
}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!
-
itsinmyhead
- Forum Commoner
- Posts: 25
- Joined: Wed Aug 13, 2008 11:33 am
Re: If/Else Across Two Tables
Alrighty, I've come across something completely different that I've never used before - preg_match. Here's my code:
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?
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.";
}
}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?