Posts Tagged SQL

Develop a search engine for a database-driven website

Nowadays people are getting really lazy. Because the big search engines, like Google, provides customized search engine for individual websites. A lot of website developers just simply adopt the Google search engine for the websites they are developing. Compare to write their own search engine, this is relatively easy. However, a home-brewed search engine may be worth to pursue if you have some unique information and requirement. Plus you do not send checks to the big search engine companies. I came across the following article about developing fast and nice search engines.

Writing Your Own Search Engine Using SQL Server

Share

Tags: ,

GROUP domain from url in MySql

Problem

I have a large database that contains many urls, there are many domains repeating and i;m trying to get only the domain. eg:

http://example.com/someurl.html
http://example.com/someurl_on_the_same_domain.html
http://example.net/myurl.php
http://example.org/anotherurl.php

and i want to get only domains, eg:

http://example.com
http://example.net
http://example.org

My query is:

SELECT idsite FROM table GROUP BY site ORDER BY id DESC LIMIT 50

Solution

SELECT
    SUBSTR(site, 1 , IF(LOCATE('/', site, 8), LOCATE('/', site, 8)-1, LENGTH(site)))
        as OnlyDomain
    FROM tablename
    GROUP BY OnlyDomain
    ORDER BY id DESC LIMIT 50

The posted was from the following URL:
http://stackoverflow.com/questions/3590908/group-domain-from-url-in-mysql
Share

Tags: , ,

Improve MySQL search by using Union statement

I was developed a complex stored procedure in MySQL to search phrases. It took me a while to make it work as I wanted. I noticed that my procedure still have a flaw that have duplicate record and cannot count the number of records correctly when there are duplicates. In addition, it cannot correctly sort the record by relevance. I came across the follwing MySQL SQL example. It provides me good trick to do this.

MYSQL UNION DISTINCT WITH RESULT PRIORITY

(From http://dev.horemag.net/2008/07/31/mysql-union-distinct-with-result-priority/)

I had to modify the search algorithm today, the idea was to fetch the exact matches first then the split words matches. The status quo was:

 
(SELECT product_id FROM products WHERE a=1)
  UNION DISTINCT
(SELECT product_id FROM products WHERE a=2 OR a=1)

I wanted to be sure that the result from the first sub-query are before the second one. The first thing that kame to my mind was:

 
(SELECT product_id, 1 as priority FROM products WHERE a=1)
  UNION DISTINCT
(SELECT product_id, 2 as priority FROM products WHERE a=2 OR a=1)
ORDER BY priority

but unfortunately this breaks the UNION DISTINCT clause and the results are duplicated.

I came out with the following solution:

 
SELECT MIN(priority) AS priority, product_id
FROM (
  (SELECT product_id, 5 AS priority FROM products WHERE a=1)
    UNION
  (SELECT product_id, 6 AS priority FROM products WHERE a=1 OR a=2)
) AS t
GROUP BY (product_id)
ORDER BY priority
Share

Tags: , , , ,

SQL in MS Access 2007

Microsoft Access 2007 is a powerful tool to handle data, large or small, simple or complex. Recently I was forced to use it to do something because the data source was stored in an Access database. It comes out that it just inspired me to learn a little bit more about the new functions of the Access and applied SQL skills to operate the database and manipulate the data. Its SQL is little bit different from what I am familiar with, the MySQL SQL language. Even though, I was able to manipulate the data quickly by the help from googling the internet. The following lists some examples that I used and little bit different from what I know. They took me a little bit time but worth to do it.

Put select data into a new table

	SELECT field1, field2, ...
	INTO newtable
	FROM oldtable;

Insert select data into a existing table

	INSERT INTO table1 (field1, field2, ... )
	SELECT field1, field2, ...
	FROM table2;

Update with string function

	UPDATE table1 SET field3 =  field1 & field2
	WHERE left(field2,2) = field1;

Update data from another table

	UPDATE table1 a INNER JOIN table2 b ON a.id=b.id
	SET field1 = a.field1/b.field2;

Select from 2+ tables (inner join)

	SELECT a.field1, a.field2, ..., b.field1, b.field2, ...
	FROM table1 a INNER JOIN table2 b on a.id2 = b.id2
	GROUP BY a.field1, a.field2;
Share

Tags: , , , , , , ,

A PHP example to generate WordPress-like category list

In one of my post I described a way to use SQL stored procedure to extract data in a particular order and then generate a list. you can find the post from the following link.

http://www.sunfinedata.com/database/mysql-database/a-sql-stored-procedure-to-combine-multiple-select-sets-into-one/

In following PHP code is used to use the data returned from the SQL stored procedure and generate a checkbox list with the parent list item hang out and child list item indented. User can use the checkbox to select multiple items.


protected function getCheckboxList($result, $select_id, $select_name, $id_array=array())
{
if (! $result) { return ""; }
// the result points to an array of an array
$recordlist = "<ul class=\"category\">";
$openul = 0;
foreach($result as $key => $value)
{
$pair = array();
foreach($value as $key1 => $value1)
{
$pair[] = $value1;
}
if ($pair[2] == 0)
{
if ($openul >0)
{
$recordlist .= "</ul>";
$openul = 0;
}
}
$recordlist .= "<li><label class=\"selectid\">";
$recordlist .= "<input type=\"checkbox\" value=\"".$pair[0]."\"";
if (($id_array[$pair[0]]))
{
$recordlist .= " selected=\"selected\" />";
}
$recordlist .= ">".$pair[1]."</lable></il>";
if ($pair[2] == 0)
{
$recordlist .= "<ul class=\"sublist\">";
$openul = 1;
}
}
if ($openul >0) { $recordlist .= "</ul>"; }
$recordlist .= "</ul>";
return $recordlist;
}

In order to show the list in the expected order, the following CSS code is used.


/*************************
category list
**************************/
#category_panel
{
height: 300px;
overflow: auto;
font-size: 88%;
/*padding: .5em .9em;*/
border: 1px solid #D8D8D8;
}

#category_panel ul.category
{
list-style-type: none;
margin: 0px 0px 0px 0px;
padding: 0px 0px 0px 0px;
}

#category_panel .category ul /*CSS for Simple Tree Menu*/
{
text-align: left;
margin: 0px 0px 0px 0px;
padding: 0px 0px 0px 12px;
}

#category_panel .category li /*Style for LI elements in general (excludes an LI that contains sub lists)*/
{
list-style: none;
margin: 0;
padding: 0;
line-height: 12px;
/* padding: 0px 0px 0px 18px;
margin: 0px 0px 2px 3px;
*/}

#category_panel .category li.sublist /* Style for LI that contains sub lists (other ULs). */
{
cursor: pointer;
margin-left: 0px;
}

#category_panel .category li.sublist ul /*Style for ULs that are children of LIs (submenu) */
{
display: none; /*Hide them by default. Don't delete. */
}

#category_panel .category li.sublist ul li /*Style for LIs of ULs that are children of LIs (submenu) */
{
cursor: default;
}

You are free to adapt these code in your web applications. Let me know if you have improved code and comments.

Share

Tags: , , ,