MySQL Database Handling in PHP


Posted On Jun 10 2018 by

Most interactive websites nowadays require data to be presented dynamically and interactively based on input from the consumer. As an instance, a customer might need to log into a retail website to check his purchasing history. In this example, the web site would have stored two kinds of information to allow the customer to perform the test — the client's personal login details; along with the client's bought items. This information could be stored in two types of storage — flat files or databases.

Flat files are only feasible in very low to low volume websites as flat files have 3 inherent weaknesses:

The inability to index the information. This makes it necessary to potentially read ALL the data sequentially. This is a significant issue when there are a great deal of documents from the flat file because the time necessary to read the flat file is proportionate to the amount of documents in the file.

The inability to efficiently control access by users to the information

The inefficient storage of the information. Typically, the data Wouldn't be encrypted or compressed as this would exacerbate the Issue no. 1 over

The solution which is, in my estimation, the only feasible method, is to save the information in a database. Among the most prevalent databases in use is MySQL. Data which is stored in a database can easily be indexed, managed and stored efficiently. Apart from that, most databases also provide a suite of accompanying utilities that allow the database administrator to maintain the database — as an example, backup and restore, etc..

Websites scripted using PHP are very well suited to the MySQL database as PHP has a custom and integrated MySQL module that communicates very efficiently with MySQL. PHP can also communicate with MySQL through the standard ODBC as MySQL is ODBC-compliant, but this won't be as effective as using the custom MySQL module for PHP.

The rest of this Report is a tutorial on How Best to use PHP to:

Connect to a MySQL database

Execute standard SQL statements against the MySQL database

Starting a Session with MySQL

Before the PHP script can communicate with the database to query, insert or update the database, the PHP script will first have to connect to the MySQL server and specify which database in the MySQL server to function on.

The mysql_connect() and also mysql_select_db() functions are provided for this use. To be able to connect to this MySQL server, the host name/address; a username; along with a valid password is necessary. After a connection is successful, the database has to be specified.

The following 2 code excerpts illustrate how to perform the server connection and database selection:

@mysql_connect("servername", "Accounts", "Document") or die("can't connect to DB! ");

@mysql_select_db("databasename") or die("Cannot select DB! ");

The @ operator is used to suppress any error messages which mysql_connect() and mysql_select_db() functions may produce if an error happened. The die() function can be used to terminate the script execution and display a custom error message.

Executing SQL Statements against a MySQL database

Once the connection and database selection is successfully performed, the PHP script can now proceed to run on the database using standard SQL statements. The mysql_query() function is used for executing standard SQL statements against the database. In another example, the PHP script queries a table named tbl_login from the previously selected database to find out whether a username/password set offered by the user is legitimate.

Assumption:

The tbl_login table has 3 columns named login, password, last_logged_in. The last_logged_in column stores the time that the user logged to the computer system.

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

// through the POST method. For the purpose of this instance, we're manually coding it.

$username = "john";

$passwd = "mypassword";

// We generate a SELECT SQL statement for execution.

$sql="SELECT * FROM tbl_login WHERE login = '". $username. "' AND password '". $passwd. "'";

// Execute the SQL statement against the currently selected database.

// The results will be kept in the $r variable.

$r = mysql_query($sql);

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

// determine of this mysql_query() was successfully implemented.

if(! $r)

$err=mysql_error();

print $err;

exit(); Visit https://alkanyx.com/ if you're interested in php scripts for login and registration.

// If everything went well, check if the query returned an effect — i.e. when the username/password

// pair was found in the database. The mysql_affected_rows() function is used for this function.

// mysql_affected_rows() will return the Amount of rows from the database table that was changed

// from the last query

if(mysql_affected_rows()==0)

Publish "Username/password set is invalid. Please try again. ";

else

// If successful, read out the last logged in time into a $last variable for display to the consumer

$String=mysql_fetch_array($r);

Publish "Login successful. You last logged in at ". $last. ". ";

The above example demonstrated how a SELECT SQL statement is executed against the selected database. The identical approach can be used to execute other SQL statements (e.g. UPDATE, INSERT, DELETE, etc.) against the database with the mysql_query() and mysql_affected_rows() functions.

Last Updated on: June 10th, 2018 at 11:01 pm, by


Written by Katherine K. Vaca


Leave a Reply

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