In this tutorial, we would observe how to connect with mysql database using PHP.
Database is an integral part of eCommerce websites. If take a sample space of software applications from any freelancing website, more than eighty percent would comprise database in some way. When we install, the xampp the mysql server is automatically installed. Please note that mysql work bench or heidisql are not the mysql server, rather these are just front end interfaces. The mysql server is installed in the background and runs as a service. You can use command line to execute query. Just like, heidisql and workbench etc, Tomcat with PHP also provides an interface. On localhost it can be accessed in following way.
But if the mysql sever is not installed on your local machine, you would replace the localhost with the IP of the mysql server machine.
The method to create the mysql server has been demonstrated in above video. Here after creating the video, we create a simple form, submit the form to a PHP page and on php page, we put the form data into database. Below is the code for the the page where a form was developed.
<form action="form_post_mysql_action.php" method="post">
Enter your name<br/>
<input type="text" name="n"/><br/>
Enter your email<br/>
<input type="email" name="e"/><br/>
<input type="submit" value="sign up"/>
after the form is submitted we are landed at form_post_mysql_action.php with following code
$n = $_POST["n"];
$e = $_POST["e"];
$con = new mysqli("localhost","root","","wad");
$q = "insert into mytable (name, email) value('" .$n . "','" . $e . "')";
if($con->query($q) == TRUE)
echo "values inserted";
Line 5: $n is assigned value got from form name field.
Line 5: $e is assigned value got from form email field.
Line 8: $con object is created using PHP built in class mysqli. The class constructor takes following arguments.
- The address of machine where mysql is installed.
- The user name of mysql, by default it is “root”
- The password and by default it is empty.
- The name of the database. in this case, it is
Line 10: the variable $q is assigned a string that is actually a SQL query. The single quotes around variables in the insert query conform to the fact that the string values are enclosed by single quotes.
Line 12 : $con->query($q) method returns Boolean for non-select queries. So in this case, it would return true if query successfully executed and false otherwise.
Line 15: $con->error would contain the error message if $con->query($q) reurns false i.e. the SQL query fails.
Line 17: the $con->close(); would close the the sql connection. It is very economical to close the connection as it slows the the data base operations and if too many connections are kept open. Also, the hostin companies have in general an upper limit of allowed number of SQL connections.