mysql SELECT ( need to connect to 2 tables and show results for a search )

Question

I want to connect 2 tables in my query to show results.
I have lastname, firstname etc in the first table.
In the second table I have training, date...

I also have in the second table an ID reference that links lastname and firstname to training and date.
So whenever I create a training record into the database it uses the same ID for the same person.
I want to be able to connect both tables in my query and show results if I search for lastname.
It will also list all the training that they have completed if I click there name in the search that came up.
I am very new at MySQL database.
I am using phpmyadmin for MySQL and running a localhost database for testing purposes...
I currently can search a single table and bring up results, but I cannot figure out the union to link them and have the names list as a link to show there training. and having it keep there name at the top for a printout... here is the code I am using that I got from a tutorial.

    <form>
  <div align="right">
    <input type="button" value="Print Results" onClick="myprint()">
  </div>
</form>
<?php

// TAKE THE INFORMATION FROM FORM.
$search = $_GET['search'];

// IF THERE IS NOT A KEYWORD GIVE AN ERROR
if (!$search) 
echo "You didn't enter a keyword";
else
{
  echo "<td>You searched for: <strong>$search </strong></td>";
  mysql_connect('localhost','loginid','password');
  mysql_select_db('trainingrecords');  
  $id=@$_GET['id'];

  //QUERY IS THE CODE WHICH WILL MAKE THE SEARCH IN YOUR DATABASE.
  //I WROTE AN EXPLANATION ABOUT IT AFTER THE CODE.  
  $query="SELECT * FROM username 
          WHERE MATCH( lastname, firstname, location, created) 
          AGAINST('%$search%' IN BOOLEAN MODE)";
  $result1 = MySQL_query($query);  
  if(!$result1) {  
    echo MySQL_error()."<br>$query<br>";
  }  
  if (MySQL_num_rows($result1) > 0) {
    echo "<table width='750' align='center' border='1' 
           cellspacing='2' cellpadding='2'>";
    while($result2 = MySQL_fetch_array($result1)) {

      //A short description from category.
      $description = $result2['location'];
      $searchPosition = strpos($description, $search);
      $shortDescription = substr($description, $searchPosition, 150);

      echo "<td><strong>{$result2['lastname']} 
            {$result2['firstname']}</td></strong><td>
            $shortDescription</td><td>{$result2['created']}</td><tr/>";
    }
    echo "</table>";
  } else {
    echo "No Results were found in this category.<br>";
  } 
  echo "<br>";
}
?>

score 1 · Answer 1 · answered Aug 25 '11 at 12:52

I think you want something like this:

SELECT * FROM username, training
    where username.lastname = $lastname and username.firstname = $firstname
        and username.id = training.id;

which would require you to pass in your $lastname and $firstname values as separate variables.

score 1 · Answer 2 · answered Aug 25 '11 at 12:52

1

Its not union its join you want

Select a.id,a.firstname,a.lastname,a.location,a.date,b.trainingdate 
from username a
inner join
training b on a.id=b.id

Add your where clause on the end

answered Aug 25 '11 at 12:52

Bob Vale

18,094
1
42
49

score 1 · Answer 3 · answered Aug 25 '11 at 12:53

1

What you need is a JOIN

$query="SELECT * FROM username u LEFT JOIN training t on (t.userid = u.id) WHERE MATCH( lastname, firstname, location, created) AGAINST('%$search%' IN BOOLEAN MODE)";

answered Aug 25 '11 at 12:53

bpgergo

15,669
5
44
68

score 1 · Accepted Answer · edited May 23 '17 at 11:55

1- You have an SQL injection hole
Make sure to escape all your variables using mysql_real_escape_string
And make use to properly quote string inside the query _(otherwise mysql_real_escape_string will not work)_

$search = mysql_real_escape_string($GET['search']);
....
$query="SELECT * FROM username 
      WHERE MATCH( lastname, firstname, location, created) 
      AGAINST('%".$search."%' IN BOOLEAN MODE)";
//            ^             ^ quotes were already there, good!

2- If you want to get results from multiple tables you'll have to do a join

$search = "SELECT u.*, t.* FROM username u
  INNER JOIN training t ON (u.id = t.user_id)
  WHERE MATCH( lastname, firstname, location, created) 
        AGAINST('%".$search."%' IN BOOLEAN MODE)";

Normally you do not want to select all the fields, If you explicitly state the fields you want show to the user, the query will run faster.

Here's a decent tutorial on php and MySQL: http://www.tizag.com/mysqlTutorial/
And here's what happens if you have SQL-injection holes: How does the SQL injection from the "Bobby Tables" XKCD comic work?

Thanks Johan this works great! Now my only issue is my string search. when I search lastname and if there are two people with same last name is brings them both up and when I search firstname lastname together. it seems to ignore it and just pulls both up anyways. How can I get it to Match only the firstname and lastname on my search. rather than just looking for both and giving me all results? — yeahwhatever, Aug 25 '11 at 13:52
I also am having trouble ordering the search by lastname or whichever I choose. Is there a way to have my table with headers for each field that if you click them it orders them by that field? and have the default being lastname? — yeahwhatever, Aug 25 '11 at 14:14

score 0 · Answer 5 · answered Aug 25 '11 at 12:52

0

-- something basic like this?

SELECT u.id, u.name, t.link
FROM users u
LEFT JOIN trainings t ON u.id = t.user_id
WHERE u.id = 5

answered Aug 25 '11 at 12:52

Chris G.

3,963
2
21
40

score 0 · Answer 6 · answered Aug 25 '11 at 12:53

0

Maybe you want something like this:

SELECT username.* FROM username
JOIN training ON (training.id = username.id)
WHERE MATCH( lastname, firstname, location, created)    
      AGAINST('%$search%' IN BOOLEAN MODE)

answered Aug 25 '11 at 12:53

Alexander Sulfrian

3,533
1
16
9

mysql SELECT ( need to connect to 2 tables and show results for a search )

6 Answers6