php mysqli prepared statements select

Question

Trying to get a function working to create simple CRUD "Select" with multiple parameters to any table. I think I got the hardest part, but couldn't fetch the data right now. Maybe I'm doing something wrong I can't figure out.

My prepared statement function:

function prepared_query($mysqli, $sql, $params, $types = ""){
    $types = $types ?: str_repeat("s", count($params));
    if($stmt = $mysqli->prepare($sql)) { 
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        return $stmt;
    } else {
        $error = $mysqli->errno . ' ' . $mysqli->error;
        error_log($error);
    }
}

The query creator:

function create_select_query($table, $condition = "", $sort = "", $order = " ASC ", $clause = ""){
    $table = escape_mysql_identifier($table);
    $query = "SELECT * FROM ".$table;
    if(!empty($condition)){
        $query .= create_select_query_where($condition,$clause);
    }
    if(!empty($sort)){
        $query .= " ORDER BY ".$sort." $order";
    }
    return $query;
}

The helper function to create the WHERE clause:

function create_select_query_where($condition,$clause){
    $query          = " WHERE ";
    if(is_array($condition)){
        $pair       = array();
        $size       = count($condition);
        $i = 0;
        if($size > 1){
            foreach($condition as $field => $val){
                $i++;
                if($size-1 == $i){
                    $query .= $val." = ? ".$clause. " ";
                }else{
                    $query .= $val." = ? ";
                }
            }        
        }else{
            foreach($condition as $field => $val){
                $query .= $val." = ? ";
            }
        }
    }else if(is_string($condition)){
        $query .= $condition;
    }else{
        $query = "";
    }
    return $query;
}

The select function itself:

function crud_select($conn, $table, $args, $sort, $order, $clause){
    $sql = create_select_query($table, array_keys($args),$sort, $order, $clause);
    print_r($sql);
    if($stmt = prepared_query($conn, $sql, array_values($args))){
        return $stmt;
    }else{
        $errors [] = "Something weird happened...";
    } 
}

When I create the query, it seems to be OK but can't fetch the data. If I create an array with only one argument the query translates into:

SELECT * FROM `teste_table` WHERE id = ?

If I create with multiple parameters, it turns like this:

SELECT * FROM `teste_table` WHERE id = ? AND username = ?

So, how can I properly fetch the data from the select. This should be used for multiple purposes, so I could get more than one result, so the best way would be fetch data as array I guess.

I guess I'm close, but can't figure it out. Thanks

Your Common Sense · Accepted Answer

In PHP >= 8.2 there is a function that lets you execute a SELECT query (as well as any other kind of query) in one go, execute_query():

$query = 'SELECT Name, District FROM City WHERE CountryCode=? ORDER BY Name LIMIT ?';
$rows = $mysqli->execute_query($query, ['DEU', 5])->fetch_all(MYSQLI_ASSOC);

For the older versions you will need a helper function like this:

function prepared_query($mysqli, $sql, $params, $types = ""){
    $types = $types ?: str_repeat("s", count($params));
    $stmt = $mysqli->prepare($sql)) { 
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return $stmt;
}

Just stick to it and it will serve you all right.

$sql = "SELECT * FROM `teste_table` WHERE id = ? AND username = ?";
$stmt = prepared_query($mysqli, $sql, [$id, $name]);
$row = $stmt->get_result()->fetch_assoc();

Fora a real CRUD, however, you could use the approach called Table Gateway Pattern. For this, you can create a class, that would implement methods for all basic operations on a table:

Creating a record (using INSERT query)
Reading a record (Using Primary key lookup)
Updating a record
Deleting a record

Then you would extend this class for the every table used.

And then you will have a CRUD solution that is concise, readable and safe.

Here is a sample Table Gateway I wrote for the occasion

By simply extending the basic class, you can get everything you want in a much simpler code:

// add a class for the table, listing its name and columns explicitly
class UserGateway extends BasicTableGateway {
    protected $table = 'gw_users';
    protected $fields = ['email', 'password', 'name', 'birthday'];
}

// and then use it in your code
$userGateway = new UserGateway($pdo);

$user = $userGateway->read($id);
echo "Read: ". json_encode($user),PHP_EOL;

See - it's really simple, yet safe and explicit.

Remember that for the more complex queries you will have to either use plain SQL or add new methods to the UserGateway class, such as

public function getByEmail($email) {
    return $this->getBySQL("SELECT * from `{$this->table}` WHERE email=?",[$email]);
}

Speaking of your current approach, it's just unusable. I told you to limit your select function to a simple primary key lookup. And now you opened a can of worms. As a result you are getting entangled implementation code and unreadable application code.

$table, $args, $sort, $order, $clause

What all these variables are for? How you're going to call this function - a list of gibberish SQL stubs in a random order instead of plain and simple SQL string? And how to designate a list of columns to select? How to use JOINS? SQL functions? Aliases? Why can't you just write a single SQL statement right away? You already have a function for selects, though without this barbaric error reporting code you added to it:

php mysqli prepared statements select

Answers (1)

Related Questions