Using a query result in another query

Question

This is my first query, i want to use the multiple itemID's extracted for another query.

$conn = new mysqli(server, dbuser, dbpw, db);
$email = $_GET['email'];
$querystring = "SELECT itemID from mycart where email = '".$email."' ";
$result = $conn->query($querystring);
$rs = $result->fetch_array(MYSQLI_ASSOC);

The second query that need

$query = "SELECT * from CatalogueItems where itemID = '".$itemID."'";

How do i make these 2 query run?

Did you execute either of those queries? Putting a piece of SQL in a string variable does Absolutely Nothing to actually run that query or get the results from that query — RiggsFolly, Nov 23 '18 at 11:01
You shouldn't directly concatenate submitted data into a DB query, because that leaves you vulnerable to [SQL injection attacks](http://bobby-tables.com/)! For [PHP](http://bobby-tables.com/php), use prepared statements with bound parameters. — Agi Hammerthief, Nov 23 '18 at 11:20

Tim Biegeleisen · Answer 1 · 2018-11-23T11:11:31.967

1

Refer to the first query as a subquery in the second:

$query = "SELECT * from CatalogueItems WHERE itemID IN ";
$query .= "(" . $querystring . ")";

This is preferable to your current approach, because we only need to make one single trip to the database.

Note that you should ideally be using prepared statements here. So your first query might look like:

$stmt = $conn->prepare("SELECT itemID from mycart where email = ?");
$stmt->bind_param("s", $email);

edited Nov 23 '18 at 11:11

answered Nov 23 '18 at 11:01

Tim Biegeleisen

502,043
27
286
360

There might be multiple items in a Cart. So instead of `=`, we can rather use `IN(..)` – Madhur Bhaiya Nov 23 '18 at 11:11
This sort of dawned on me after seeing your join answer. Join is probably faster here anyway. – Tim Biegeleisen Nov 23 '18 at 11:12

Madhur Bhaiya · Accepted Answer · 2018-11-23T11:52:04.830

1

Firstly, Your code is open to SQL injection related attacks. Please learn to use Prepared Statements

Now, from a query point of view, you can rather utilize JOIN to make this into a single query:

SELECT ci.* 
FROM CatalogueItems AS ci
JOIN mycart AS mc ON mc.itemID = ci.itemID 
WHERE mc.email = $email  /* $email is the input filter for email */

PHP code utilizing Prepared Statements of MySQLi library would look as follows:

$conn = new mysqli(server, dbuser, dbpw, db);
$email = $_GET['email'];

$querystring = "SELECT ci.* 
                FROM CatalogueItems AS ci
                JOIN mycart AS mc ON mc.itemID = ci.itemID 
                WHERE mc.email = ?";  // ? is the placeholder for email input

// Prepare the statement
$stmt = $conn->prepare($querystring);

// Bind the input parameters
$stmt->bind_param('s', $email);  // 's' represents string input type for email

// execute the query
$stmt->execute();

// fetch the results
$result = $stmt->get_result();
$rs = $result->fetch_array(MYSQLI_ASSOC);

// Eventually dont forget to close the statement
// Unless you have a similar query to be executed, for eg, inside a loop
$stmt->close();

edited Nov 23 '18 at 11:52

answered Nov 23 '18 at 11:01

Madhur Bhaiya

28,155
10
49
57

I have tried this method, however i am getting this error "Call to a member function fetch_array() on a non-object in". – Nov 23 '18 at 11:48
@bobby do you get any errors while running the query ? – Madhur Bhaiya Nov 23 '18 at 11:49
@bobby check the updated answer. added one more line utilizing `get_result()` – Madhur Bhaiya Nov 23 '18 at 11:52
will i be able to pull the result from the first query too? – Nov 23 '18 at 11:55
@bobby yes, because I have used `ci.*` It will fetch all the columns from `CatalogueItems` table which also has `ItemID` in it. – Madhur Bhaiya Nov 23 '18 at 11:58
i see, what do i do if i want to join "quantity" and "itemSize" from mycart items to CatalogueItems table. – Nov 23 '18 at 12:10
@bobby change `select ci.*` to `select ci.*, mc.quantity, mc.itemSize` and so on.. – Madhur Bhaiya Nov 23 '18 at 12:13

score 0 · Answer 3 · answered Nov 23 '18 at 11:05

This creates a variable out of your result

$query = "SELECT itemID FROM mycart WHERE email = :email";

$stm = $conn->prepare($query);
$stm->bindParam(':email', $email, PDO::PARAM_STR, 20);
$stm->execute();
$result = $stm->fetchAll(PDO::FETCH_OBJ);
                        foreach ($result as $pers) {
                                $itemID = $pers->itemID;
                        }

Using a query result in another query

3 Answers3

Linked

Related