UPDATE: - So I have found that the cause of the problem is that I am running a previous query (in this case a multi_query) that appears to be out of sync with the next query. If I close and reopen the connection, it works fine, but there is probably a better way to go about solving this problem. Any pointers on a better way are appreciated.
Complete code:
if (filter_var($_POST['survey']['contact']['contactEmail'], FILTER_VALIDATE_EMAIL)) {
$survey = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
$survey = $survey['survey'];
$email = $survey['contact']['contactEmail'];
include 'databaseconnection.php';
/*
* This part works...
*/
$query = "BEGIN;INSERT INTO interests(contactEmail) VALUES ('$email');INSERT INTO equipment(contactEmail) VALUES ('$email'); INSERT INTO classes(contactEmail) VALUES ('$email');INSERT INTO housing(contactEmail) VALUES ('$email');INSERT INTO newSkills(contactEmail) VALUES ('$email');INSERT INTO selfIdentity(contactEmail) VALUES ('$email');INSERT INTO areYouA(contactEmail) VALUES ('$email');INSERT INTO locationIdeas(contactEmail) VALUES ('$email');INSERT INTO involved(contactEmail) VALUES ('$email');INSERT INTO contact(contactEmail) VALUES ('$email');";
foreach ($survey as $key => $val) {
foreach ($val as $k => $v) {
$query.= "UPDATE $key SET $key.$k = '$v' WHERE $key.contactEmail='$email';";
}
}
$query.="COMMIT;";
if (!($mysqli->multi_query($query))) {
echo "Error: (" . $mysqli->errno . ") " . $mysqli->error;
}
/*
* This subsequent query does not work unless I close the connection and restart it, giving an " Error: (2014) Commands out of sync; you can't run this command now "
*/
/*******************************************/
$mysqli->close();
include 'databaseconnection.php';
/*******************************************/
$query = "SELECT distinct * FROM interests, equipment, classes, housing, newSkills, selfIdentity, areYouA, locationIdeas, involved, contact WHERE interests.contactEmail='$email' AND equipment.contactEmail='$email' AND classes.contactEmail='$email' AND housing.contactEmail='$email' AND newSkills.contactEmail='$email' AND selfIdentity.contactEmail='$email' AND areYouA.contactEmail='$email' AND locationIdeas.contactEmail='$email' AND involved.contactEmail='$email' AND contact.contactEmail='$email';";
if (!($result = $mysqli->query($query))) {
echo "Error: (" . $mysqli->errno . ") " . $mysqli->error;
}
$statistics = $result->fetch_array(MYSQLI_ASSOC);
var_dump($statistics);
} else {
echo 'Please enter a valid email address';
}
I am baffled as to why this query is not returning any results. The code is supposed to be returning someone's entry in a survey for confirmation that it was received and the '$email' is the email address of the person who did the survey. But it appears not to be returning anything (nothing is returned from a var_dump of the $result variable) and I get the following message"
The relevant code:
$result = $mysqli->query("SELECT distinct * FROM interests, equipment, classes, housing, newSkills, selfIdentity, areYouA, locationIdeas, involved, contact WHERE interests.contactEmail='$email' AND equipment.contactEmail='$email' AND classes.contactEmail='$email' AND housing.contactEmail='$email' AND newSkills.contactEmail='$email' AND selfIdentity.contactEmail='$email' AND areYouA.contactEmail='$email' AND locationIdeas.contactEmail='$email' AND involved.contactEmail='$email' AND contact.contactEmail='$email';");
var_dump($result);
$statistics = $result->fetch_array(MYSQLI_ASSOC);
foreach ($statistics as $k => $v) {
echo $k . ' : ' . $v;
}
The Error Message:
Fatal error: Call to a member function fetch_array() on a non-object in C:\xampp\htdocs\otherrealmhomepageredesign\serversidestuff\SurveySubmission.php on line 41
When I do a dummy search for a test email using straight sql in MySQL Workbench, I get the results I am looking for.
Straight SQL:
SELECT distinct *
FROM interests, equipment, classes, housing, newSkills, selfIdentity, areYouA, locationIdeas, involved, contact
WHERE interests.contactEmail='bob@t.qdTT' AND equipment.contactEmail='bob@t.qdTT' AND classes.contactEmail='bob@t.qdTT' AND housing.contactEmail='bob@t.qdTT' AND newSkills.contactEmail='bob@t.qdTT' AND
selfIdentity.contactEmail='bob@t.qdTT' AND areYouA.contactEmail='bob@t.qdTT' AND locationIdeas.contactEmail='bob@t.qdTT' AND involved.contactEmail='bob@t.qdTT' AND contact.contactEmail='bob@t.qdTT';
Results:
'bob@t.qdTT', NULL, NULL, 'on', 'on', NULL, NULL, 'dbf gbcvb', 'bob@t.qdTT', NULL, NULL, NULL, 'on', NULL, NULL, NULL, NULL, 'on', 'dfxbv ', 'bob@t.qdTT', 'on', NULL, NULL, 'on', NULL, NULL, NULL, NULL, NULL, 'erdgettrf', 'bob@t.qdTT', 'yes', 'rtfhthtrfgh', 'bob@t.qdTT', 'no', 'ehfnsrtehtertdf', 'bob@t.qdTT', 'other', 'dherdgdfxbdfb', 'bob@t.qdTT', 'on', 'on', 'on', 'on', 'on', 'on', 'on', 'on', 'on', 'on', 'on', 'on', 'on', 'rhgergbdbgdfbdf', 'bob@t.qdTT', 'sfgdregbegerg', 'bob@t.qdTT', 'no', 'aegsdgdr', 'estgdfbdfxbdfx ', 'bob@t.qdTT', 'Asghd', '378767'