Page 1 of 2

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

Posted: Mon Aug 30, 2004 12:39 pm
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!

Posted: Mon Aug 30, 2004 12:49 pm
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.

Posted: Mon Aug 30, 2004 12:54 pm
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:

Posted: Mon Aug 30, 2004 12:55 pm
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
';
?>

Posted: Mon Aug 30, 2004 12:58 pm
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!
?>

Posted: Mon Aug 30, 2004 12:58 pm
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

Posted: Mon Aug 30, 2004 1:02 pm
by Lord Sauron
Mmm, not sure if this results in an error without any more code.

Code: Select all

<?php
&%(^$()HELLO! 
?>

Posted: Mon Aug 30, 2004 1:04 pm
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

Posted: Mon Aug 30, 2004 1:05 pm
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

Posted: Mon Aug 30, 2004 1:08 pm
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"); 
?>

Posted: Mon Aug 30, 2004 1:09 pm
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

Posted: Mon Aug 30, 2004 1:12 pm
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

Posted: Mon Aug 30, 2004 1:14 pm
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.

Posted: Mon Aug 30, 2004 1:14 pm
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

Posted: Mon Aug 30, 2004 1:16 pm
by markl999
After:
$fetchname = mysql_fetch_array($query1) or die(mysql_error());
What does:
var_dump($fetchname); show?