Retrieving,Sorting & Displaying Multiple Results from Mutliple Tables

Andrius · March 7, 2007

Hello!

I need to know how to run a php / mysql query to do the following.

I have 1 database (lets call it "database" for now) within this database there are multiple tables ("News","Feature","Header_feature")

For NEWS I need only the 6 latest results pulled from that table and displayed in a table. The "News" table has the following columns "ID","Title","Date","Body","Image"

For Feature I need only 1 result retrieved from the database and displayed under the variable $ticker as it will display as a scrolling marquee within a table. The "Feature" table has the following columns "ID","Ticker".

For Header_feature I need only 1 result retrieved from the database and displayed in the following manner <a href="$link"><img src"$header_feature"></a> The "Header_feature table has the following columns "ID","image","link"

How would I code this all into 1 page? I cant seem to get the query to do everything I want it to and display the results properly. You can see what I have so far at www.getlivemedia.com The $ticker is the scrolling text near the top of the page and the news is obviously the bulk of the page and $feature_image is the broken image.

ANY and ALL help is greatly appreciated.

artacus · March 8, 2007

I have 1 database (lets call it "database" for now)

That makes the left cheek of my smart ass tingle

Ok, what I dont understand is why you would have a Feature and Header_feature table. It doesn't see like you would need them because you are just pulling the 6 most recent articles.

Andrius · March 8, 2007

Header_feature = Image for ads at the top of the page (I could rename to Feature_ad if it pleases you)

Feature = The Scrolling Marquee (I could rename table to "Marquee")

News = News Articles

All tables are UNRELATED. Now how would I do what I mentioned above? Any idea?

artacus · March 8, 2007

(I could rename to Feature_ad if it pleases you)

Leave the snide comments to me please. I'm a trained professional. Actually you SHOULD rename the "Date" field IN "News" because that's a reserved word.

Ok, so the way you phrased your subject made it sound like you wanted to join these tables some how. And I was trying to figure out why.

So you'd probably pull the header_feature and feature rows first.

Get a random row like so:

SELECT * FROM header_feature ORDER BY RAND() LIMIT 1

Fenway has some ideas how to do this more efficiently in the mysql forum.

To pull the most recent articles

SELECT * FROM news ORDER BY `Date` DESC LIMIT 6

Andrius · March 8, 2007

what you described I already have. Let me post my badly written file here and you can obliterate it lol.

<?php

$username = "*****";
$password = "*****";
$database = "*****";
$host = "*****";

mysql_connect($host,$username,$password);

@mysql_select_db($database) or die("Unable to select database");

$query = "SELECT * FROM News,Feature,Header_feature ORDER BY date DESC LIMIT 0, 6";
$result = mysql_query($query);

$num = mysql_numrows($result);

mysql_close();

?>

  <table width="1000" height="100%" border="0" cellpadding="0" cellspacing="0">
    <tr>
      <td> </td>
      <td width="102" height="163"><img src="Images/1.gif" width="102" height="163" /></td>
      <td width="482" height="163"><img src="Images/2.gif" width="482" height="163" /></td>
      <td width="233" height="163"><img src="Images/3.gif" width="233" height="163" /></td>
      <td width="117" height="163"><a href="Login.php"><img src="Images/4.gif" width="117" height="163" border="0"/></a></td>
      <td><img src="Images/4addon.gif" width="34" height="163" /></td>
    </tr>
    <tr>
      <td> </td>
      <td width="102" height="77"><img src="Images/5.gif" width="102" height="77" /></td>
      <td background="Images/6_bg.gif"><div align="center"><img src="Images/banner_top.jpg" width="468" height="60" /></div></td>
      <td width="233" height="77"><img src="Images/7.gif" width="233" height="77" /></td>
      <td width="117" height="77"><img src="Images/8.gif" width="117" height="77" /></td>
      <td> </td>
    </tr>
    <tr>
      <td width="32" height="40"><img src="Images/9.gif" width="32" height="40" border="0" usemap="#Map4" /></td>
      <td width="102" height="40"><img src="Images/10.gif" width="102" height="40" border="0" usemap="#Map3" /></td> <td width="482" height="40"><img src="Images/11.gif" width="482" height="40" border="0" usemap="#Map2" /></td>
      <td width="233" height="40"><img src="Images/12.gif" width="233" height="40" border="0" usemap="#Map" /></td>
      <td width="117" height="40"><img src="Images/13.gif" width="117" height="40" /></td>
      <td width="34" height="40"><img src="Images/14.gif" width="34" height="40" /></td>
    </tr>
    <tr>
      <td width="32" height="80" valign="top" background="Images/20_runner_BG.gif"><img src="Images/15.gif" width="32" height="80" /></td>
      <td height="80" colspan="3" valign="top" background="Images/bg_inner.gif"><div align="center">
        <table width="815" border="0" align="right" cellpadding="0" cellspacing="0">
          <tr>
            <td width="8" height="22"><img src="Images/feature_1.gif" alt="Get Live Media" width="8" height="22" /></td>
            <td width="257" height="22"><img src="Images/feature_2.gif" alt="Get Live Media" width="257" height="22" /></td>
            <td width="18" height="22"><img src="Images/feature_3.gif" alt="Get Live Media" width="18" height="22" /></td>
            <td width="169" height="22"><img src="Images/feature_4.gif" alt="Get Live Media" width="169" height="22" /></td>
            <td width="20" height="22"><img src="Images/feature_5.gif" alt="Get Live Media" width="20" height="22" /></td>
            <td width="301" height="22"><img src="Images/feature_6.gif" alt="Get Live Media" width="301" height="22" /></td>
            <td width="42" height="22"><img src="Images/feature_7.gif" alt="Get Live Media" width="42" height="22" /></td>
          </tr>
          <tr>
            <td width="8" height="55"><img src="Images/feature_8.gif" alt="Get Live Media" width="8" height="55" /></td>
            <td width="257" rowspan="3" background="Images/feature_BG.gif">
		<div align="center">
<?

$feati = 0;

while($feati < $num)
{
    $feature_image      = mysql_result($result,$feati,"image");
$feature_link       = mysql_result($result,$feati,"link");

$feati++;
}


echo "	<img src=\"Images/$feature_image\" border=\"1\" />   ";
?>			


		</div>
		</td>
            <td width="18" height="55"><img src="Images/feature_10.gif" alt="Get Live Media" width="18" height="55" /></td>
            <td width="169" height="55" background="Images/feature_11.gif"><div align="center"><img src="Images/feature2.jpg" width="165" height="52" bordercolor="#ffffff" border="1" /></div></td>
            <td width="20" height="55"><img src="Images/feature_12.gif" alt="Get Live Media" width="20" height="55" /></td>
            <td width="301" height="55" background="Images/feature_13.gif"><div align="center">
              
<!-- THIS IS THE START OF THE TICKER  -->	

<?

$fi = 0;

while($fi < $num)
{
    $ticker      = mysql_result($result,$fi,"feature");

$fi++;
}


echo "		  
		  <table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
                <tr>
                  <td class='style1'><marquee scrolldelay=150> $ticker </marquee></td>
                </tr>
              </table>
";
?>
		  
		  
            </div></td>
            <td width="42" height="55"><img src="Images/feature_14.gif" alt="Get Live Media" width="42" height="55" /></td>
          </tr>
          <tr>
            <td width="8" height="19"><img src="Images/feature_15.gif" alt="Get Live Media" width="8" height="19" /></td>
            <td width="18" height="19"><img src="Images/feature_16.gif" alt="Get Live Media" width="18" height="19" /></td>
            <td width="169" height="19"><img src="Images/feature_17.gif" alt="Get Live Media" width="169" height="19" /></td>
            <td width="20" height="19"><img src="Images/feature_18.gif" alt="Get Live Media" width="20" height="19" /></td>
            <td width="301" height="19"><img src="Images/feature_19.gif" alt="Get Live Media" width="301" height="19" /></td>
            <td width="42" height="19"><img src="Images/feature_20.gif" alt="Get Live Media" width="42" height="19" /></td>
          </tr>
          <tr>
            <td width="8" height="56"><img src="Images/feature_21.gif" alt="Get Live Media" width="8" height="56" /></td>
            <td width="18" height="56"><img src="Images/feature_22.gif" alt="Get Live Media" width="18" height="56" /></td>
            <td height="56" background="Images/feature_23.gif"><div align="center"><img src="Images/feature3.jpg" alt="DJ" width="165" height="52" border="1" bordercolor="#ffffff" /></div></td>
            <td width="20" height="56"><img src="Images/feature_24.gif" alt="Get Live Media" width="20" height="56" /></td>
            <td height="56" background="Images/feature_25.gif"><div align="center"><img src="Images/feature4.jpg" width="295" height="54" /></div></td>
            <td width="42" height="56"><img src="Images/feature_26.gif" alt="Get Live Media" width="42" height="56" /></td>
          </tr>
          <tr>
            <td width="8" height="24"><img src="Images/feature_27.gif" alt="Get Live Media" width="8" height="24" /></td>
            <td width="257" height="24"><img src="Images/feature_28.gif" alt="Get Live Media" width="257" height="24" /></td>
            <td width="18" height="24"><img src="Images/feature_29.gif" alt="Get Live Media" width="18" height="24" /></td>
            <td width="169" height="24"><img src="Images/feature_30.gif" alt="Get Live Media" width="169" height="24" /></td>
            <td width="20" height="24"><img src="Images/feature_31.gif" alt="Get Live Media" width="20" height="24" /></td>
            <td width="301" height="24"><img src="Images/feature_32.gif" alt="Get Live Media" width="301" height="24" /></td>
            <td width="42" height="24"><img src="Images/feature_33.gif" alt="Get Live Media" width="42" height="24" /></td>
          </tr>
          <tr>
            <td height="24" colspan="7">
<!-- START OF NEWS SECTION -->			

		<div align="center"><?
$i = 0;

while($i < $num)
{
    $subject     = mysql_result($result,$i,"subject");
    $body        = mysql_result($result,$i,"body");
$image       = mysql_result($result,$i,"image");
$date        = mysql_result($result,$i,"date");

    echo "<table width='752' border='0' align='center' cellpadding='1' cellspacing='0' bgcolor='#FFFFFF'>
  <tr>
        <td colspan=\"2\" bgcolor='#363636' beight='20'><span class='style1'><div align='left'>
          <table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
            <tr>
              <td width=\"130\"><img src=\"Images/article_header.jpg\" width=\"130\" height=\"20\" /></td>
              <td width=\"622\"><span class='style1'><div align='left'><strong>$subject</strong></div>
        </span></td>
            </tr>
          </table>
        </div>
        </span></td>
      </tr>
    <td width=\"100\">
<table width='750' border='0' align='center' cellpadding='0' cellspacing='0'>
      <tr>
        ";
	    
    if(!empty($image))
    {
      $image_url = "/news_images/".$image;

      $image_link = '<img src="'.$image_url.'" alt="Article Image" align=left height=116 width=116 border=0>';

      echo "
  <td width='120' bgcolor='#363636' valign='top'>
	<div align='center'>
	<a href='$image_url'>$image_link</a>		</div>		</td>	
    <td width='630' valign=top bgcolor='#363636'>				
	<div align='right' class='style1'>
			Article Added: $date		</div>
<hr>
	<div align='left' class='style3'>	
			$body		</div>	</td>
</tr>   
    </table></td>
  </tr>
</table><br>";
     }

 ELSE

 echo"
 <td width='750' valign=top bgcolor='#363636'>				
	<div align='right' class='style1'>
			Article Added: $date		</div>
<hr>
	<div align='left' class='style3'>	
			$body		</div>

</td>
</tr>   
    </table></td>
  </tr>
</table><br>";


    $i++;
}
?>			
<A HREF="Archive_news.php"><img src='Images/btn_archive.gif' width="121" height="26" border="0"></a></div>


		</td>
          </tr>
        </table>
      </div></td>
      <td width="117" valign="top" bgcolor="#000000"><div align="center">
        <table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
          <tr>
            <td width="117" height="80"><img src="Images/18.gif" alt="Get Live Media" width="117" height="80" /></td>
          </tr>
          <tr>
            <td height="100%"><div align="center"><img src="Images/banner_right.jpg" alt="MyCanadate" width="100" height="487" longdesc="FREE ONLINE DATING" /></div></td>
          </tr>
        </table>
      </div></td>
      <td width="34" height="80" valign="top" background="Images/21_runner_BG.gif"><img src="Images/19.gif" width="34" height="80" /></td>
    </tr>
    
<tr>
      <td width="32" height="100%" background="Images/22.gif"> </td>
      <td height="100%" colspan="4" valign="top" background="Images/23_runner_BG.gif"> </td>
      <td width="34" height="100%" background="Images/24.gif"> </td>
    </tr>
  </table>
</div>
<map name="Map" id="Map"><area shape="rect" coords="-40,3,81,37" href="Forum/" target="_blank" alt="Live Online FORUM" />
</map>
<map name="Map2" id="Map2"><area shape="rect" coords="439,3,494,38" href="Forum/" target="_blank" alt="Live Online FORUM" />
<area shape="rect" coords="-18,5,63,35" href="Series.php" alt="Live Online Series" />
<area shape="rect" coords="65,6,158,34" href="Culture.php" alt="Culture - What's Hot!" />
<area shape="rect" coords="161,6,224,36" href="Club.php" alt="Live Events" />
<area shape="rect" coords="229,6,362,33" href="Music.php" alt="Music & Entertainment" />
<area shape="rect" coords="366,6,435,35" href="Local.php" alt="Videos NEW AND OLD" />
</map>
<map name="Map3" id="Map3"><area shape="rect" coords="42,3,127,38" href="Series.php" alt="Live Online Series" />
<area shape="rect" coords="-22,6,37,36" href="Index.php" alt="News / Home" />
</map>
<map name="Map4" id="Map4"><area shape="rect" coords="11,5,45,35" href="Index.php" alt="News / Home" />
</map>

artacus · March 8, 2007

Why don't you post a specific question that you are having trouble with then instead of giving me 300 lines of code to read thru.

Andrius · March 8, 2007

question is located in 1st post. I cant run all the queries and have them display on differant areas of the page. For example

<?

$feati = 0;

while($feati < $num)
{
    $feature_image      = mysql_result($result,$feati,"image");
$feature_link       = mysql_result($result,$feati,"link");

$feati++;
}

doesn't display the "image" and "link" from the table "Header_feature" it's trying to pull the image from "News" because there are some of the same column names in each table such as "image" is seen in "News" and "Header_feature" tables. Is there anyway around this or do I have to re-name column names so there are no dupes amung tables

artacus · March 8, 2007

Ok, you're making it way harder than it needs to be.

First, try using mysql_fetch_assoc() or mysql_fetch_array()

Second, try to do all your php stuff in one location if you can instead of interspersing it w/ html

Make your db calls like so:

//get feature_header
$query = "SELECT * FROM header_feature ORDER BY RAND() LIMIT 1";
$result = mysql_query($query) or die(mysql_error());
$header_row = mysql_fetch_assoc($result);

//get header
$query = "SELECT * FROM feature ORDER BY RAND() LIMIT 1";
$result = mysql_query($query) or die(mysql_error());
$feature_row = mysql_fetch_assoc($result);
$feature_img = "<img src='Images/$feature_row[image]' border='1' /> ";

$query = "SELECT * FROM news ORDER BY `Date` DESC LIMIT 6";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
     $news_disp .= "\n\t<tr><td>$row[title]</td>...";
}

When you get to the point in your html where you want to display the stuff you've built in php, just echo it out

<table>

<?=$news_disp ?>

</table>

Andrius · March 8, 2007

wow.. just learned a lot in a few lines. thanks a ton! got msn? I may have some business for you

Sign In

Retrieving,Sorting & Displaying Multiple Results from Mutliple Tables

Recommended Posts

Andrius

Link to comment

Share on other sites

artacus

Link to comment

Share on other sites

Andrius

Link to comment

Share on other sites

artacus

Link to comment

Share on other sites

Andrius

Link to comment

Share on other sites

artacus

Link to comment

Share on other sites

Andrius

Link to comment

Share on other sites

artacus

Link to comment

Share on other sites

Andrius

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information