Getting data from one table based on results of another SQL PHP
<p>I know this involves JOINS but I can't seem to find a working solution to what I'm trying to do.</p>

<p>I have 2 custom tables :</p>

<pre><code>table1    |    table2
---------------------
id             id
uid            uid
track_id       track_id
date           date
art            active
info
blah
blah2
</code></pre>

<p>First I want to select everything WHERE <strong>uid</strong>=55 AND <strong>active</strong>=1 from <strong>table2</strong> :</p>

<pre><code>$tracks = $wpdb-&gt;get_results( "SELECT * FROM table2 WHERE uid = 55 AND active = 1");
</code></pre>

<p>And then match the <strong>track_id</strong> from <strong>table2</strong> with results from <strong>table1</strong> so I can traverse the <strong>table1</strong> data.</p>

<p>I know I can do it like this :</p>

<pre><code>foreach( $tracks as $track ) {

$this_track = $track-&gt;track_id;

$results = $wpdb-&gt;get_results( "SELECT * FROM table1 WHERE track_id = $this_track");

// Do stuff here

}
</code></pre>

<p>But this is the part where it gets tricky...</p>

<p>I then want to <strong>ORDER</strong> the $results from <strong>table1</strong> by <strong>date</strong> <strong>DESC</strong> from <strong>table2</strong></p>

<p>And this is where I'm lost...</p>

<p>Effectively I want (pseudo code) :</p>

<pre><code>$results = $wpdb-&gt;get_results( "SELECT * FROM table1 WHERE track_id = $this_track" ORDER BY date DESC FROM table2);
</code></pre>

<p>As well as that last bit, I know I can do this entire routine with JOINS to keep this all in one query and make it way more efficient but I just don't know how.</p>

<p>So just to be clear, my overall routine should be like this :</p>

<p>Get all instances of track_id from table2 where user_id=55 and active=1, then use those results to match the track_id to every result in table1 with the same track_id and then sort the results by date back over from table2</p>

<p>Psuedo code, I know it contains nonsense :</p>

<pre><code>$finalresults = $wpdb-&gt;get_results( "SELECT * FROM table2 where uid=55 AND active=1 THEN SELECT * FROM table1 WHERE track_id = "the track_id from the first query" THEN ORDER BY date DESC FROM table2);
</code></pre>