php mysqli prepared statements select

✔ Recommended 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 explicitlyclass 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:

Source: stackoverflow.com

Answered By: Your Common Sense 

Without seeing your code, it's difficult to pinpoint exactly what might be causing the issue, but here are some general tips that might help:

1. Check your SQL query: Make sure your SQL query is correct and that it returns the expected results. You can test your SQL query directly in your database management system to ensure that it works correctly.

2. Check your parameter bindings: Make sure that you're binding the correct parameters to your prepared statement. You can use var_dump() or print_r() to check the values of your variables and make sure they're being passed correctly.

3. Check your data fetching: After executing your prepared statement, make sure you're fetching the data correctly. You can use var_dump() or print_r() to check the values of your fetched data and make sure they match what you expect.

Here's a simple example of a CRUD "Select" function using prepared statements with multiple parameters in PHP using mysqli:

php
function selectData($table, $columns, $conditions, $params) {
    $conn = new mysqli("localhost", "username", "password", "database");

    // Prepare the SQL query
    $sql = "SELECT $columns FROM $table WHERE $conditions";
    $stmt = $conn->prepare($sql);

    // Bind the parameters to the prepared statement
    $types = str_repeat("s", count($params));
    $stmt->bind_param($types, ...$params);

    // Execute the prepared statement
    $stmt->execute();

    // Fetch the results
    $result = $stmt->get_result();

    // Loop through the results and fetch each row as an associative array
    $rows = array();
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }

    // Close the prepared statement and database connection
    $stmt->close();
    $conn->close();

    // Return the fetched rows
    return $rows;
}

To use this function, you would call it with the following parameters:

php
$table = "my_table";
$columns = "id, name, email";
$conditions = "id = ? AND name = ?";
$params = array("1", "John");

$data = selectData($table, $columns, $conditions, $params);

This would select the rows from the "my_table" table where the "id" column equals 1 and the "name" column equals "John", and return an array of associative arrays representing each row.