当前位置: 动力学知识库 > 问答 > 编程问答 >

php - how to use mysqli to retrieve terms from database

问题描述:

I don't know if I have got this correct but what I am trying to do is that a user enters in a term, or multiple terms in a text-box and after the user submits the text-box, it should display any results that contains the term. But I cannot seem to get it working so my question is that am I on the right track when it comes to using mysqli to be able to retrieve terms from the database when entered in the text-box? I am not sure if the query is correct with the like statement and if it is looping through each term but if anyone can help it will be much appreciated :)

I am receiving a warning as well which is this which needs to be fixed:

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn't match number of parameters in prepared statement in ... on line 84. How can this be fixed?

Below is the mysqli side of the code:

 <?php

$username="xxx";

$password="xxx";

$database="mobile_app";

$mysqli = new mysqli("localhost", $username, $password, $database);

/* check connection */

if (mysqli_connect_errno()) {

printf("Connect failed: %s\n", mysqli_connect_error());

die();

}

$questioncontent = (isset($_GET['questioncontent'])) ? $_GET['questioncontent'] : '';

?>

<form action="previousquestions.php" method="get">

<p>Search: <input type="text" name="questioncontent" value="<?php echo $questioncontent; ?>" onchange="return trim(this)" /></p>

<p><input id="searchquestion" name="searchQuestion" type="submit" value="Search" /></p>

</form>

<?php

if (isset($_GET['searchQuestion'])) {

$searchquestion = $questioncontent;

$terms = array(explode(" ", $searchquestion));

//loop through each term

foreach ($terms as &$each) {

$each = '%'.$each.'%';

$questionquery = "

SELECT q.QuestionContent

FROM Question q

WHERE ";

$i=0;

$whereArray = array();

$orderByArray = array();

$orderBySQL = "";

$paramString = "";

//loop through each term

foreach ($terms as &$each) {

$each = '%'.$each.'%';

$i++;

//if only 1 term entered then perform this LIKE statement

if ($i == 1){

$questionquery .= "q.QuestionContent LIKE ? ";

} else {

//If more than 1 term then add an OR statement

$questionquery .= "OR q.QuestionContent LIKE ? ";

$orderBySQL .= ",";

}

$orderBySQL .= "IF(q.QuestionContent LIKE ? ,1,0)";

$whereArray = "%" . $each . "%";

$orderByArray = $each;

$paramString = "ss";

}

$questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY " . $orderBySQL;

$stmt=$mysqli->prepare($questionquery)or die($mysqli->error);;

$stmt->bind_param($paramString, array_merge($whereArray, $orderByArray));

$stmt->execute();

$stmt->bind_result($dbQuestionContent);

$questionnum = $stmt->num_rows();

}

?>

网友答案:

This should do most of what you want, I think - I've not tested it, so there may be typos; I'll leave fixing those as an exercise for the reader.

$terms = array(explode(" ", $searchquestion));

$questionquery = "SELECT q.QuestionContent FROM Question q WHERE ";

$i=0;

$whereArray = array();
$orderByArray = array();
$orderBySQL = "";
$paramString = "";

//loop through each term
foreach ($terms as &$each) {
    $i++;
    //if only 1 term entered then perform this LIKE statement
    if ($i == 1){
        $questionquery .= "q.QuestionContent LIKE ? ";
    } else {
        //If more than 1 term then add an OR statement
        $questionquery .= "OR q.QuestionContent LIKE ? ";
        $orderBySQL .= ",";
    }

    $orderBySQL .= "IF(q.QuestionContent LIKE ? ,1,0)"; 

    $whereArray[] = "%" . $each . "%";
    $orderByArray[] = $each;

    $paramString .= "ss";
}  

$questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY " . $orderBySQL; 
$stmt=$mysqli->prepare($questionquery)or die($mysqli->error); ;  
$stmt->bind_param($paramString, array_merge($whereArray, $orderByArray));
分享给朋友:
您可能感兴趣的文章:
随机阅读: