Using JOIN method properly?

[dyr]

I'm attempting to use the JOIN method on my read_message.php:

<?php

$userfinal = $_SESSION['id'];

$messageid = $_GET['messageid'];
$message = mysql_query("SELECT * FROM messages WHERE message_id = '$messageid' AND to_user = '$userfinal'");
$message=mysql_fetch_assoc($message);

echo "<b>Title:</b>
".$message['message_title']."<br>";

echo "<b>From:</b>
".$message['from_user']."<br><br>";

echo "<b>Message:</b>
<br>".$message['message_contents']."<br>";

echo '<form name="backfrm" method="post" action="inbox.php">';

echo '<input type="submit" value="Back to Inbox">';

echo '</form>';

$q="UPDATE messages SET message_read='1' WHERE message_id = '$messageid' AND to_user = '$userfinal'";
mysql_query($q);

?>

 

Instead of from_user displaying JUST the person's ID#, I want it to display their username and ID #.    The from_user is previously declared when sending the first message, in which it takes the current user's session ID#.  I'm wondering how I could declare the username and ID#, displayed like so in the read message (but not required when sending a new message):

 

Dyr (#1)

 

My first thought was using the join method, but not sure if that's the right solution or not.

[darkfreaks]

have you tried using UNION or UNION ALL 

[dyr]

$message = mysql_query("SELECT callname FROM users
UNION
SELECT * FROM messages WHERE message_id = '$messageid' AND to_user = '$userfinal'");

 

Thanks, I'm new to php and forgot about UNION!  I've tried doing it and it doesn't seem to want to work, leading me to believe I'm implementing that method wrong.  What mistakes am I making, I'm following the examples listed on how to use the UNION feature.

 

[darkfreaks]

http://www.mysqltutorial.org/sql-union-mysql.aspx

[dyr]

Right, except I can't use UNION because I only want the callname from the users table but want all rows from the messages table, so the balance is uneven. 

[fenway]

Right, except I can't use UNION because I only want the callname from the users table but want all rows from the messages table, so the balance is uneven. 

Then you'll need to a add a whole bunch of NULLs.  Or handle this in script, since you're getting little benefit from combining them server-side.

[dyr]

How would you suggest I modify the script to include the ID#?  It grabs the ID# from the user's session, and as far as I can tell can't grab more than one variable per session for something like this?  Thus, can't grab the username.

[fenway]

I'm just saying that you're getting no server-side benefit from the UNION.

[dyr]

I'm a bit new to PHP, how would you suggest I combine the user ID# and the display name through script?

[jbonnett]

Here's one of my mail scripts...

 

<?php
   include("include/session.php");
   if(!$session->logged_in){
             die;
   }
        include("header.php");
?>
   <h1>User Message System</h1>
   <form method="POST" action="mail.php">
      <input type="submit" name="mailAction" value="Compose" /><input type="submit" name="mailAction" value="Inbox" />
   </form>
Back To [<a href=main.php>Main</a>]
<?php

   if(!empty($_POST['mailAction']) && isset($_POST['mailAction'])){
      $action = $_POST['mailAction'];
   } else {
      $action = 'Inbox';
   }



   if(($action=="Compose") || ($action=="Reply")) {
   
      if(isset($_POST['mailSubject']) && !empty($_POST['mailSubject'])){
         $mailSubject = 'Re: '.$_POST['mailSubject'];
      } else {
         $mailSubject = "";
      }
      
      if(isset($_POST['mailFrom']) && !empty($_POST['mailFrom'])){
         $mailTo = $_POST['mailFrom'];
      } else {
         $mailTo = "";
      }
      
      ?>
   <form action="mail.php" method="POST">
             <table border="0">
           <tr><td><b>To:</b></td><td><input type="text" value="<? echo $req_user ?>" name="mailTo" size="20" value="<?=$mailTo;?>"></td></tr>
      <tr><td><b>Subject:</b></td><td><input type="text" name="mailSubject" size="20" value="<?=$mailSubject;?>"></td></tr>
             </table>
                <b>Message:</b><br>
                <textarea rows='16' cols='45' name='mailMessage'></textarea><br>
                <input type="submit" name="mailAction" value="Send" />
   </form>

      <?php
   }
   
   
   if($action=="Send") {
/* Requested Username error checking */

$userto = trim($_POST['mailTo']);
if(!$userto || strlen($userto) == 0 ||
   !eregi("^([0-9a-z])+$", $userto) ||
   !$database->usernameTaken($userto)){
   die("Username Does Not Exist!");
}
         
      if(empty($_POST['mailSubject']) || !isset($_POST['mailSubject'])){
         echo "Subject Blank";
      } else {
         $subject = $_POST['mailSubject'];
      }
      
      if(empty($userto) || !isset($userto)){
         echo "To Blank";

      } else {
         $mailTo = $userto;
      }
      
      if(empty($_POST['mailMessage']) || !isset($_POST['mailMessage'])){
         echo "Message Blank";
      } else {
         $message = $_POST['mailMessage'];
      }
      
      $date = date('m/d/Y')." at ".date('g:i.s')." ".date('a');
      
      $q   = "INSERT INTO mail (UserTo, UserFrom, Subject, Message, SentDate, status) 
                  VALUES ('$mailTo','$session->username','$subject','$message','$date','unread')";
      if(!($send = $database->query($q))){
         echo "A letter could not be sent to ".$mailTo."!";
      } else {
         echo "Message Sent to ".$mailTo."!";
      }
      
   }

   
   if($action=="Inbox") {
   
      $user = $session->username;
      $q = "SELECT * FROM mail WHERE UserTo = '$user' ORDER BY SentDate DESC";
      $getMail = $database->query($q) or die(mysql_error());

      echo "<div id=\"inbox\">";
      
      if(mysql_num_rows($getMail) == 0){
         echo "<p>You have no mail</p><br /><br />";
      } else {         
         ?>
         <table>
            <tr class="title">
               <td colspan="2" align="center">Action</td>
               <td>Status</td>
               <td>From</td>
               <td>Subject</td>
               <td>Time</td>
            </tr>
         </div>
         <?php
         while($mail = mysql_fetch_array($getMail)){
         echo "<form action='mail.php' method='post'>";
            ?>
               <tr>
                  <input type="hidden" name="mail_id" value="<?php echo $mail['mail_id']; ?>" />
                  <td align="center"><input type="submit" name="mailAction" value='View' /></td>
                  <td align="center"><input type="submit" name="mailAction" value="Delete" /></td>
                  <td><?php echo $mail['status']; ?></td>
                  <td><?php echo $mail['UserFrom']; ?></td>
                  <td><?php echo $mail['Subject']; ?></td>
                  <td><?php echo $mail['SentDate']; ?></td>
               </tr>
            <?php
         echo "</form>";

         }
      }         
      echo "</table>";
   
   }
   
   
   if($action == "View") {
   
      
      $mail_id = $_POST['mail_id'];
      $user = $session->username;
      $result = $database->query("SELECT * FROM mail WHERE UserTo = '$user' AND mail_id = '$mail_id'") or die ("cant do it");
      $row = mysql_fetch_array($result);
      
      
      if($row['UserTo'] == $session->username or $session->isAdmin) {
         echo "<font face=verdana><b>This isn't your mail!";
         exit;
      }
      
      $q = "UPDATE mail SET status='read' WHERE UserTo='$session->username' AND mail_id='$row[mail_id]'";
      $database->query($q) or die("An error occurred resulting that this message has not been marked read.");
      
      ?>
         <form method="post" action="mail.php">
            <div id="single">
               <p class="grid_1">From: </p><p class="grid_2"><?php echo $row['UserFrom']; ?><input type="hidden" name="mailFrom" value="<?php echo $row['UserFrom']; ?>" /></p>
               <p class="grid_1 clear">Subject: </p><p class="grid_2"><?php echo $row['Subject']; ?><input type="hidden" name="mailSubject" value="<?php echo$row['Subject']; ?>" /></p>
               <p class="grid_4 clear">body: <br /><?php echo $row['Message']; ?><br /></p>
               <p class="grid_4 clear" align="right"><input type="submit" name="mailAction" value="Reply" /></p>
            </div>
         </form>

      <?php
   }
   
   
   if($action == 'Delete') {
      $id = $_POST['mail_id'];
      $query = $database->query("DELETE FROM mail WHERE mail_id='$id' LIMIT 1");
      
      if(!$query) {
         echo "The message wasn\'t deleted";
      } else {
         header("Location: mail.php");
                      die;
      }
   }
?>

[fenway]

I'm a bit new to PHP, how would you suggest I combine the user ID# and the display name through script?

I just meant to combine them in php.