PHP MYSQL get a users first name and surname from their user ID

[dannyp100]

I'm currently writing a function that will return a users firstname and surname instead of their user ID.

I need help with the code and logic.

 

Here is my function:

 

<?php
function getNameFromID($userID){

include 'gradnetconn.php';
$query = ("SELECT userFirstName , userSurname FROM gn_users WHERE userID = userID");
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
return $row['userFirstName\'. \'userSurname'];
}
}

 

Here is where i return it in a users inbox to see who their message was from:

 

<?php
include 'gradnetconn.php';
include 'functions.php';
session_start();

$messageTo = $_SESSION['userID'];
$sql = mysql_query("SELECT * FROM gn_messages WHERE messageTo = $messageTo AND messageDeleted = '0'
ORDER BY messageDate DESC");
?>
<table width='95%'>
<tr><th>From</th><th>Message Subject</th><th>Date Recieved</th></tr>
<?php
 while($return = mysql_fetch_object($sql)) {

  $messageDate = gmdate("M d Y H:i:s",$return->messageDate);
  if($return->messageRead == "0") {
  $messageRead = "notread.jpg";
  }
  else {
  $messageRead = "read.jpg";
  }
  echo "<tr><td><img src='".$messageRead."' /></td>

$fromUser = getNameFromID($return->messageFrom);

<tr><td>$return->fromUser</td><td><a href='viewmessage1.php?messageID=$return->messageID'>$return->messageSubject</a>
  </td><td>$messageDate</td></tr>";
}

?>
</table>

 

 

Any help/Suggestions?

 

Thanks

[Jessica]

You can't just concatenate random stuff inside an array key string and expect it to do anything.

 

You can however concat it IN the query. Either of these.

SELECT CONCAT_WS(' ', userFirstName, userSurname) AS full_name FROM gn_users -- Rest of query
SELECT CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_users -- Rest of query

[dannyp100]

thankyou very much!

Below the SQL statement i would just return $full_name in the $row?

Then when i call the function in the inbox page, it would display the name instead of the userID?

 

thanks

[Jessica]

Did you try it?

[dannyp100]

Tried to SQL statement and it works perfectly, thankyou so much!

All i need to understand now is how to put it in a row and then return it on the inbox page so it displays full_name?

 

<?php
function getNameFromID($userID){

include 'gradnetconn.php';
$query = ("SELECT CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_users WHERE userID = userID");
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
return $row['full_name'];
}
}

 

Then the part on the bottom of my inbox code:

 

$fromUser = getNameFromID($return->messageFrom);
  <tr><td>$fromUser</td><td><a href='viewmessage1.php?messageID=$return->messageID'>$return->messageSubject</a>
  </td><td>$messageDate</td></tr>";
}

?>
</table>

 

Comes up with errors

Notice: Undefined variable: fromUser on line 34

 

Notice: Undefined variable: fromUser on line 36

= getNameFromID(6);

 

thanks

[Jessica]

That's not valid PHP. Is that your actual code? It has syntax errors. You can't just start writing HTML without either putting it in a string or exiting PHP. Please post your *actual* code. 

[dannyp100]

functions.php

 

<?php
function getNameFromID($userID){

include 'gradnetconn.php';
$query = ("SELECT CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_users WHERE userID = userID");
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
echo( $row['full_name']);
}
}

 

inbox code:

 

<?php
include 'gradnetconn.php';
include 'functions.php';
session_start();

$messageTo = $_SESSION['userID'];
$sql = mysql_query("SELECT * FROM gn_messages WHERE messageTo = $messageTo AND messageDeleted = '0'
ORDER BY messageDate DESC");
?>
<table width='95%'>
<tr><th>From</th><th>Message Subject</th><th>Date Recieved</th></tr>
<?php
 while($return = mysql_fetch_object($sql)) {

  $messageDate = gmdate("M d Y H:i:s",$return->messageDate);
  if($return->messageRead == "0") {
  $messageRead = "notread.jpg";
  }
  else {
  $messageRead = "read.jpg";
  }
  echo "<tr><td><img src='".$messageRead."' /></td>
  $fromUser = getNameFromID($return->messageFrom);
  <tr><td>$fromUser</td><td><a href='viewmessage1.php?messageID=$return->messageID'>$return->messageSubject</a>
  </td><td>$messageDate</td></tr>";
}

?>
</table>

[dannyp100]

apologies!! I have just realised a silly error, heres the ammended inbox code:

 

<?php
include 'gradnetconn.php';
include 'functions.php';
session_start();

$messageTo = $_SESSION['userID'];
$fromUser = getNameFromID($return->messageFrom);
$sql = mysql_query("SELECT * FROM gn_messages WHERE messageTo = $messageTo AND messageDeleted = '0'
ORDER BY messageDate DESC");
?>
<table width='95%'>
<tr><th>From</th><th>Message Subject</th><th>Date Recieved</th></tr>
<?php
 while($return = mysql_fetch_object($sql)) {

  $messageDate = gmdate("M d Y H:i:s",$return->messageDate);
  if($return->messageRead == "0") {
  $messageRead = "notread.jpg";
  }
  else {
  $messageRead = "read.jpg";
  }
  echo "<tr><td><img src='".$messageRead."' /></td>

  <tr><td>$fromUser</td><td><a href='viewmessage1.php?messageID=$return->messageID'>$return->messageSubject</a>
  </td><td>$messageDate</td></tr>";
}

?>
</table>

[Jessica]

Well for one, your function echos the text, not returns it. 

 

You also have random PHP inside a string. You can put variables inside a string, you can't put entire exp<b></b>ressions and functions inside them and have it work.

 

Edit: If you keep changing it we can't help you. Now your code is using $return before it's defined. Read your error messages and attempt them.

 

 

The HUGE problem is that you're running a query inside a loop by doing this. You should INNER JOIN the gn_messages table to the gn_users table and do ONE query.

Edited February 8, 2013 by Jessica

[dannyp100]

Sorry if got confused, i fixed all the silly errors i made.

Heres the functions.php code:

 

<?php
function getNameFromID($userID){

include 'gradnetconn.php';
$query = ("SELECT CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_users WHERE userID = userID");
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
return( $row['full_name']);
}
}

 

Inbox code:

 

<?php
include 'gradnetconn.php';
include 'functions.php';
session_start();

$messageTo = $_SESSION['userID'];

$sql = mysql_query("SELECT * FROM gn_messages WHERE messageTo = $messageTo AND messageDeleted = '0'
ORDER BY messageDate DESC");
?>
<table width='95%'>
<tr><th>From</th><th>Message Subject</th><th>Date Recieved</th></tr>
<?php
 while($return = mysql_fetch_object($sql)) {
  $fromUser = getNameFromID($return->messageFrom);
  $messageDate = gmdate("M d Y H:i:s",$return->messageDate);
  if($return->messageRead == "0") {
  $messageRead = "notread.jpg";
  }
  else {
  $messageRead = "read.jpg";
  }
  echo "<tr><td><img src='".$messageRead."' /></td>

  <tr><td>$fromUser</td><td><a href='viewmessage1.php?messageID=$return->messageID'>$return->messageSubject</a>
  </td><td>$messageDate</td></tr>";
}

?>
</table>

 

So it works now!

The only problem is, in the user ID field, it is returning the wrong name for the messages. It is returning

the name of 'Nial McGowan' which is the first full_name field when the query is run. I doesn't seem to be matching the Full_name with the correct userID

[Jessica]

$query = ("SELECT CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_users WHERE userID = userID");

 

userID will always be equal to userID

 

 

Come on. 

 

You need to make this one query with an INNER JOIN. See my post above.

[dannyp100]

I have never done inner joins before, i'm a total beginner.

I know this will be wrong but

$query = ("SELECT CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_users INNER JOIN gn_messages WHERE userID = userID");

 

would the userID have to be equal to the 'messageTo' field is thats the column where the name appears in the inbox page?

[dannyp100]

sorry *messageFrom

[Jessica]

userID is a column.

userID is a column.

Does userID = userID? Yes, on EVERY SINGLE ROW. 

 

Does 1=1? Yes, on EVERY SINGLE ROW.

Does 'bob' = 'bob'? Yes, on EVERY SINGLE ROW.

 

See a theme?

 

 

You did it correctly for the first query:

"SELECT * FROM gn_messages WHERE messageTo = $messageTo AND messageDeleted = '0'
ORDER BY messageDate DESC"

 

Now add your inner join to that query. 

"SELECT gn_messages.*,  
CONCAT(userFirstName, ' ', userSurname) AS full_name
FROM gn_messages 
INNER JOIN gn_users
ON gn_users.user_id = gn_messages.messageTo -- assuming you want the user's name who it is to. You'll need to do this join AGAIN to get the sender's name as well.
WHERE messageTo = $messageTo AND messageDeleted = '0'
ORDER BY messageDate DESC"

 

On a totally unrelated note I suggest picking a naming convention and sticking with it. Switching from camelCase to underscores_and_back is really annoying.

[dannyp100]

I managed to figure it out just before you posted this

Thankyou for your help and advice and bearing with me!

[Jessica]

Good job. Post the query for me?

[dannyp100]

This is probably a rookie way of doing it, but these are the functions in order for the full name to display on the inbox and outbox

1st function for inbox, second for outbox

<?php
function getNameFromID1($userID){
include 'gradnetconn.php';
$query = ("SELECT CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_users INNER JOIN gn_messages WHERE userID = messageFrom");
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
return( $row['full_name']);
}
}
?>
<?php
function getNameFromID2($userID){
include 'gradnetconn.php';
$query = ("SELECT CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_users INNER JOIN gn_messages WHERE userID = messageTo");
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
 return( $row['full_name']);
}
}
?>

[DaveyK]

Maybe I am not following this thread correctly but...

 

<?php
function getNameFromID1($userID){
include 'gradnetconn.php';
$query = ("SELECT CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_users INNER JOIN gn_messages WHERE userID = messageFrom");
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
return( $row['full_name']);
}
}
?>

 

this function has a variable $userID but you don't even use it in the query. You may wanna start with that.

 

 

EDIT: Also the JOIN does not have an ON clause.

Edited February 8, 2013 by DaveyK

[Jessica]

Look at the query I posted and tell me what is different for mine and yours.

 

You don't NEED separate functions to run separate queries for this. JUST DO ONE QUERY ONLY EVER ONE.

[dannyp100]

Still having trouble with the query. This is the code ive got now so on a users inbox, they can see the full_name of

the person that the message was from.

 

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource

 

 

 

<?php
include 'gradnetconn.php';
include 'functions.php';


session_start();



$messageFrom = $_SESSION['userID'];


$sql = mysql_query("SELECT gn_messages.*,  
CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_messages INNER JOIN gn_users ON gn_users.user_id = gn_messages.messageFrom
WHERE messageFrom = $messageFrom AND messageDeleted = '0'");


?>


<table width='95%'>
<tr><th>From</th><th>Message Subject</th><th>Date Recieved</th></tr>


<?php


 while($return = mysql_fetch_object($sql)) {


$messageDate = gmdate("M d Y H:i:s",$return->messageDate);


  if($return->messageRead == "0") {
  $messageRead = "notread.jpg";
  }
  else {
  $messageRead = "read.jpg";
  }


  echo "<tr><td><img src='".$messageRead."' /></td>




  <tr><td>$return->messageFrom</td><td><a href='viewmessage1.php?messageID=$return->messageID'>$return->messageSubject</a>
  </td><td>$messageDate</td></tr>";
}



?>
</table>

[Jessica]

See the link in my signature on debugging SQL. You also need to sanitize your user input.

[dannyp100]

Would you be able to tell me why the correct user that the message is from isn't appearing.

This is my inbox code:

 

 

<?php
include 'gradnetconn.php';
include 'functions.php';


session_start();



$messageTo = $_SESSION['userID'];


$sql = mysql_query("SELECT * FROM gn_messages WHERE messageTo = $messageTo AND messageDeleted = '0'
ORDER BY messageDate DESC");


?>


<table width='95%'>
<tr><th>From</th><th>Message Subject</th><th>Date Recieved</th></tr>


<?php


 while($return = mysql_fetch_object($sql)) {


  $fromUser = getNameFromID1($return->messageFrom);


  $messageDate = gmdate("M d Y H:i:s",$return->messageDate);


  if($return->messageRead == "0") {
  $messageRead = "notread.jpg";
  }
  else {
  $messageRead = "read.jpg";
  }


  echo "<tr><td><img src='".$messageRead."' /></td>




  <tr><td>$fromUser</td><td><a href='viewmessage1.php?messageID=$return->messageID'>$return->messageSubject</a>
  </td><td>$messageDate</td></tr>";
}



?>
</table>

 

This is my function that i have called in the inbox code:

 

 

<?php

function getNameFromID1($userID){

 

include 'gradnetconn.php';

$query = ("SELECT CONCAT(userFirstName, ' ', userSurname) AS full_name FROM gn_users INNER JOIN gn_messages WHERE gn_users.userID = gn_messages.messageFrom");

$result = mysql_query($query);

while($row = mysql_fetch_array($result)) {

return( $row['full_name']);

}

}

?>

 

 

The wrong full_name is appearing. The full name should be 'steven steven' when its saying 'Daniel Petrie'

Really frustrated on this now could you give me any solution or logic?

[Jessica]

YOU NEED TO CHECK FOR ERRORS IN YOUR SQL.

 

YOU NEED TO USE ONLY ONE QUERY AND I WROTE IT FOR YOU. NOT A SEPARATE FUNCTION JUST OOOOONNNNNEEEEE QUERY.

 

I CAN'T MAKE IT ANY CLEARER THAN THAT.

[dannyp100]

SELECT gn_messages.*,  
CONCAT(userFirstName, ' ', userSurname) AS full_name
FROM gn_messages 
INNER JOIN gn_users
ON gn_users.user_id = gn_messages.messageTo  (-- assuming you want the user's name who it is to. You'll need to do this join AGAIN to get the sender's name as well).
WHERE messageTo = $messageTo AND messageDeleted = '0'
ORDER BY messageDate DESC"

 

 

OH right, oh yeah i understand now! Sorry, i read it all wrong!

 

SELECT gn_messages.*,  
CONCAT(userFirstName, ' ', userSurname) AS full_name
FROM gn_messages 
INNER JOIN gn_users
ON gn_users.user_id = gn_messages.messageTo  
INNER JOIN gn_users
ON gn_users.user_id =gn_messages.messageFrom
WHERE messageTo = $messageTo AND messageDeleted = '0'
ORDER BY messageDate DESC"

 

So where you've put in the note in the query above (assuming.......etc)

I have to do the join again like above?

[Jessica]

If you don't already have the messageTo user's name, then yes. You need to give the table an alias, so it would be like this

SELECT gn_messages.*,  
CONCAT(to_user.userFirstName, ' ', to_user.userSurname) AS to_full_name,
CONCAT(from_user.userFirstName, ' ', from_user.userSurname) AS from_full_name,
FROM gn_messages 
INNER JOIN gn_users to_user
ON to_user.user_id = gn_messages.messageTo  
INNER JOIN gn_users from_user
ON from_user.user_id =gn_messages.messageFrom
WHERE messageTo = $messageTo AND messageDeleted = '0'
ORDER BY messageDate DESC"

Edited February 9, 2013 by Jessica