Need help connecting tables...

gravlund · 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?

Barand · May 7, 2007

Any suggestions?

Yes, use

...

tags around you code when you post and use

<?php

?>

gravlund · May 7, 2007

OK, thanx for the tip:)

..but how about a little help with the query, please?

Barand · 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.

gravlund · May 8, 2007

I don't know what I'm doing wrong, but now I got no results at all...

gravlund · 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?

per1os · 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.

gravlund · May 10, 2007

Nothing happens...blank page

Barand · 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']};"

gravlund · 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>";

Barand · May 11, 2007

Follow Frost110's suggestion and it may give a clue

gravlund · May 13, 2007

Tried it... nothing but a blank page..

Any other suggestions?

Barand · 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

per1os · 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.

Barand · 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.

gravlund · 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>";
}

gravlund · 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

Barand · May 18, 2007

Aren't you passing the itempicid in the link and not the albundetailid?

gravlund · 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?

gravlund · May 23, 2007

No suggestions at all??

Please help me!

Barand · May 23, 2007

how is itempicid column defined?

gravlund · May 23, 2007

how do I find that out ?

Barand · 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;

gravlund · May 23, 2007

see here http://www.metalformaniacs.com/upload.php

gravlund · 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>";
?>

Sign In

Need help connecting tables...

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information