I have a MySQL query that selects all audioids
from a certain user in a subscribe
table.
I then have another query which takes this list of audioids
and matches them against a field called opids
in a table called audioposts
. It then selects the titles
from that audioposts
table and joins the users from a users
table at the userid
.
Is there a way I can turn these two queries into one query?
query1 = "SELECT audioid FROM subscribe WHERE userid = $userid"; query2 = "SELECT ap.audioid, ap.title, us.name FROM audioposts ap INNER JOIN audioposts a2 ON a2.audioid = ap.opid INNER JOIN users us ON us.id = a2.userid WHERE ap.opid = $newaudio";
Here is the current two query code which I’d like to replace with one query. I’ve not yet translated this into prepared statements as it’s easier for me to visualize it the old-fashioned way. Plus I’ll be converting this into NodejS eventually anyway;
$selectaudioid = "SELECT audioid FROM subscribe WHERE userid = $userid"; $audioResult=$dblink->query($selectaudioid); if ($audioResult->num_rows>0) { while ($row = $audioResult->fetch_assoc()) { $newaudio = $row[audioid]; $getallaudio = " SELECT ap.audioid, ap.title, us.name FROM audioposts ap INNER JOIN audioposts a2 ON a2.audioid = ap.opid INNER JOIN users us ON us.id = a2.userid WHERE ap.opid = $newaudio AND ap.opid <> '0'"; $getallresult = $dblink->query($getallaudio); if ($getallresult->num_rows>0) { while ($row = $getallresult->fetch_assoc()) { $dbdata[]=$row; }}}}
Advertisement
Answer
Just add another join with subscribe
.
SELECT ap.audioid, ap.title, us.name FROM audioposts ap INNER JOIN audioposts a2 ON a2.audioid = ap.opid INNER JOIN subscribe s ON s.audioid = ap.opid INNER JOIN users us ON us.id = a2.userid WHERE s.userid = $userid AND ap.opid <> '0'