MySQL Database Dealing with in PHP

Posted in WordPress on Jul 18, 2014

Most interactive sites presently need knowledge to be offered dynamically and interactively based mostly on input from the person. For example, a client could will need to log into a retail web-site to examine his purchasing record. In this instance, the website would have stored two varieties of information in buy for the shopper to perform the check – the customer’s own login information and the customer’s ordered merchandise. This data can be stored in two sorts of storage – flat data files or databases.

Flat information are only feasible in very small to minimal quantity websites as flat data files have 3 inherent weaknesses:

The incapacity to index the facts. This would make it vital to most likely study ALL the data sequentially. This is a important challenge if there are a good deal of data in the flat file mainly because the time essential to read through the flat file is proportionate to the range of records in the flat file.

The lack of ability to successfully manage entry by customers to the data

The inefficient storage of the data. In most instances, the data would not be encrypted or compressed as this would exacerbate the problem no. one above

The alternative which is, in my view, the only feasible process, is to store the information in a databases. A person of the most widespread databases in use is MySQL. Data that is saved in a database can very easily be indexed, managed and stored efficiently. Other than that, most databases also deliver a suite of accompanying utilities that make it possible for the database administrator to preserve the database – for instance, backup and restore, etcetera.

Sites scripted making use of PHP are pretty perfectly suited for the MySQL databases as PHP has a tailor made and built-in MySQL module that communicates quite efficiently with MySQL. PHP can also connect with MySQL through the standard ODBC as MySQL is ODBC-compliant, However, this will not be as effective as using the tailor made MySQL module for PHP.

The relaxation of this article is a tutorial on how to use PHP to:

Hook up to a MySQL database

Execute common SQL statements versus the MySQL database

Commencing a Session with MySQL

In advance of the PHP script can communicate with the databases to question, insert or update the database, the PHP script will to start with will need to connect to the MySQL server and specify which database in the MySQL server to run on.

The mysql_join() and mysql_pick out_db() features are offered for this objective. In get to link to the MySQL server, the server title/handle a username and a legitimate password is essential. At the time a link is productive, the databases requirements to be specified.

The pursuing two code excerpts illustrate how to perform the server relationship and databases collection:

@mysql_join(“[servername]”, “[username]”, “[password]”) or die(“Can not hook up to DB!”)

@mysql_choose_db(“[databasename]”) or die(“Can’t pick DB!”)

The @ operator is utilized to suppress any mistake messages that mysql_connect() and mysql_choose_db() functions may perhaps make if an mistake transpired. The die() function is made use of to stop the script execution and exhibit a custom made error message.

Executing SQL Statements versus a MySQL database

When the link and databases assortment is efficiently executed, the PHP script can now move forward to function on the database using common SQL statements. The mysql_question() operate is made use of for executing standard SQL statements from the database. In the subsequent illustration, the PHP script queries a desk termed tbl_login in the formerly picked databases to ascertain if a username/password pair offered by the consumer is valid.

Assumption:

The tbl_login desk has 3 columns named login, password, previous_logged_in. The final_logged_in column suppliers the time that the user past logged into the program.

// The $username and $passwd variable should rightly be set by the login sort

// by means of the Publish technique. For the purpose of this case in point, we’re manually coding it.

$username = “john”

$passwd = “mypassword”

// We generate a Decide on SQL statement for execution.

$sql=”Pick * FROM tbl_login Exactly where login = ‘”.$username.”‘ AND password = ‘”.$passwd.”‘”

// Execute the SQL assertion towards the at the moment picked database.

// The outcomes will be stored in the $r variable.

$r = mysql_question($sql)

// Following the mysql_question() command executes, the $r variable is examined to

// identify of the mysql_question() was effectively executed.

if(!$r)

$err=mysql_mistake()

print $err

exit()

// If everything went well, check if the query returned a outcome – i.e. if the username/password

// pair was observed in the databases. The mysql_impacted_rows() function is utilised for this objective.

// mysql_affected_rows() will return the range of rows in the databases table that was affected

// by the last query

if(mysql_influenced_rows()==)

print “Username/password pair is invalid. You should try yet again.”

else

// If thriving, study out the final logged in time into a $final variable for show to the user

$row=mysql_fetch_array($r)

$past=$row[“previous_logged_in”]

print “Login thriving. You last logged in at “.$previous.”.”

The above example shown how a Find SQL assertion is executed towards the picked database. The very same method is made use of to execute other SQL statements (e.g. UPDATE, INSERT, DELETE, and so forth.) in opposition to the database employing the mysql_question() and mysql_afflicted_rows() functions.

By John L

Leave a Reply

Your email address will not be published. Required fields are marked *