Posts Tagged stored procedure

A generic framework of a stored SQL procedure to split a string

When programming MySQL stored procedures, sometime we need something like the string split function in other programming languages. Unfortunately MySQL does not provide a string split function. To fulfill the work, I searched online and piece information from multiple sources together to generate the following generate framework to split a string based on a specific delimiter.

CREATE PROCEDURE `deletewords`(
my_words varchar(1024))
BEGIN
DECLARE i_occur INT(2);
DECLARE i_delimiter VARCHAR(1);
DECLARE i INT(2);
DECLARE i_word VARCHAR(20);

-- space as delimiter
SET i_delimiter = ",";
SET i = 1;
-- get how many keywords
SET i_occur = length(my_words) - length(replace(my_words, i_delimiter, "")) + 1;

IF (i_occur > 1) THEN
WHILE (i <= i_occur) DO
-- construct union statement
SET i_word = SUBSTRING_INDEX(SUBSTRING_INDEX(my_words, i_delimiter,i),i_delimiter, -1);
-- here you can insert your SQL statements to process the individual word
SET i = i + 1;
END WHILE;
END IF;
END
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: , , ,

A SQL stored procedure to combine multiple select sets into one

The data table include id, parent_id, title and position. Parent_id is one of the ids in the table. So it is self referenced table.

The goal is to extract all records in a particular order, order the records based on parent record first, then order all categories within the same parent. In this way, we can generate a list with two tiers. First tier includes parent, then second tier includes all children. We can close and open the second tier in the list with ease.

Here is a SQL stored procedure to extract all records in the above described order.


DELIMITER $$

CREATE DEFINER=`zhanshan`@`%` PROCEDURE `category_list2`()
BEGIN
DECLARE select_clause VARCHAR(5000);
DECLARE i_select_clause VARCHAR(1000);
DECLARE no_more_record INT(1);
DECLARE i_category_id INT(10) UNSIGNED;

-- declare a cursor for id_category
DECLARE my_cursor CURSOR FOR
SELECT id_category
FROM tws_category
WHERE channel_id = 0
ORDER BY position;

-- loop through the category table
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record=1;
SET no_more_record=0;
SET i_select_clause = "";
SET select_clause = "";
OPEN my_cursor;
record_loop:WHILE(no_more_record=0) DO
FETCH my_cursor INTO i_category_id;
IF no_more_record=1 THEN
LEAVE record_loop;
END IF;
-- send the category data in each channel
SET i_select_clause = CONCAT("(SELECT id_category, title, channel_id
FROM tws_category
WHERE (id_category=",i_category_id,") OR (channel_id = ",i_category_id,")
ORDER BY channel_id, position)");

IF select_clause = '' THEN
SET select_clause = i_select_clause;
ELSE
SET select_clause = CONCAT(select_clause, " UNION ", i_select_clause);
END IF;
END WHILE record_loop;
CLOSE my_cursor;
SET no_more_record=0;

-- select select_clause;

SET @sql=select_clause;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;

END

Share

Tags: , ,

Using MySQL stored procedure to create a sort list

I have a table that includes recursive data columns. All information is used to generate a dropdown list or option list. In this example, a two layers table is used. It will be easy to extend the examples to the multiple layer tables. The first layer is called channel and the second layer is called topics.

There are two MySQL stored procedure developed here. The first one is used to get all ids of a give channel. The second one is the control stored procedure which will call the first one.

The one extracts all topics ids for a given channel.

DELIMITER $$

CREATE DEFINER=`zhanshan`@`%` PROCEDURE `category_channel`(
my_channel_id INT(11) UNSIGNED,
OUT ids VARCHAR(200))
BEGIN
DECLARE all_ids VARCHAR(2000);
DECLARE category_id INT(10) UNSIGNED;
DECLARE no_more_record INT(1);

-- declare a cursor for id_category
DECLARE my_cursor CURSOR FOR
SELECT id_category
FROM tws_category
WHERE channel_id = my_channel_id
ORDER BY position;

-- loop through the category table
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record=1;
SET all_ids = "";
SET no_more_record=0;
OPEN my_cursor;
record_loop:WHILE(no_more_record=0) DO
FETCH my_cursor INTO category_id;
IF no_more_record=1 THEN
LEAVE record_loop;
END IF;
IF (all_ids = '') THEN
SET all_ids = category_id;
ELSE
SET all_ids = CONCAT(all_ids, ",", category_id);
END IF;
END WHILE record_loop;
CLOSE my_cursor;
SET no_more_record=0;

SET ids = all_ids;
END

For the above SQL stored procedure, the key is the out parameter in the procedure declaration. The out variable is used to communicate back to the calling stored procedure. The second MySQL stored procedure


DELIMITER $$

CREATE DEFINER=`zhanshan`@`%` PROCEDURE `category_list2`()
BEGIN
DECLARE select_clause VARCHAR(1000);
DECLARE all_ids VARCHAR(2000);
DECLARE no_more_record INT(1);
DECLARE ids VARCHAR(1000);
DECLARE category_id INT(10) UNSIGNED;

-- declare a cursor for id_category
DECLARE my_cursor CURSOR FOR
SELECT id_category
FROM tws_category
WHERE channel_id = 0
ORDER BY position;

-- loop through the category table
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record=1;
SET all_ids = "";
SET no_more_record=0;
OPEN my_cursor;
record_loop:WHILE(no_more_record=0) DO
FETCH my_cursor INTO category_id;
IF no_more_record=1 THEN
LEAVE record_loop;
END IF;
IF all_ids = '' THEN
SET all_ids = category_id;
ELSE
SET all_ids = CONCAT(all_ids, ",", category_id);
END IF;
CALL category_channel(category_id, ids);
IF all_ids = '' THEN
SET all_ids = ids;
ELSE
SET all_ids = CONCAT(all_ids, ",", ids);
END IF;
END WHILE record_loop;
CLOSE my_cursor;
SET no_more_record=0;

SET select_clause = CONCAT("SELECT id_category, channel_id, title FROM tws_category
ORDER BY find_in_set(id_category,'", all_ids, "')");

select select_clause;

SET @sql=select_clause;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;

END

In this stored procedure, the key SQL function find_in_set(field, a list). It will allows you provide a sorted list for a give column. The output of the select clause will use the defined sorted list to list all records.

Share

Tags: , , , , , , ,

Handling UTF8 charset correctly in MySQL stored procedure

When using MySQL Workbench version 5.2.32 ref 7496 to insert utf8 encoding data to tables through stored procedures, a problem was encountered. All utf8 encoding characters were converted to “?” in the table. However, when directly using insert statement, everything is normal and resulted data in the tables were correct. That implied that something in the stored procedures did not correctly set. After searching a while on the internet, finally a solution was reached. Just simply add “CHARACTER SET utf8” at the end of parameter in the stored procedure. That will ensure the stored procedure receive and handle the correct encoding characters. Here is an example of the head of a stored procedure.


CREATE DEFINER=`zhanshan`@`%` PROCEDURE `source_add`(
my_userid INT(10),
my_title VARCHAR(255) CHARACTER SET utf8,
my_url VARCHAR(255),
my_description TEXT CHARACTER SET utf8,
my_language INT(1) UNSIGNED,
my_ip VARCHAR(15))
BEGIN

Share

Tags: , , , , , ,