mysql syntax highlighter by jquery

Written by @kerixa 23 February 2014

For the websites that usually contain programming codes, such as those that sell free codes or solve programming problems, It is essential to use a syntax highlighter. Without a syntax analyzer, the programmer's eyes get tired and can not read the code perfectly. The following code is a sample syntax highlighter and with linearization it has, the lines are not mixed together and the audience's attention is drawn back again.

Code Snippet:

                                                <!-- this script is provided by coded by: Kerixa Inc. -->
<!-- this script got from by: Krishna Eydat -->
<style type="text/css">
* {margin: 0; padding: 0;}
body {padding: 30px;}

#highlight {display: none;}

/*styles for the pre tag*/
#layer {
	font-family: monospace;
	font-size: 12px;
	line-height: 20px;
	border: 1px solid #ccc;
	background: linear-gradient(white, white 19px, #ddd 19px, #ddd 20px);
	background-size: 20px 20px;
	padding: 20px 30px;
	white-space: pre-wrap; /*to wrap words*/
	word-wrap: break-word; /*IE 5.5+*/

.sc {color: brown;}
.keyword {color: purple; font-weight: bold;}
.string {color: darkgreen;}
.comment {color: green; background: lightyellow;}
.function {color: red;}
<!-- pre tag to contain the highlighted code -->
<pre id="layer"></pre>

<!-- textarea to contain the raw sql -->
<textarea id="highlight" name="highlight">
#This is a mysql syntax highlighter
-- This is a comment
And this is a multi line comment
SELECT things FROM table;

SELECT AS agent, ``, d.first_payment_date, d.account_number, as disbursement_id, d.amount as loan_amount, d.late_fee, as bid, as borrower, as payment_frequency, as application_id, 

(SELECT COUNT(id) FROM payment_schedule WHERE disbursement_id = AND skip = '1') as skip_count, 

IFNULL(SUM(r.principal), 0) as paid_principal, 

IF(d.interest_calculation_method_id = 2, d.amount, IFNULL((SELECT SUM(principal) FROM payment_schedule WHERE disbursement_id = AND skip = '0' AND date <= CURDATE()), 0)) as psprincipal, 

IF(d.interest_calculation_method_id IN(2, 3), d.amount - (SELECT IFNULL(SUM(principal), 0) FROM receipts WHERE disbursement_id =, IFNULL((SELECT SUM(principal+interest) FROM payment_schedule WHERE disbursement_id = AND skip = '0' AND date <= CURDATE()), 0)) as psamount, 

FROM disbursements d 
LEFT JOIN receipts r ON 
LEFT JOIN applications ap ON d.application_id =  
LEFT JOIN agents ag ON ap.vt_user_id = ag.vt_user_id 
LEFT JOIN borrowers b ON ap.borrower_id = 
LEFT JOIN time_frequencies tf ON d.payment_frequency_id = 

WHERE d.status = 'active'
ORDER BY due_date

<!-- jquery -->
<script src="" type="text/javascript"></script>
<script type="text/javascript">
	//full list of reserved words:
	//adding lowercase keyword support
	var len = k.length;
	for(var i = 0; i < len; i++)
	var re;
	var c = $("#highlight").val(); //raw code
	//regex time
	//highlighting special characters. /, *, + are escaped using a backslash
	//'g' = global modifier = to replace all occurances of the match
	//$1 = backreference to the part of the match inside the brackets (....)
	c = c.replace(/(=|%|\/|\*|-|,|;|\+|<|>)/g, "<span class=\"sc\">$1</span>");
	//strings - text inside single quotes and backticks
	c = c.replace(/(['`].*?['`])/g, "<span class=\"string\">$1</span>");
	//numbers - same color as strings
	c = c.replace(/(\d+)/g, "<span class=\"string\">$1</span>");
	//functions - any string followed by a '('
	c = c.replace(/(\w*?)\(/g, "<span class=\"function\">$1</span>(");
	//brackets - same as special chars
	c = c.replace(/([\(\)])/g, "<span class=\"sc\">$1</span>");
	//reserved mysql keywords
	for(var i = 0; i < k.length; i++)
		//regex pattern will be formulated based on the array values surrounded by word boundaries. since the replace function does not accept a string as a regex pattern, we will use a regex object this time
		re = new RegExp("\\b"+k[i]+"\\b", "g");
		c = c.replace(re, "<span class=\"keyword\">"+k[i]+"</span>");
	//comments - tricky...
	//comments starting with a '#'
	c = c.replace(/(#.*?\n)/g, clear_spans);
	//comments starting with '-- '
	//first we need to remove the spans applied to the '--' as a special char
	c = c.replace(/<span class=\"sc\">-<\/span><span class=\"sc\">-<\/span>/g, "--");
	c = c.replace(/(-- .*?\n)/g, clear_spans);
	//comments inside /*...*/
	//filtering out spans attached to /* and */ as special chars
	c = c.replace(/<span class=\"sc\">\/<\/span><span class=\"sc\">\*<\/span>/g, "/*");
	c = c.replace(/<span class=\"sc\">\*<\/span><span class=\"sc\">\/<\/span>/g, "*/");
	//In JS the dot operator cannot match newlines. So we will use [\s\S] as a hack to select everything(space or non space characters)
	c = c.replace(/(\/\*[\s\S]*?\*\/)/g, clear_spans);
	$("#layer").html(c); //injecting the code into the pre tag
	//as you can see keywords are still purple inside comments.
	//we will create a filter function to remove those spans. This function will noe be used in .replace() instead of a replacement string
	function clear_spans(match)
		match = match.replace(/<span.*?>/g, "");
		match = match.replace(/<\/span>/g, "");
		return "<span class=\"comment\">"+match+"</span>";
<div><a target="_blank" href=""><span style="font-size: 8pt; text-decoration: none">HTML Best Codes</span></a></div>
</html><a target='_blank' href='' style='font-size: 8pt; text-decoration: none'>Html Best Codes</a>                                                


About @kerixa

I am Krishna Eydat. I studied Software Engineering at University of Waterloo in Canada. I lead a few tech companies. I am passionate about the way we are connected. I would like to be part of something big or be the big deal!



Here you can leave us commments. Let us know what you think about this code tutorial!

0 / 300

Online Users: 12
Recent Members: Jenisha, Mr Joseph Charles, Nikhil 999+, bishalthakur
advertisement 2