I am trying to search 5 tables by title and return the results into an array and then merge the 5 arrays so it can be encoded in JSON and echoed using mysqli. If the result is from the first table, the book table, it outputs fine, but if the result comes from the other 4 tables it outputs [[]]. Any help or tips would be appreciated. Thanks.
The following is my code:
<?php
$mysqli = new mysqli("localhost", "root", "", "mediainventory");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ")" . $mysqli->connect_error;
}
if (!($stmt = $mysqli->prepare("SELECT Library_ID, Title, Author, Type, Department, Status, Due_Date FROM Book WHERE Title=?"))){
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->bind_param("s", $_GET['title'])) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
$id = NULL;
$title = NULL;
$creator = NULL;
$type = NULL;
$department = NULL;
$status = NULL;
$dueDate = NULL;
$book = array();
$music = array();
$movie = array();
$software = array();
$periodical = array();
if (!$stmt->bind_result($id, $title, $creator, $type, $department, $status, $dueDate)) {
echo "Binding result failed: (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {
$book = array($id, $title, $creator, $type, $department, $status, $dueDate);
}
if (!($stmt = $mysqli->prepare("SELECT Library_ID, Title, Artist, Type, Department, Status, Due_Date FROM Music WHERE Title=?"))){
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->bind_param("s", $_GET['Title'])) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
if(!$stmt->bind_result($id, $title, $creator, $type, $department, $status, $dueDate)) {
echo "Binding result failed: (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {
$music = array($id, $title, $creator, $type, $department, $status, $dueDate);
}
if (!($stmt = $mysqli->prepare("SELECT Library_ID, Title, Director, Type, Department, Status, Due_Date FROM Movie WHERE Title=?"))){
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->bind_param("s", $_GET['Title'])) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->bind_result($id, $title, $creator, $type, $department, $status, $dueDate)) {
echo "Binding result failed: (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {
$movie = array($id, $title, $creator, $type, $department, $status, $dueDate);
}
if (!($stmt = $mysqli->prepare("SELECT Library_ID, Title, Developer, Type, Department, Status, Due_Date FROM Software WHERE Title=?"))){
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->bind_param("s", $_GET['Title'])) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->bind_result($id, $title, $creator, $type, $department, $status, $dueDate)) {
echo "Binding result failed: (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {
$software = array($id, $title, $creator, $type, $department, $status, $dueDate);
}
if (!($stmt = $mysqli->prepare("SELECT Library_ID, Title, Publisher, Type, Department, Status, Due_Date FROM Periodical WHERE Title=?"))){
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->bind_param("s", $_GET['Title'])) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->bind_result($id, $title, $creator, $type, $department, $status, $dueDate)) {
echo "Binding result failed: (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {
$periodical = array($id, $title, $creator, $type, $department, $status, $dueDate);
}
$output[] = array_merge($book, $movie, $music, $software, $periodical);
if (empty($output)){
echo "false";
}
else {
echo json_encode($output);
}
$stmt->close();
$mysqli->close();
?>