PHP class for MySQL
An object approach for handling MySQL queries. PHP is using a couple of functions for querying the database, so I have somewhat truncated this functions in a compact class.
Methods used in this class:
- mysql($table_name,$attributes) - this is the constructor class that have both parameters optional
- $table_name - is the name of the table used inside the query. This name is replaced in the query by using the %tbl code where you want your table name to appear.
- $attributes - if you already have an object with some attributes inside it, you can pass all of them to the MySQL class by using this parameter.
- connect($server,$username,$password) - this function simply makes a connection to MySQL server
- $server - this will be the name of the server (in most cases is localhost)
- $username - username for connection
- $password - password for connection
- select($db) - this function is called after you have established a connection to the MySQL server. Whit it you can select a database inside your server.
- $db - the name of your database to select
- load($id,$id_field) - this function loads inside the object only one row where $id_field=$id
- $id - a numeric representation of the primary key field that you want to select
- $id_field - the name of your id field that implicitly is `id`
- delete($id,$id_field) - this function deletes a row with the id=$id.
- $id - a numeric representation of the primary key filed that you want to delete
- $id_field - the name of your id field that implicitly is `id`
- sql($query) - this function will perform a query to your database connection
- $query - this is a string representation of the query executed
- rows - this is an array filled with all the selected rows resulted after the query
- errno - if greater then 0, then you have an error in your sql query
- error - the actual error message return by the query
- count - the number of rows affected by the last query
- insert_id - if you have an auto incremented column in your table, then after an insert, this variable will contain the actual number of the inserted row.
|
Download MySQL Class |
Examples
For our examples, I will use the following database:
DB Name: mysql_class
Table Name: first_table
Table Content & Fields:
| ID | Name | Field |
| 1 | John | IT |
| 2 | Sarah | Management |
| 3 | Alex | Sports |
Example 1
# insert a row
# declare a new instance of our class
$mysql=new mysql(‘first_table’);
# executing an insert query
$mysql->sql("insert into %tbl (name,field) values (’Peter’,'IT’)");
# checking for errors
if ($mysql->errno==0)
{
# displaying the last inserted id
$last_insert=$mysql->insert_id;
echo "Your new row has id=".$last_insert;
}
else
{
# we have an error in our query
echo "MySQL error: ".$mysql->error;
}
Example 2
# fetch a single row
# declare a new instance of our class
$mysql=new mysql(‘first_table’);
# fetch the row with id=2
$mysql->load(2);
# display fields content
echo $mysql->id;
echo $mysql->name;
echo $mysql->field;
Example 3
# delete a row
# declare a new instance of our class
$mysql=new mysql(‘first_table’);
# delete the row inserted last, the first example
$mysql->delete($last_insert);
echo "Deleted the row with id=".$last_insert;
Example 4
# select more rows
# declare a new instance of our class
$mysql=new mysql(‘first_table’);
# proceed with the query
$mysql->sql("select * from %tbl where name=’john’ || name=’alex’");
if ($mysql->errno==0)
{
# echo the number of rows affected by the last query
echo "Rows affected: ";
echo $mysql->count;
# now we go through all the selected rows and display the name
foreach($mysql->rows as $k=>$row)
{
echo $k.". ".$row->name;
}
}
else
{
# we have an error in our query
echo "MySQL error: ".$mysql->error;
}
Example 5
# select one row with the sql function
# declare a new instance of our class
$mysql=new mysql(‘first_table’);
# execute query
$mysql->sql("select count(*) as total from %tbl where field=’IT’");
# nou echo the result
echo $mysql->total;
#this final example shows you that when your result is only one row,
#then the fields returned by the query ar directly included in the object,
#but also in the rows array.
Example 6
# shows you a way to display all the content of the object
echo "<pre>";
print_r($mysql);
echo "</pre>";
del.icio.us Peter on May 2nd 2007 in PHP Stuff
equinoxe responded on 07 Jun 2007 at 7:28 pm #
Salut Petre,
Mai, ce zici tu aici nu-i rau deloc, dar, parca mai degraba ai folosi Cake PHP.
stuart responded on 04 Oct 2007 at 7:03 pm #
very nice
Giacomo responded on 05 Dec 2007 at 1:28 am #
Very good class. Thank you.