Use result from one query as an input into another query on MySQL

Question

I am trying to use a result from one query as input for another query but I am stumped. I thought about using JOIN but I think in this case the two queries need to me run separately. Essentially I have a list of articles in my database. As I loop through the list or articles that I obtained from my first query I want to search a second table to find out the number of votes that each article has.

This is the code:

<?php
  $sql=mysql_query("SELECT * FROM discussion_links WHERE link_side = 'Michigan'");
  while($row = mysql_fetch_array($sql)) {
?>

<div class="top-links-wrapper">
    <div>
       <div class="link-info">
           <a class="link-title" href="http://<?php echo $row['link_url'] ?>">
           <?php echo $row['link_title'] . "</a>"; ?>
           <p class="link-source"><?php echo $row['link_source'] . "</p>" ?>
       </div>
       <div class="link-vote-wrapper">
           <span class="link-votes">
           <?php    
                $sql2=mysql_query("SELECT * FROM link_votes WHERE link_id = " . $row['link_id'] .")";
                $num_rows = mysql_num_rows($sql2);
                echo "$num_rows";
            ?>
           </span>
       </div>
    </div>
</div>

Thanks

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — Madara's Ghost
– Madara's Ghost, Commented Jun 13, 2012 at 18:00
Thanks @Truth, new to this so better learn the new standards. — Danconia
– Danconia, Commented Jun 13, 2012 at 18:09

Moyed Ansari · Accepted Answer · 2012-06-13 18:03:20Z

7

You can join both queries try this

SELECT dl.* , lv.*
FROM discussion_links dl
LEFT JOIN link_votes lv ON lv.link_id = dl.link_id
WHERE link_side = 'Michigan'

answered Jun 13, 2012 at 18:03

Moyed Ansari

8,4612 gold badges38 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manse · Accepted Answer · 2012-06-13 18:05:15Z

1

Change

$sql2=mysql_query("SELECT * FROM link_votes WHERE link_id = " . $row['link_id'] .")";

to

$sql2=mysql_query("SELECT * FROM link_votes WHERE link_id = " . $row['link_id']);

The closing bracket ) is part of the function call not the query

or better would be to create a single query :

SELECT * FROM discussion_links d 
JOIN link_votes v
on d.link_id = v.link_id
WHERE d.link_side = 'Michigan'

You should replace the SELECT * and just select the columns you require

edited Jun 13, 2012 at 18:05

answered Jun 13, 2012 at 18:00

Manse

38.1k11 gold badges88 silver badges112 bronze badges

2 Comments

Danconia Over a year ago

Thanks, just noticed the error with the ')'. Will try that 2nd query you suggest.

Manse Over a year ago

@LuisP you may need to do a left join as there may be articles that have no votes

Danconia · Accepted Answer · 2012-06-13 18:04:15Z

0

Fixed, had an error in the 2nd query:

$sql2=mysql_query("SELECT * FROM link_votes WHERE link_id = " . $row['link_id']);

It now works.

answered Jun 13, 2012 at 18:04

Danconia

5632 gold badges12 silver badges28 bronze badges

Collectives™ on Stack Overflow

Use result from one query as an input into another query on MySQL

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related