在桌面内的一个字段中列出的MySQL用户名查询另一个表并返回用户结果

Question

So I am new to PHP and I have been on a roll lately developing a website. My question is this. I have all users listed inside of a field in one table.

This is a list of one of these fields: admin, user1, user2, user3.

Now what I need to do is take them users from that ONE field, I am guessing put them into an array and then search each user inside of another table called users and then list the users info such as their rating and bio.

Thanks in advance for any help.

I reposted this with pictures to help you get an idea of what I am looking to accomplish.

I am looking to get the users from this table listed inside of that cell jobs Table

and find them in this table and then list them users table

Hey I have edited the code in the following way and I am getting an error.

$jobID = 1;
$jq = $con-> prepare('SELECT applied_names FROM jobs WHERE jobID=1 LIMIT 1');
$jq-> execute();
$jq-> bind_result($usernames);
$jq-> fetch();
$jq-> close();

$stm = $con->prepare("SELECT username FROM users WHERE username IN (?)");
$stm->execute(array($usernames));
$result = $stm->get_result()->fetch_assoc();
$stm->close();

The error and warning says: Warning: mysqli_stmt::execute() expects exactly 0 parameters, 1 given on line 14

and Fatal error: Call to a member function fetch_assoc() on boolean on line 15

Answer 1

You can use a subquery and the keyword IN for this:

SELECT * FROM meta_table WHERE userID IN (SELECT id FROM users)

If you post table & column names, I will be able to give you a more precise query.

EDIT: after seeing your edited post with the images, this way is not possible. you would still be able to use the IN after you have got the field with the usernames in PHP.

$usernames = $row['applied_names'];
$stm = $pdo->prepare("SELECT * FROM users WHERE username IN (?)");
$stm->execute(array($usernames));

And for mysqli:

$usernames = $row['applied_names'];
$stm = $con->prepare("SELECT * FROM users WHERE username IN (?)");
$stm->bind_param('s', $usernames);
$stm->execute();

In my opinion, you should have a users_jobs table with columns id userID & jobID then you can use queries like this:

SELECT username,email,jobName,jobStatus FROM users_jobs 
JOIN users ON users.id = users_jobs.userID
JOIN jobs ON jobs.id = users_jobs.jobID

This will produce one row with all the columns you have specified in the SELECT