RSS

Monthly Archives: June 2014

Faceted navigation in PHP and MySQL

By: mightywebdeveloper.com

In this tutorial we are going to build a simple PHP/MySQL faceted navigation from scratch. In case you don’t know what faceted navigation is (or faceted search, layered navigation), it’s the nice filtering mechanism you see on most e-commerce sites in some form or another next to the product listing (see image).

Magento faceted navigation example

With each newly selected filter the resultset is narrowed down accordingly which allows user to quickly find the items (products, classifieds, articles, etc.) that meet their criteria. This search mechanism is most useful when a dataset needs to be filtered based onmultiple properties.

Unfortunately MySQL does not support faceted search out-of-the-box. There are dedicated external search applications such as SOLR or ElasticSearch which index the facets automatically for filtering. However not everyone has the knowledge or resources required to run such an application. Luckily we can achieve similar results while maintaining good performance using MySQL and PHP by setting up an index table and making some clever table joins.

The example data

You can download an SQL export of all the example data here.
Let me first introduce the dataset we’re going to work our magic on for this example:

table: products
+----+-----------------------+----------+-----------------+--------+
| id | name                  | type     | brand           | colour |
+----+-----------------------+----------+-----------------+--------+
|  1 | Black Jacket by Acme  | jacket   | Acme Apparel    | black  |
|  2 | Blue Jacket by Acme   | jacket   | Acme Apparel    | blue   |
|  3 | Blue Jacket by FF     | jacket   | Fictive Fashion | blue   |
|  4 | Orange Trousers by FF | trousers | Fictive Fashion | orange |
+----+-----------------------+----------+-----------------+--------+

The faceted navigation in this example without any of the filters selected will look something like this:

Type:
Jacket (3)
Trousers (1)
Brand:
Acme Apparel (2)
Fictive Fashion (2)
Colour:
Black (1)
Blue (2)
Orange (1)

The user can filter the product overview simply clicking on the filter links which makes it a form of navigation to the user (hence the term faceted navigation).

Note the item counts next to each facet value, these will be updated to reflect the current resultset whenever a filter is added or removed.

 

Faceted navigation index tables

To perform the faceted search in MySQL we need to create two extra tables. One table contains a list of the product facets and the other contains facts about the products.

table: product_facets
+----+--------+
| id | name   |
+----+--------+
|  1 | type   |
|  2 | brand  |
|  3 | colour |
+----+--------+

table: product_facts
+------------+----------+------------+-----------------+
| product_id | facet_id | facet_name | value           |
+------------+----------+------------+-----------------+
|          1 |        1 | type       | jacket          |
|          1 |        2 | brand      | Acme Apparel    |
|          1 |        3 | colour     | black           |
|          2 |        1 | type       | jacket          |
|          2 |        2 | brand      | Acme Apparel    |
|          2 |        3 | colour     | blue            |
|          3 |        1 | type       | jacket          |
|          3 |        2 | brand      | Fictive Fashion |
|          3 |        3 | colour     | blue            |
|          4 |        1 | type       | trousers        |
|          4 |        2 | brand      | Fictive Fashion |
|          4 |        3 | colour     | orange          |
+------------+----------+------------+-----------------+

We used the following PHP function to automatically populate the facts table with data. You’ll need to make sure the product_facts table gets updated for each CRUD operation on the original dataset.

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
/**
 * Populates the product_facts table with data from the products table
 * @param object $mysql_connection the mysql connection object
 */
function populate_product_facts($mysql_connection) {
  //empty product_facts table
  mysqli_query($mysql_connection"TRUNCATE product_facts");  
  //fetch all product data
  $result = mysqli_query($mysql_connection"SELECT * FROM products");
  //loop through resultset rows
  while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $facet_id = 1;
    //loop through table columns
    foreach ($row as $key => $value) {
      //create facts for all product fields except 'id' and 'name'
      if ($key != "id" && $key != "name") {
        $sql "INSERT INTO product_facts VALUES (" $row['id'] . ",$facet_id,'$key','$value');";
        mysqli_query($mysql_connection$sql);
        $facet_id++;
        echo "Added fact: (".$row['id'].", $facet_id, $key, $value) <br/>";
      }
    }
  }
}

Now that we have set up all the required tables we will look at how to query the tables in the next section.

Querying the data

Let’s say a user just opened the product browse page and hasn’t selected any filters yet. To retrieve the product data we can simply execute the query:

1
SELECT FROM products;

This will return a list of all products.

To construct the faceted navigation HTML we need to query the product_facts table.

1
2
3
SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf
GROUP BY pf.facet_id, pf.value;

Which produces the following result:

+------------+-----------------+---+
| facet_name | value           | c |
+------------+-----------------+---+
| type       | jacket          | 3 |
| type       | trousers        | 1 |
| brand      | Acme Apparel    | 2 |
| brand      | Fictive Fashion | 2 |
| colour     | black           | 1 |
| colour     | blue            | 2 |
| colour     | orange          | 1 |
+------------+-----------------+---+

As you can see this contains all the data required to output the faceted navigation (including counts).

Now let’s say that a user selects the colour blue so that only blue products are shown. This changes the product query to:

1
2
SELECT FROM products
WHERE colour='blue';

Similarly the query to retrieve the facet data becomes:

1
2
3
4
5
SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf
JOIN products p ON pf.product_id = p.id
WHERE p.colour='blue'
GROUP BY pf.facet_id, pf.value;

Which results in:

+------------+-----------------+---+
| facet_name | value           | c |
+------------+-----------------+---+
| type       | jacket          | 2 |
| brand      | Acme Apparel    | 1 |
| brand      | Fictive Fashion | 1 |
| colour     | blue            | 2 |
+------------+-----------------+---+

We can add an ORDER BY statement to sort the list of filters alphabetically:

1
2
3
4
5
SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf
JOIN products p ON pf.product_id = p.id
GROUP BY pf.facet_id, pf.value
ORDER BY pf.facet_name, pf.value;

When the user selects multiple filters we simply add multiple conditions in the WHERE clause based on the selected filters.

In the next post I’ll explain how to create the interactive faceted navigation HTML interface based on the work we’ve done so far.

If you have any questions please leave a comment below!

Copy from: http://www.mightywebdeveloper.com/coding/faceted-navigation-in-php-mysql/

Advertisements
 
Leave a comment

Posted by on June 26, 2014 in MySQL Server, PHP

 

How to Create a CSS3 Mega Drop-Down Menu

By: Valeriu Timbuc

Preview

Topic: CSS3
Difficulty: Intermediate
Estimated Completion Time: 1 hour

Hello guys! In this tutorial I will teach you how to create a pure CSS3 Mega Menu. Mega Menus are usually used on corporate/e-commerce websites, but they become more popular because they are a great way to display/organize content. The design of this mega menu can be found on the The Bricks UI. Let’s start with the HTML markup…

HTML Markup

Create an unordered list with the class “nav” (<ul>), then for each menu item we will add a new list item (<li>) with an anchor tag (<a>) inside. If you want the menu item to be a mega menu just add a <div> inside of the <li>.

SEE ALSO: Animating SVG Gradients

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<ul class="nav">
    <li>
        <a href="#">What's new</a>
        <div>
            Mega Menu Content...
        </div>
    </li>
    <li><a href="#">Top rated</a></li>
    <li>
        <a href="#">Earnings</a>
        <div>
            Mega Menu Content...
        </div>
    </li>
    <li><a href="#">Rings</a></li>
    <li><a href="#">Bracelets</a></li>
    <li><a href="#">All Categories</a></li>
    <li class="nav-search">
        <form action="#">
            <input type="text" placeholder="Search...">
            <input type="submit" value="">
        </form>
    </li>
</ul>

Reset and Container Basic Styles

As the default styles of the browsers differ from each other, we’ll add some reset styles to keep consistency between them and avoid future issues.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
.nav,
.nav a,
.nav ul,
.nav li,
.nav div,
.nav form,
.nav input {
    margin0;
    padding0;
    bordernone;
    outlinenone;
}
.nav a { text-decorationnone; }
.nav li { list-stylenone; }

Then we will add some basic styles to the menu container and float the list items to the left in order to show them on the same line.

1
2
3
4
5
6
7
8
9
10
11
.nav {
    display: inline-block;
    positionrelative;
    cursordefault;
    z-index500;
}
.nav > li {
    displayblock;
    floatleft;
}

Styling the Menu Links

We will start with the common styles like padding, height, position, etc. Then we’ll set the typography styles such as font size, color, text shadow, etc. We’ll also set a background color and a left and right border. To keep the styles transitions smooth we’ll add a CSS3 transition. Note that we set the same property five times, this is required because this property is not fully implemented in all browsers yet, and as this property is on testing, all the different browsers use a prefix before the property. So if you want to support some current and older browsers use this prefixes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
.nav > li > a {
    positionrelative;
    displayblock;
    z-index510;
    height54px;
    padding0 20px;
    line-height54px;
    font-familyHelveticaArialsans-serif;
    font-weightbold;
    font-size13px;
    color#fcfcfc;
    text-shadow0 0 1px rgba(0,0,0,.35);
    background#372f2b;
    border-left1px solid #4b4441;
    border-right1px solid #312a27;
    -webkit-transition: all .3s ease;
    -moz-transition: all .3s ease;
    -o-transition: all .3s ease;
    -ms-transition: all .3s ease;
    transition: all .3s ease;
}

For the hover state we will only change the background color. To finish the menu links styling we will add rounded corners to the first link using the :first-child pseudo selector and remove the left border.

1
2
3
4
5
6
.nav > li:hover > a { background#4b4441; }
.nav > li:first-child > a {
    border-radius: 3px 0 0 3px;
    border-leftnone;
}

Styling the Search Form

We’ll start with the form container: set the position to relative, add a left border like we have on the menu links and set the width to inherit. By setting the width to inherit it will use the width from the child inputs.

1
2
3
4
5
6
7
.nav > li.nav-search > form {
    positionrelative;
    width: inherit;
    height54px;
    z-index510;
    border-left1px solid #4b4441;
}

Then we will style the text input: float it to the left, set the height, padding, etc. To hide the text input we will set the width to 1px and remove the left and right padding. On the :hover and :focus state we will update the width and padding values.

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
.nav > li.nav-search input[type="text"] {
    displayblock;
    floatleft;
    width1px;
    height24px;
    padding15px 0;
    line-height24px;
    font-familyHelveticaArialsans-serif;
    font-weightbold;
    font-size13px;
    color#999999;
    text-shadow0 0 1px rgba(0,0,0,.35);
    background#372f2b;
    -webkit-transition: all .3s ease 1s;
    -moz-transition: all .3s ease 1s;
    -o-transition: all .3s ease 1s;
    -ms-transition: all .3s ease 1s;
    transition: all .3s ease 1s;
}
.nav > li.nav-search input[type="text"]:focus { color#fcfcfc; }
.nav > li.nav-search input[type="text"]:focus,
.nav > li.nav-search:hover input[type="text"] {
    width110px;
    padding15px 20px;
    -webkit-transition: all .3s ease .1s;
    -moz-transition: all .3s ease .1s;
    -o-transition: all .3s ease .1s;
    -ms-transition: all .3s ease .1s;
    transition: all .3s ease .1s;
}

The styles for the submit input are similar to the text input, we’ll only set a fixed width and height, add a background icon and rounded corners using the border-radiusproperty. We have also set transitions for both inputs in order to have some smooth and nice animations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
.nav > li.nav-search input[type="submit"] {
    displayblock;
    floatleft;
    width20px;
    height54px;
    padding0 25px;
    cursorpointer;
    background#372f2b url(../img/search-icon.png) no-repeat center center;
    border-radius: 0 3px 3px 0;
    -webkit-transition: all .3s ease;
    -moz-transition: all .3s ease;
    -o-transition: all .3s ease;
    -ms-transition: all .3s ease;
    transition: all .3s ease;
}
.nav > li.nav-search input[type="submit"]:hover { background-color#4b4441; }

 

 

Copy from: http://designmodo.com/create-css3-mega-menu/

 
Leave a comment

Posted by on June 18, 2014 in CS Style

 

Combines multiple files into one file

By Sochinda Tith

For Mac Terminal

cat *.txt > com.txt

 

For Window CMD

copy /b *.txt com.txt

 
Leave a comment

Posted by on June 13, 2014 in Uncategorized

 

Float div on center of page

By: corelangs.com

Div on center of screen

In CSS, float-based layouts are not much flexible, however, a float is somewhat more difficult to center.

Is there any way to center floating elements?

Floated div centered

This is an age old question. You can float left or right, but there’s no way to float center in CSS layout.

Positioning DIV element at center of screen

Centre Div in Middle of Screen

You can solve it in a simple way.

margin: 0 auto;

The above code states that the TOP margin and Bottom margin are set to 0 and LEFT margin and Right margin set to auto (automatic). Here the automatic left and right margins push the element into the center of its container.

How to display div in center of screen

mergin : 0 auto

From the above picture you can understand how it display at the center of the screen.

margin: 0 auto;

is equal to

margin 0 auto 0 auto;

or you can write like the following:

margin-top:0; margin-right:auto; margin-bottom:0; margin-left:auto;

The following source code shows how to center Div in the middle of browser.

<!DOCTYPE html> <html > <head> <style type=”text/css”> .centerDiv { width: 60%; height:200px; margin: 0 auto; background-color:#FFA500 ; } </style> </head> <body> <div class=”centerDiv”> </div> </body> </html>

Two column layout Div at the center of the screen

Center Floated Divs in a Container

Center a Floating div

Source Code

<!DOCTYPE html> <html > <head> <style type=”text/css”> .centerDiv { width: 60%; height:200px; margin: 0 auto; background-color:#FFA500 ; } .div1 { width: 50%; height:200px; background-color:#A52A2A ; float:left; } .div2 { width: 50%; height:200px; background-color:#FFA500 ; float:left; } </style> </head> <body> <div class=”centerDiv”> <div class=”div1″> </div> <div class=”div2″> </div> </div> </body> </html>

Position Div Exactly at the center of the screen

Center div on page vertically , Center div on page horizontally

Exactly center means, position Div horizondaly and virtically at the center of the screen.

div vertcally center horizondally center

Source Code

<!DOCTYPE html> <html > <head> <style type=”text/css”> .exactCenter { width:200px; height:200px; position: fixed; background-color: #00FF00; top: 50%; left: 50%; margin-top: -100px; margin-left: -100px; } </style> </head> <body> <div class=”exactCenter”> </div> </body> </html>

Horizontal Divs at the center of the browser

Show div on center of screen

center floating div elements

Source Code

<!DOCTYPE html> <html > <head> <style type=”text/css”> .div1 { width: 50%; height:70px; background-color:#A52A2A ; margin: 0 auto; } .div2 { width: 50%; height:70px; background-color:#FFA500 ; margin: 0 auto; } .div3 { width: 50%; height:70px; background-color:#008000 ; margin: 0 auto; } </style> </head> <body> <div class=”div1″> </div> <div class=”div2″> </div> <div class=”div3″> </div> </body> </html>

Three column layout Div at the center of the screen

Place div on center of screen

Center Floated DIVS In Container

Source Code

<!DOCTYPE html> <html > <head> <style type=”text/css”> .centerDiv { width: 60%; height:200px; margin: 0 auto; background-color:#FFA500 ; } .div1 { width: 33%; height:200px; background-color:#A52A2A ; float:left; } .div2 { width: 34%; height:200px; background-color:#FFA500 ; float:left; } .div2 { width: 33%; height:200px; background-color:#008000 ; float:left; } </style> </head> <body> <div class=”centerDiv”> <div class=”div1″> </div> <div class=”div2″> </div> <div class=”div3″> </div> </div> </body> </html>

Copy from: http://www.corelangs.com/css/box/center-div.html

 
Leave a comment

Posted by on June 9, 2014 in CS Style

 

Prepire Environment for RequireJS, Backbone, and Bower Starter Template on Windows

By Sochinda Tith,

Required application:

  1. Nodejs installer (http://nodejs.org/download/)
  2. Msysgit (http://msysgit.github.io/)

Download both application and install into Windows OS

Open “CMD” as administrator and run:

$ npm install bower-installer
$ npm install -g requirejs
del %HOMEDRIVE%%HOMEPATH%\AppData\Roaming\npm\r.js
DOSKEY r.js=r.js.cmd $*

*** Notation: for new version bower, we have to change component.js to bower.js

If there any error’s called javascript source map, please go to setting in browser and un-check this option.

Please follow instruction in bellow video:

 
Leave a comment

Posted by on June 6, 2014 in Backbone, Javascript, RequireJs

 

Building Mobile Apps with HTML and a Local Database

by 

After my recent post, Crafting Native Looking iOS Apps with HTML, a number of you asked for an offline version that would use a Local Database (instead of the simple in-memory store) and provide a mechanism to automatically keep the local database in sync with a server database.

I’ll save automatic data synchronization strategies for a future post, but here is the first step: an “offline” version of the application that uses the device’s or the browser’s local database as its data provider. This version still uses Backbone.js as its architectural framework. Backbone.js makes it easy to change its default data access mechanism (which assumes RESTful services). You just replace the default Backbone.sync implementation and provide your own data access logic: in this case, some local SQL logic.

Web SQL vs IndexedDB

As you probably know, there have been two competing database APIs for HTML. From the W3C web site:

  • The Web SQL specification defines an API for storing data in databases that can be queried using a variant of SQL. This specification is no longer in active maintenance and the Web Applications Working Group does not intend to maintain it further.
  • The Indexed Database specification defines APIs for a database of records holding simple values and hierarchical objects. It is a working draft, and “work in progress”.

Even though the W3C is no longer actively maintaining the spec, this application uses the Web SQL API because, as a mobile app, its two main target platforms are iOS and Android, which both currently support Web SQL but not IndexedDB. More detailed platform support information can be found on caniuse.com (Web SQL and IndexedDB).

Chrome, Safari, and Opera on the desktop also support Web SQL, which means that you can run the application in these browsers. Try it here. For example, using the Chrome Developer Tools you could debug the application and inspect the database as shown in this screenshot:

Firefox and IE don’t support Web SQL. You could easily create an alternative version of EmployeeDAO (described below) that uses IndexedDB instead. You could also create a version of the application that uses either Web SQL or IndexedDB depending on the platform it’s running on.

Code Highlights

The source code is available in the localdb folder of the backbone-directory repository on GitHub. Here is a quick walkthrough…

The data access logic is encapsulated in EmployeeDAO, which also has a “populate” function to populate the employee table with sample data.

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
directory.dao.EmployeeDAO = function(db) {
    this.db = db;
};
_.extend(directory.dao.EmployeeDAO.prototype, {
    findByName: function(key, callback) {
        this.db.transaction(
            function(tx) {
                var sql = "SELECT e.id, e.firstName, e.lastName, e.title, count(r.id) reportCount " +
                    "FROM employee e LEFT JOIN employee r ON r.managerId = e.id " +
                    "WHERE e.firstName || ' ' || e.lastName LIKE ? " +
                    "GROUP BY e.id ORDER BY e.lastName, e.firstName";
                tx.executeSql(sql, ['%' + key + '%'], function(tx, results) {
                    var len = results.rows.length,
                        employees = [],
                        i = 0;
                    for (; i < len; i = i + 1) {
                        employees[i] = results.rows.item(i);
                    }
                    callback(employees);
                });
            },
            function(tx, error) {
                alert("Transaction Error: " + error);
            }
        );
    },
    findById: function(id, callback) {
        // removed for brevity
    },
    findByManager: function(managerId, callback) {
        // removed for brevity
    },
    populate: function(callback) {
        // removed for brevity
    }
});

Models are annotated with a “dao” attribute to indicate which data object to use to access their underlying data.

1
2
3
4
5
6
7
8
9
10
11
12
13
directory.models.Employee = Backbone.Model.extend({
    dao: directory.dao.EmployeeDAO,
});
directory.models.EmployeeCollection = Backbone.Collection.extend({
    dao: directory.dao.EmployeeDAO,
    model: directory.models.Employee,
});

With that infrastructure in place, you can then override Backbone.sync to access data from the local database instead of RESTful services:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Backbone.sync = function(method, model, options) {
    var dao = new model.dao(directory.db);
    if (method === "read") {
        if (model.id) {
            dao.findById(model.id, function(data) {
                options.success(data);
            });
        } else if (model.managerId) {
            dao.findByManager(model.managerId, function(data) {
                options.success(data);
            });
        }
        // removed for brevity
    }
};

Source Code

The source code is available in the localdb folder of the backbone-directory repository on GitHub.

 

Copy from: http://coenraets.org/blog/2012/04/building-mobile-apps-with-html-and-a-local-database/

 
Leave a comment

Posted by on June 4, 2014 in Javascript