How to use PHP to query MySQL for certain rows, then for each row, based on a UID, query another table based on value?

Question

I am making an application for people to sell items and make product offers. If a user views their product offers, it must contain each offers information, as well as details of each user for that offer.
First tell the database which offers need to be queried, activated by an ajax onclick function. (ive got that)

Second, query the OFFERS table for offers with status 'o' and a PID equal to the $_GET['pid'](5).

OFFERS
------------------------------
| uid | pid | status | price |
------------------------------
|  5  |  5  |   o    |  49   |
|  7  |  5  |   o    |  45   |
------------------------------

Then query the USERS table for information based on each offer? After that, for each result, based on the the UID, query the users table for information related to the user.

USERS
-------------------------------------
| uid |  fname   |  lname  | rating |
-------------------------------------
|  5  |   John   |   Jan   |   4.3  |
|  7  |   Mark   |   Mull  |   4.2  |
-------------------------------------

Here is a snippet of my PHP statement to the database.

$stmt = $conn->prepare('SELECT offerdate,price FROM offers WHERE pid=? AND status=?');
$o = 'o';
$stmt->bind_param('is', $_POST['pid'],$o);
$stmt->execute();
$stmt->bind_result($offerdate,$price);

The other statement I need

$stmt = $conn->prepare('SELECT fname,lname,rating, FROM users WHERE uid=?');
$stmt->bind_param('i', );
$stmt->execute();
$stmt->bind_result($fname,$lname,$rating);

Any help would be awesome, thank you!

Does this answer your question? [I have an array of integers, how do I use each one in a mysql query (in php)?](https://stackoverflow.com/questions/330268/i-have-an-array-of-integers-how-do-i-use-each-one-in-a-mysql-query-in-php) — El_Vanja, Mar 13 '20 at 01:31

score 0 · Accepted Answer · answered Mar 13 '20 at 01:33

0

Do a single query that joins the two tables.

SELECT offerdate, price, fname, lname, rating
FROM offers
INNER JOIN users ON users.uid = offers.uid
WHERE offers.pid = ? AND offers.status = ?

answered Mar 13 '20 at 01:33

Barmar

741,623
53
500
612

how come i use inner join, not join left? – Dimitri Bostrovich Mar 13 '20 at 01:47
Can there be an `offers.uid` that doesn't match `users.uid`? You only need `LEFT JOIN` when there might not be a match, but you still need to return the row. – Barmar Mar 13 '20 at 01:52

score 0 · Answer 2 · answered Mar 13 '20 at 01:33

0

You can get that result in one query with a join:

select
    o.offerdate,
    o.price
    u.fname,
    u.lname,
    u.rating
from offers o
inner join users u on u.uid = o.uid 
where o.pid = ? and o.status = ?

answered Mar 13 '20 at 01:33

GMB

216,147
25
84
135

How to use PHP to query MySQL for certain rows, then for each row, based on a UID, query another table based on value?

2 Answers2