table linking

[conan318]

i have 2 tables one is called users which holds all the users profile details and another table called status when a users updates there status and posts there status to the page inside the status table statusid, status, and username. now what iam trying to do is when some clicks on someone status to bring up there profile from the users table.

 

my code which doesnt work

$data = mysql_query("SELECT * FROM users,  Where status.username=users.username  ORDER BY id DESC LIMIT 1;") or die(mysql_error());
//Puts it into an array 
while($info = mysql_fetch_array( $data )) 
{ 

 

thanks in advance

[Skewled]

$data = mysql_query("SELECT * FROM users LEFT JOIN status  ON status.username=users.username  ORDER BY id DESC LIMIT 1;") or die(mysql_error());
//Puts it into an array 
while($info = mysql_fetch_array( $data )) 
{ 

 

Using a JOIN to link the tables, give it a shot.

[conan318]

thanks that worked can u explain in english for this newbie why it worked or post a link to somewhere on this so i can learn more about this thanks again

[Skewled]

http://www.tizag.com/mysqlTutorial/mysqljoins.php

 

That's a small tutorial on using JOINS I would recommend that you also check out the documentation on MySQL here:

 

http://dev.mysql.com/doc/refman/5.5/en/join.html

[conan318]

thanks mate just tested it further but it seems to load the same profile ill check out the tutorial

[Skewled]

$data = mysql_query("SELECT * FROM users LEFT JOIN status  ON status.username=users.username WHERE username = '".$username."' ORDER BY id DESC LIMIT 1;") or die(mysql_error());

 

 

$username could be from a $_GET variable and default to a $_SESSION variable when your viewing your own status, if that makes sense.

 

Here's a $_GET example using a $_SESSION variable to determine who is viewing the page and what information should be displayed.

 

<?php
// Let's pretend you have a session saved when a user logs into the site and it's set to
// $_SESSION['username']

// This should work if you're passing the username in a url link to the script where your
// going to be viewing the user status

if (!isset($_GET['username'])) {
       $data = mysql_query("SELECT * FROM users LEFT JOIN status  ON status.username=users.username WHERE username = '".$_SESSION['username']."' ORDER BY id DESC LIMIT 1;") or die(mysql_error());
}  else  {
       $getuname = mysql_real_escape_string($_GET['username']);
       $data = mysql_query("SELECT * FROM users LEFT JOIN status  ON status.username=users.username WHERE username = '".$username."' ORDER BY id DESC LIMIT 1;") or die(mysql_error());
}

// go on with your bussiness here for how you deal with the data
// Like fetching an array from the above query and displaying the results how you
// see fit
?>

 

 

Given the original query you'll always be pulling the first row from the database each time.

 

Hope that helps!

 

 

[conan318]

<?php
session_start();
$myusername=$_SESSION['myusername'];
require "database.php";

if (!isset($_GET['myusername'])) {
       $data = mysql_query("SELECT * FROM users LEFT JOIN status  ON status.username=users.username WHERE username = '".$_SESSION['myusername']."' ORDER BY id DESC LIMIT 1;") or die(mysql_error());
}  else  {
       $getuname = mysql_real_escape_string($_GET['username']);
       $data = mysql_query("SELECT * FROM users LEFT JOIN status  ON status.username=users.username WHERE username = '".$myusername."' ORDER BY id DESC LIMIT 1;") or die(mysql_error());

 

error

Column 'username' in where clause is ambiguous ??

 

[Skewled]

opps forgot the JOIN

 

WHERE username = '".$username."'

 

Should be:

 

WHERE status.username = '".$username."'

 

 

That should do it.

[conan318]

is it

 

WHERE username = '".$username."' or

 

WHERE username = '"$_SESSION.$username."'

[Skewled]

Everything remains the same you just have to change

 

WHERE username

 

to:

 

WHERE status.username

 

Also, that example I gave you isn't going to work unless your using $_GET to load the profile page and passing username to the script, and I'm assuming that your using $_SESSION variables. So you should take it as just an example and not try to plug it into exsisting code.

[conan318]

this is my code im using $_session[myusername] now is the $_get getting the username from status table or is it getting the username from the page b4 this im bit confused only used the $_Get to get infomation from a form.

 

im getting a blank screen atm with no errors

 

<?php
session_start();
$myusername=$_SESSION['myusername'];
require "database.php";

if (!isset($_GET['username'])) {
       $data = mysql_query("SELECT * FROM users LEFT JOIN status  ON status.username=users.username WHERE status.username = '".$_SESSION['myusername']."' ORDER BY id DESC LIMIT 1;") or die(mysql_error());
}  else  {
       $getuname = mysql_real_escape_string($_GET['username']);
       $data = mysql_query("SELECT * FROM users LEFT JOIN status  ON status.username=users.username WHERE status.username = '".$username."' ORDER BY id DESC LIMIT 1;") or die(mysql_error());
while($info = mysql_fetch_array( $data )) {





//Outputs the image and other data
Echo '<form name="newmsgfrm" method="post" action="new_message.php">';
Echo '<input type="submit" value="Send a New Message">';
Echo '</form>';
Echo "<img src='http://datenight.netne.net/images/".$info['img'] ."' width='150' height='250''> <br>"; 

  Echo "<b>Name:</b> ".$info['username'] . "<br> <hr>"; 

}

 

[Skewled]

I don't know how your accessing the script below, are you clicking a link to view the user? If you are then are you sending $_GET? I fixed the below code you didn't end the If/Else loop properly.

 

 

<?php
session_start();
$myusername=$_SESSION['myusername'];
require "database.php";

if (!isset($_GET['username'])) {
       $data = mysql_query("SELECT * FROM users LEFT JOIN status  ON status.username=users.username WHERE status.username = '".$_SESSION['myusername']."' ORDER BY id DESC LIMIT 1;") or die(mysql_error());
}  else  {
       $getuname = mysql_real_escape_string($_GET['username']);
       $data = mysql_query("SELECT * FROM users LEFT JOIN status  ON status.username=users.username WHERE status.username = '".$getuname."' ORDER BY id DESC LIMIT 1;") or die(mysql_error());
}

while($info = mysql_fetch_array( $data )) {





//Outputs the image and other data
Echo '<form name="newmsgfrm" method="post" action="new_message.php">';
Echo '<input type="submit" value="Send a New Message">';
Echo '</form>';
Echo "<img src='http://datenight.netne.net/images/".$info['img'] ."' width='150' height='250''> <br>"; 

  Echo "<b>Name:</b> ".$info['username'] . "<br> <hr>"; 

}

 

[conan318]

just tryed your code above and its bring up the users profile details not the persons profile they click on

if(isset($_POST['submit']))
{
    $status = $_POST['status'];
    mysql_query("INSERT INTO status (status, username ) VALUES ('$status','$myusername')") OR die("Could not send the message: <br>".mysql_error()); 
}
?>
<div class="send">
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
   

<INPUT type="text" name="status" SIZE="50"   VALUE="">  
<input type="hidden" name="submit"  value="Update Status">
   
</form>
</div>
<?

  
  $data = mysql_query("SELECT * FROM users, status Where users.username=status.username  ORDER BY statusid DESC LIMIT 50;") or die(mysql_error());
//Puts it into an array 
while($info = mysql_fetch_array( $data )) 
{ 
?>

<div class="status">
  <?
Echo "<a href='viewprofile.php'>  <img src='http://datenight.netne.net/images/".$info['img'] ."' width='50' height='50''></a> ". "   " . $info['username'] . " " . "says  " . $info['status'] . "<br> <hr>"; 



}


?>

on this page sends the form and echo's back on the same page

the image is where the link to profile 

[Skewled]

So try this:

 

<a href='viewprofile.php?= ' . $info['username'] . '><img src='http://datenight.netne.net/images/".$info['img'] ."' width='50' height='50''></a> ". "   " . $info['username'] . " " . "says  " . $info['status'] . "<br> <hr>"; 

 

That way when they click the link you'll pass the username over, just // comment out your link and paste this one under it. If it works yay, if not you still have the original.

[conan318]

Echo '<a href="viewprofile.php?username=' . $info['username'] . '"><img src="http://datenight.netne.net/images/'.$info['img'] .'" width="50" height="50"></a> '." ". $info['username'] . " " . "says  " . $info['status'] . "<br> <hr>"; 

 

 

is all working fine now took me awhile but we got there...

 

thanks for your help saved me hours