Jump to content

MySQL query multiple tables

kingnutter

By kingnutter
in PHP Coding Help

Recommended Posts

kingnutter Member

kingnutter

Posted
Posted

Hi everyone,

 

I am trying to echo information from two table on one page to no avail. Please see code below.

 

The field moj_id is in the tracks table too if that changes anything.

 

Many thanks.

 

Gary

 

<html>
<head></head>
<body>

<!-- standard page header begins -->
<p> <p>

<table width="100%" cellspacing="0" cellpadding="5">
<tr>
<td></td>
</tr>
<td bgcolor="Brown"><font size="-1" color="White" face="courier new">
<b>Mojo Cover CDs</b></font>
</td>
</tr>
</table>
<!--standard page header ends -->


<?php
// includes
include('conf.php');
include('functions.php');

// check for record id
if ((!isset($_GET['id']) || trim($_GET['id']) == ''))


{
die('Missing record ID: No such CD in Database');
}

// open database connection
$connection = mysql_connect($host, $user, $pass)
or die ('Unable to connect');

//select database
mysql_select_db($db) or die ('Unable to select database');

// generate and execute query
$id = $_GET['id'];
$query = "SELECT track_no, track_title, track_artist FROM tracks WHERE moj_id = '$id' ORDER BY track_no";
$query2 = "SELECT moj_title FROM mojocd WHERE moj_id='$id'
";

$result = mysql_query($query)
or die ("Error in query: $query. " . mysql_error());
$result2 = mysql_result($query2)
or die ("Error in query: $query2. " . mysql_error());
?>
<font face="courier new">

<? php echo "$result2";

if (mysql_num_rows($result2) > 0
{
// get resultset as object
while ($row = mysql_fetch_array($result2))

//print details

{
?>

	<p />
	<b><?php echo $row->moj_title; ?>
	</b>

<?php echo "$result2"; ?>

<?php

// if records present
if (mysql_num_rows($result) > 0)
{
// iterate through resultset
// print CD titles
while($row = mysql_fetch_object($result))
{
?>
<table>
<tr>
	<td>
	<?php echo $row->track_no; ?>
	</td>
	<td>
	<?php echo $row->track_title; ?></td>

	<td><?php echo $row->track_artist; ?>
	</td>
	</tr>
<?php
}


// if no records present
// display message

?>
</table>

<?php
} else

{
?>
<font size="-1">None CDs!</font>
<?php
}
// close database connection
mysql_close($connection);
?>

<!-- standard page footer begins -->
<p>
<table width="100%" cellspacing="0" cellpadding="5">
<tr>
<td align="center"><font size="-2">
A King Nutter Production</td>
</tr>
</table>
<!-- standard page footer ends -->

</body>
</html>

Link to comment
https://forums.phpfreaks.com/topic/149433-mysql-query-multiple-tables/
Share on other sites
kingnutter Member

kingnutter

Posted
  • Author
Posted

Hmm. Not sure I agree with that example kittrellbj. The lines you refer to are all continued with an 'or die' statement.

 

Still, I've gone over it all carefully and still no joy. Ocassionally I will get $result2 to give me "Resource ID x" which means nothing to me.

 

Am I right to split my query into $result and $result2?

 

Current non-working code below.

 

<html>
<head></head>
<body>

<!-- standard page header begins -->
<p> <p>

<table width="100%" cellspacing="0" cellpadding="5">
<tr>
<td></td>
</tr>
<td bgcolor="Brown"><font size="-1" color="White" face="courier new">
<b>Mojo Cover CDs</b></font>
</td>
</tr>
</table>
<!--standard page header ends -->


<?php
// includes
include('conf.php');
include('functions.php');

// check for record id
if ((!isset($_GET['id']) || trim($_GET['id']) == ''))


{
die('Missing record ID: No such CD in Database');
}

// open database connection
$connection = mysql_connect($host, $user, $pass)
or die ('Unable to connect');

//select database
mysql_select_db($db) or die ('Unable to select database');

// generate and execute query
$id = $_GET['id'];
$query = "SELECT track_no, track_title, track_artist FROM tracks WHERE moj_id = '$id' ORDER BY track_no";

$result = mysql_query($query)
or die ("Error in query: $query. " . mysql_error());

$result2 = mysql_query("SELECT moj_title FROM mojocd WHERE moj_id='$id'")
or die;

?>
<font face="courier new">

<?php echo "$result2"; ?>

<?php


// if records present
if (mysql_num_rows($result) > 0)
{
// iterate through resultset
// print CD titles
while($row = mysql_fetch_object($result));
{
?>
<table>
<tr>
	<td>
	<?php echo $row->track_no; ?>
	</td>
	<td>
	<?php echo $row->track_title; ?></td>

	<td><?php echo $row->track_artist; ?>
	</td>
	</tr>
<?php
}


// if no records present
// display message

?>
</table>

<?php
} else

{
?>
<font size="-1">None CDs!</font>
<?php
}
// close database connection
mysql_close($connection);
?>

<!-- standard page footer begins -->
<p>
<table width="100%" cellspacing="0" cellpadding="5">
<tr>
<td align="center"><font size="-2">
A King Nutter Production</td>
</tr>
</table>
<!-- standard page footer ends -->

</body>
</html>

  • 2 weeks later...
kittrellbj Member

kittrellbj

Posted
Posted

Actually, you are right about that, sorry.  Didn't have my glasses on. :P

 

When you receive a Resource ID error, it means that you are trying to echo something that can't be echo'ed under normal means.  For instance, you have $result2 which is a pull from the database.  Even if PHP pulls a single records (or single value) from MySQL, it places it into an array.  So, $result2 needs to be turned into a usable value.

 

To do this, you use mysql_fetch_* (array, assoc, object, etc.) to turn it into a usable value.  So, try this:

 

instead of

<?php echo $result2; ?>

 

just for troubleshooting reasons:

<?php
$count = mysql_num_rows($result2);
echo "$count <BR><BR>";
$result_r = mysql_fetch_assoc($result2);
print_r($result2);
?>

 

And see what the array looks like.  If $count is greater than 1, you will have to use a loop to cycle through to echo them all.

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.