Tag: mysql

Dynamic Suggested List: HTML5 + jQuery + PHP + MySQL

Today lets build a dynamic suggested list input field using HTML5, jQuery, PHP and MySQL.

Dynamic-suggested-list-html5-jquery-php-mysql

We have 55 company names in our database table – and the import file(.sql) can be downloaded at the end of this tutorial page. By using jQuery we pass user input to suggest.php file and the result is embedded into index.html file.

Related read: Suggested Entries Using datalist Element: HTML5

HTML file
index.html

<!DOCTYPE HTML>
<html>
<head>
<title>Dynamic Auto-Suggestion using datalist element: HTML5</title>
<meta charset="utf-8"/>
<script src="jQuery.js"></script>
<script src="myScript.js"></script>
</head>
<body>
 
<form>
<input type="text" list="myCompanies" name="company" id="suggest" />
<datalist id="myCompanies">
 
</datalist>
</form>
 
</body>
</html>

Here we have included jQuery library file before our script myScript.js
We also have an input field of type text, with an id = suggest, name = company and list = myCompanies. And a datalist element is linked/associated with input field, by matching it’s id with the name of input field’s list attribute. The value for option tag will be filled by jQuery, using the results output by suggest.php file.

PHP file
suggest.php

< ?php

    $db         = mysqli_connect('localhost', 'root', '', 'search');
    
    $company    = $_GET['company'];
    
    $sql        = "SELECT name FROM table1 WHERE name like '$company%' ORDER BY name";
    
    $res        = $db->query($sql);
    
    if(!$res)
        echo mysqli_error($db);
    else
        while( $row = $res->fetch_object() )
            echo "<option value="".$row->name."">";
        
?>

Here we connect php file to database(search). Get the keyword entered by the user in the input field and form a sql query. By using query() method, we process it and finally format it the way we need it i.e., we wrap the company names inside option tag’s value attribute.

jQuery file
myScript.js

$(document).ready(function(){
    $("#suggest").keyup(function(){
        $.get("suggest.php", {company: $(this).val()}, function(data){
            $("datalist").empty();
            $("datalist").html(data);
        });
    });
});

For every keyup event on the input field we trigger jQuery method $.get() and pass the user input keys to suggest.php file and the result is collected back by the callback function and the result set is stored in a variable called data.

Now we first make sure to clear out previous data present inside datalist element i.e., we clear the previous results by removing any option tags present inside datalist element. Now we fill the datalist element with the new data/result output from suggest.php file.

Since we’re using jQuery here, page does not reload and the suggested entries looks real and spontaneous.

Here is the list of company names I’ve taken for this tutorial

3M
7-Eleven
Accenture
Acer
Adidas
Adobe systems
Amazon.com
AMD
AOL
Apache
Apple inc
Appolo group
Aricent
Ask.com
Asus
AT&T
Bank of America
BBC
BE Aerospace
BMC Software
Boeing
Bosch Brewing Company
Boston Acoustic
CA Technologies
Citrix Systems
Cognizant Technolog
Cognizant Technology Solutions
Convergys
Dell
Delphi
DHL
Divx Inc
eBay
EMC Corporation
Exelon
Facebook
Ford Motor Company
Fox Entertainment Group
GCI
GoDaddy
Goodrich Corporation
Google
Hawaiian Airlines
Hewlett-Packard
Honeywell
IBM
Intel
Johnson & Johnson
KFC
McDonalds
Microsoft
Motorola
Nvidia
Red Hat
Yahoo!

Video Tutorials: Dynamic Suggested List: HTML5 + jQuery + PHP + MySQL


[youtube https://www.youtube.com/watch?v=YqMtE8UO-xw]

YouTube Link: https://www.youtube.com/watch?v=YqMtE8UO-xw [Watch the Video In Full Screen.]


Note: For any older browsers which do not support datalist element or list attribute, it will simply fall back to text type input field and it doesn’t produce any error.

Fetch/Extract Data From Database Without Refreshing Webpage: jQuery

Video tutorial illustrating, Fetching / Extraction of Data From Database Without Refreshing Webpage: jQuery + PHP + JSON + MySQL

You need not worry, even if you don’t know any one of these technology. Follow the tutorial, practice and understand, so that you could implement it for your applications.

Also read: Insert Data Into Database Without Refreshing Webpage.

In this tutorial we’ll be showing you, how to extract the data present in our MySQL database table without refreshing the webpage.

HTML code
index.html

1
2
3
4
5
6
7
8
9
10

<html>
<head>
<title>Fetch/Extract Data From Database: jQuery + JSON + PHP+ AJAX</title>
</head>
<body>
<ul></ul>
<script type="text/javascript" src="script/jquery-1.8.2.min.js"></script>
<script type="text/javascript" src="script/my_script.js"></script>
</body>
</html>

Here we have a unordered list. We’ll fill it’s list items by extracting the data from the database, using jQuery and PHP.

PHP File: Database Connection Code
db.php

1
2
3
4

< ?php
      $conn = mysql_connect('localhost', 'root', '');
  $db   = mysql_select_db('company');
?>

Here we’re connecting to the database called company.

Also Read: Connecting To MySQL server From PHP

PHP File: Extracting / Fetching Data From Database table and JSON Encoding
fetch.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14

< ?php
        include_once('db.php');
 
$sql = "SELECT * FROM people";
$res = mysql_query($sql);
$result = array();
 
while( $row = mysql_fetch_array($res) )
    array_push($result, array('name' => $row[0],
                          'age'  => $row[1],
  'company' => $row[2]));
 
echo json_encode(array("result" => $result));
?>

Here we connect to the database by including db.php file.
Next, select all the fields from the people table.
Extract data one-by-one and store it inside the array variable $result, with key => value pair (associative array).
Finally, using PHP standard function json_encode(), we encode the fetched data.

jQuery File: Accessing The Data And Displaying
my_script.js

1
2
3
4
5
6
7
8
9
10
11

function updates() {
 $.getJSON("fetch.php", function(data) {
       $("ul").empty();
   $.each(data.result, function(){
    $("ul").append("<li>Name: "+this['name']+"</li>
                            <li>Age: "+this['age']+"</li>
                            <li>Company: "+this['company']+"</li>
                            <br />");
   });
 });
}

Using the jQuery AJAX method’s shortcut, $.getJSON() method, we parse fetch.php and process the data passed to the callback function.

We empty the unordered list to avoid duplicate entries later on.

Now loop through all the objects present in the jSON output of fetch.php
Using it’s key, retrieve it’s respective values and append it to the unordered list, as it’s list items.

jQuery File: AJAXing
my_script.js

1
2
3
4
5
6

function done() {
  setTimeout( function() { 
  updates(); 
  done();
  }, 200);
}

Call updates() function and itself[ done() ] inside setTimeout() function with a time gap of 200 milliseconds.

jQuery File: Full/Final Code
my_script.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

$(document).ready( function() {
 done();
});
 
function done() {
  setTimeout( function() { 
  updates(); 
  done();
  }, 200);
}
 
function updates() {
 $.getJSON("fetch.php", function(data) {
       $("ul").empty();
   $.each(data.result, function(){
    $("ul").append("<li>Name: "+this['name']+"</li>
                            <li>Age: "+this['age']+"</li>
                            <li>Company: "+this['company']+"</li>
                            <br />");
   });
 });
}

Call done() once the document is loaded.
You could also call done() function once the window is focused or once some button is clicked or any other user events. This would reduce the database calls considerably.

Fetching / Extraction of Data From Database Without Refreshing Webpage: jQuery


[youtube https://www.youtube.com/watch?v=G_7oYeNU_3g]

YouTube Link: https://www.youtube.com/watch?v=G_7oYeNU_3g [Watch the Video In Full Screen.]



This would highly enhance the user experience and would help in building rich web applications which look more like desktop / standalone applications.

Please share this video with your friends and also LIKE it on social network sites and on YouTube. Thanks you <3

Related Read:
Insert Data Into Database Without Refreshing Webpage

Insert Data Into MySQL: jQuery + AJAX + PHP

Video tutorial illustrates insertion of data into MySQL database using jQuery and PHP, using AJAX method i.e., $.post() method of jQuery.

There are 5 shortcuts for AJAX in jQuery, ultimately calling ajax method, and by default configured to have different parameters.
$.get
$.post
$.getJSON
$.getScript
$.load

Tutorial Theme: Insert Data Into Database Without Refreshing Webpage

In this tutorial we’ll illustrate $.post method of jQuery.

HTML code
index.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<html>
<head><title>Insert Data Into MySQL: jQuery + AJAX + PHP</title></head>
<body>
 
<form id="myForm" action="userInfo.php" method="post">
Name: <input type="text" name="name" /><br />
Age : <input type="text" name="age" /><br />
<button id="sub">Save</button>
</form>
 
<span id="result"></span>
 
<script src="script/jquery-1.8.1.min.js" type="text/javascript"></script>
<script src="script/my_script.js" type="text/javascript"></script>
</body>
</html>

Here we have a form with an id called myForm, which has 2 input box.
A button with id sub. And a span tag with an id of result.

Once the user enters his/her name, age and hits Save button, the information will be sent to userInfo.php file present in the action field, via post method.

Using AJAX technique, we would make sure the form doesn’t redirect to userInfo.php file after the submission, and we’ll display appropriate message to the user on index.html file itself after the user entered values has been processed by userInfo.php file.

Database Connection code: PHP
db.php

1
2
3
4

< ?php
  $conn = mysql_connect('localhost', 'root', '');
  $db   = mysql_select_db('test');
?>

We connect our application to mysql server using the user name root( and we leave password field empty, as we haven’t set any password to it on our machine ).
Also select the database called test.

Create Database called test.
A table user inside the database test.
user table has 2 fields called name and age.

Processing Page code: PHP
userInfo.php

1
2
3
4
5
6
7
8
9
10
11

< ?php
 include_once('db.php');
 
$name = $_POST['name'];
$age = $_POST['age'];
 
if(mysql_query("INSERT INTO user VALUES('$name', '$age')"))
  echo "Successfully Inserted";
else
  echo "Insertion Failed";
?>

Once the user hits the Save button of our form in index.html, the information is being sent to userInfo.php

Where the user entered values are being copied to local variables $name and $age.
Next a simple MySQL query is formed to insert the data into the table user.

Using mysql_query() standard php function, the MySQL query is being processed.
Using if else conditional statements, we check if the process was successful or failed. Based on which we echo the result.

jQuery code: AJAXing
my_script.js

1
2
3
4
5
6

$("#sub").click( function() {
 $.post( $("#myForm").attr("action"), 
         $("#myForm :input").serializeArray(), 
         function(info){ $("#result").html(info); 
  });
});

Once the user clicks on the button with the id sub, $.post() method is invoked.
$.post() is a shortcut to $.ajax() method.

General Syntax:
$.post( ‘pass_data_to_this_url’, ‘data’, ‘callback_function’ );

We fetch the URL from myForm form, from its action attribute.
Serialize all the user entered input’s.
SerializeArray makes the input into property: value pair.
After the data is passed to the url and is being processed, the result will be returned back to the callback function and is caught in info variable, and is inserted inside the span tag using html() method – which is similar to innerHTML() method of JavaScript.

jQuery code: Disable Form Redirection
my_script.js

1
2
3

$("#myForm").submit( function() {
  return false;
});

Select the form and make sure to return false upon submission.

jQuery code: Clear Input Fields
my_script.js

1
2
3
4
5

function clearInput() {
$("#myForm :input").each( function() {
   $(this).val('');
});
}

input-fields-form

Write a custom function. Select the input fields and set each of its value to none or empty.
Call this function once the user clicks the submit button.

Full jQuery code
my_script.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

$("#sub").click( function() {
 $.post( $("#myForm").attr("action"), 
         $("#myForm :input").serializeArray(), 
         function(info){ $("#result").html(info); 
   });
 clearInput();
});
 
$("#myForm").submit( function() {
  return false;
});
 
function clearInput() {
$("#myForm :input").each( function() {
   $(this).val('');
});
}

Note that, we have called clearInput() function inside the click event of #sub.

Video Tutorial: Insert Data Into MySQL: jQuery + AJAX + PHP


[youtube https://www.youtube.com/watch?v=gvGb5Z0yMFY]

YouTube Link: https://www.youtube.com/watch?v=gvGb5Z0yMFY [Watch the Video In Full Screen.]



Note:
You have to implement addslash and stripslash or any other methods into your PHP code, to secure your script from SQL Injection.

If the PHP returns json encoded data, then we need to use json in $.post() method as follows:

1
2
3
4
5

$("#sub").click( function() {
 $.post( $("#myForm").attr("action"), 
         $("#myForm :input").serializeArray(), 
         function(info){ $("#result").html(info); },
         "json" );

But in our example, we are not returning json encoded data from userInfo.php file, so we do not specify encoding information in $.post() method.

Build VOTE / LIKE, UNLIKE Feature: PHP & MySQL

In Voting system we need to make sure, one person votes only once, to a particular poll. Or One person can only like a particular image once.
Person who votes must be allowed to vote down and the person who likes must be allowed to unlike the same image / item etc.

Looks complicated? Relax, its not that complicated!

Let me explain the logic and direct you to some videos..
First, build your User Signup and Login forms.
Make username as primary key.

Now create another table with two fields. id(primary key and auto_increment) and image.

Now another table with two fields again: username and id
Make username and id as primary keys..that is, composite primary key.

In database design, a composite key is a key that consists of 2 or more attributes that uniquely identify an entity occurrence. Each attribute that makes up the compound key is a simple key in its own right.

This way, same user can not like / vote for the same image twice! It would throw error, as it violets composite key rule. You could make use of Die() and show appropriate message or still better use if else and if the query has not executed(due to violation of composite key rule) show links in the else part to vote down or unlike. There you could simply delete the entry from vote table.

Video Tutorial: Build VOTE / LIKE, UNLIKE Feature: PHP & MySQL


[youtube https://www.youtube.com/watch?v=XZ6hBLNayr0]

YouTube Link: https://www.youtube.com/watch?v=XZ6hBLNayr0 [Watch the Video In Full Screen.]



Take 3 tables: user, image and vote
username is primary key in user table.
id is primary key in image table. attributes: id and image
id and username are composite primary key in vote table.

Vote / Like, Unlike

1

mysql> INSERT INTO vote VALUES( ‘2’, ’apple’ );

Count Vote / Like

1

mysql> SELECT count(*) FROM vote WHERE id= ‘2’;

Vote down / Unlike

1

mysql> DELETE FROM vote WHERE( id=‘2’ AND username=’apple’ );

Make sure to take the values of id and username dynamically. Am showing 2 and apple for the sake of illustration.
username will be present inside the session variable once the user logs in.
id value will be associated with the like or vote link/button of the image. [id is unique to each individual image]

Taking advantage of the error thrown by the composite primary key. i.e., using if else we check if the user has already voted or liked particular image or participated in a particular poll, if yes, we show him/her unlike or vote down link/button.

Vote, Vote Down or Like, Unlike Feature

1
2
3
4

If( mysql_query(“INSERT INTO vote VALUES( ‘2’, ’apple’ )”)  )
  echo “Thanks for the vote”;
else
  echo “<a href=‘vote.php?down=1’>Vote Down</a>”;

You could mix this simple logic with some CSS and other stuffs like AJAX and implement the voting system easily in a short period of time, like a pro!

Simple / Basic Pagination / Navigation: PHP & MySQL

The Best and The most Simple Pagination / Navigation using PHP and MySQL

To know more about our simple Create, read, update and delete application, follow these short videos and notes:
First look at these short videos:
Connect to the database
Simple / basic insertion operation
Insertion of Records into Database using Forms: PHP & MySQL (important)
SELECT / LIST Records From Database Table: PHP & MySQL
UPDATE / EDIT Records In Database Table: PHP & MySQL
Delete / Remove Records In Database Table: PHP & MySQL

These are the entries in our table apple:
1Google USA
2Apple USA
3Microsoft USA
4Oracle USA
10IBM
11HP
12Symantec
13Adobe
14Cisco
15McAfee
16Red Hat
17Sun Microsystems
18Intel
19Salesforce
20Facebook
21Technotip
22MindTree
23Tate Consultancy Ser
24Cognizant
25Citigroup
26Maestro
27Visa
28KingFisher
29HDFC
30ICICI
31SBI
32SBM
33Twitter
34LinkedIn
35BlueDart
36VRL
37Zappos
38FlipKart
39Amazon
40Yahoo!
41ebay
42PayPal

We would split these entries into 5 segments each and display 5 records at a time.

In this video tutorial we’re showing the latest entry first. If you want to show first entry first, then take another database field called date and insert the date and time of the insertion operation. Now using the WHERE clause and LIMIT fetch the data from database and display as you wish.

WHERE clause and LIMIT, to fetch depending on date and time
mysql query

1

mysql> SELECT * from apple [ WHERE some_condition ] ORDER BY date ASC LIMIT $start, 5;
1

mysql> SELECT * from apple [ WHERE some_condition ] ORDER BY date DESC LIMIT $start, 5;

In our tutorial, we have taken id as primary key and as AUTO_INCREMENT field.

1
2
3
4
5
6
7
8
9
10
11

$rs = mysql_query("SELECT count(*) FROM apple");
$rw = mysql_fetch_array($rs);
 
if( !(isset($_GET['page'])) )
    $start = $rw[0] - 5;
else
    $start = $rw[0] - ($_GET['page'] * 5);
 
 
$res = mysql_query("SELECT * FROM apple LIMIT $start, 5") 
                              or Die("More entries coming, stay tuned!");

First we need to calculate the number of records present inside the table. Next decide onto how many items you want to display.
Based on this, subtract the actual number of records with number of items you actually want to display.

If use explicitly passes $_GET[‘page’] i.e., by clicking on the navigation or pagination numbers, then multiply the number with the number of items you want to display and subtract it with the actual number of records present inside the table.

The final step is to, pass the $start and the number till which you want to fetch from $start.

In our case, we want to show 5 items from the table apple, so we give the limit: starting from $start to 5 records after $start.

Video Tutorial: Simple / Basic Pagination / Navigation: PHP & MySQL


[youtube https://www.youtube.com/watch?v=06o4C32B6Bg]

YouTube Link: https://www.youtube.com/watch?v=06o4C32B6Bg [Watch the Video In Full Screen.]



Complete Code
index.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

< ?php
include_once('db.php');
 
if(isset($_POST['name']))
{
  $name = $_POST['name'];
 
  if(mysql_query("INSERT INTO apple VALUES('','$name')"))
echo "Successful Insertion!";
  else
echo "Please try again";
}
 
 
$rs = mysql_query("SELECT count(*) FROM apple");
$rw = mysql_fetch_array($rs);
 
if( !(isset($_GET['page'])) )
    $start = $rw[0] - 5;
else
    $start = $rw[0] - ($_GET['page'] * 5);
 
 
$res = mysql_query("SELECT * FROM apple LIMIT $start, 5") 
                                  or Die("More entries coming, stay tuned!");
 
 
?>
<html>
<head>
 
<style type="text/css">
 li { list-style-type: none; display: inline; padding: 10px; text-align: center;}
// li:hover { background-color: yellow; }
</style>
 
</head>
<body>
<form action="." method="POST">
Name: <input type="text" name="name"/><br />
<input type="submit" value=" Enter "/>
</form>
 
<h1>List of companies ..</h1>
<ul>
< ?php
while( $row = mysql_fetch_array($res) )
  echo "<li>$row[name] 
                <li><a href='edit.php?edit=$row[id]'>edit</a></li>
                <li><a href='delete.php?del=$row[id]'>delete</a></li><br />";
 
?>
</ul>
<ul>
<li><a href="index.php?page=1">1</a></li>
<li><a href="index.php?page=2">2</a></li>
<li><a href="index.php?page=3">3</a></li>
<li><a href="index.php?page=<?php 
  if(isset($_GET['page'])) 
     $next = $_GET['page'] +1; 
  else 
     $next=2; 
  echo $next; ?>">next</a>
</li>
</ul>
</body>
</html>

Error:
Notice: Undefined index: page

Make sure you have written proper code near next link in the navigation / pagination numbering..

1
2
3
4
5
6
7
8
9
10

<li>
<a href="index.php?page=<?php 
  if(isset($_GET['page'])) 
     $next = $_GET['page'] +1; 
  else 
     $next=2; 
 
  echo $next; 
?>">next</a>
</li>

First we check if $_GET[‘page’] is set. If not set, then we give a value of 2 to next orelse we assign the next value to $_GET[‘page’].

Also check:
GET method in action
POST method in action