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. It is old, deprecated since PHP 5.5 and completely removed in PHP 7.0. Use mysqli or PDO_mysql instead. — axiac
– axiac, Commented May 1, 2017 at 12:47
Thanks for the heads up, but i'm aware mysql_query is deprecated. — loxi95
– loxi95, Commented May 1, 2017 at 12:50

Arie B. · Accepted Answer · 2017-05-01 12:53:42Z

1

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

answered May 1, 2017 at 12:53

Arie B.

2901 silver badge11 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:03:05Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related