mushkincode
mushkincode

Reputation: 67

MYSQL PHP Use results of query to requery in foreach loop (complicated - I think)

Here's what I want to do: I am making a listmaking application and I have 2 MYSQL databases, one with a table that contains information on each list like name and then a second db that contains lists as individual tables, listitems are rows in those tables. I want to get select all lists from the first (lookup) table and echo some info and then get all of the corresponding listitems from the corresponding table. I would like to echo a div with the title from the first table then use that table to also make the second query for the items. Is it possible to do a foreach loop for each row returned?

Here's my code so far:

$mysqli2 = new mysqli("localhost", "****", "****", "full_lists");
if ($mysqli2->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " .$mysqli->connect_error;
}

$mysqli = new mysqli("localhost", "****", "****", "lists");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!($stmt = $mysqli->prepare("SELECT listname, dbname FROM lists"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}   

$stmt->bind_result($list, $dbname);

//First statement gets the name of another table in a different database

while ($stmt->fetch()) {
$stmt2 = $mysqli2->prepare("SELECT listitems FROM {$dbname}");
//Second statement uses first to run another select statement getting
//table rows
    $stmt2->execute();
    $stmt2->bind_result($item);
    while ($stmt2->fetch()) { //replace with foreach loop?
????

    }
    }

This is where I'm stuck, I want to echo a div for each $list from the first select statement and then use the $dbname returned to issue the second statement. Inside each $div I would like to put the cooresponding row items like a list. Any way I could do a foreach loop to do foreach result instead of a while loop?

EDIT: here are pictures of the tables, on separate databases but same server example table with info on each list table: https://i.sstatic.net/7kEOn.jpg example list items table: https://i.sstatic.net/XrzoD.png Thanks For Any Help

Upvotes: 0

Views: 413

Answers (1)

phpchap
phpchap

Reputation: 382

There is nothing wrong with using a while loop in these cases and from what you've said you can try this:

while ($stmt->fetch()) {
    $stmt2 = $mysqli2->prepare("SELECT listitems FROM {$dbname}");

    $stmt2->execute();
    $stmt2->bind_result($item);

    echo '<div>';
    echo '<ol>';

    while ($stmt2->fetch()) {
        echo '<li>' . $item . '</li>';
    }

    echo '</ol>';
    echo '</div>';
}

Upvotes: 0

Related Questions