Databases
StackOverflow2013
Postsdbo
POcombining 2 mysql queries into 1
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POcombining 2 mysql queries into 1
    primarykey
    data
    text
    <p>i am having trouble creating a single mysql query for what i am trying to do here.</p> <p>first off, i will show you the table structures and fields of the tables i am using for this particular query:</p> <pre><code>users: - id - name - photo_name - photo_ext user_attacks: - id - level user_news_feed: - id - id_user - id_status - id_attack - id_profile - id_wall - the_date user_status: - id - status user_wall: - id - id_user - id_poster - post </code></pre> <p>whenever the user posts an attack, or status update, updates their profile, or posts on someones wall, it inserts the relevant data into its respective table and also inserts a new row into the user_news_feed table.</p> <p>now, what i want to do is select the last 10 news feed items from the database. these news feed items need to grab relevant data from other tables as long as their value is not 0. so if the news feed is for a status update, the id_status would be the id of the status update in the user_status table, and the "status" would be the data needing to be selected via a left join. hope that makes sense.</p> <p>heres my first mysql query:</p> <pre><code>$sql = mysql_query("select n.id_user, n.id_status, n.id_attack, n.id_profile, n.id_wall, n.the_date, u.id, u.name, u.photo_name, u.photo_ext, s.status from `user_news_feed` as n left join `users` u on (u.id = n.id_user) left join `user_status` s on (s.id = n.id_status) where n.id_user='".$_GET['id']."' order by n.id desc limit 10 "); </code></pre> <p>now this works great, except for 1 problem. as you can see the user_wall table contains the id's for 2 different users. id_user is the user id the post is being made for, and id_poster is the user id of the person making that wall post. if the user makes a wall post on his/her own wall, it is inserted into the database as a status update into the user_status table instead.</p> <p>so i have a conditional statement within the while loop for the first query, which has another sql query within it. here is the whole code for the while loop and second sql query:</p> <pre><code>while ($row = mysql_fetch_assoc($sql)) { if ($row['id_wall'] != 0) { $sql_u = mysql_query("select u.id, u.name, u.photo_name, u.photo_ext, w.post from `user_wall` as w left join `users` u on (u.id = w.id_poster) where w.id='".$row['id_wall']."' "); while ($row_u = mysql_fetch_assoc($sql_u)) { $row['photo_name'] = $row_u['photo_name']; $row['photo_ext'] = $row_u['photo_ext']; $row['id_user'] = $row_u['id']; $row['name'] = $row_u['name']; $content = $row_u['post']; } } else { if ($row['id_status'] != 0) $content = $row['status']; else if ($row['id_attack'] != 0) $content = '&lt;i&gt;Had an attack&lt;/i&gt;'; else if ($row['id_profile'] != 0) $content = '&lt;i&gt;Updated profile&lt;/i&gt;'; } echo '&lt;li'.(($count == $total_count) ? ' class="last"' : '').'&gt;'; echo '&lt;img src="images/profile/'.$row['photo_name'].'_thumb.'.$row['photo_ext'].'" alt="" /&gt;'; echo '&lt;div class="content"&gt;'; echo '&lt;b&gt;&lt;a href="profile.php?id='.$row['id_user'].'"&gt;'.$row['name'].'&lt;/a&gt;&lt;/b&gt;'; echo '&lt;span&gt;'.$content.'&lt;/span&gt;'; echo '&lt;small&gt;'.date('F j, Y \a\t g:ia', $row['the_date']).'&lt;/small&gt;'; echo '&lt;/div&gt;'; echo '&lt;div style="clear: both;"&gt;&lt;/div&gt;'; echo '&lt;/li&gt;'; } </code></pre> <p>i hope what i am trying to do here makes sense. so basically i want to have both sql queries ($sql, and $sql_u) combined into a single query so i do not have to query the database every single time when the user_news_feed item is a wall post.</p> <p>any help would be greatly appreciated and i apologise if this is confusing.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USscarhand
    1. USscarhand
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POcombining 2 mysql queries into 1
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONote you have an SQL injection vulnerability with `$_GET['id']`.
      singulars
      1. POcombining 2 mysql queries into 1
      1. USJonathan Leffler
    2. COit is checked above with an escaped query. if a user is not found with $_GET['id'] (escaped of course) it throws a "profile not found" page and the queries in the OP are not executed at all.
      singulars
      1. POcombining 2 mysql queries into 1
      1. USscarhand
    3. COOK - that was not visible to us, of course, but I'm glad to hear you head off the trouble before it gets to this code.
      singulars
      1. POcombining 2 mysql queries into 1
      1. USJonathan Leffler
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload