I’ve got the following code which queries a table. Then it uses the result to make another query. That result is then used to make a third query.
But how do I grab the userid field from the 2nd query in order to grab a name from a users table and join that to the result of the 3rd query?
Please note once I figure out the code I will convert this to a prepared statement. It’s just easier for me to work with legacy code when figuring out queries.
$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 opid, userid from audioposts WHERE audioid = $newaudio" ; $getallresult = $dblink->query($getallaudio); if ($getallresult->num_rows>0) { while ($row = $getallresult->fetch_assoc()) { $opid = $row[opid]; $opuserid = $row[userid]; $getreplies = "SELECT * from audioposts ap WHERE opid = $opid AND opid NOT IN (SELECT opid FROM audioposts WHERE audioposts.opid = '0' )"; $getreplyresults = $dblink->query($getreplies); if ($getreplyresults->num_rows>0) { while ($row = $getreplyresults->fetch_assoc()) { $dbdata[]=$row; } } } } } } "SELECT * from audioposts ap WHERE opid = $opid AND opid NOT IN (SELECT opid FROM audioposts WHERE audioposts.opid = '0' )"; $getreplyresults = $dblink->query($getreplies); if ($getreplyresults->num_rows>0) { while ($row = $getreplyresults->fetch_assoc()) { $dbdata[]=$row; } } } } } } echo json_encode($dbdata);
The result I need are rows of json encoded instances of $getreplyresults
with the $row[userid] from the original result joined to each row.
Advertisement
Answer
Here’s what I did in the end. Now I just have to figure out how to convert this to a prepared statement in order to avoid malicious injection.
$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; }}}}