Posts

Export Column Data with PhpMyAdmin

Sometimes you would want to export your MySQL data, but on a specific number of columns only.

For example, you have a users table that has has more than two columns (firstname, lastname, username, password, etc.) and you only want to export the user's names and usernames.

In short you are exporting only two columns from your users table, and PhpMyAdmin can help us with that.

Here's how to do that:

1. Find you table and to to structure tab. In this example, we have "coan" database and "users" table.

2. Select two columns you want to export and click "Browse" (see red arrow below). We selected the "firstname" and "username".

3. Now you can export the selected columns by clicking "Export" (see red arrow) below the page.

4. Now you can "quick export" it. An SQL file will be downloaded, containing your exported column data.

You can play around with "custom" export if you want, there will be options like entering specific number of rows you want to be exported.

Home PHP

How To Salt, Hash and Store Passwords Securely?

What is password hashing?

It turns a string (of any length) to a fixed length “fingerprint” that cannot be reversed. For example, my password is “i1love2coding3″, when hashed, it can be converted to a 60 character “ytwqwxpbx1oxbfvmpoaafckmat2zkdsjaxs…” which will be stored to the database.

RELATED: PHP Login Script with Session Tutorial – Step by Step Guide!

Why do we have to hash passwords?

I think the main reason why we have to hash passwords is to prevent passwords from being stolen or compromised.

You see, even if someone steal your database, they will never read your actual or cleartext password.

I know that some PHP frameworks or CMS already provide this functionality, but I believe that it is important for us to know how its implementation can be made.

php+hash+password+database+with+a+record

We are going to use a Portable PHP Password Hashing Framework called phpass (pronounced “pH pass”) recommended by a lot of forums and is used by some famous Web applications like phpBB3, WordPress, Drupal, Vanilla, etc.

This post will focus and provide you a quick grasp and basic idea on how to salt, hash and store passwords in a MySQL database. This is essential to your PHP login script.

Let’s Code

Our SQL table structure looks like this:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(32) NOT NULL,
  `password` char(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

libs/PasswordHash.php – our password framework file, yes, it is just this one file. You can download it here.

libs/DbConnect.php – configuration to be connected to database.

register.php – The user registration page, this is where we are going to save the user’s password. On this example web app, we require these two fields only during registration.

<html>
    <head>
        <title>registration page - php salt and hash password - www.codeofaninja.com</title>
        <link type="text/css" rel="stylesheet" href="css/style.css" />
    </head>
<body>

<div id="loginForm">

    <?php
    // save the username and password
    if($_POST){
    
        try{
            // load database connection and password hasher library
            require 'libs/DbConnect.php';
            require 'libs/PasswordHash.php';
            
            /* 
             * -prepare password to be saved
             * -concatinate the salt and entered password 
             */
            $salt = "ipDaloveyBuohgGTZwcodeRJ1avofZ7HbZjzJbanDS8gtoninjaYj48CW" . $_POST['email'];
            $password = $salt . $_POST['password'];
            
            /* 
             * '8' - base-2 logarithm of the iteration count used for password stretching
             * 'false' - do we require the hashes to be portable to older systems (less secure)?
             */
            $hasher = new PasswordHash(8,false);
            $password = $hasher->HashPassword($password);

            // insert command
            $query = "INSERT INTO users SET email = ?, password = ?";

            $stmt = $con->prepare($query);

            $stmt->bindParam(1, $_POST['email']);
            $stmt->bindParam(2, $password);

            // execute the query
            if($stmt->execute()){
                echo "<div>Successful registration.</div>";
            }else{
                echo "<div>Unable to register. <a href='register.php'>Please try again.</a></div>";
            }
            
        }
        
        //to handle error
        catch(PDOException $exception){
            echo "Error: " . $exception->getMessage();
        }
    }
    
    // show the registration form
    else{
    ?>

    <!-- 
        -where the user will enter his email and password
        -required during registration
        -we are using HTML5 'email' type, 'required' keyword for a some validation, and a 'placeholder' for better UI
    -->
    <form action="register.php" method="post">
    
        <div id="formHeader">Registration Form</div>
        
        <div id="formBody">
            <div class="formField">
                <input type="email" name="email" required placeholder="Email" />
            </div>
            
            <div class="formField">
                <input type="password" name="password" required placeholder="Password" />
            </div>
        
            <div>
                <input type="submit" value="Register" class="customButton" />
            </div>
            <div id='userNotes'>
                Already have an account? <a href='login.php'>Login</a>
            </div>
        </div>
        
    </form>
    
    <?php
    }
    ?>
    
</div>

</body>
</html>

login.php – the user login page, we are going to check if the users’s password is valid or not .

<html>
    <head>
        <title>login page - php salt and hash password - www.codeofaninja.com</title>
        <link type="text/css" rel="stylesheet" href="css/style.css" />
    </head>
<body>

<div id="loginForm">

    <?php
    // form is submitted, check if acess will be granted
    if($_POST){
    
        try{
            // load database connection and password hasher library
            require 'libs/DbConnect.php';
            require 'libs/PasswordHash.php';
            
            // prepare query
            $query = "select email, password from users where email = ? limit 0,1";
            $stmt = $con->prepare( $query );
            
            // this will represent the first question mark
            $stmt->bindParam(1, $_POST['email']);
            
            // execute our query
            $stmt->execute();
            
            // count the rows returned
            $num = $stmt->rowCount();
            
            if($num==1){
                
                //store retrieved row to a 'row' variable
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
            
                // hashed password saved in the database
                $storedPassword = $row['password'];
                
                // salt and entered password by the user
                $salt = "ipDaloveyBuohgGTZwcodeRJ1avofZ7HbZjzJbanDS8gtoninjaYj48CW";
                $postedPassword = $_POST['password'];
                $saltedPostedPassword = $salt . $postedPassword;
            
                // instantiate PasswordHash to check if it is a valid password
                $hasher = new PasswordHash(8,false);
                $check = $hasher->CheckPassword($saltedPostedPassword, $storedPassword);
                
                /*
                 * access granted, for the next steps,
                 * you may use my php login script with php sessions tutorial :) 
                 */
                if($check){
                    echo "<div>Access granted.</div>";
                }
                
                // $check variable is false, access denied.
                else{
                    echo "<div>Access denied. <a href='login.php'>Back.</a></div>";
                }
                
            }
            
            // no rows returned, access denied
            else{
                echo "<div>Access denied. <a href='login.php'>Back.</a></div>";
            }
            
        }
        //to handle error
        catch(PDOException $exception){
            echo "Error: " . $exception->getMessage();
        }
    
        
    }
    
    // show the registration form
    else{
    ?>

    <!-- 
        -where the user will enter his email and password
        -required during login
        -we are using HTML5 'email' type, 'required' keyword for a some validation, and a 'placeholder' for better UI
    -->
    <form action="login.php" method="post">
    
        <div id="formHeader">Website Login</div>
        
        <div id="formBody">
            <div class="formField">
                <input type="email" name="email" required placeholder="Email" />
            </div>
            
            <div class="formField">
                <input type="password" name="password" required placeholder="Password" />
            </div>
        
            <div>
                <input type="submit" value="Login" class="customButton" />
            </div>
        </div>
        <div id='userNotes'>
            New here? <a href='register.php'>Register for free</a>
        </div>
    </form>
    
    <?php
    }
    ?>
    
</div>

</body>
</html>

css/style.css – just for some styling.

body{
    font: 20px "Lucida Grande", Tahoma, Verdana, sans-serif;
    color: #404040;
}

input[type=text],
input[type=password],
input[type=email]{
    padding:10px;
    width:100%;
}

#userNotes{
    font-size:0.7em;
    text-align:left;
    padding:10px;
}

#actions{
    padding:10px;
}

#infoMesssage{
    padding:10px;
    background-color:#BDE5F8;
    color:black;
    font-size:0.8em;
}


#successMessage{
    padding:10px;
    background-color:green;
    color:white;
}

#failedMessage{
    padding:10px;
    background-color:red;
    color:white;
    font-size:15px;
}

#formBody{
    padding:5px;
}

#loginForm{
    
    text-align:center;
    border:thin solid #000;
    width:300px;
    margin:7em auto 0 auto;
}

#formHeader{
    border-bottom:thin solid gray;
    padding:10px;
    background:#f3f3f3;
}

#loginForm{
    
}

.customButton {
    padding:5px;
    width:100%;
    -moz-box-shadow:inset 0px 1px 0px 0px #bbdaf7;
    -webkit-box-shadow:inset 0px 1px 0px 0px #bbdaf7;
    box-shadow:inset 0px 1px 0px 0px #bbdaf7;
    background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #79bbff), color-stop(1, #378de5) );
    background:-moz-linear-gradient( center top, #79bbff 5%, #378de5 100% );
    filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#79bbff', endColorstr='#378de5');
    background-color:#79bbff;
    -moz-border-radius:6px;
    -webkit-border-radius:6px;
    border-radius:6px;
    border:1px solid #84bbf3;
    display:inline-block;
    color:#ffffff;
    font-family:arial;
    font-size:15px;
    font-weight:bold;
    text-decoration:none;
    text-shadow:1px 1px 0px #528ecc;
    cursor:pointer;
}

.customButton:hover {
    background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #378de5), color-stop(1, #79bbff) );
    background:-moz-linear-gradient( center top, #378de5 5%, #79bbff 100% );
    filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#378de5', endColorstr='#79bbff');
    background-color:#378de5;
}

.customButton:active {
    position:relative;
    top:1px;
}
/* This imageless css button was generated by CSSButtonGenerator.com */

Demo Screenshots

Empty database.

Empty database.

Registration form.

Registration form.

Just a sample HTML5 validation.

Just a sample HTML5 validation.

After successful registration.

After successful registration.

Our database will have the record.  Notice the password field, it was hashed.

Our database will have the record.
Notice the password field, it was hashed.

Our login page.

Our login page.

Just an example HTML5 validation during login.

Just an example HTML5 validation during login.

Login with wrong credentials.

Login with wrong credentials.

After login with correct username and password.

After login with correct username and password.

Some references

Please note that password hashing is often wrongly referred to as “password encryption”. Hashing is a more appropriate term since encryption is something that is supposed to be easily reversible. ~ phpass

If there’s something you want to add, something wrong, or any questions, please let me know in the comments. Thanks a lot!

You can download all the code used in this tutorial for only $9.99 $5.55!

Thank you for learning from our post about: How To Salt, Hash and Store Passwords Securely?

RELATED: PHP Login Script with Session Tutorial – Step by Step Guide!

Home jQuery

Two Example Usage of jQuery On() Method

How are you guys? Many friends asks me what is the use of jQuery on() method, where and when can we use it and why is it important.

So in this tutorial, I’m going to give you two example situations where we can use the jQuery on() method.

Home>jQuery Two Example Usage of jQuery On() Method

Loading and submitting a from without page refresh.

By definition, jQuery on() method attaches an event handler function (click, submit, etc.) to one or more elements (div, p, etc.). In my experience, I used jQuery on() when:

  • I want to load a new HTML form on a DIV and submit it without page refresh.
  • I want to reload a list of records and use the actions like ‘Delete’.

By the way, you can download all the codes used in this post:

Loading Different Forms to a DIV

This code can load the ‘Add User’ and ‘Add Role’ form to the ‘formContainer’ DIV (one at a time). Our goal is to submit the form without page refresh. To achieve that we are going to use the on() method. See the live demo:

form_w_on.php code:

<html>
    <head>
        <title>jQuery On() Tutorial - submit with on()</title>
    
        <style>
        #addNewUser, #addNewRole{
            cursor:pointer;
            float:left;
            text-decoration:underline;
        }
        
        #formContainer{
            border:thin solid #000;
            padding:0.5em;
            margin:1em 0 0 0;
        }
        
        .clearBoth{
            clear:both;
        }
        </style>
        
    </head>
<body>

<div id='addNewUser'>[+ New User]</div>
<div id='addNewRole'>[+ New Roles]</div>

<div class='clearBoth'></div>

<div id='formContainer'>
    <!--here is where the form will be loaded -->
</div>

<script src='js/jquery-1.9.1.min.js'></script>
<script>
$(document).ready(function(){

    // when user clicks '[+ New User]', it will load the add_user.php file
    $('#addNewUser').click(function(){
        
        $('#formContainer').load('add_user.php', function(){
            console.log('user form loaded!');
        });
        
    });
    
    // when user clicks '[+ New Roles]', it will load the add_role.php file
    $('#addNewRole').click(function(){
        $('#formContainer').load('add_role.php', function(){
            console.log('role form loaded!');
        });
    });
    
    // when the user submits the 'add new user' form
    $(document).on('submit', '#addUserForm', function(){ 
        alert('Add new user form is submitted!');
        return false;
    });

    // when the user submits the 'add new role' form
    $(document).on('submit', '#addRoleForm', function(){ 
        alert('Add new role form is submitted!');
        return false;
    });
    
    
});
</script>

</body>
</html>

add_user.php code:

<form id='addUserForm'>
    <div>Firstname: <input type='text' name='firstname' /></div>
    <div>Lastname: <input type='text' name='lastname' /></div>
    <div><input type='submit' value='Save' /></div>
</form>

add_role.php code:

<form id='addRoleForm'>
    <div>User Role: <input type='text' name='role' /></div>
    <div>Description: <input type='text' name='description' /></div>
    <div><input type='submit' value='Save' /></div>
</form>

Without the on() method, jQuery code usually looks like this (form_w_out_on.php):

<script>
$(document).ready(function(){

    // when user clicks '[+ New User]', it will load the add_user.php file
    $('#addNewUser').click(function(){
        
        $('#formContainer').load('add_user.php', function(){
            console.log('user form loaded!');
        });
        
    });
    
    // when user clicks '[+ New Roles]', it will load the add_role.php file
    $('#addNewRole').click(function(){
        $('#formContainer').load('add_role.php', function(){
            console.log('role form loaded!');
        });
    });
    
    // when the user submits the 'add new user' form
    $('#addUserForm').submit(function() {
        alert('Add user form is submitted!');
        return false;
    });

    // when the user submits the 'add new role' form
    $('#addRoleForm').submit(function() {
        alert('Add new role form is submitted!');
        return false;
    });
    
});
</script>

Here’s a live demo without using the on() method, it reloads the whole page.

Loading Table List to a DIV

jquery-on-tutorial---load-table-list

In this example, when the user clicks on the “[Load List]” text, it will load a table list of data (list.php) with a ‘Delete’ action right across each records.

index.php code:

<html>
    <head>
        <title>jQuery on() tutorial - loading lists</title>
        
        <style>
        #myTable{
            float:left;
            margin:1em 0 0 0;
        }
        
        #myTable th, 
        #myTable td{
            border:thin solid #000;
            padding:1em;
        }
        
        .deleteAction{
            cursor:pointer;
        }
        
        #loadAction{
            cursor:pointer;
            text-decoration:underline;
        }
        </style>
        
    </head>
<body>

<div id='loadAction'>[Load List]</div>

<div id='listContainer'>
    <!--here is where the form will be loaded -->
</div>

<script src='js/jquery-1.9.1.min.js'></script>
<script>
$(document).ready(function(){

    $('#loadAction').click(function(){
        
        $('#listContainer').load('list.php', function(){
            console.log('list loaded!');
        });
        
    });
    
    /*
    // using this code won't pop up 'Are you sure?'
    $('.deleteAction').click(function(){
        if(confirm('Are you sure?'){

             // do things if ok

        }
    });
    */
    
    $(document).on('click', '.deleteAction', function(){ 
        if(confirm('Are you sure?')){

             // do things if ok

        }
    });
    
});
</script>


</body>
</html>

Similarly, as indicated in the code comments, ‘Are you sure?’ alert dialog won’t be shown without the help of the jQuery on() method.

list.php – the data to be loaded in the listContainer div.

<table id='myTable'>
    <tr>
        <th>Name</th>
        <th>Action</th>
    </tr>
    <tr>
        <td>Mike Dalisay</td>
        <td>
            <div class='deleteAction'>Delete</div>
        </td>
    </tr>
    <tr>
        <td>Marykris De Leon</td>
        <td>
            <div class='deleteAction'>Delete</div>
        </td>
    </tr>
</table>

There are other previously used methods almost similar the jQuery on(), like live() and delegate(). But among those methods, jQuery on() is recommended to use (if you’re using jQuery 1.7+). Users of older versions of jQuery should use delegate() in preference to live().

If you want to read more about this topic,here are some links that can help.

Download Source Codes
You can download all the code used in this tutorial for only $9.99 $5.55!

Related Tutorials

Home jQuery

A Quick Way To Create A Vertical Accordion With jQuery

Today we are going to create a vertical accordion from scratch, using jQuery of course!

We will have few lines of code that would be easy to understand.

I used this code when I was asked to create an accordion for a website’s headlines.

A Quick Way To Create A Vertical Accordion With jQuery

So in this example code, we will have:

  • Three headlines, one headline is opened on load by default.
  • When a use clicks on a headline with hidden content, it will slide down the hidden content to be shown and close (slide up) the previously opened headline.

CSS – You may do your tweaks to design your own accordion (change colors, add images, etc.) but make sure the main style are the following.

body {
    font-family:Georgia, serif;
}

.headlineTitle {
    font-weight:bold;
    padding:5px 0;
}

.item {
    border:thin solid #c0c0c0;;
    margin:0 0 0.5em 0;
}

.itemContents {
    display:none;
    margin:.05em 0 0 0;
    padding:10px;
}

.itemTitle {
    background-color:#FAEBD7;
    cursor:pointer;
    font-weight:bold;
    padding:10px;
}

.openedContent {
    display:block;
}

HTML - Our html scructure would look like this. This could be generated multiple times by your server side script like PHP (reading records from a database), but in this example, we are using static HTML.

<div class="itemsContainer">
<div class="item">
    <div class="itemTitle opened">First Headline</div>
    <div class="itemContents openedContent">
        Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras eu elit dui, sed dapibus ante. Donec fermentum, metus et convallis vulputate, justo mauris viverra mauris, ut dictum nisi eros sit amet ante. Suspendisse velit erat, iaculis a feugiat sed, pretium eu magna. Praesent pharetra nisi eu odio bibendum dapibus. Nullam sollicitudin auctor vulputate. Fusce ultrices molestie justo et feugiat. Aenean elit tortor, scelerisque quis ultricies in, pharetra quis quam.
    </div>
</div>
<div class="item">
    <div class="itemTitle">Second Headline</div>
    <div class="itemContents">
        Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras eu elit dui, sed dapibus ante. Donec fermentum, metus et convallis vulputate, justo mauris viverra mauris, ut dictum nisi eros sit amet ante. Suspendisse velit erat, iaculis a feugiat sed, pretium eu magna. Praesent pharetra nisi eu odio bibendum dapibus. Nullam sollicitudin auctor vulputate. Fusce ultrices molestie justo et feugiat. Aenean elit tortor, scelerisque quis ultricies in, pharetra quis quam.
    </div>
</div>

jQuery – makes our accordion work with few lines of codes.

$('.itemTitle').click(function () {

    // show the content only if it is hidden        
    if ($(this).closest('.item').find('.itemContents').is(":hidden")) {
    
        //open the content          
        $(this).closest('.item').find('.itemContents').slideDown("fast");
        
        //close previously opened content           
        $(this).closest('.itemsContainer').find('.opened').closest('.item').find('.itemContents').slideUp("fast");
        
        // remove the "opened" class from previously opened content         
        $(this).closest('.itemsContainer').find('.opened').removeClass('opened');
        
        //add class to mark the clicked item is opened          
        $(this).addClass("opened");
    }
});

Download Source Code
You can download all the code used in this tutorial for only $9.99 $5.55!

Related Tutorials

Home jQuery

Dynamic and Dependent Drop Down List Using AJAX

This tutorial will teach you how to make Dynamic and Dependent Drop Down List using AJAX.

We are using PHP and MySQL as well.

Example #1

1.0 Create index.php

Create index.php with the following Basic HTML Code

<!DOCTYPE html>
<html>
<head>
    <title>Dynamic Dropdown Example</title>
</head>
<body>

</body>
</html>

2.0 Retrieve 'Teams' from database

Put the following code between the 'body' tags of the previous section.

<?php 
// retrieve teams from the database 
// include database and object files
include_once 'config/database.php';
include_once 'objects/team.php';

// instantiate database and product object
$database = new Database();
$db = $database->getConnection();

$team = new Team($db);

$stmt = $team->read();
$num = $stmt->rowCount();

if($num>0){
	echo "<select id='teams-dropdown'>";
	echo "<option value='0'>Select team...</option>";
	while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
		extract($row);
		echo "<option value='{$id}'>{$name}</option>";
	}
	echo "</select>";
}
?>

3.0 Create database.php

Create database.php file. The previous section will not work without the database connection. Put the following code.

<?php
class Database{

	// specify your own database credentials
	private $host = "localhost";
	private $db_name = "nba";
	private $username = "root";
	private $password = "";
	public $conn;

	// get the database connection
	public function getConnection(){

		$this->conn = null;

		try{
			$this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
		}catch(PDOException $exception){
			echo "Connection error: " . $exception->getMessage();
		}

		return $this->conn;
	}
}
?>

4.0 Create team.php

Create 'objects' folder and inside it, create team.php file. The section before the previous section will not work without this. Put the following code inside it.

<?php
class Team{

	// database connection and table name
	private $conn;
	private $table_name = "teams";

	// object properties
	public $id;
	public $name;

	public function __construct($db){
		$this->conn = $db;
	}
	
	// read products
	public function read(){

		$query = "SELECT id, name
				FROM " . $this->table_name . "
				ORDER BY name";

		// prepare query statement
		$stmt = $this->conn->prepare($query);

		// execute query
		$stmt->execute();

		return $stmt;
	}
	
}
?>

5.0 Create empty select box

Open index.php and put the following code under the code in section 2.0 above.

<!-- create empty select box -->
<select id='players-dropdown'>
	<option value='0'>Select player...</option>
</select>

6.0 Prepare jQuery script

Include the jQuery library and its initial script. Put the following code under the code of the previous section.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.0.0/jquery.min.js"></script>
<script>
$(document).ready(function(){

});
</script>

7.0 Detect change of 'teams' dropdown

We have to detect when the user selects a team. Put the following code under "$(document).ready(function(){" of the previous section.

// detect change of dropdown
$("#teams-dropdown").change(function(){
	
	// get id of selected team 
	var team_id=$(this).find(':selected').val();
	
});

8.0 Get JSON data

Now, we will have to get the list of players based on selected team. Put the following code under "var team_id" line of code of the previous section.

// set json url
var json_url="players_json.php?team_id=" + team_id;

// get json data
jQuery.getJSON(json_url, function(data){
	
});

9.0 Create players_json.php

Create players_json.php file. This file will render the list of players based on selected team. We are going to use the 'JSON' data format because JavaScript can read this format easily. Put the following code inside this file.

<?php 
// set json headers
header("Access-Control-Allow-Methods: GET");
header('Content-Type: application/json');

// retrieve players from the database 
// get team id parameter
$team_id=isset($_GET['team_id']) ? $_GET['team_id'] : die('Team ID not found.');

// include database and object files
include_once 'config/database.php';
include_once 'objects/player.php';

// instantiate database and product object
$database = new Database();
$db = $database->getConnection();

$player = new Player($db);

$player->team_id=$team_id;
$stmt = $player->read();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($results);
?>

10.0 Create player.php file

Go inside the 'objects' folder and create the player.php file. This file will retrieve players data from the database. Put the following code inside it.

<?php
class Player{

	// database connection and table name
	private $conn;
	private $table_name = "players";

	// object properties
	public $id;
	public $team_id;
	public $name;

	public function __construct($db){
		$this->conn = $db;
	}
	
	// read products
	public function read(){

		$query = "SELECT id, team_id, name 
				FROM " . $this->table_name . " 
				WHERE team_id=:team_id
				ORDER BY name";

		// prepare query statement
		$stmt = $this->conn->prepare($query);

		// santize
		$this->team_id=htmlspecialchars(strip_tags($this->team_id));
		
		// bind value 
		$stmt->bindParam(":team_id", $this->team_id);
		
		// execute query
		$stmt->execute();

		return $stmt;
	}
	
}
?>

11.0 Change players dropdown

Now we will try to change the content of 'players' drop down. Open your index.php file and put the following code after "jQuery.getJSON(json_url, function(data){" line of index.php file.

// empty contents of players dropdown
$("#players-dropdown").html("");
$("#players-dropdown").append("<option value='0'>Select player...</option>");

// put new dropdown values to players dropdown
jQuery.each(data, function(key, val){
	$("#players-dropdown").append("<option value='" + val.id + "'>" + val.name + "</option>")
});

Example #2

I used this code when I have a small list of records (e.g. authors or categories) that can be picked using a drop-down list and then, I want to load the related information instantly without refreshing the page.

So in this post, we are going to code that does:

  • A drop down list with small number of names or authors
  • When a user selected an item from the drop down, it will load the related data below it.

We are going to use 4 files only, see below:

1.0 jQuery CDN

Prepare our favorite JavaScript library.

2.0 db_connect.php

We will use this file for database connection.

<?php
$host = "YOUR_DATABASE_HOST";
$db_name = "YOUR_DATABASE_NAME";
$username = "YOUR_DATABASE_USERNAME";
$password = "YOUR_DATABASE_PASSWORD";

try{
	$con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}

// to handle connection error
catch(PDOException $exception){
	echo "Connection error: " . $exception->getMessage();
}
?>

Here's the database table we used. Run the following SQL query on your PhpMyAdmin.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `firstname`, `lastname`, `username`, `password`) VALUES
(1, 'John', 'Doe', 'johnny', 'john'),
(2, 'Albert', 'Minston', 'albert', 'albert');

3.0 index.php

This will show the users drop-down list, records are from the database users table.

<!DOCTYPE html>
<html lang="en">
	<head>
		<title>Example #2 - Dynamic and Dependent Drop Down List Using AJAX</title>
		<style>
		body{
			font-family:arial,sans-serif;
		}

		select{
			margin:0 0 10px 0;
			padding:10px;
		}

		td {
			background-color:#e8edff;
			padding: 10px;
		}
		</style>
	</head>
<body>

<?php
// connect to database
include_once "db_connect.php";

// retrieve list of users and put it in the select box
$query = "SELECT id, firstname, lastname, username FROM users";
$stmt = $con->prepare($query);
$stmt->execute();

//this is how to get number of rows returned
$num = $stmt->rowCount();

// make sure there are records on the database
if($num > 0){

// this will create selec box / dropdown list with user records
echo "<select id='users'>";

	// make a default selection
	echo "<option value='0'>Select a user...</option>";

	while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
		extract($row);
		echo "<option value='{$id}'>{$firstname} {$lastname}</option>";
	}
echo "</select>";

}

// if no user records
else{
	echo "<div>No records found</div>";
}

// this is where the related info will be loaded
echo "<div id='userInfo'></div>";
?>

<script src="js/jquery-1.9.1.min.js" ></script>
<script>
$(document).ready(function(){
	$("#users").change(function(){

		// get the selected user's id
		var id = $(this).find(":selected").val();

		// load it in the userInfo div above
		$('#userInfo').load('data.php?id=' + id);

	});
});
</script>

</body>
</html>

4.0 data.php

This contains the query and will show the table with information related to the selected drop-down item.

<?php
include 'db_connect.php';

try {

	// prepare query
	$query = "SELECT firstname, lastname, username
			FROM users
			WHERE id = ?";

	$stmt = $con->prepare( $query );

	// this is the first question mark above
	$stmt->bindParam(1, $_REQUEST['id']);

	// execute our query
	$stmt->execute();

	// store retrieved row to a variable
	$row = $stmt->fetch(PDO::FETCH_ASSOC);

	// values to fill up our table
	$firstname = $row['firstname'];
	$lastname = $row['lastname'];
	$username = $row['username'];

	// our table
	echo "<table>";
		echo "<tr>";
			echo "<td>Firstname: </td>";
			echo "<td>{$firstname}</td>";
		echo "</tr>";
		echo "<tr>";
			echo "<td>Lastname: </td>";
			echo "<td>{$lastname}</td>";
		echo "</tr>";
		echo "<tr>";
			echo "<td>Username: </td>";
			echo "<td>{$username}</td>";
		echo "</tr>";
	echo "</table>";

}catch(PDOException $exception){

	// to handle error
	echo "Error: " . $exception->getMessage();
}
?>

Download Source Code

You can download all the code used in this tutorial for only $9.99 $5.55!

Thank you for learning from our post about: Dynamic and Dependent Drop Down Menu with PHP, MySQL and jQuery!

How To Get Facebook App ID and Secret Keys?

After creating a Facebook page where we will pull our data, we have to create an app to get an appId and appSecret keys. Those keys are required to use the Facebook PHP SDK and do our queries like pulling Facebook photos, events, videos, and feeds. Here’s a step by step tutorial on how to get those keys.

How To Get Facebook App ID and Secret Keys?

Facebook developer apps dashboard.

Step 1

Here’s the link where you would start: create a Facebook app. On the pop up, specify your app name (the name of your website or app) and app namespace (used for your Facebook app URL e.g., http://apps.facebook.com/yournamespace)

Creating an app.

Creating an app.

Step 2

You’ll be asked to enter a security code to verify that you’re a human and not a bot or malware that creates random Facebook apps. After entering the text in the box, click the “Continue” button.

A security check.

A security check.

Step 3

Congrats! now you can copy your Facebook appId and appSecret. See the arrows below.

Your Facebook appId and appSecret keys.

Your Facebook appId and appSecret keys.

Step 4

Prepare your App ID and App Secret keys, we’ll use it to get your access token, here’s how:

https://graph.facebook.com/oauth/access_token?client_id=YOUR_APP_ID&client_secret=YOUR_APP_SECRET&grant_type=client_credentials

Replace YOUR_APP_ID and YOUR_APP_SECRET with your own and run the link on the browser.

Home Facebook

How Do You Start A Facebook Page?

Hi guys! Most of the top posts here in my blog is related to web development with Facebook. Many asks beforehand how to create a Facebook page where we will pull our data. So here’s a step by step tutorial on how to do such task.

How Do You Start A Facebook Page?

Our final output.

Photos are kinda small, you have to click each photos below to enlarge.

Step 1

Go to Facebook “Create A Page” section that can be found in this link: Create a Page. You should see this page:

Create a Facebook page.

Create a Facebook page.

Step 2

In this example, we are going to create a “Brand or Product” for our website. Click “Brand or Product” box and fill up the required filled.
On the “Category” dropdown, select “Website”.
On the “Brand or Product Name” field, I’ll enter “Mike Dalisay Works” for example.
Check “I agree to Facebook Pages Terms” checkbox.
It should look like this:

Creating a brand or product Facebook page.

Creating a brand or product Facebook page.

Step 3

Click the “Get Started” button. It will make you set up some information for your Facebook page such as profile picture, about, Facebook web address, and enable ads option. Just follow the flow.

Facebook page info set up.

Facebook page info set up.

Facebook page profile picture.

Facebook page profile picture.

Facebook page “About” info

Facebook page “About” info

Facebook web address.

Facebook web address.

Enable ads. You can click the skip button.

Enable ads. You can click the skip button.

Step 4

At this stage, you already have successfully created your Facebook page! Congrats! Now Facebook will give you some guidelines on how to use your Facebook page.

Like your own page.

Like your own page.

Invite others to like your page.

Invite others to like your page.

Do your first post!

Do your first post!