[SOLVED] I'm sure this has been posted before but I can't fi

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Antnee
Forum Newbie
Posts: 24
Joined: Fri Aug 27, 2004 7:04 am
Location: Nottingham

[SOLVED] I'm sure this has been posted before but I can't fi

Post by Antnee »

Getting data from multiple tables

Ok, simply I am trying to get data from two different tables but I can only get it from one. I am using PHP4 and MySQL (Not sure the version)

Here is my code:

Code: Select all

<?php
session_start(); 
header("Cache-control: private"); // LEAVE ME IN ELSE YOU'LL GET PROBLEMS IN IE!!! //

//-- Include the connection script --//
include ("../action/connect.php");

//-- Start setting variables --//
	//-- Message Counter --//
	$msgs = "0";

	//-- Fetch Message queries --//
	$sql = "SELECT * FROM message WHERE send_to ='".$username."' ORDER by new DESC, important DESC, realdate DESC, datesent DESC";
	$query = mysql_query($sql);

	//-- Fetch Username queries --//
	$sql1 = "SELECT * FROM verify WHERE username ='".$from."'";
	$query1 = mysql_query($sql1);
	
	$fetchname = mysql_fetch_array($query1);

//-- Start of Table --//
echo '
<table width="770" border="0">
  <tr>
    <td width="20">&nbsp;</td>
    <td width="150"><font face="Tahoma, Arial" size="3"><b>From</b></font></td>
    <td width="250"><font face="Tahoma, Arial" size="3"><b>Date Sent</b></font></td>
    <td width="350"><font face="Tahoma, Arial" size="3"><b>Subject</b></font></td>
  </tr>
  <tr>';

//-- Start fetching messages from Database --//
while ($fetchdoggy = mysql_fetch_array($query)) {
	echo '<td><font face="Tahoma, Arial" size="2">';
	
	//-- Count messages as you go --//
	$msgs = $msgs + "1";
	
		//-- Label if the message is new or not --//
		if ($fetchdoggy['new'] == "1") {
			echo "NEW</font></td>";
			} else {
			echo "</font></td>";
		}


//-- THIS IS THE PROBLEM BIT, IGNORE TRYING TO FIX THE REST!!! --//

		//-- Get the senders name from the database --//
		$from = $fetchdoggy['from'];
		echo '<td><font face="Tahoma, Arial" size="2">'.$fetchname['firstname'].' '.$fetchname['lastname'].'</font></td>';

//-- END OF PROBLEM CODE --//

	//-- Get the date the message was sent --//
	echo '<td><font face="Tahoma, Arial" size="2">'.$fetchdoggy['datesent'].'</font></td>';

	//-- Get the subject and create a link to open the message --//
	echo '<td><font face="Tahoma, Arial" size="2"><a style="text-decoration: none" href="readmail.php?msgid='.$fetchdoggy['id'].'" target="_top">';

		//-- Highlight message subject if is important --//
		if ($fetchdoggy['important'] == "1") {
			echo '<font color=#CC0000>'.$fetchdoggy['subject'].'</font></a></font></td></tr>';
			} else {
			echo $fetchdoggy['subject'].'</a></font></td></tr>';
		}
	}
	//-- End of table --//
	echo "</table>";
	
	//-- If there are no messages to display, show a message saying so --//
	if ($msgs == "0") {
	echo '<div align="center"><font face="Tahoma, Arial" size="3"><b><br/><br/><br/><br/><br/><br/>There are no messages in your inbox</b></font></div>';
}
?>
Any takers on why it's not working? I'm sure it's really obvious to you guys but I'm no expert and I've never pulled info from more than one table at a time

Thanks guys!
Last edited by Antnee on Mon Aug 30, 2004 1:08 pm, edited 3 times in total.
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post by Lord Sauron »

You're opening, closing and again opening and closing your php tags. That means the result of your query is unknown in the second php part.

Why don't you write it all in php?

Just remove the closing and opening part in the middle.
Antnee
Forum Newbie
Posts: 24
Joined: Fri Aug 27, 2004 7:04 am
Location: Nottingham

Post by Antnee »

OK, it's all PHP, but it still doesn't work. Any other ideas? (please?)

I've updated the code above rather than re-post it

All I need to do is figure out how to get info from two tables in the same database. Does anyone have an example I can study or can you write me a quick one??? :roll:
Last edited by Antnee on Mon Aug 30, 2004 12:56 pm, edited 1 time in total.
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post by Lord Sauron »

Do you receive any errors?

And what do you exactly mean with "but I can only get it from one". Does that mean the second table is empty? Or there is no query result?

By the way; no way this works. You can't simply say

Code: Select all

<?php
ECHO '
// A bunch of table code
';
?>
Last edited by Lord Sauron on Mon Aug 30, 2004 1:01 pm, edited 2 times in total.
Antnee
Forum Newbie
Posts: 24
Joined: Fri Aug 27, 2004 7:04 am
Location: Nottingham

Post by Antnee »

No errors, but then my host seems to be set up to not show ANY errors, I could write this and only get a white page:

Code: Select all

<?php
&%(^$()HELLO!
?>
Antnee
Forum Newbie
Posts: 24
Joined: Fri Aug 27, 2004 7:04 am
Location: Nottingham

Post by Antnee »

Lord Sauron wrote:Do you receive any errors?

By the way; no way this works. You can't simply say
ECHO '
// A bunch of table code
';
Oh, it works though! Hmm
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post by Lord Sauron »

Mmm, not sure if this results in an error without any more code.

Code: Select all

<?php
&%(^$()HELLO! 
?>
Last edited by Lord Sauron on Mon Aug 30, 2004 1:04 pm, edited 1 time in total.
Antnee
Forum Newbie
Posts: 24
Joined: Fri Aug 27, 2004 7:04 am
Location: Nottingham

Post by Antnee »

Lord Sauron wrote:And what do you exactly mean with "but I can only get it from one". Does that mean the second table is empty? Or there is no query result?
There is no result. The script will display a page which works like an email inbox. It shows new messages, subjects, dates sent etc. What I need it to do is get the real username from the user table that tallys up to the username that sent the message.

Originally it was set up to store the username in the message table but this will take up a lot of space in the database in the long run, hence why I tried to re-write it
Antnee
Forum Newbie
Posts: 24
Joined: Fri Aug 27, 2004 7:04 am
Location: Nottingham

Post by Antnee »

Lord Sauron wrote:Off course this is an empty page, you're not echoing

Code: Select all

<?php
&%(^$()HELLO! 
?>
OK, bad example, what I mean is that there is no errors no matter what. I know I made a mistake when I get a white page instead of the layout I was expecting
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post by Lord Sauron »

Guess the query is incorrect. What kinda database do you use?

Try it with a die.

Code: Select all

<?php
$sql = "SELECT * FROM message WHERE send_to ='".$username."' ORDER by new DESC, important DESC, realdate DESC, datesent DESC"; 
$query = mysql_query($sql) OR die("wrong query"); 
?>
Antnee
Forum Newbie
Posts: 24
Joined: Fri Aug 27, 2004 7:04 am
Location: Nottingham

Post by Antnee »

No no, that query works fine, that one gets the message out and works perfectly, its the one that gets the names from the VERIFY table that I have the problem with.

Code: Select all

<?php
   $sql1 = "SELECT * FROM verify WHERE username ='".$from."'";
   $query1 = mysql_query($sql1);
?>
It's MySQL BTW
Last edited by Antnee on Mon Aug 30, 2004 1:12 pm, edited 1 time in total.
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post by Lord Sauron »

Mmm, better look - the second query could be wrong

Watch the semicolon:

Code: Select all

<?php
    $sql1 = "SELECT * FROM verify WHERE username ='".$from."'";
?>
change into:

Code: Select all

<?php
   $sql1 = "SELECT * FROM verify WHERE username ='".$from."';";
?>
use a die on the second one too, to be sure
Last edited by Lord Sauron on Mon Aug 30, 2004 1:14 pm, edited 1 time in total.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Add some debugging:

Code: Select all

$sql1 = "SELECT * FROM verify WHERE username ='".$from."'";
echo $sql1;  //make sure the query looks ok
$query1 = mysql_query($sql1) or die(mysql_error());
Aslo put error_reporting(E_ALL); as the first line of your script to make sure there's no errors you're not seeing. A var_dump($fetchname); will show you what's in $fetchname too.
Last edited by markl999 on Mon Aug 30, 2004 1:15 pm, edited 1 time in total.
Antnee
Forum Newbie
Posts: 24
Joined: Fri Aug 27, 2004 7:04 am
Location: Nottingham

Post by Antnee »

Nope, that doesn't work either. There's nothing wring with the query. I can enter them directly into MySQL and tehy all return perfectly, it's got to have something to do with the way I'm calling it
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

After:
$fetchname = mysql_fetch_array($query1) or die(mysql_error());
What does:
var_dump($fetchname); show?
Post Reply