Using modal windows to insert records in a mysql database

Today I’m going to teach to you how to use modal windows. If the word modal windows doesn’t ring a bell. Then here’s my explanation: Modal windows are pop-up windows which can be used to provide information to the user, like error or success messages. It can also be used as a prompt, which means that the user cannot proceed with the next process unless he interacts with the modal window Lastly, it can be used to perform database operations. Let’s go ahead and get started.

Requirements

• Wampserver
• Jquery
• Jquery UI
• EZ Sql

 

Prepare
First you need to prepare the directory that you will be working on. Extract the zip files from the jquery and jquery ui websites, if you haven’t already extracted them. Then edit the jquery ui css file. Search for .png files. Make sure that they are correctly linked to the images folder for jquery ui. There are 2 ways to do this, first you move the whole directory that is specified in the link. Second, change the part of the link which doesn’t refer to the filename (Ex. jqui/images/). Just paste that under the find. And under replace with, type the address where the images folder is located. Finally, just click on replace all.

On every file that we will be dealing with. We need to link the jquery core, jquery ui, and the jquery ui css. You can either paste those links on a php file and just include them. But if you are too lazy like me, then just copy the codes below. Make sure that you edit them to match the location, and the filename.

<link rel="stylesheet" href="jq_ui.css" type="text/css"/>
<script type="text/javascript" src="jq.js"></script>
<script type="text/javascript" src="JQUI.js"></script>

 

Modal window stylesheet

Next, we place the stylesheet for the modal window.

<style>
		body { font-size: 62.5%; }
		label, input { display:block; }
		input.text { margin-bottom:12px; width:95%; padding: .4em; }
		fieldset { padding:0; border:0; margin-top:25px; }
		h1 { font-size: 1.2em; margin: .6em 0; }
		div#users-contain { width: 350px; margin: 20px 0; }
		div#users-contain table { margin: 1em 0; border-collapse: collapse; width: 100%; }
		div#users-contain table td, div#users-contain table th { border: 1px solid #eee; padding: .6em 10px; text-align: left; }
		.ui-dialog .ui-state-error { padding: .3em; }
		.validateTips { border: 1px solid transparent; padding: 0.3em; }
</style>

 

Modal window form

Next, we build the form that we will use to insert records.

<div id="dialog-form" title="Create new user">
	<p class="validateTips">All form fields are required.</p>

	<form>
	<fieldset>
		
		<label for="name">Name</label>
		<input type="text" name="name" id="name" class="text ui-widget-content ui-corner-all"  value=""/>
		<label for="email">Email</label>
		<input type="text" name="email" id="email" class="text ui-widget-content ui-corner-all"  value=""/>
		<label for="password">Password</label>
		<input type="password" name="password" id="password"  class="text ui-widget-content ui-corner-all"  value=""/>
	</fieldset>
	</form>
</div>

First thing that you might have notice in the code for the form is that it doesn’t have a name, action , and method attributes. It’s just plain form, with no attributes since we will be using ajax to talk to the database.

Table

Next, we create a table which will serve as a visual. So that the user will see that the record has indeed been added.

<div id="users-contain" class="ui-widget">
	<h1>Existing Users:</h1>
	<table id="users" class="ui-widget ui-widget-content">
		<thead>
			<tr class="ui-widget-header ">
				<th>Name</th>
				<th>Email</th>
				
			</tr>
		</thead>
		<tbody>
		<!--  New records will be appended here-->	
		</tbody>
	</table>
</div>
<button id="create-user">Create new user</button>

 

Field values

Then we create the javascript that will control the modal window. Things like validation, manipulating database through ajax happens here. Note that the preceding codes will go inside the the document.ready function.

var name = $( "#name" ),
email = $( "#email" ),
password = $( "#password" ),
allFields = $( [] ).add( name ).add( email ).add( password ),
tips = $( ".validateTips" );

 

Check string length

Then here’s a function that will check the length of the string which is inputted by the user. It takes up 4 arguments:

• value – the variables that we declared above are the values (Ex. name, email, password)
• name – just a simple description for the value (Ex. username)
• min – minimum value or string length. (Ex. 5)
• max – maximum value or string length. (Ex. 20)

function checkLength( o, n, min, max ) {
	if ( o.val().length > max || o.val().length < min ) {
		o.addClass( "ui-state-error" );
		updateTips( "Length of " + n + " must be between " +
			min + " and " + max + "." );
		return false;
	                } else {
		
                                return true;
			}
		}

 

Regular expressions

There’s also a function which uses regular expressions to validate if the inputted string matches the specified pattern.

function checkRegexp( o, regexp, n ) {
	if ( !( regexp.test( o.val() ) ) ) {
		o.addClass( "ui-state-error" );
		updateTips( n );
			return false;
		} else {
			return true;
			}
		}

 

Modal window

Next, we call up the modal window.

$( "#dialog-form" ).dialog({


});

 

Modal window properties

Properties for the modal window. You can edit the values for width and height if it doesn’t fit with the form that you wish to place.

autoOpen: false,
height: 300,
width: 350,
modal: true,

Another property, is the buttons. You can add up as many buttons as you want. But don’t forget to specify what it will do.

buttons: {

}

To create a button, just specify its name enclose it in double quotes. Followed by a colon. Then the usual function declaration.

"Create an account": function() {

},

Inside the create an account button. We call up the checkLength() function using the values for name, email and password as arguments. This simply means that if the user will click on the create an account button. bValid will be equal to false.

var bValid = true;
allFields.removeClass( "ui-state-error" );

bValid = bValid && checkLength( name, "username", 3, 16 );
bValid = bValid && checkLength( email, "email", 6, 80 );
bValid = bValid && checkLength( password, "password", 5, 16 );

After that, we also call up the function for regular expression. As I have said earlier, this will check if the inputted string matches the specified pattern.

	bValid = bValid && checkRegexp( name, /^[a-z]([0-9a-z_])+$/i, "Username may consist of a-z, 0-9, underscores, begin with a letter." );
					
	bValid = bValid && checkRegexp( email, /^((([a-z]|d|[!#$%&'*+-/=?^_`{|}~]|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF])+(.([a-z]|d|[!#$%&'*+-/=?^_`{|}~]|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF])+)*)|((x22)((((x20|x09)*(x0dx0a))?(x20|x09)+)?(([x01-x08x0bx0cx0e-x1fx7f]|x21|[x23-x5b]|[x5d-x7e]|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF])|(\([x01-x09x0bx0cx0d-x7f]|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF]))))*(((x20|x09)*(x0dx0a))?(x20|x09)+)?(x22)))@((([a-z]|d|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF])|(([a-z]|d|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF])([a-z]|d|-|.|_|~|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF])*([a-z]|d|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF]))).)+(([a-z]|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF])|(([a-z]|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF])([a-z]|d|-|.|_|~|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF])*([a-z]|[u00A0-uD7FFuF900-uFDCFuFDF0-uFFEF]))).?$/i, "eg. ui@jquery.com" );
					bValid = bValid && checkRegexp( password, /^([0-9a-zA-Z])+$/, "Password field only allow : a-z 0-9" );

When the value of bValid is true. We append the username and email inputted by the user into the table that we created earlier. We also used the ajax() function to insert those valid data into the mysql database. We  will create the crud.php file later, so don’t try the script yet because it won’t work.

if ( bValid ) {
					
$( "#users tbody" ).append( "<tr>" +
"<td>" + name.val() + "</td>" + 
"<td>" + email.val() + "</td>" + 
"</tr>" ); 
						
$.ajax({
type: "POST",
url: "crud.php?act=create",
data: "pangalan=" + name.val() + "&email=" + email.val() + "&password=" + password.val(),
success: function(data){
							
}
 });
$( this ).dialog( "close" );
}

Next, we now close the create new account button by adding a comma. Then we create another button called cancel. Which has only one purpose in life, to close the modal window.

Cancel: function() {
      $( this ).dialog( "close" );
}

Next, we close the buttons, by adding a comma. After that, create a close() function. This will clear all the fields whenever you close the modal window. This is to make sure that the next user who will register will see a blank form.

close: function() {
	allFields.val( "" ).removeClass( "ui-state-error" );
}

Finally we close the the dialog() function. Remember that the modal window will not show up immediately after the page has loaded, we will need to call it in order for it to show up. And here’s the code for it.

$( "#create-user" ).click(function() {
	$( "#dialog-form" ).dialog( "open" );
	});

We now close the document.ready() function.

Database

If you want to use the same table. Then you can execute the script below, in either phpmyadmin or mysql console.

CREATE TABLE IF NOT EXISTS `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `email` varchar(30) NOT NULL,
  `password` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

 
Crud.php

Next, let’s create a new file called crud.php. This is where the ajax() function will dump all the data that it has fetched from the modal window. The script is pretty simple. First we included the ez sql files. Then we get the value of act from the url. If you scroll up and check the ajax() function above. You will see that the value of act is create. We then check if the values needed by the insert query is not empty. Then we declared an object of the ezsql class and named it to $db. Finally, I created a switch statement for more flexibility. So that I can add actions for edit, list, search and delete later. All in the same file.

<?php
						
include('shared/ez_sql_core.php');
include('mysql/ez_sql_mysql.php');
$action = $_GET['act'];
						
if(!empty($_POST['pangalan'])&&($_POST['email'])&&($_POST['password'])){
	$name = $_POST['pangalan'];
	$email = $_POST['email'];
	$pword = md5($_POST['password']);
	}
	$db = new ezSQL_mysql("root", "", "modal_db", "localhost");
						
						
	switch($action){
						
	case 'create':
	        $db->query("INSERT INTO user_info SET name='$name', email='$email', password='$pword'");
	break;
                }


							

Conclusion and Life story
We learned the basics of using modal window to insert a record in the mysql database, edit the properties of modal window. And use the checklength()  and  regularexpression()  and ajax() functions.

I’m really running out of time now. I still have something to do. That’s why I’m cutting the story short. Maybe I’ll try to make  a tutorial for updating records from a modal window next time. For the mean time, you can go ahead and download the files that we used here. Yeah, I really saved that link for those reading from top to bottom. Thanks, see you next time whoever you are.