title: MYSQL query to select from multiple tables, order by date, different table structures
body: <p>I'm making a newsfeed type of thing and I want to select from multiple tables. The two tables I'll focus on for this question are "posts" and "photos".</p>
  
  <p>Here's my query for just posts:</p>
  
  <pre><code>mysql_query("
              SELECT * FROM posts 
              WHERE toID='$id' AND state='0' 
              ORDER BY id DESC LIMIT 10");
  </code></pre>
  
  <p>My posts table has the following column names:</p>
  
  <pre><code>Table: posts
  
  id  toID   fromID   post   state     date
  1    1       1      Aloha    0       1
  2    1       1      Hello    0       3
  </code></pre>
  
  <p>My photos table has the following:</p>
  
  <pre><code>Table: photos
  
  id  userID  photo    state    date
  1     1       2       0       2
  2     1       6       0       4
  </code></pre>
  
  <p>Expected result:</p>
  
  <pre><code>Aloha
  2
  Hello
  6
  </code></pre>
  
  <p>Maybe something like:</p>
  
  <pre><code> SELECT * 
   (SELECT * FROM posts WHERE toID=$id AND state=0) AND
   (SELECT * FROM photos WHERE userID=$id AND state=0)
   ORDER BY date
  </code></pre>
  
  <p>When it selects these from the database it should select from where toID and userID are the same. state should equal 0 for both, (0 means visible) and they should be ordered by date. Also I need to create a new variable to pass to my query, so I can then in my php determine which table the information is coming from. Lastly I would like it to group the photos by date, so let's say a user uploaded 20 photos within a 30 minute period, they will only return one row. I use php time() to store my date. </p>