Reputation: 67
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
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