Looping mulitple SQL queries in multiple arrays

Question

I'm a PHP beginner. I've dabbled before, but when I've begun to get the knack a new project takes me elsewhere. If anyone can assist; I'd appreciate it.

I am using multiple queries and arrays to retrieve data between two mySQL tables starting from 1 initial known variant.

I'd like each query to process all results from the previous query. This is not occurring.

Current results: The first query echos all results. The second query echos one result. The third query echos 1 result. The final echo displays all the final results (desired, but missing the first 148 rows).

Desired results: Echo all 149 results from all three queries then echo a table/array of all 3 queries (to confirm correlation).

<?php
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
//Select all POST_IDs for variation 2.1M
$sql = "SELECT post_id FROM wp_postmeta WHERE meta_value = '2-1m'";
$result = $conn->query($sql);
//Array and display POST_IDs
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["post_id"]. "<br>";
}
} else {
    echo "0 results";
}
//Prepare POST_IDs for next query
foreach ($result as $row){
$postids = $row["post_id"];
    }
        //Use POST_IDs to select all PARENT_IDs
    $sql2 = "SELECT post_parent FROM wp_posts WHERE ID = ($postids)";
$result2 = $conn->query($sql2);
//Array and display PARENT_IDs
if ($result2->num_rows > 0) {
    // output data of each row
    while($row2 = $result2->fetch_assoc()) {
        echo "parentid: " . $row2["post_parent"]. "<br>";
    }
} else {
    echo "0 results";
}
//Prepare PARENT_IDs for next query
foreach ($result2 as $row2){
$parentids = $row2["post_parent"];
}
//Select PRICES using PARENT_IDs and META_KEY for Price
$sql3 = "SELECT meta_value FROM wp_postmeta WHERE meta_key = '_price' AND post_id = ($parentids)";
$result3 = $conn->query($sql3);
if ($result3->num_rows > 0) {
    // output data of each row
    while($row3 = $result3->fetch_assoc()) {
        echo "price: " . $row3["meta_value"]. "<br>";
    }
} else {
    echo "0 results";
}
//Array and display PRICES
foreach ($result3 as $row3){
$prices = $row3["meta_value"];
}
//Display all retrieved data
echo "<div><p>" . $postids . " " . $parentids . " " . $prices . "</p></div>";
$conn->close();
?>

Of course all this can be done with one query and one loop using JOIN in SQL but there won't be successive print outs but flatter echo line. — Parfait
– Parfait, Commented Feb 6, 2018 at 14:57
Really? How would one structure that? And could the result be used to update another table (as is the next goal of this routine?) — ImmortalViper
– ImmortalViper, Commented Feb 6, 2018 at 15:03
Not sure what that next step is but MySQL allows JOIN in update queries. — Parfait
– Parfait, Commented Feb 6, 2018 at 15:14

Syscall · Accepted Answer · 2018-02-06 15:25:55Z

1

You're overriding your variables instead of cumulate them into an array:

foreach ($result as $row){
    $postids = $row["post_id"];
}

Should be :

$postids = [];
foreach ($result as $row){
    $postids[] = $row["post_id"];
}

Then :

"WHERE ID = ($postids)"

Should be :

if (!empty($postids)) {
 ... "...WHERE ID IN (".implode(',', $postids).")..." ...
}

NB: you should have a look to parameterized queries : Parameterized Queries PHP/MySQL

The same thing happend for $parentids.

edited Feb 6, 2018 at 15:25

answered Feb 6, 2018 at 14:46

Syscall

19.8k10 gold badges44 silver badges60 bronze badges

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

1 Comment

ImmortalViper Over a year ago

Thanks. This is bang on.

Collectives™ on Stack Overflow

Looping mulitple SQL queries in multiple arrays

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related