Jump to content

MySQL query multiple tables

kingnutter

By kingnutter
in PHP Coding Help

 Share

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
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.