doujia4041 2014-08-13 12:50
浏览 17
已采纳

查询不使用使用直接SQL工作的php返回结果

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'

展开全部

  • 写回答

1条回答 默认 最新

  • donxbje866688 2014-08-14 10:06
    关注

    This is a known feature / bug with mysqli. You have to free all the results from the queries you've done so far before you can make more.

    After this...

    if (!($mysqli->multi_query($query))) {
        echo "Error: (" . $mysqli->errno . ") " . $mysqli->error;
    }
    

    Try...

    while($mysqli->more_results())
    {
        $mysqli->next_result();
        $discard = $mysqli->store_result();
    }
    

    While INSERT and UPDATE don't return result sets, they do return statuses that will be considered results to mysqli

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 python 下载群辉文件
  • ¥50 代码还没怎么运行但是需要代码功能调用数据
  • ¥15 vue请求不到数据,返回状态200,数据为html
  • ¥15 访问url时不会自动调用其 Servlet的doGet()
  • ¥15 用白鹭引擎开发棋牌游戏的前端为什么这么难找
  • ¥35 哪位专业人士知道这是什么原件吗?哪里可以买到?
  • ¥15 关于#c##的问题:treenode反序列化后获取不到上一节点和下一节点,Fullpath和Handle报错
  • ¥15 一部手机能否同时用不同的app进入不同的直播间?
  • ¥20 输入import torch显示Intel MKL FATAL ERROR,系统驱动1%,: Cannot load mkl_intel_thread.dll.
  • ¥15 点云密度大则包围盒小
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部