Table name is a variable from same query (PHP)

Question

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.

You have to know the table name to get it. You could loop over the people table and get the names, then run separate queries within the loop. Seems taxing. — StackSlave
– StackSlave, Commented Jun 24, 2016 at 2:20
Having variable table names like this is horrible database design. Dynamic data should be in table data, not table and column names. — Barmar
– Barmar, Commented Jun 24, 2016 at 2:25
Why don't you have a single blocks table that has columns name and blocked_UUID? — Barmar
– Barmar, Commented Jun 24, 2016 at 2:25
I would not make separate tables based on the name in this case. I would have a table with name | blockedUUID columns or something like that and enter the name and blockedUUID so you can easily query the results you want. — StackSlave
– StackSlave, Commented Jun 24, 2016 at 2:29
@BooberBunz If you feel the need for a unique column, give it an auto-increment ID column. But you can also have a unique key on (name, blocked_UUID). — Barmar
– Barmar, Commented Jun 24, 2016 at 2:33

Barmar · Accepted Answer · 2016-06-24 02:31:56Z

2

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)

answered Jun 24, 2016 at 2:31

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nerdy Bunz Over a year ago

doesn't the table need to have a column where all values are unique in order to work properly? Sorry I'm new.

Barmar Over a year ago

No, there's no such requirement, but you can always add an auto-increment ID column.

Collectives™ on Stack Overflow

Table name is a variable from same query (PHP)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related