Need a bit of query help

[simcoweb]

I have 3 tables: featured1, featured2, featured3 that all contain identical field names (id, image, title, blurb, link). The data from these will be displayed in a single column table in respective rows. What i'm trying to do is to create an 'include' file to place into the page. Where i'm a bit stuck is how I can query all 3 tables and display the contents properly since the fieldnames are identical.

 

All I have so far is:

 

<?php
// run query to get data to display
include 'db_config2.inc.php';
$sql = "SELECT * FROM featured1, featured2, featured3";
$results = mysql_query($sql) or die(mysql_error());

echo "<fieldset><legend>Featured Properties</legend>";
echo "<table width='100%' border='0' cellspacing='4'>";
echo "<tr><td style='color: #FFFFFF; border: 2px solid #FFFFFF; background-color: #F3F3F3'>
              <p class='featured'><span lang='en-us'>Property One</span></p>
              <p class='bodytext' align='center'>
              <img border='0' src='images/featured1.jpg' width='160' height='100'><br>
              <span lang='en-us'>This hot property is ready to move into. Give 
              us a call today!</span></td></tr>
              <tr>
              <td style='color: #FFFFFF; border: 2px solid #FFFFFF; background-color: #F3F3F3'>
              <p class='featured'><span lang='en-us'>Property Two</span></p>
              <p class='bodytext' align='center'>
              <img border='0' src='images/featured2.jpg' width='160' height='100'><br>
              <span lang='en-us'>This hot property is ready to move into. Give 
              us a call today!</span></td></tr>
              <tr>
              <td style='color: #FFFFFF; border: 2px solid #FFFFFF; background-color: #F3F3F3'>
              <p class='featured'><span lang='en-us'>Property Three</span></p>
              <p class='bodytext' align='center'>
              <img border='0' src='images/featured2.jpg' width='160' height='100'><span lang='en-us'><br>
              This hot property is ready to move into. Give us a call today!</span></td></tr>";
echo "</table>";
echo "</fieldset>";
?>

[pikemsu28]

try using UNION

 

$query = "(select * from featured1) UNION (select * from featured2) UNION (select * from featured3)"

 

http://dev.mysql.com/doc/refman/5.0/en/union.html

[simcoweb]

Cool. Just reading a short blurb on it seems like that's the way to go. I'll work it up and report back. Thanks!

[simcoweb]

Ok, question on this. The UNION aspect of the query works fine. Now what I need is a bit of guidance on how i'd handle the 3 arrays of this query so that I can display the items in the HTML code. Anyone?

[teng84]

try using UNION

 

$query = "(select * from featured1) UNION (select * from featured2) UNION (select * from featured3)"

Ok, question on this. The UNION aspect of the query works fine. Now what I need is a bit of guidance on how i'd handle the 3 arrays of this query so that I can display the items in the HTML code. Anyone?

 

what ?

the above code returns same as when u use the single select

 

can you explain a bit more

[simcoweb]

Ok, i'll clarify. There's 3 tables, 3 sets of results. In order to display those in my HTML  I would typically use a mysql_fetch_array something like:

 

while($row = mysql_fetch_array($query)) {

echo ".$row['title'].";

 

But since all three tables return a field named 'title' how do I separate those in the display?

[teng84]

wait im wondering why do you use union instead of join??

[simcoweb]

Well, if i'm not mistaken, the JOIN matches data against tables like WHERE a.id = b.id or similar. The way I understand UNION is it makes multiple selects and turns them into one.

 

Basically I have 3 tables displaying similar data with identical field names. I'm trying to figure out the best way to:

 

1. query all 3 at once instead of individually

2. how to display the data once queried

[teng84]

when u use union then what you mean ther is to have an array (if use the fetch assoc or array)

containing the fields of those table

 

so when you display them its the combination of the three

 

but the way i see your question it seems like you need to use something like insert the all that union in the select and thats not proper although it will work

[teng84]

select combine.putherethefields from(select id from featured1) UNION (select * from featured2) UNION (select * from featured3)as combine

 

ad some condition to where abd order by etc

 

select the select? ^^ that will work

[simcoweb]

Hmm, not sure if I follow that. If I do an order by then it would need to be ordered by the table name somehow.

[teng84]

yup