gravlund Posted May 7, 2007 Share Posted May 7, 2007 Hi! Please, please help me with this query... The tables from the database I'm using is: band album albumdetail itempic Example: http://www.metalformaniacs.com/band_test.php?band=133 Current query: <?php include "addinfo.php"; if(isset($_GET['band']) && is_numeric($_GET['band'])) { $bandid = $_GET['band']; } else { echo "Unknown band..."; exit(); } $sql = "SELECT * FROM band WHERE bandid=" . $bandid; $result = mysql_query($sql); $bandInfo = mysql_fetch_array($result); echo "<title>" . "Metal For Maniacs - The Ultimate " . $bandInfo['bandname'] . " Discography" . "</title>"; echo "<h2><b>" . $bandInfo['bandname'] . "</b></h2>"; if($bandInfo['picture'] != "") echo "<img style='float:right; margin-right:10px;margin-bottom:10px;' src='" . $bandInfo['picture'] ."'>"; echo " <b>Formed in:</b> " .$bandInfo['formed'] ."<br> <b>Location:</b> " . $bandInfo['location'] ."<br> <b>Status:</b> " . $bandInfo['status'] ."<br> <b>URL: </b><a href='". $bandInfo['url'] . "' target=_blank>" . $bandInfo['url'] ."</a> <br><br> <b>Current/past label(s):</b><br> ". nl2br($bandInfo['prevLabel']) . "<br> <br> ". nl2br($bandInfo['notes']) ."<br> <br> <br>"; if($bandInfo['logo'] != "") echo "<img src='" . $bandInfo['logo'] ."' style=''>"; echo" <br><br> "; //get album info $sql = "SELECT * FROM album WHERE bandid=" . $bandInfo['bandid']; $result = mysql_query($sql); while ($albumInfo = mysql_fetch_array($result)) { echo "<h2>" . $albumInfo['name'] ."</h2>"; if($albumInfo['picture'] != "") echo "<img style='float:left; margin-right:10px;margin-bottom:30px' src='" . $albumInfo['picture'] ."' HEIGHT='200'>"; else echo "<img style='float:left; margin-right:10px;margin-bottom:30px' src='nia.jpg' HEIGHT='200'>"; //track table echo "<table align=right class=track>"; $tracks = explode("\n", $albumInfo['tracks']); for($i = 1; $i <= count($tracks); $i++) { echo "<tr> <td>" . $i .".</td> <td>" . $tracks[$i-1] . "</td> </tr>"; } echo "</table>"; echo "<table> <tr> <td>"; echo "<b>Type:</b> " . $albumInfo['type'] ."<br> <b>First Released:</b> " . $albumInfo['released'] ."<br> <br><br><br><br>" . nl2br($albumInfo['notes']) ."<br> <br></td></tr>"; echo "</table>"; //<b>Line-up:</b><br> //n/a<br> echo " <table style='border:1 solid \#545460;width:850px;clear:both'> <tr style='background-color:3A3A44'><td width='30'>Format<td width='225'>Record Label<td width='150'>Catalog#<td width='85'>Made In<td width='30'>Year<td width='330'>Details</td></tr>"; //get album details $detailSQL = "SELECT * FROM albumdetail WHERE albumid=" . $albumInfo['albumid']; $detailResult = mysql_query($detailSQL); while ($details = mysql_fetch_array($detailResult)) { foreach($details as $detail) { if($detail == "") $detail = "n/a"; } echo "<tr><td>" . $details['format'] ."</td> <td>" . $details['label'] ."</td> <td>" . $details['catalognr'] ."</td> <td>" . $details['madein'] ."</td> <td>" . $details['year'] . "</td> [color=red] <td>" . $details['details'] ."</td>[/color] <td> edit </td>"; } echo "<tr><td colspan=7 align=right><br>Add a variation</td> </table> <br><br><br><br>"; } echo " <br><br><center><<<<a href='bands_test.php'>Back to Band Archives</a></center>"; include "outro.php"; ?> The section in the query that I need to change is the one in red the tables 'albumdetail' and 'itempic' has the "albumdetailid" in common, and if an item in the 'albumdetail' has a picture it will be added in the 'itempic' table. So what I want is this: IF a listing has a picture the text in the " . $details['details'] ." will be a link which views the related pictures, such as href='itempic.php?itempic= Do you understand? Any suggestions? Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/ Share on other sites More sharing options...
Barand Posted May 7, 2007 Share Posted May 7, 2007 Any suggestions? Yes, use ... tags around you code when you post and use <?php ?> Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-247556 Share on other sites More sharing options...
gravlund Posted May 7, 2007 Author Share Posted May 7, 2007 OK, thanx for the tip:) ..but how about a little help with the query, please? Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-247567 Share on other sites More sharing options...
Barand Posted May 7, 2007 Share Posted May 7, 2007 use SELECT albumdetail.*, itempic.picture FROM LEFT JOIN itempic ON albumdetail.albumdetailid = itempic.albumdetailid WHERE albumid = {$albumInfo['albumid']}; if there is no matching picture, itempic.picture will be NULL. If there is a picture record then it will contain the pic. Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-247580 Share on other sites More sharing options...
gravlund Posted May 8, 2007 Author Share Posted May 8, 2007 I don't know what I'm doing wrong, but now I got no results at all... Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-248073 Share on other sites More sharing options...
gravlund Posted May 10, 2007 Author Share Posted May 10, 2007 Sorry... here is the modified query (I am only a newb, so don't laugh): //get album details $detailSQL = "SELECT albumdetail.*, itempic.pic1 FROM LEFT JOIN itempic ON albumdetail.albumdetailid = itempic.albumdetailid WHERE albumid = {$albumInfo['albumid']};" . $albumInfo['albumid']; $detailResult = mysql_query($detailSQL); while ($details = mysql_fetch_array($detailResult)) { foreach($details as $detail) { if($detail == "") $detail = "n/a"; } echo "<tr><td>" . $details['format'] ."</td> <td>" . $details['label'] ."</td> <td>" . $details['catalognr'] ."</td> <td>" . $details['madein'] ."</td> <td>" . $details['year'] . "</td> <td>" if ($details['pic1'] !="") echo "<a href='itempic.php?itempic=". $details['itempicid'] ."'>". $details['details'] . "</a>"; else echo . $details['details'];"</td> <td> edit </td>"; So can you tell me why I don't get any results? Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-249861 Share on other sites More sharing options...
per1os Posted May 10, 2007 Share Posted May 10, 2007 Have you tried running the query in phpMyAdmin to daignose it and make sure it works with known data you are passing in? I would also suggest adding this line for dev. <?php //get album details $detailSQL = "SELECT albumdetail.*, itempic.pic1 FROM LEFT JOIN itempic ON albumdetail.albumdetailid = itempic.albumdetailid WHERE albumid = {$albumInfo['albumid']};" . $albumInfo['albumid']; $detailResult = mysql_query($detailSQL) or die(mysql_error()); To see if the sql statement is throwing an error. Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-249877 Share on other sites More sharing options...
gravlund Posted May 10, 2007 Author Share Posted May 10, 2007 Nothing happens...blank page Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-250011 Share on other sites More sharing options...
Barand Posted May 10, 2007 Share Posted May 10, 2007 Sorry, I left out the albumdetail tablename in the FROM clause. Query should be $detailSQL = "SELECT albumdetail.*, itempic.pic1 FROM albumdetail LEFT JOIN itempic ON albumdetail.albumdetailid = itempic.albumdetailid WHERE albumid = {$albumInfo['albumid']};" Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-250085 Share on other sites More sharing options...
gravlund Posted May 11, 2007 Author Share Posted May 11, 2007 I still get a blank page, what's wrong? $detailSQL = "SELECT albumdetail.*, itempic.pic1 FROM albumdetail LEFT JOIN itempic ON albumdetail.albumdetailid = itempic.albumdetailid WHERE albumid = {$albumInfo['albumid']};" . $albumInfo['albumid']; $detailResult = mysql_query($detailSQL); while ($details = mysql_fetch_array($detailResult)) { foreach($details as $detail) { if($detail == "") $detail = "n/a"; } echo "<tr><td>" . $details['format'] ."</td> <td>" . $details['label'] ."</td> <td>" . $details['catalognr'] ."</td> <td>" . $details['madein'] ."</td> <td>" . $details['year'] . "</td>" if ($details['pic1'] !=""); echo "<td><a href='itempic.php?itempic=". $details['itempicid'] ."'>". $details['details'] . "</a>"; else echo . $details['details'] . "</td> <td> edit </td>"; Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-250455 Share on other sites More sharing options...
Barand Posted May 11, 2007 Share Posted May 11, 2007 Follow Frost110's suggestion and it may give a clue Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-250745 Share on other sites More sharing options...
gravlund Posted May 13, 2007 Author Share Posted May 13, 2007 Tried it... nothing but a blank page.. Any other suggestions? Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-251948 Share on other sites More sharing options...
Barand Posted May 13, 2007 Share Posted May 13, 2007 <?php //get album details $detailSQL = "SELECT albumdetail.*, itempic.pic1 FROM albumdetail LEFT JOIN itempic ON albumdetail.albumdetailid = itempic.albumdetailid WHERE albumid = {$albumInfo['albumid']};" $detailResult = mysql_query($detailSQL) or die(mysql_error()); ?> Should give an error message if nothing else Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-251966 Share on other sites More sharing options...
per1os Posted May 13, 2007 Share Posted May 13, 2007 <?php //get album details $detailSQL = "SELECT albumdetail.*, itempic.pic1 FROM albumdetail LEFT JOIN itempic ON albumdetail.albumdetailid = itempic.albumdetailid WHERE albumid = {$albumInfo['albumid']};" $detailResult = mysql_query($detailSQL) or die(mysql_error()); ?> Should give an error message if nothing else I would modify it like this for testing: <?php //get album details $detailSQL = "SELECT albumdetail.*, itempic.pic1 FROM albumdetail LEFT JOIN itempic ON albumdetail.albumdetailid = itempic.albumdetailid WHERE albumid = {$albumInfo['albumid']};" $detailResult = mysql_query($detailSQL) or die(mysql_error()); echo 'No SQL Error here!<br />'; while ($row = mysql_fetch_assoc($detailResult)) { echo '<pre>',print_r($row),'</pre><br />'; } ?> See what happens. At the very least something should be printed to the screen. Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-252083 Share on other sites More sharing options...
Barand Posted May 13, 2007 Share Posted May 13, 2007 Note, if you are running the query on its own and not in the original script you need to replace {$albumInfo['albumid']} in the query with a valid album id. Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-252165 Share on other sites More sharing options...
gravlund Posted May 18, 2007 Author Share Posted May 18, 2007 I finally got it to work: $detailSQL = "SELECT albumdetail.*, itempic.* FROM albumdetail LEFT JOIN itempic ON albumdetail.albumdetailid = itempic.albumdetailid WHERE albumid=" . $albumInfo['albumid']; $detailResult = mysql_query($detailSQL); while ($details = mysql_fetch_array($detailResult)) { foreach($details as $detail) { if($detail == "") $detail = "n/a"; } echo "<tr><td>" . $details['format'] ."</td> <td>" . $details['label'] ."</td> <td>" . $details['catalognr'] ."</td> <td>" . $details['madein'] ."</td> <td>" . $details['year'] . "</td>"; if($details['pic1'] != "") echo "<td><a href='itempic.php?itempic=". $details['itempicid'] ."'>". $details['details'] . "</a></td>"; else echo "<td>" .$details['details'] . "</td>"; echo "<td> edit </td>"; } echo "<tr><td colspan=7 align=right><br>Add a variation</td> </table> <br><br><br><br>"; } Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-256372 Share on other sites More sharing options...
gravlund Posted May 18, 2007 Author Share Posted May 18, 2007 But now I don't seem to get the query on the linked page to work.. (itempic.php) here's what I have for a test: <? include "test_intro.php"; if(isset($_GET['itempic']) && is_numeric($_GET['itempic'])) { $details = $_GET['itempic']; } $picSQL = "SELECT * FROM itempic WHERE albumdetailid=". $details['albumdetailid']; $picResult = mysql_query($picSQL)or die (mysql_error()); while ($pics = mysql_fetch_array($picResult)) { echo $pics['pictext1']." "; } include "outro.php"; ?> See the page here http://www.metalformaniacs.com/itempic.php?itempic=2 Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-256378 Share on other sites More sharing options...
Barand Posted May 18, 2007 Share Posted May 18, 2007 Aren't you passing the itempicid in the link and not the albundetailid? Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-256381 Share on other sites More sharing options...
gravlund Posted May 19, 2007 Author Share Posted May 19, 2007 Ofcourse, lol I've inserted 18 rows in the itempic table now, but the page will only show information for itempicid's up to 9... The links on this page works: http://www.metalformaniacs.com/band.php?band=40 but not on this page: http://www.metalformaniacs.com/band.php?band=5 why can this be? Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-256955 Share on other sites More sharing options...
gravlund Posted May 23, 2007 Author Share Posted May 23, 2007 No suggestions at all?? Please help me! Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-259767 Share on other sites More sharing options...
Barand Posted May 23, 2007 Share Posted May 23, 2007 how is itempicid column defined? Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-259800 Share on other sites More sharing options...
gravlund Posted May 23, 2007 Author Share Posted May 23, 2007 how do I find that out ? Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-259939 Share on other sites More sharing options...
Barand Posted May 23, 2007 Share Posted May 23, 2007 Depends on what mysql admin software you use. One way is use mysql command line and enter describe itempic; at the prompt; Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-259949 Share on other sites More sharing options...
gravlund Posted May 23, 2007 Author Share Posted May 23, 2007 see here http://www.metalformaniacs.com/upload.php Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-259953 Share on other sites More sharing options...
gravlund Posted May 25, 2007 Author Share Posted May 25, 2007 Any suggestions? If it helps... here is the code for the itempic.php: <style> .outro a{font-family:verdana;font-size:13px;color:silver;text-decoration:none;} .outro a:hover {font-family:verdana;font-size:13px;color:#F0B432;text-decoration:overline underline} .gravlund{font-family:verdana;font-size:13px;color:#FFFFFF} .gravlund a{font-family:verdana;font-size:12px;color:#408080;text-decoration:none} .gravlund a:active {font-family:verdana;font-size:12px;color:#CCFFFF;text-decoration:none} .gravlund a:visited{font-family:verdana;font-size:12px;color:#4F5C71;text-decoration:none} .gravlund a:hover{font-family:verdana;font-size:12px;color:#F0B432;text-decoration:overline underline} .gravlund img {border:1px solid silver} .gravlund td {font-family:verdana;font-size:10px;color:silver} .gravlund hr{width:413px;} </style> </head> <body class=outro style='background-color:#3A3A45'> <center> <table> <tr><td class=gravlund style='padding-top:30px;padding-bottom:5px;'> <? if(isset($_GET['itempic']) && is_numeric($_GET['itempic'])) { $details = $_GET['itempic']; } $picSQL = "SELECT * FROM itempic WHERE itempicid=". $details['albumdetailid']; $picResult = mysql_query($picSQL)or die (mysql_error()); while ($pics = mysql_fetch_array($picResult)) { if($pics['pictext1'] != "") echo "<center>" . $pics['pictext1']."<br>"; if($pics['pic1'] != "") echo "<img src='" . $pics['pic1'] ."'><br><br>"; if($pics['pictext2'] != "") echo "<center>" . $pics['pictext2']."<br>"; if($pics['pic2'] != "") echo "<img src='" . $pics['pic2'] ."'><br><br>"; if($pics['pictext3'] != "") echo "<center>" . $pics['pictext3']."<br>"; if($pics['pic3'] != "") echo "<img src='" . $pics['pic3'] ."'><br><br>"; if($pics['pictext4'] != "") echo "<center>" . $pics['pictext4']."<br>"; if($pics['pic4'] != "") echo "<img src='" . $pics['pic4'] ."'><br><br>"; if($pics['pictext5'] != "") echo "<center>" . $pics['pictext5']."<br>"; if($pics['pic5'] != "") echo "<img src='" . $pics['pic5'] ."'><br><br>"; if($pics['pictext6'] != "") echo "<center>" . $pics['pictext6']."<br>"; if($pics['pic6'] != "") echo "<img src='" . $pics['pic6'] ."'><br><br>"; if($pics['pictext7'] != "") echo "<center>" . $pics['pictext7']."<br>"; if($pics['pic7'] != "") echo "<img src='" . $pics['pic7'] ."'><br><br>"; if($pics['pictext8'] != "") echo "<center>" . $pics['pictext8']."<br>"; if($pics['pic8'] != "") echo "<img src='" . $pics['pic8'] ."'><br><br>"; if($pics['pictext9'] != "") echo "<center>" . $pics['pictext9']."<br>"; if($pics['pic9'] != "") echo "<img src='" . $pics['pic9'] ."'><br><br>"; } echo "</table></center>"; echo "</center> </body> </html>"; ?> Link to comment https://forums.phpfreaks.com/topic/50383-need-help-connecting-tables/#findComment-261520 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.