Andrius Posted March 7, 2007 Share Posted 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. Link to comment https://forums.phpfreaks.com/topic/41698-retrievingsorting-displaying-multiple-results-from-mutliple-tables/ Share on other sites More sharing options...
artacus Posted March 8, 2007 Share Posted 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. Link to comment https://forums.phpfreaks.com/topic/41698-retrievingsorting-displaying-multiple-results-from-mutliple-tables/#findComment-202142 Share on other sites More sharing options...
Andrius Posted March 8, 2007 Author Share Posted 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? Link to comment https://forums.phpfreaks.com/topic/41698-retrievingsorting-displaying-multiple-results-from-mutliple-tables/#findComment-202151 Share on other sites More sharing options...
artacus Posted March 8, 2007 Share Posted 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 Link to comment https://forums.phpfreaks.com/topic/41698-retrievingsorting-displaying-multiple-results-from-mutliple-tables/#findComment-202157 Share on other sites More sharing options...
Andrius Posted March 8, 2007 Author Share Posted 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> Link to comment https://forums.phpfreaks.com/topic/41698-retrievingsorting-displaying-multiple-results-from-mutliple-tables/#findComment-202191 Share on other sites More sharing options...
artacus Posted March 8, 2007 Share Posted 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. Link to comment https://forums.phpfreaks.com/topic/41698-retrievingsorting-displaying-multiple-results-from-mutliple-tables/#findComment-202210 Share on other sites More sharing options...
Andrius Posted March 8, 2007 Author Share Posted 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 Link to comment https://forums.phpfreaks.com/topic/41698-retrievingsorting-displaying-multiple-results-from-mutliple-tables/#findComment-202257 Share on other sites More sharing options...
artacus Posted March 8, 2007 Share Posted 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> Link to comment https://forums.phpfreaks.com/topic/41698-retrievingsorting-displaying-multiple-results-from-mutliple-tables/#findComment-202275 Share on other sites More sharing options...
Andrius Posted March 8, 2007 Author Share Posted March 8, 2007 wow.. just learned a lot in a few lines. thanks a ton! got msn? I may have some business for you Link to comment https://forums.phpfreaks.com/topic/41698-retrievingsorting-displaying-multiple-results-from-mutliple-tables/#findComment-202313 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.