Data Integrated Newsletter

Happy new year everyone! This is like my last blog post for year 2011, I wanted to make it a special one but the environment is already noisy and I’ve lost my motivation to even write something that’s why I’m just going to announce the official newsletter for this blog. It’s a newsletter about javascript, php, jquery and everything about web design and development. Its basically just your typical newsletter where you will find links to cool stuff around the web. Here’s an example of what you will be expecting to see:

image 

If you want to subscribe just visit the link below and submit your email.

Data Integrated Newsletter 

If you don’t want to submit your email then you can just check out the archives.

Data Integrated Newsletter Archives

I guess that’s all I have to say, once again happy new year!

Web Development and Programming Resources

 

VB.Net

Java

PHP

jQuery

Web Development


Podcasts


Microsoft

Newsletter

Video Tutorials

Pages

How to setup for a free website hosting

In this tutorial I’m going to show you how to setup a website on a free hosting site. Setting up a website on a free hosting site is useful if you want to let other people test the website that you have made without actually having to pay a single cent. But remember that free isn’t really the best solution if you want to tweak something on the configuration files like you usually do in your localhost. Because you can’t actually do stuff like edit your php.ini file or apache .htaccess file on free hosting sites.

 

Create an account

First you have to create an account at xtreemhost.

image

Next, select the free account. Don’t be fooled by the BIG sign up button, click the link below it instead.

image

Then create your co.cc domain. Use the link to go the the co.cc site.

image

Once you got an available domain. Click on the checkbox and input the domain name that you have created.

Next, fill out the fields. Make sure that the email address that you entered is valid and that you have access to it.

image

After the registration, an email will be sent to you which contains your website address, and vista panel login information such as the username and the password.

 

Account Settings

Next, go to cpanel.xtreemhost.com and then login using your username and password.

Once you’re logged in. Click on the Account Settings, which can be found under the help and tutorials section. This is where you will see the mysql username, password and host that you will be using in your database configuration file. This is very important if the site that you are trying to host uses a database.

image

 

Phpmyadmin

Next is phpmyadmin where you can create and manage the database that is used by your site. Note that xtreemhost puts your username as the prefix for each of the database that you create, be sure to use the database name that is generated by xtreemhost in your database configuration file. The database configuration file that you’re going to upload on xtreemhost might look something like this:

$db = new Mysqli("sql304.xtreemhost.com", "xth_9920566", "111AAA", "xth_8888888_newsletter");

And this is what it looks like on your local machine:

$db = new Mysqli("localhost", "root", "1111", "newsletter");

It’s different that’s why you have to be careful not to upload the localhost version of your database configuration file if you want things to work out the way you expect it.

 

File Management

The most important thing that you have to learn is how to upload files to a free hosting site. You can do that by going to the online file manager under the File Management menu. Here is what the file manager looks like looks like:

image

Most of the time you don’t really have to care about the files in the root folder since you will be working inside of the htdocs folder. The htdocs is where you are going to upload all the files. Initially there’s only the index.html file along with the file that tells you that htdocs is where you should upload your file. Here is what mine looks like:

image

There are three ways in which you can upload your files: Java Upload, Flash Upload, and regular upload. Most of the time the regular upload will work just fine but if you want a fancier way of uploading your files you can use the java or the flash upload.

Here is what the regular upload looks like:

image

I recommend using the archive upload since you only have to upload a single zip file and it will automatically be unzipped for you. For this trick to work you must first zip your whole website in the machine that you’re working on, remember to edit your database configuration file before zipping.

 

Accessing your website

The address where you can access your website is included in the email sent by xtreemhost just go check it out if you’re not sure of the correct address.

 

Conclusion

That’s it for this tutorial! You’ve learned how to setup files on a free hosting site. Hosting your site for free has its own limitations like the lack of configurability, limited disk space, and it doesn’t actually let you have your own domain because your website address always ends with the .xtreemhost.com.

How to use prepared statements with mysqli

Prepared statements is basically a way to avoid sql injection on your script. If you want to learn more about prepared statements here’s a link to a Wikipedia article and stackoverflow question on best practices on using prepared statements.

To summarize the contents of the two links above it basically says that you need to use prepared statements in order to prevent sql injection attacks and if you need to insert, modify, or remove lots of data from you database. Prepared statements are basically faster than your average query since they are compiled before execution.

 

Methods

Here are a few methods that we need to use when using prepared statements.

  • prepare() – takes up a query as an argument. You need to mask the values that you want to supply to the query using the question mark symbol(?).
  • bind_param() used to bind parameters to a prepared statement. You specify how many arguments it would take by using a string which has the same length as the values that you want to pass in. For example if you want to supply a user id, password, department and role you need to input a string with a length which is equal to 4, and then the following arguments would be the actual values that you want to use on your query.
  • execute() as the name suggests this is used to execute the prepared statement along with the arguments that has been bind to it.
  • bind_result() – use this method to bind the results of a prepared statement into their respective variables.
  • fetch() – use this method to fetch the results that has been returned by the prepared statement. It doesn’t take up any arguments, just call it immediately after binding the results to their specific variables.

 

Inserting records

Next, let’s try to insert records. On line’s 1 to 4 we assigned the data that we want to insert to each of their variables. Next we try to check if the query is a valid query using an if statement. Note that we are using question marks(?) as a mask for the actual values that we want to insert in the query. And then we call up the bind_param() method. As you can see, we used “ssss” as our first argument this specifies that we are submitting for string arguments to the prepared statement. The next arguments are the actual values that you want to pass in. Make sure that they are of the same order with the fields that you used in your query. This means that if you have first set the value of UserID in your query then you also use it as first value that you bind. Just examine the example below if you’re confused with my messy explanation. Lastly, you execute it using the execute() method.

$user_id = "lily's"; $hashed_pw = md5("1234"); $department = 1; $role = "ROL3"; if($query = $db->prepare("INSERT INTO sys_users

SET UserID=?, strPassword=?, intDepartment=?, intRole=?")){

$query->bind_param("ssss", $user_id, $hashed_pw,

$department, $role); $query->execute(); }

 

Updating records

Updating records is almost the same with the inserting of records the only difference is the query that you are performing. Always remember that when calling bind_param, you use the actual values in the order that they appear on the query. In the case of an update query, the field that you use in the where clause will always be the last argument that you supply on the bind_param() method.

$user_id = 'yohasakura'; $hashed_pw = md5('9877'); $role_id = 'ROL9'; $dept_id = 3; if($query = $db->prepare("UPDATE sys_users SET strPassword=?, intRole=?, intDepartment=? WHERE UserID=?")){ $query->bind_param('ssss', $hashed_pw, $role_id,

$dept_id, $user_id); $query->execute(); }

 

Deleting records

Same story, different query. In a delete query often times we only need to supply one argument to the query that’s why I use just “s” as the first argument in the bind_param() method. And the second is the user id, the field the you used in your where clause.

$user_id = 'yohasakura';
if($query = $db->prepare("DELETE FROM sys_users WHERE UserID=?")){
	$query->bind_param("s", $user_id);
	$query->execute();
}

 

Fetching a single record

Same story, just used two additional methods namely bind_result() and fetch(). Note that when using bind_result() the order of the fields that you have selected in your query doesn’t actually matter, it is how you output the values which gives the meaning to it.

$user_id = 'tcollector'; if($query = $db->prepare("SELECT UserID, intRole, intDepartment FROM sys_users WHERE UserID=?")){ $query->bind_param('s', $user_id); $query->execute(); $query->bind_result($userID, $department, $role); $query->fetch(); printf("User: %s <br/> Role:%s <br/> Department: %s<br/>",

$userID, $role, $department); }

Also note that the %s is being substituted with the 2nd to the last arguments that you specify in the print_f() function, this is where the order of how you output things matters. But if you don’t want to take care of the order, its perfectly fine to do it this way

$user_id = 'tcollector';
if($query = $db->prepare("SELECT UserID, intRole, intDepartment 
FROM sys_users WHERE UserID=?")){

	$query->bind_param('s', $user_id);
	$query->execute();
	$query->bind_result($userID, $department, $role);
	$query->fetch();
	echo 'User: '. $userID.'<br/>';
	echo 'Role: '. $role.'<br/>';
	echo 'Department: '. $department.'<br/>';
}

 

Fetching multiple records

Same story, just added a while loop to loop through all the records that has been returned. This basically says that while a result is still being returned you need to output something. And yes, you can use the variables that was bind to output things out. Just make sure that when binding, you follow the same order indicated in your query. If you use SELECT * you might want to follow the order that is in your table, for example if UserID is the first field from the left then you also supply it as the first argument when using bind_result().

$department_id = 2;
if($query = $db->prepare("SELECT UserID, intRole, intDepartment 
FROM sys_users WHERE intDepartment=?")){

	$query->bind_param('s', $department_id);
	$query->execute();
	$query->bind_result($user, $role, $department);
	
	while($query->fetch()){
		echo $user.'<br/>';
	}
}

 

Conclusion

Okay I guess that’s it for this short introduction on how to use prepared statements in mysqli. Prepared statements is a great way to prevent sql injection on your queries, you won’t actually need to sanitize strings that you supply on your queries if you use prepared statements and you don’t have to worry about sql injection.

How to use mysqli in php

Mysqli short for MySQL improved is an extension used for talking to a mysql database. If you want to dig in to mysqli you can use the online book at php.net as a reference. Might be a little intimidating for beginners but nevertheless contains all the information you need to know about mysqli.

This article serves as an introduction to mysqli. I’m going try my best to make this a lot more easier to learn. Whether you have already dealt with mysql database before or just a total beginner in connecting to databases.

 

Requirements

 

Connecting using mysqli

Make sure that you have the same or higher version of php, mysql, and apache as mine before proceeding.

First, you need to create a new mysqli object which takes up 4 arguments. The host, user, password and the database that you’re trying to connect with.  Also check for connection errors, then immediately terminate the current script if there are errors. This is to make sure that your program won’t consume any more resources while trying to execute the other script below it.

//host, user, password, database

$db = new Mysqli("localhost", "root", "", "orgbpls");

 

//check for connection errors

if ($db->connect_errno) {
    die('Connect Error: ' . $db->connect_errno);
}

 

Insert records

First, the very basic inserting new rows on a table. Just use the object that we created earlier to call up the query() function in this case our mysqli object is called $db.
The query() function takes up an sql query as its argument. It returns 1 if the query that you supplied executed successfully and 0 if its not.

$create_user = $db->query("INSERT INTO sys_users

SET UserID='yayquery'");

 

Update records

Nothing new here, we just supplied an update query as the argument of the query() function. What were doing here is that were changing whatever the current password of the user with the user id of yayquery into the hashed version of 1234.

$hashed_pw = md5('1234');

$update_user = $db->query("UPDATE sys_user 
SET strPassword='$hashed_pw' WHERE UserID='yayquery'");

 

Delete

This might probably getting boring now. But you also use the query() function to delete rows.

$delete_user = $db->query("DELETE FROM sys_users 
WHERE UserID='yayquery'");

 

Fetch a single record

When fetching records from the database, you need to make use of the fetch_object() together with the query() function. First you perform a select query, assign it to a variable in this case the variable is called $user.
And then use that variable to call the fetch_object() function. Assign the result to another variable, in this case the variable $u. Finally use it to get the value of the fields that you specified in your query, in this case were using it to output the user id.

$user = $db->query("SELECT UserID, intDepartment, intRole 
FROM sys_users WHERE UserID='tcollector'");

$u = $user->fetch_object();
echo $u->UserID;

 

Fetch multiple records

The idea is the same with fetching a single record all you have to do is to loop through the results using while loop.

$results = $db->query("SELECT * FROM sys_users");

while($row = $results->fetch_object()){
	echo $row->UserID.'<br/>';
}

 

Conclusion

That’s it for this tutorial, you’ve learned the basics of using mysqli in php. You’ve learned how to perform basic database operations.

How to use ezSQL

I guess its only appropriate for me to write an introductory article about ezsql since I’m using it on most of the articles that I create.
Just so you know, there’s already an ezsql documentation and an article written by Jean Baptiste Jung on Catswhocode:

Yeah, its already been written before so why the heck I’m still writing a how-to article on it. I might not have said this before but the main reason I’m writing a blog is for me to better understand the things that I have learned. Because I believe that you never really know something until you write or talk about it.

 

Requirements

 

Installation

First you need to download wampserver and ezsql library. I’ve already included the links in the requirements section.

Next, setup your test folder. Then create your database configuration file so that you won’t be including the ezsql files on every page that you create. The database configuration file will include the ezsql files, so the only thing that you will need to include on your pages is your database configuration file. This is what your database configuration file should look like.

<?php
include('ez_sql/shared/ez_sql_core.php');
include('ez_sql/mysql/ez_sql_mysql.php');

date_default_timezone_set('Asia/Manila'); //timezone

$db = new ezSQL_mysql('root', '', 'orgbpls', 'localhost'); //user, password, database name, host
?>

As you can see there are two files associated with the ezsql library. One is the ez sql core, and the other is the ezsql mysql. The ezsql core as the name suggests contains the core functionalities of ezsql. The ezsql mysql file contains the functionalities needed to query the mysql database. Database specific components as they might call it.

After creating the database configuration file, all you have to do now is to include it on your pages.

 

Create new records

To create new record on a table we call up the query function which takes up a single argument, the query that we need to perform in this case an insert query.

$user = 'bamboo'; $password = '1234'; $hashed_password = md5($password); $create_user = $db->query("INSERT INTO sys_users

SET UserID='$user', strPassword='$hashed_password'");

 

Updating records

We call up the same query() function to update existing records on our table. But this time we need to supply it with an update query. The code below will change the password of the user bamboo from the hashed version of 1234 to the hashed version of 6789.

$user = 'bamboo'; $password = '6789'; $hashed_password = md5($password); $update_user = $db->query("UPDATE sys_users

SET strPassword='$hashed_password' WHERE UserID='$user'");

 

Deleting records

Again, we use the query() function to delete records from the tables. The only thing that’s changing is the query that we supply. This time use a delete query.

$user = 'bamboo';
$delete_user = $db->query("DELETE FROM sys_users
WHERE UserID='$user'");

 

Note: the query() function returns 1 if the query is successfully executed on the database, and 0 if its not. Which means that you can actually use the value that it returns to determine if you have made a change into the database or not.

 

Selecting a variable

To select a single value from a field in a table row we use the get_var() function. The code below returns the department to which the user belongs. Make sure that you only select a single field in your table since only the first field specified in the query will be the one which will be returned if you select multiple fields.

$user = 'tcollector'; $user_department = $db->get_var("SELECT intDepartment

FROM sys_users WHERE UserID='$user'"); echo $user_department;

 

Selecting a row

To select a single row we use the get_row() function.  This allows us to select multiple fields in a single table row. To get the values in the specific rows you have to assign the results to a variable, in this case the variable is called $user. That is where the results are being stored whenever the query that you specified returned something. All you have to do now is to access the different field values by using their fieldname. In this case the fieldnames are UserID, intDepartment and intRole.

$user_id = 'tcollector';
$user = $db->get_row("SELECT intDepartment, UserID, intRole 
FROM sys_users WHERE UserID='$user_id'");

echo $user->UserID.'<br/>';
echo $user->intDepartment.'<br/>';
echo $user->intRole.'<br/>';

 

Selecting multiple rows

To select multiple rows we use the get_results() function. This returns an array so we need to use a loop in order to get the values that we want. The idea is the same with selecting a single row, the only difference is that you need to use a loop through the result that is being returned.

$users = $db->get_results("SELECT * FROM sys_users");
if(!empty($users)){
?>
<table>
	<thead>
		<tr>
		<th>UserID</th>
		<th>Department</th>
		</tr>
	</thead>
	<tbody>
	<?php foreach($users as $k=>$v){ ?>
		<tr>
		<td><?php echo $v->UserID; ?></td>
		<td><?php echo $v->intDepartment; ?></td>
		</tr>
	<?php } ?>	
	</tbody>
</table>
<?php } ?>

If for some reason you want to know how many records has been returned you can use the num_rows() function. All you have to do is to call it right after you perform a select query. The code below will output the number of records returned in the last query that you have performed.

$users = $db->get_results("SELECT * FROM sys_users");
$number_of_records = $db->num_rows;
echo $number_of_records;

 

Escaping illegal characters

You can also escape illegal characters from a string using ezsql’s escape() function. This function just adds up backslashes to any illegal characters that it finds on the string that you supply. Its basically the same with the addslash() function in php.

$super_password = $db->escape("di$ i$ super's password%z");
echo $super_password;

 

Debugging

There is also a function which you can use to debug your queries if they aren’t behaving in a way that you expected. To use it just call the debug() function, it doesn’t need any arguments as it uses the last query that you have performed, just make sure to call it right after performing a query.

$user = 'tcollector'; $user_department = $db->get_var("SELECT intDepartment

FROM sys_users WHERE UserID='$user'");

$db->debug();

Just to show you an example of how useful it is. It returns the actual result of the query that you have performed complete with all the fields that you have selected along with their respective data type. Cool right?

image

 

Conclusion

I guess that’s all there is to it. You’ve learned the basics of ezsql. How to perform queries and select records from the database easily.

How to submit javascript arrays to php

In this tutorial I’m going to show you a little example on how to submit javascript arrays to a php script.

 

Requirements

  • jQuery

 

HTML

First we have to write the html where we are going to input the items that we will put in the arrays.

<table>
<thead>
	<tr>
		<th>Name</th>
		<th>Language</th>
	</tr>
</thead>
<tbody>
	<tr>
		<td><input type="text" class="name_class" name="name[]"/></td>
		<td><input type="text" class="lang_class" name="lang[]"/></td>
	</tr>
	<tr>
		<td><input type="text" class="name_class" name="name[]"/></td>
		<td><input type="text" class="lang_class" name="lang[]"/></td>
	</tr>
	<tr>
		<td><input type="text" class="name_class" name="name[]"/></td>
		<td><input type="text" class="lang_class" name="lang[]"/></td>
	</tr>
</tbody>
</table>

Then the submit button.

<p>
<input type="button" id="btn_submit" value="submit"/>
</p>

Lastly, the <div> where we are going to place the result later on.

<div id="result"></div>

 

Javascript

Next, write the 2 function that will loop through all the contents of the textboxes allotted for the names and the textboxes for the languages. To make things easier, use the jQuery .each() function. The .each() is a function which iterates through  certain elements. You can make use of this to loop through all the contents of the element that you specify. In this case we are looping through the contents of the name_class and lang_class.

function name_loop(){
	var names_array = [];
	$('.name_class').each(function(index){
		names_array[index] = $.trim($(this).val());
	});
	return names_array;
}
	
	
function lang_loop(){
	var language_array = [];
	$('.lang_class').each(function(index){
		language_array[index] = $.trim($(this).val());
	});
	return language_array;
}

Note that you can only specify a single element on a .each() function. That’s the reason why we built 2 functions which basically does the same thing.

Next we need to specify what will happen when the user clicks on the submit button. Were just going to call the 2 functions that we wrote earlier then assign each of them to their own variables. And then we used the jQuery .load() function to call the php script where we are going to pass the arrays and at the same time loads the output from the php script back to the element that we specified, in this case the div with the id of result.

$('#btn_submit').click(function(){
	var names = name_loop(); //stores the array of names
	var languages = lang_loop();  //stores the array of languages
		
	$('#result').load('page.php', {'names' : names, 'languages' : languages}); //submit to php script
	$('input[type=text]').val(''); //empty all textbox
});

Lastly, create another file called page.php. This is the script where you are submitting the javascript arrays. And since we have passed in arrays, we’ll need a loop to actually iterate through all the contents of the array. For this purpose foreach loop is perfect. It’s the same with the jQuery.each() function. But this time were not iterating through element contents, were iterating through the contents of the array.

<table border="1">
<thead>
	<tr>
		<th>Name</th>
		<th>Language</th>
	</tr>
</thead>
<tbody>
	<?php foreach($_POST['names'] as $k=>$v){ ?>
	<tr>
		<td><?php echo $v; ?></td>
		<td><?php echo $_POST['languages'][$k]; ?></td>
	</tr>
	<?php } ?>
</tbody>
</table>

 

Conclusion

That’s it! You’ve learned how to pass javascript arrays into php using AJAX. You also learned how to use the jQuery’s .each() and .load() functions.