CODEWITHSUNDEEP
phpmysql
user8829737
user8829737

Reputation:

Using a query result in another query

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?

Upvotes: 3

Views: 2982

Answers (3)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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();

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

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);

Upvotes: 1

Jop Rill
Jop Rill

Reputation: 65

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;
                        }

Upvotes: 0

Related Questions