How to create a prepared statement to replace mysqli

I am trying to turn this section of SQL query into a prepared statement but it has related functions calling its results so am having a hard time tying it all up so here is

This is the SQL statement:

{
    $name = explode(' ', $key, 2); // Break String into Array.

    if(empty($name[1])) {
        $sql = "SELECT * 
                FROM users 
                WHERE users.user_firstname = '$name[0]' 
                OR users.user_lastname= '$name[0]'";

    } else {
        $sql = "SELECT * 
                FROM users 
                WHERE users.user_firstname = '$name[0]' 
                AND users.user_lastname= '$name[1]'";
    }

    include 'includes/userquery.php';
}

The functions calling it:

$query = mysqli_query($conn, $sql); 
if(mysqli_num_rows($query) == 0){
    echo '<div >';
    echo 'There is no results given the keyword, try to widen your search query.';
    echo '</div>';
    echo '<br>';
}

while($row = mysqli_fetch_assoc($query)){
    include 'includes/post.php';
    echo '<br>';
}

I tried some ways of turning the query into prepared but I get errors such as ? placeholder isn't identified or functions receiving bool

CodePudding user response：

You can simplify the whole process if you start by loading 2 vars before starting the query with the right values from your $name array.

Then a simple change to the query to make it parameterised and bindable will allow you to prepare, bind and execute the query

Then a small change to the fetching process should have you off and running

{
    $name = explode(' ', $key, 2); // Break String into Array.

    if(empty($name[1])) {
        $n1 = $name[0];
        $n2 = $name[0];
        $sql = "SELECT * 
                FROM users 
                WHERE users.user_firstname = ? 
                OR users.user_lastname= ?";
    } else {
        $n1 = $name[0];
        $n2 = $name[1];
        $sql = "SELECT * 
                FROM users 
                WHERE users.user_firstname = ? 
                AND users.user_lastname= ?";
    }


    $stmt = $conn->prepare($sql);
    $stmt->bind_param('ss', $n1,$n2);
    $result = $stmt->execute();
    $query = $stmt->get_result();
    
    include 'includes/userquery.php';
}

The function (I assume thats whats being included) would then become

if($query->num_rows == 0){
    echo '<div >';
    echo 'There is no results given the keyword, try to widen your search query.';
    echo '</div>';
    echo '<br>';
}

while($row = $query->fetch_assoc()){
    include 'includes/post.php';
    echo '<br>';
}

CodePudding user response：

They way you use include is non-standard, usually includes are done at the top of file, only when the included files returns something its called in middle of code, this way its like copy pasting code to specific block, which does work but it cause confusion like you have variable $sql in includes/userquery.php but from where does it come from ? its not defined in that file and not even included, ofc now when you writing your code you know but later no way you will remember. Its way better to use functions which you include on top of file then call at place you want the functionality and pass data as parameters, that way its clear whats going on. Now lets see what you can do with your current code:

{
    $name = explode(' ', $key, 2); // Break String into Array.

    if(empty($name[1])) {
        $sql_param_types = 'ss';
        $sql_params = [&$name[0], &$name[0]]; // note the reference here, cause its required by bind_param to be refs
        $sql = "SELECT * 
                FROM users 
                WHERE users.user_firstname = ? 
                OR users.user_lastname= ?";

    } else {
        $sql_param_types = 'ss';
        $sql_params = [&$name[0], &$name[1]]; // note the reference here, cause its required by bind_param to be refs
        $sql = "SELECT * 
                FROM users 
                WHERE users.user_firstname = ?
                AND users.user_lastname= ?";
    }

    include 'includes/userquery.php';
}

userquery.php:

$stmt = mysqli_prepare($conn, $sql);
call_user_func_array([$stmt, 'bind_param'], array_merge([$sql_param_types], $sql_params));
$stmt->execute();
$result = $stmt->get_result();

if($result->num_rows == 0){
    echo '<div >';
    echo 'There is no results given the keyword, try to widen your search query.';
    echo '</div>';
    echo '<br>';
}

while($row = $result->fetch_assoc()){
    include 'includes/post.php';
    echo '<br>';
}