How to use the result of one query in another query (PHP/MySQL)

Question

I have 2 tables (artist, cd) and I'm trying to use the result of the first query which returns an artID and make it equal to the artID in the 2nd table(cd) where artID is a foreign key but I'm not sure how to do it. Any help would be appreciated.

$strqueryID="SELECT artID FROM artist WHERE artName= '" . $_POST["category"] . "' ";
$resultsID=mysql_query ($strqueryID) or die(mysql_error());

$strqueryCD="INSERT INTO cd SET cdTitle='" . $_POST['title'] . "', artID='" . ??? . "' cdPrice='" . $_POST['price'] . "', cdGenre='" . $_POST['genre'] . "', cdNumTracks='" . $_POST['tracks'] . "'";
$resultsCD=mysql_query ($strqueryCD) or die(mysql_error());

**The `mysql` PHP extension is dead** -- Stop using the [`mysql` PHP extension](http://php.net/manual/en/function.mysql-connect.php). It is old, deprecated since PHP 5.5 and completely removed in PHP 7.0. Use [`mysqli`](http://php.net/manual/en/book.mysqli.php) or [`PDO_mysql`](http://php.net/manual/en/ref.pdo-mysql.php) instead. — axiac, May 01 '17 at 12:47
Thanks for the heads up, but i'm aware mysql_query is deprecated. — loxi95, May 01 '17 at 12:50

score 1 · Accepted Answer · answered May 01 '17 at 12:53

You can use one single query, like this:

$strqueryCD="
INSERT INTO cd (cdTitle, artID, cdPrice, cdGenre, cdNumTracks) 
VALUES(
   '" . $_POST['title'] . "', 
   (SELECT artID FROM artist WHERE artName= '" . $_POST["category"] . "'), 
   '" . $_POST['price'] . "', 
   '" . $_POST['genre'] . "', 
   '" . $_POST['tracks'] . "')
";

also, google 'sqlinjection' before you continue

score 0 · Answer 2 · edited May 23 '17 at 12:03

So, first thing's first - you shouldn't be using mysql_* functions now in 2017. I mean, really - they're actually even removed in later versions of PHP (7.0+). Refer to this StackOverflow post for more information.

Now, for your question at hand. Given the fact that you've searched for (and found) a given artID, you'll first have to get the actual "rows" from the $resultsID variable. In this example, we'll do it in a typical while loop:

while ($row = mysql_fetch_assoc($resultsID)) {
    $strqueryCD="INSERT INTO cd SET cdTitle='" . $_POST['title'] . "', artID='" . $row['artID'] . "' cdPrice='" . $_POST['price'] . "', cdGenre='" . $_POST['genre'] . "', cdNumTracks='" . $_POST['tracks'] . "'";
    $resultsCD=mysql_query ($strqueryCD) or die(mysql_error());
}

That should now loop over the artIDs that you've found in your first query and use them in the subsequent insert(s).

--

Disclaimer: I've disregarded the fact that user input is being passed straight into the query itself, as it's just too much "out of scope" for this post.

How to use the result of one query in another query (PHP/MySQL)

2 Answers2