Simple mysqli solution: $db = new mysqli('localhost','user','password','database'); $resource = $db->query('SELECT * FROM table WHERE 1'); while ( $rows = $resource->fetch_assoc() ) { print_r($rows);//echo "{$row['field']}"; } $resource->free(); $db->close();
The query() / mysqli_query() function performs a query against a database.
The fetch_row() / mysqli_fetch_row() function fetches one row from a result-set and returns it as an enumerated array.
The multi_query() / mysqli_multi_query() function performs one or more queries against the database.
Here's how you properly fetch the result
$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id,username FROM users WHERE username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
$stmt->bind_result($id,$username);
while ($stmt->fetch()) {
echo "Id: {$id}, Username: {$username}";
}
or you can also do:
$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id, username FROM users WHERE username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo "Id: {$row['id']}, Username: {$row['username']}";
}
I hope you realise I got the answer directly from the manual here and here, which is where you should've gone first.
From comments it is found that LIKE wildcard characters (_
and %
) are not escaped by default on Paramaterised queries and so can cause unexpected results.
Therefore when using "LIKE" statements, use this 'negative lookahead' Regex to ensure these characters are escaped :
$param = preg_replace('/(?<!\\\)([%_])/', '\\\$1',$param);
As an alternative to the given answer above you can also use the MySQL CONCAT function thus:
$stmt = $db->prepare("SELECT id,Username FROM users WHERE Username LIKE CONCAT('%',?,'%') ");
$stmt->bind_param("s", $param);
$stmt->execute();
Which means you do not need to edit your $param
value but does make for slightly longer queries.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With