Using Join to combine 3 columns

[mgn2683]

Hi,

I have a few questions. First off, I have a recordset where I am selecting the columns name_pre, name_first and name_last. I want to have the name displayed as Mr. John Smith, just combining all 3 columns.

The second portion where I am running into problems is displaying the aforementioned Join. Basically, I am going to have a list and detail page, where a person can select a user's name from a menu that uses the recordset. The only option I have is to pick name_pre, first or last. How can I set it up so that a person's entire name will be displayed as Mr. John Smith?

Thanks in advance.

[Will Gresham]

Can you post what code you have at the moment?

When you say join, do you mean the LEFT JOIN, RIGHT JOIN... in SQL to get the data across multiple tables, or do you mean joining the strings from the 3 columns in PHP?

[mgn2683]

Here is the recordset, minus the Join

SELECT assignment.Candidate, assignment.Chaplain, assignment.Reader, assignment.Id, tbl_user.Id, tbl_user.name_pre, tbl_user.name_first, tbl_user.name_last
FROM assignment, tbl_user
WHERE assignment.Id=tbl_user.Id
ORDER BY assignment.Candidate ASC

[Shanti C]

try this way...

$r=mysql_query("SELECT assignment.Candidate, assignment.Chaplain, assignment.Reader, assignment.Id, tbl_user.Id, tbl_user.name_pre, tbl_user.name_first, tbl_user.name_last
FROM assignment, tbl_user
WHERE assignment.Id=tbl_user.Id
ORDER BY assignment.Candidate ASC");
$result=mysql_fetch_array($r);
echo $result['name_pre'].$result['first'].$result['last'];

[mgn2683]

try this way...

$r=mysql_query("SELECT assignment.Candidate, assignment.Chaplain, assignment.Reader, assignment.Id, tbl_user.Id, tbl_user.name_pre, tbl_user.name_first, tbl_user.name_last
FROM assignment, tbl_user
WHERE assignment.Id=tbl_user.Id
ORDER BY assignment.Candidate ASC");
$result=mysql_fetch_array($r);
echo $result['name_pre'].$result['first'].$result['last'];

For some reason this didn't work. My screen came up blank. Even if I get the join, is it possible to display it in a drop down dynamic menu? Basically, I want users to be able to select a person's name from the database.

[Shanti C]

try this:

echo $r1="SELECT assignment.Candidate, assignment.Chaplain, assignment.Reader, assignment.Id, tbl_user.Id, tbl_user.name_pre, tbl_user.name_first, tbl_user.name_last
FROM assignment, tbl_user
WHERE assignment.Id=tbl_user.Id
ORDER BY assignment.Candidate ASC"
$r=mysql_query($r1);

And make sure that query is correct...

[mgn2683]

Ok, I got it to display the names how I wanted it to. Here is my query:

SELECT assignment.Candidate, assignment.Chaplain, assignment.Reader, assignment.Id, tbl_user.Id,  CONCAT_WS(' ', `tbl_user`.name_pre, `tbl_user`.name_first, `tbl_user`.name_last, `tbl_user`.name_suffix)AS f_name
FROM assignment, tbl_user
WHERE assignment.Id=tbl_user.Id
ORDER BY assignment.Candidate ASC

My one issue is that I need to have the names of BOTH Readers and Chaplains displayed in separate menus. Readers and Chaplains have different user levels within the database. Is there a way I could have a Where clause to separate them for each menu, or would it be easier to have two different queries, one for reader and one for chaplains?

[BMX322]

It is best to simplify your queries by running them seperatly. One for the Readers, one for the Chaplains, etc...
Use concatenation inside the SQL query.
FieldName + ' ' +FieldName. There is a space inside the single quotes.
Then, in your PHP you can then loop through the $results and put each record in its own HTML OPTION tag. Here's something I've done. It assumes you have an ID field and are also returning that field.
In this case I only have two fields (name not seperated like it should be) but you can use concatenation for your query. The concatenation goes in the SELECT statement.

$sql = "Select * from Instructors";

$result = mysql_query($sql);
  if (!$result){
    die ('Query failed $results = mysql_query!' . mysql_error());
  }else{while ($row = mysql_fetch_array($result)){
        $id = $row['I_ID'];
        $name = $row['I_Name'];
          print "<option value=\"$id\">$name</option>";
       }
}