MySQL highlighter

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

ziggy3000
Forum Contributor
Posts: 205
Joined: Fri Mar 23, 2007 3:04 pm

Post by ziggy3000 »

i tried to read, but im only in middle school, (next year is high school WOOT). i am getting confused. but anyway, i got it to work.
ziggy3000
Forum Contributor
Posts: 205
Joined: Fri Mar 23, 2007 3:04 pm

Post by ziggy3000 »

but now, it won't highlight anything between single quotes :(
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Really? When I tested your code, it worked fine on single quotes. Maybe you could try using single quotes all the way through like the rest of your regex, and just escape the inner quotes.
ziggy3000
Forum Contributor
Posts: 205
Joined: Fri Mar 23, 2007 3:04 pm

Post by ziggy3000 »

is there any way to exclude the equal sign in the preg_replace?
here's my code now

Code: Select all

$sql = preg_replace("/'(.+?)'/i", "<span style=\"color:red\"> '\\1' </span>", $sql);
	$sql = str_replace("\n", '<br />', $sql);
 	$sql = preg_replace("/(=)/", "<span style='color:green'> \\1 </span>", $sql);
	$sql = preg_replace('/"(.+?)"/i', "<span style='color:red'> \"\\1\" </span>", $sql);
	$sql = preg_replace('/`(.+?)`/i', "<span style='color:red'> `\\1` </span>", $sql);
	$sql = preg_replace('/^(CREATE|ALTER|DROP)\s+(VIEW|TABLE|DATABASE|SCHEMA)\s+/i', "<span style='color:blue'>$1 $2 </span>", $sql);
	$sql = preg_replace('/(SELECT|UPDATE)\s+/i', "<span style='color:blue'>\\1 </span>", $sql);
	$sql = preg_replace('/(MAX|AVG|SUM|COUNT|MIN|FROM|INTO)\s+/i', "<span style='color:green'> \\1 </span>", $sql);
	$sql = preg_replace('/(ASC|DESC|ORDER BY|LIMIT|LEFT|JOIN|WHERE|MODIFY|CHANGE|DISTINCT)/i', "<span style='color:green'> \\1 </span", $sql);
	$sql = preg_replace('/(LIKE|NOT LIKE|REGEXP)/i', "<span style='color:orange'> \\1 </span>", $sql);
	$sql = preg_replace('/(INT|VARCHAR|TINYINT|BIGINT|BINARY|BIT|BLOB|BOOL|BOOLEAN|CHAR|' .
'CHARACTER|DATE|DATETIME|DEC|DECIMAL|DOUBLE|ENUM|FLOAT|FLOAT4|FLOAT8|INT1|INT2|INT3|' .
'INT4|INT8|INTEGER|LONGBLOB|LONGTEXT|MEDIUMBLOB|MEDIUMTEXT|MEDIUMINT|MIDDLEINT|NCHAR|' .
'NUMERIC|REAL|SERIAL|SET|SMALLINT|TEXT|TIME|TIMESTAMP|TINYBLOB|TINYTEXT|VARBINARY|YEAR|' .
'PRIMARY|AUTO INCREMENT)/i', "<span style='color:darkred'> \\1 </span>", $sql);
	$sql = preg_replace('/;/i', "<b style='color: green;'> ;</b>", $sql);
	$sql = preg_replace('/[0-9]{6}/i', "<span style='color: red;'> \\1 </span>", $sql);
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Exclude? In what way?

/[^=]/ ?

Edit: You should really wrap that string for us.
ziggy3000
Forum Contributor
Posts: 205
Joined: Fri Mar 23, 2007 3:04 pm

Post by ziggy3000 »

i meant that it replaces
'(.+?)'
with
= "color:red" > '(.+?)'
i want it to not include the html code in the output
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

That's because the you call the regex that replaces the equal (=) sign after that one. What you'll want to do is either call the regex on the equal sign first, or not at all.
ziggy3000
Forum Contributor
Posts: 205
Joined: Fri Mar 23, 2007 3:04 pm

Post by ziggy3000 »

so i should use str_replace? but when i am using str_replace, it doesn't highlight :( nor echo html code :)
or how would i get it to work?
ziggy3000
Forum Contributor
Posts: 205
Joined: Fri Mar 23, 2007 3:04 pm

Post by ziggy3000 »

nevermind, i fixed it by using div and css :D
ziggy3000
Forum Contributor
Posts: 205
Joined: Fri Mar 23, 2007 3:04 pm

Post by ziggy3000 »

i have encountered a problem. after i highlight anything between single quotes(which is surrounded by code tags) the code tag ends... how would i fix this
here is my code,

Code: Select all

$sql = preg_replace("/'(.+?)'/i", "<div><span> '\\1' </span></div>", $sql);
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

ziggy3000 wrote:so i should use str_replace? but when i am using str_replace, it doesn't highlight :( nor echo html code :)
or how would i get it to work?
I just meant to move the line that replaces the equal (=) sign higher up so that it doesn't replace the equal sign elsewhere in your code.
ziggy3000 wrote:i have encountered a problem. after i highlight anything between single quotes(which is surrounded by code tags) the code tag ends... how would i fix this
And what is happening? I don't see any errors there.
ziggy3000
Forum Contributor
Posts: 205
Joined: Fri Mar 23, 2007 3:04 pm

Post by ziggy3000 »

this is how i echo my code

Code: Select all

<?php
$code = explode('<br />', $code);
echo '<code>';
for ($a=0; $a < $total_lines; $a++){
echo $code[$a];
}
echo '</code>';
?>
when i highlight single quoted strings, it ends the code tag also(</code>) so the stuff after the single quotes is not in code format
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Well, I don't see a very obvious solution... Sounds like you've changed a lot of the code. However, I can't see what problem you're referring ot in that little snippet.

Post the code, and the resulting HTML from it. It shouldn't be too hard to pinpoint the problem, then.
ziggy3000
Forum Contributor
Posts: 205
Joined: Fri Mar 23, 2007 3:04 pm

Post by ziggy3000 »

my entire function

Code: Select all

function mysql($sql){

	// highlighting the code
	echo "<style>
	code { padding-top: 2px; padding-right: 2px; padding-bottom: 2px; padding-left: 2px }
	\n
	div{
		display: inline;
	}\n
	div span {
		color:red;
	}
code.sql {
	color: grey;
}
	</style>\n\n";
	$sql = str_replace("\n", '<br />', $sql);
	$sql = preg_replace("/'(.+?)'/i", "<div><span> '\\1' </span></div>", $sql);
 	$sql = preg_replace("/(=)/", "<span style='color:green'> = </span>", $sql);
	$sql = preg_replace('/"(.+?)"/i', "<span style='color:red'> \"\\1\" </span>", $sql);
	$sql = preg_replace('/`(.+?)`/i', "<span style='color:red'> `\\1` </span>", $sql);
	$sql = preg_replace('/(CREATE|ALTER|DROP)\s+(VIEW|TABLE|DATABASE|SCHEMA)\s+/i', "<span style='color:blue'>$1 $2 </span>", $sql);
	$sql = preg_replace('/(SELECT|UPDATE)\s+/i', "<span style='color:blue'>\\1 </span>", $sql);
	$sql = preg_replace('/(MAX|AVG|SUM|COUNT|MIN|FROM|INTO)\s+/i', "<span style='color:green'> \\1 </span>", $sql);
	$sql = preg_replace('/(ASC|DESC|ORDER BY|LIMIT|LEFT|JOIN|WHERE|MODIFY|CHANGE|DISTINCT)/i', "<span style='color:green'> \\1 </span>", $sql);
	$sql = preg_replace('/(LIKE|NOT LIKE|REGEXP)/i', "<span style='color:orange'> \\1 </span>", $sql);
	$sql = preg_replace('/(INT|VARCHAR|TINYINT|BIGINT|BINARY|BIT|BLOB|BOOL|BOOLEAN|CHAR|CHARACTER|DATE|DATETIME|DEC' . '|DECIMAL|DOUBLE|ENUM|FLOAT|FLOAT4|FLOAT8|INT1|INT2|INT3|INT4|INT8|INTEGER|LONGBLOB|' . 'LONGTEXT|MEDIUMBLOB|MEDIUMTEXT|MEDIUMINT|MIDDLEINT|NCHAR|NUMERIC|REAL|SERIAL|SET|SMALLINT|TEXT|TIME|TIMESTAMP|' . 'TINYBLOB|TINYTEXT|VARBINARY|YEAR|PRIMARY|AUTO INCREMENT)/i', "<span style='color:darkred'> \\1 </span>", $sql);
	$sql = preg_replace('/;/', "<b style='color: green;'> ;</b>", $sql);
//	$sql = preg_replace("/()/i", "<div style='color:'> \\1 </div>", $sql);		
	//$sql = stripslashes($sql);
	
	// output the Code
	
	$lines = explode('<br />', $sql);
	$total_lines = count($lines);
	
	
	echo 'Code:<code><br><table border="0" style="border-collapse: collapse" cellpadding="4" cellspacing="0"><tr>';
	echo '<td><table border="0" cellpadding="0" cellspacing="0"><tr><td><code style="color: #e28000;">';
    for ($ln = 0;$ln < $total_lines;$ln++) {
     echo ($ln+1)."<br />";
    }
	echo '</code></td></tr></table></td><td><table border="0" cellpadding="0" cellspacing="0"><tr><td><code class="sql">';
	 for ($hc = 0; $hc < $total_lines; $hc++){
		echo ($lines[$hc]).'<br />';
	}
	echo "</code></td></tr></table></td></tr></table><br>\n";

	
}
this is what i am parsing
create database site
select 'hi' FROM site DESC LIMIT 10
like WHERE `hi` = '$hi' = INT;
and here is my html output

Code: Select all

Code:<code><br></code><table style="border-collapse: collapse;" border="0" cellpadding="4" cellspacing="0"><tbody><tr><td><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td><code style="color: rgb(226, 128, 0);">1<br>2<br>3<br></code></td></tr></tbody></table></td><td><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td><code class="sql"><span style="color: blue;">create database </span>site
<br><span style="color: blue;">select </span></code><div><span> 'hi' </span></div> <span style="color: green;"> FROM </span>site <span style="color: green;"> DESC </span> <span style="color: green;"> LIMIT </span> 10 

<br><span style="color: orange;"> like </span> <span style="color: green;"> WHERE </span> <span style="color: red;"> `hi` </span> <span style="color: green;"> = </span> <div><span> '$hi' </span></div> <span style="color: green;"> = </span> <span style="color: darkred;"> INT </span><b style="color: green;"> ;</b>
as you can see, it ends the code tad after highlighting SELECT. i don't want it to do that
Post Reply