PHP: Search in one table and in another..

Question

Hello. I am making an autosuggesting function, when the user writes something in the field it stores it in:

$queryString = $db->real_escape_string($_POST['queryString']);

I want it to autosuggest after the users friends. The user´s friends is in users_friends, but only the friend´s ID. Now their full_name is in the table "users". And i want when you search it should in users for the full_name + check if its friends with the user.

As you may understand i do not expect all my users to know eachother id´s so writing e.g "52" "233", but searching for their full_name s.

UPDATE:

I know tried doing this: $query = $db->query("SELECT uf.bID FROM users friends, users_friends uf WHERE uf.uID = '1' AND uf.type = 'friend' AND friends.full_name LIKE '$queryString%' LIMIT 10;" );

It selects the bID, from the users friends WHERE the userid is 1 and are friend.

Now i start to see some results i think. When i write a full_name that im friends with, i get the id of the user(the id that is stored in bID). Now i just need to grab the full_name in "users" where id = bID..

table: users
id | full_name

table: users_friends
id | uID | bID

So conclusion of all this (trying to make a better summary in order to make you understand better: )

When you type in e.g Jack in the search field, then the $queryString is now "jack". Then it is taking "Jack"(full_name in users), grabbing his id(id in users), if he exists there ofcourse, and then match it with bID (in users_friends) where uID is $USER; ($user is the current user that are logged in´s id.)

Hope this was easier to understand, please leave comment if theres something unclear.

Answer 1

Do you want to read data from many tables at once??

SELECT table1.id, table2.name FROM table1, table2 WHERE ...

My english is not very good to understand everything :D

Answer 2

Using your database-table definition & assuming PHP Variables:

$querystring - holds an escaped string of a potential friend
$USER - holds an escaped integer of the logged in user

Your query would be:

SELECT friends.id,friends.full_name
FROM users friends,users_friends uf
WHERE friends.full_name LIKE '$querystring%'
  AND uf.bID=friends.id
  AND uf.uID=$USER
ORDER BY friends.full_name
LIMIT 10

You don't need the new-lines in there, I just added them for readability (if this is PHP with MySQL the new-lines will be ignored anyway). Once again $querystring should be escaped with mysqli-escape-string or the like. $USER should be escaped too if it's coming from the user's browser (even in a cookie) {eg. $USER=(int)$USER; will at least cast it into an integer - if it's not a number (contains text for example)}

What you'll get for results - if you're searching for "Jack^" (your example) - is a list of any friends of the current logged in user with name starting with Jack, sorted by full name (alphabetically). So if the user has 3 friends:

7 | Jack Dee
8 | Andrew Cee
9 | Jack Bee 

This will return:

9 | Jack Bee 
7 | Jack Dee

Answer 3

I didn't understood your question But you can use this query:

mysql_query("SELECT * FROM table_one, table_two WHERE table_one.id = table_two.id");

Answer 4

So, as i figure it out, you've got the current user's id in $USER and its query string in $queryString, and what you want is the names of the user's friends based on the $queryString, am I right?

So, assuming the database's schema is as you've put:

table: users
id | full_name

table: users_friends
id | uID | bID

See if this query works out for you, then:

SELECT users.full_name 
FROM users INNER JOIN users_friends ON users.id=users_friends.uID
WHERE bID=$USER AND users.full_name LIKE '$queryString%'
LIMIT 10;

Where $USER and $queryString are your variables.