function reuse_generateXML() { /* Filename to write XML to */ // - As $XML_FILENAME require 'xmlGeneratorConfig.php'; /* Database Connection function */ /* It's messy, but assumed to be defined prior to this page being included */ //require $DATABASE_CONNECT; /* SimpleXML Declaration */ $sxml = new SimpleXMLElement('<?xml version="1.0" encoding="UTF-8" ?><reuse/>'); /* Revision */ $revision = $sxml->addChild("Revision", uniqid()); /* Business List*/ $businessList = $sxml->addChild("BusinessList"); /* connect to database */ /* OLD include( $DATABASE_CRED_FILE ); $mysqli = new mysqli($DBUrl, $DBUser, $DBPw, $DBName); if ($mysqli->connect_errno) { echo "Failed to connect to database (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; exit(); } */ /* NEW */ $mysqli = connectReuseDB(); /* Query for businesses and all items associated with it */ //Prepare Statement - Select all businesses and their attributes if (!($stmt = $mysqli->prepare("SELECT L.id, L.name, L.address_line_1, L.address_line_2, L.city, S.abbreviation, L.zip_code, L.phone, L.website, L.latitude, L.longitude FROM Reuse_Locations L LEFT JOIN States S ON L.state_id = S.id"))) { echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error; } $stmt->bind_result($Loc_id, $name, $address_line_1, $address_line_2, $city, $state, $zip_code, $phone, $website, $latitude, $longitude); $stmt->execute(); $stmt->store_result(); //fetch and print results while ($stmt->fetch()) { //echo "$name \r\n"; $business = $businessList->addChild("business"); $business->addChild("id", $Loc_id); $business->addChild("name", escapeSpecial($name)); $contact_info = $business->addChild("contact_info"); $address = $contact_info->addChild("address"); $address->addChild("address_line_1", escapeSpecial($address_line_1)); $address->addChild("address_line_2", $address_line_2); $address->addChild("city", escapeSpecial($city)); $address->addChild("state", $state); $address->addChild("zip", escapeSpecial($zip_code)); $contact_info->addChild("phone", escapeSpecial($phone)); $contact_info->addChild("website", escapeSpecial($website)); $latlong = $contact_info->addChild("latlong"); $latlong->addChild("latitude", $latitude); $latlong->addChild("longitude", $longitude); $catList = $business->addChild("category_list"); if (!($stmt2 = $mysqli->prepare("SELECT DISTINCT CAT.id, CAT.name FROM Reuse_Categories CAT INNER JOIN Reuse_Items ITM ON ITM.category_id = CAT.id INNER JOIN Reuse_Locations_Items L_ITM ON L_ITM.item_id = ITM.id INNER JOIN Reuse_Locations L ON L.id = L_ITM.location_id WHERE L.id = ?"))) { echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error; } $stmt2->bind_param('i', $Loc_id); $stmt2->bind_result($cat_id, $cat_name); $stmt2->execute(); $stmt2->store_result(); while ($stmt2->fetch()) { //echo "\t $cat_name \r\n"; $category = $catList->addChild("category"); $category->addChild("name", $cat_name); $subcatlist = $category->addChild("subcategory_list"); $stmt3 = $mysqli->prepare("SELECT ITM.name FROM Reuse_Categories CAT INNER JOIN Reuse_Items ITM ON ITM.category_id = CAT.id INNER JOIN Reuse_Locations_Items L_ITM ON L_ITM.item_id = ITM.id INNER JOIN Reuse_Locations L ON L.id = L_ITM.location_id WHERE L.id = ? AND CAT.id = ?"); $stmt3->bind_param('ii', $Loc_id, $cat_id); $stmt3->bind_result($subcat_name); $stmt3->execute(); $i = 0; while ($stmt3->fetch()) { //echo "\t\t $subcat \r\n"; //$i = $i + 1; $subcat = $subcatlist->addChild("subcategory", $subcat_name); } $stmt3->close(); } $stmt2->close(); } $stmt->close(); $sxml->asXML($XML_FILENAME); return true; }
} /* bind the variables */ if (!$stmt->bind_param('ii', $item, $match)) { echo "Binding failed. (" . $mysqli->connect_errno . ")" . $mysqli->connect_error; } /* execute */ if (!$stmt->execute()) { echo "Execute failed. (" . $mysqli->connect_errno . ")" . $mysqli->connect_error; } $mysqli->close(); }); /* Adding a New Item */ $app->post('/items', function () { $name = $_POST['name']; $cat = $_POST['cat']; $mysqli = connectReuseDB(); /* Check to make sure it's not a duplicate */ $result = $mysqli->query('SELECT name, id FROM Reuse_Items'); while ($row = $result->fetch_object()) { if ($row->name == $name) { $mysqli->close(); } } /* prepare the statement*/ if (!($stmt = $mysqli->prepare("INSERT INTO Reuse_Items (name, category_id) VALUES (?, ?)"))) { echo "Prepare failed : (" . $mysqli->connect_errno . ")" . $mysqli->connect_error; } /* bind the variables */ if (!$stmt->bind_param('si', $name, $cat)) { echo "Binding failed. (" . $mysqli->connect_errno . ")" . $mysqli->connect_error; }