Boober Bunz Boober Bunz - 5 months ago 16
MySQL Question

Table name is a variable from same query (PHP)

NOTE: I am aware of SQL injection possibilities, etc. This question is theoretical.

Lets say a user queries a page on a server, getListOfPeople.php, and sends their user ID as a post parameter.

The php page contains the following (simplified pseudo code):

$UUID = $_POST ["UUID"];

"SELECT name, bio, photo FROM people WHERE NOT EXISTS (SELECT blocked_UUID FROM 'name' WHERE blocked_UUID = $UUID)"


Lets say the table people looks like this:

NAME BIO PHOTO
BOB blah blah o43987h2fdof43hr43h
SARA blah blah 098u43jfewoijfwoiej
MARK blah blah re988cd9sc2h33hfddh


And the table BOB looks like this:

blocked_UUID
287364
387761
497745
329844


We want to give the people in the people table the ability to be invisible to certain users... so they simply add those unwanted users to their own table containing their list of blocked user IDs.

My problem is, in the query:

"SELECT name, bio, photo FROM people WHERE NOT EXISTS (SELECT blocked_UUID FROM 'name' WHERE blocked_UUID = $UUID)"


In the part that says, FROM 'name', I'm trying to figure out the correct syntax to use in order for that to work (querying a table where the table name is variable derived from the same query).

I would like to avoid entirely different ways of approaching the problem if possible, and stick to one single query.

Answer

The correct way to do this is to have a single blocks table:

name    blocked_UUID
BOB     287364
BOB     387761
SARA    123456
SARA    232323
MARK    112233

Then your query would be:

SELECT name, bio, photo 
FROM people 
WHERE NOT EXISTS (
    SELECT 1 FROM blocks
    WHERE blocked_UUID = $UUID
    AND blocks.name = people.name)