If you are looking for a simple way to connect to your mysql database via php and CRUD (create, read, update and delete record info), then php simple database class is the right tool for you.

With this class, you can easily create a very secure and powerful system.

Download Functions

When you go the Github link, you will notice there are several different functions in the file.

However, in order for you to use that file, you will only need update that file with your database connection settings once and then you will be on your way to create some really powerful and secure web applications.

Add Database Connection Setting:

At the top of your page(ajax or include file) :

$db_conn = array('host' => 'localhost', 'user' => 'root','pass' => '','database' => 'test', );
$db = new SimpleDBClass($db_conn)

Warning Settings

Next you will notice:

//To show query error messages set on or to hide then set to off
//Goto the class file and look for line where it says: 
public $ShowQryErrors = 'on'; //on or off

During your creation and testing of your web application, you might want to leave this on. That way you can identify where is the error occurring.

Once you are confident that your web application is ready for production, set the errors to “off”.

Functions You Will Need To Know

There are 5 different query functions:

• Select: all rows
• Insert: add row(s)
• Update: will update a field
• Delete: will delete row(s)
• Qry: general purpose query

1 mysql prevention injection function:

• CleanDBData

I would highly recommend you run your users data through “CleanDBData” function to prevent any and all sql injections that could cause problems with your mysql database.

Let’s go through each one of the query functions one at a time.

Select Function

The main purpose of this function is to select all of the rows for a given criteria and return data as an array of rows.

For example, let’s say you have a table(users) in your database and in that table there are 4 fields (rec_id, user_id, user_email, and user_password).

Table  example:

Get All Rows:

$db->select(“select * from users”)

This will get you all 4 rows in the table

Get Rows With Given Criteria

If you want to get a particular user by user id, you can use the where clause :

//Assuming this was the user you were looking for

//user_id = 'codewithmark'
$user_id = $db->CleanDBData($_POST[‘user_id’]);

$db->select(“select * from users where user_id='$user_id' ”)

This will get you the row 1 with all the columns.

Insert Function

You can easily insert records into your table like:

//Here is the syntax
$insert_arrays = array
(
	'ColumnID1' => "Value1",
	'ColumnID2' => "Value2",
);

//Call it like this:
$db->Insert('TableName', $insert_arrays);

Let’s suppose you want to insert 1 new record into your user table under columns(user_id, user_email, and user_password).

You would do like like this:

//Assuming these are the values you want to insert
$UserID = 'codewithmark';
$UserEmail = 'codewithmark';

//I would highly recommend that you always hash your password before storing it into your database.
$UserPassword =hash('sha512',  '123456');

 
//Here is the syntax
$insert_arrays = array
(
	'user_id' => $db->CleanDBData($UserID) ,
	'user_email' => $db->CleanDBData( $UserEmail ),
	'user_password' => $db->CleanDBData( $UserPassword),
);

//Call it like this:
$db->Insert('TableName', $insert_arrays);

For more Hashing Password Options

Update Function

You can easily update records into your table like:

//Here is the syntax
$strTableName = "TableName";

$array_fields = array(
  'FieldName1' => $db->CleanDBData(FieldValue1),
  'FieldName2' => $db->CleanDBData(FieldValue2),
  'FieldName3' => $db->CleanDBData(FieldValue3),
);
$array_where = array(    
  'rec_id' => 2,
  'rec_dt' => date("Y-m-d"),    
);
//Call it like this:  
$db->Update($strTableName, $array_fields, $array_where);

Actual update example

//Assuming these are the values you want to update
$UserID = 'codewithmark1';
$UserSite = 'codewithmark.com';
$RecID = '2'

//Here is the syntax
$strTableName = "users";

$array_fields = array(
  'userid' => $db->CleanDBData(FieldValue1),
  'sitename' => $db->CleanDBData(FieldValue2), 
);
$array_where = array(    
  'rec_id' => $RecID,  
);
//Call it like this:  
$db->Update($strTableName, $array_fields, $array_where)

Delete Function

You can easily delete records from your table like:

//Here is the syntax

$tabl_name = 'talbe1';

$array_where = array(    
'columnname1' => 'value',
'columnname1' => 'value',
);

//Call it like this:
$Qry = $db->Delete($tabl_name,$array_where);

Example

//Assuming this is the value you want to delete
$site_name =$db->CleanDBData('codewithmark.com'); 
$user_email =$db->CleanDBData('user_email'); 

$tabl_name = 'post';

//Here is the syntax 
$array_where = array(    
'site_name' => $site_name,
'user_email' => $user_email,
); 

//Call it like this:
$Qry = $db->Delete($tabl_name,$array_where);