一个为 PHP MySQL 编写的数据库类,它利用 PDO。
A database class for PHP-MySQL which uses the PDO extension.
[SQL]
host = 127.0.0.1
user = root
password =
dbname = yourdatabasequery("SELECT * FROM persons");Binding parameters is the best way to prevent SQL injection. The class prepares your SQL query and binds the parameters afterwards.
There are three different ways to bind parameters.
bind("id","1");
$db->bind("firstname","John");
$person = $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id");
// 2. Bind more parameters
$db->bindMore(array("firstname"=>"John","id"=>"1"));
$person = $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id"));
// 3. Or just give the parameters to the method
$person = $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id",array("firstname"=>"John","id"=>"1"));More about SQL injection prevention : http://indieteq.com/index/readmore/how-to-prevent-sql-injection-in-php
This method always returns only 1 row.
row("SELECT * FROM Persons WHERE id = :id", array("id"=>"1"));| id | firstname | lastname | sex | age |
|---|---|---|---|---|
| 1 | John | Doe | M | 19 |
This method returns only one single value of a record.
bind("id","3");
$firstname = $db->single("SELECT firstname FROM Persons WHERE id = :id");| firstname |
|---|
| Zoe |
query("SELECT * FROM Persons WHERE Firstname LIKE :firstname ", array("firstname"=>"sekit%"));| id | firstname | lastname | sex | age |
|---|---|---|---|---|
| 4 | Sekito | Khan | M | 19 |
column("SELECT Firstname FROM Persons");| firstname |
|---|
| John |
| Bob |
| Zoe |
| Kona |
| Kader |
When executing the delete, update, or insert statement by using the query method the affected rows will be returned.
query("DELETE FROM Persons WHERE Id = :id", array("id"=>"1"));
// Update
$update = $db->query("UPDATE Persons SET firstname = :f WHERE Id = :id", array("f"=>"Jan","id"=>"32"));
// Insert
$insert = $db->query("INSERT INTO Persons(Firstname,Age) VALUES(:f,:age)", array("f"=>"Vivek","age"=>"20"));
// Do something with the data
if($insert > 0 ) {
return 'Succesfully created a new person !';
}
Every method which executes a query has the optional parameter called bindings.
The row and the query method have a third optional parameter which is the fetch style. The default fetch style is PDO::FETCH_ASSOC which returns an associative array.
Here an example :
row("SELECT * FROM Persons WHERE id = :id", array("id"=>"1"), PDO::FETCH_NUM);
print_r($person_num);
// Array ( [0] => 1 [1] => Johny [2] => Doe [3] => M [4] => 19 )
More info about the PDO fetchstyle : http://php.net/manual/en/pdostatement.fetch.php
The easyCRUD is a class which you can use to easily execute basic SQL operations like(insert, update, select, delete) on your database. It uses the database class I've created to execute the SQL queries.
Actually it's just a little ORM class.
Firstname = "Kona";
$person->Age = "20";
$person->Sex = "F";
$created = $person->Create();
// Or give the bindings to the constructor
$person = new person(array("Firstname"=>"Kona","age"=>"20","sex"=>"F"));
$created = $person->Create();
// SQL Equivalent
"INSERT INTO persons (Firstname,Age,Sex) VALUES ('Kona','20','F')"Id = "17";
$deleted = $person->Delete();
// Shorthand method, give id as parameter
$deleted = $person->Delete(17);
// SQL Equivalent
"DELETE FROM persons WHERE Id = 17 LIMIT 1"Firstname = "John";
$person->Age = "20";
$person->Sex = "F";
$person->Id = "4";
// Returns affected rows
$saved = $person->Save();
// Or give the bindings to the constructor
$person = new person(array("Firstname"=>"John","age"=>"20","sex"=>"F","Id"=>"4"));
$saved = $person->Save();
// SQL Equivalent
"UPDATE persons SET Firstname = 'John',Age = 20, Sex = 'F' WHERE Id= 4"Id = "1";
$person->find();
echo $person->Firstname;
// Johny
// Shorthand method, give id as parameter
$person->find(1);
// SQL Equivalent
"SELECT * FROM persons WHERE Id = 1"all();
// SQL Equivalent
"SELECT * FROM persons 暂无开放 Issues，或尚未同步最近议题。