How to bind SQL variables in PHP?
There's e.g. PDO.
An introduction to pdo and prepared statements (including bound parameters) is located at http://docs.php.net/pdo.prepared-statements
How do I query a database with php using bind variables?
You missed the :
before term
param. Don't need to bind column name. Just use the $column
var instead of :column
.
search("genre", "electropop", $db);
function search($column, $term, $db) {
try {
$sql = "SELECT * FROM Songs WHERE $column=:term;";
$stmt = $db->prepare($sql);
$params = array(":term" => $term);
$stmt->execute($params);
$arr = $stmt->fetchALL(PDO::FETCH_ASSOC);
print (json_encode($arr));
}
catch(PDOException $ex) {
catch_error("The search failed.", $ex);
}
}
How to bind dates and other variables to php sql query? (MySql)
Some extra information to my comment given above:
If I do understand correctly have a
look at:
Using Mysqli bind_param with date and time columns?.
It looks like you can just treath it
as a string.
If you want to do it with bind_param
it is the only way I know to do it and I don't see any problems. If mysql receives a wrong formatted date it will insert a 0000-00-00 value to your table.
Can you tell me what you think could be a problem? If you insert it as a normal query you also use the same syntax as a String.
How can I use bind variables for inidcate columns of mySql table?
You shouldn´t add a new column in your table for each team. You should normalize your table to 3FN and make a new table for teams and another one for the relation user & team. What would happen if a user is member of 100 teams?? Will you have 100 extra columns for everyone?? How will you handle it??.
Or worst, if you add a new column (for everybody) for each team, if you have 500 teams to manage, you will have 500 columns for every row. How will you know wich member are in each team? Will you make a query asking for the 500 columns?
And answering your question, you can´t bind the name of columns, you will need to concat string like here:
$stmt = $this -> con -> prepare("SELECT id, name, surname, `date of birth`, `fiscal code` FROM Player WHERE ".$column." = ?");
if($stmt -> bind_param("i", $id)) {
Also, you need to change your '
and use backticks ` for your column names
How to include a PHP variable inside a MySQL statement
The rules of adding a PHP variable inside of any MySQL statement are plain and simple:
1. Use prepared statements
This rule covers 99% of queries and your query in particular. Any variable that represents an SQL data literal, (or, to put it simply - an SQL string, or a number) MUST be added through a prepared statement. No exceptions.
This approach involves four basic steps
- in your SQL statement, replace all variables with placeholders
- prepare the resulting query
- bind variables to placeholders
- execute the query
And here is how to do it with all popular PHP database drivers:
Adding data literals using mysqli
$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description)
VALUES(?, ?, 'whatever')";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $type, $reporter);
$stmt->execute();
The code is a bit complicated but the detailed explanation of all these operators can be found in my article, How to run an INSERT query using Mysqli, as well as a solution that eases the process dramatically.
For a SELECT query you will need to add just a call to get_result()
method to get a familiar mysqli_result
from which you can fetch the data the usual way:
$reporter = "John O'Hara";
$stmt = $mysqli->prepare("SELECT * FROM users WHERE name=?");
$stmt->bind_param("s", $reporter);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc(); // or while (...)
Adding data literals using PDO
$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description)
VALUES(?, ?, 'whatever')";
$stmt = $pdo->prepare($query);
$stmt->execute([$type, $reporter]);
In PDO, we can have the bind and execute parts combined, which is very convenient. PDO also supports named placeholders which some find extremely convenient.
2. Use white list filtering
Any other query part, such as SQL keyword, table or a field name, or operator - must be filtered through a white list.
Sometimes we have to add a variable that represents another part of a query, such as a keyword or an identifier (a database, table or a field name). It's a rare case but it's better to be prepared.
In this case, your variable must be checked against a list of values explicitly written in your script. This is explained in my other article, Adding a field name in the ORDER BY clause based on the user's choice:
Unfortunately, PDO has no placeholder for identifiers (table and field names), therefore a developer must filter them out manually. Such a filter is often called a "white list" (where we only list allowed values) as opposed to a "black-list" where we list disallowed values.
So we have to explicitly list all possible variants in the PHP code and then choose from them.
Here is an example:
$orderby = $_GET['orderby'] ?: "name"; // set the default value
$allowed = ["name","price","qty"]; // the white list of allowed field names
$key = array_search($orderby, $allowed, true); // see if we have such a name
if ($key === false) {
throw new InvalidArgumentException("Invalid field name");
}
Exactly the same approach should be used for the direction,
$direction = $_GET['direction'] ?: "ASC";
$allowed = ["ASC","DESC"];
$key = array_search($direction, $allowed, true);
if ($key === false) {
throw new InvalidArgumentException("Invalid ORDER BY direction");
}
After such a code, both $direction
and $orderby
variables can be safely put in the SQL query, as they are either equal to one of the allowed variants or there will be an error thrown.
The last thing to mention about identifiers, they must be also formatted according to the particular database syntax. For MySQL it should be backtick
characters around the identifier. So the final query string for our order by example would be
$query = "SELECT * FROM `table` ORDER BY `$orderby` $direction";
Binding result of a query with PHP variable
You need a proper alias
SELECT AVG(E.salary) my_avg
FROM employee as E,permanent_employee as P
WHERE E.empID=P.empID
echo "<td>{$my_avg}</td>";
How to use bindValue with LIKE operator in SQL query?
You probably want :
$query = "SELECT *
FROM books
WHERE title LIKE CONCAT( '%', :title, '%')";
...
...
statement->bindValue(':title', $title, PDO::PARAM_STR);
The bind parameter should be used as a litteral string. CONCAT can be used to concatenate the parameter with percent signs on both ends.
Related Topics
PHP Datetime() Class, Change First Day of the Week to Monday
PHP to Clean-Up Pasted Microsoft Input
How to Add Dynamic Dropdown List Column on Laravel 5.3 Registration
How to Handle User Input of Invalid Utf-8 Characters
Create New Xml File and Write Data to It
Special Characters in Fpdf with PHP
Laravel $Q->Where() Between Dates
$Stmt->Execute():How to Know If Db Insert Was Successful
MySQL Get a Random Value Between Two Values
Extract a Substring Between Two Characters in a String PHP
How to Replace All Xhtml/HTML Line Breaks (<Br>) with New Lines
Explode() into $Key=>$Value Pair
PHP Crop Image to Fix Width and Height Without Losing Dimension Ratio