jakekoekemoer

Script as updated: 11/03/2014 Added: Better inline function. Added: Start of string Concat Added: Some extra string manipulation functions <?php ini_set('max_execution_time', 120); // 120 2 minutes // 180 3 minutes // 240 4 minutes // 300 5 minutes function convert_variable($data_size, $data_type) { if (strtolower($data_type) == 'identity') { $data_type == 'auto_incriment'; } if (strtolower($data_type) == 'smalldatetime') { $data_type == 'datetime'; } if (strtolower($data_type) == 'money') { $data_type == 'decimal(19,4)'; } if (strtolower($data_type) == 'small money') { $data_type == 'decimal(10,4)'; } if (strtolower($data_type) == 'uniqueidentifier') { $data_type == 'binary(16)'; } if (strtolower($data_type) == 'sysname') { $data_type == 'char(256)'; } if ($data_size !== '0') { if (strtolower($data_type) == "nvarchar") { if (intval($data_size) >= 255 || strtolower($data_size) == 'max') { return "text"; } elseif (intval($data_size) <= 254) { return "varchar($data_size)"; } } if (strtolower($data_type) == "varchar") { if (intval($data_size) >= 255 || strtolower($data_size) == 'max') { return "text"; } } } else { return false; } } function get_size_from_variable($var) { $open_bracket_pos = strpos($var, "("); $close_bracket_pos = strpos($var, ")"); // if it wasnt found then no size was spesified if ($open_bracket_pos == false || $close_bracket_pos == false ) { return array('0', $var); } else { // return the size $size = substr($var, $open_bracket_pos + 1, $close_bracket_pos - $open_bracket_pos - 1); $data_type = substr($var, 0, $open_bracket_pos); return array($size, $data_type); } } function contains($haystack, $needle, $matchcase = false) { if (!$matchcase) { if (stripos($haystack, $needle) !== false) { return true; } else { return false; } } if ($matchcase) { if (strpos($haystack, $needle) !== false) { return true; } else { return false; } } } function build_code_line_from_words($words) { $codeline = ''; foreach ($words as $word) { $codeline .= $word . " "; } return $codeline; } function break_code_line_to_words($codeline) { $words = explode(" ", $codeline); return $words; } function build_code_line_from_characters($characters) { $codeline = ''; foreach ($characters as $character) { $codeline .= $character; } return $codeline; } /**************************************************** *START THE ACTUAL CODE ***************************************************/ if (isset($_GET['output'])) { $output = $_GET['output']; } else { $output = "none"; } if (isset($_GET['mode'])) { $mode = $_GET['mode']; } else { $mode = "debug"; } if (isset($_GET['techinfo'])) { $techinfo = $_GET['techinfo']; } else { $techinfo = "show"; } echo " /*<br> MODE = $mode <br> OUTPUT = $output <br> SHOW TECHNICAL INFORMATION = $techinfo<br> */<br>"; $blank = ""; $instring = false; $needsbegin = false; $execute_found = false; $query = array(); $parameter_default_values = array(); // count of all if statements found $ifcount = 0; // count of all begins after an if has been found $ifsbegin = 0; $file = fopen('files/sp.sql', "r"); while (!feof($file)) { $line = fgets($file); if ($output == 'html') { array_push($query, trim($line) . "<br>"); } else { array_push($query, trim($line)); } } fclose($file); $codelines = count($query) - 1; $currentline = 0; if ($mode == 'debug') { echo "DELIMITER /// <br>"; } else { echo "DELIMITER /// \r\n"; } while ($currentline <= $codelines) { // get query line // SHOULD ALWAYS BE FIRST LINE IN WHILE $codeline = $query[$currentline]; // do prelim weird character replacements $arrsearch = array( "1" => "''", "2" => "=", "3" => " ", ); $arrreplacement = array( "1" => "┴", "2" => " = ", "3" => " ", ); $codeline = str_ireplace($arrsearch, $arrreplacement, $codeline); if ($currentline >= 1) { $prevcodeline = trim($query[$currentline - 1]); } else { $prevcodeline = ''; } if ($currentline < $codelines) { $nextcodeline = trim($query[$currentline + 1]); } else { $nextcodeline = ''; } //$codeline = str_ireplace("''", "┴", $codeline); $codeline = str_ireplace(" ", " ", $codeline); // break down line $characters = str_split($codeline); $words = break_code_line_to_words($codeline); $prevlinecharacters = str_split($prevcodeline); $prevlinewords = break_code_line_to_words($prevcodeline); $nextlinecharacters = str_split($nextcodeline); $nextlinewords = break_code_line_to_words($nextcodeline); // sized of break down $lastcharacter = count($characters) - 1; $lastword = count($words) - 1; $prevlastcharacter = count($prevlinecharacters) - 1; $prevlastword = count($prevlinewords) - 1; $nextlastcharacter = count($nextlinecharacters) - 1; $nextlastword = count($nextlinewords) - 1; /******************************************************************* * GET COMMENT *******************************************************************/ // checks if the line is a comment. If the whole line is commented out from the begining the line should be skipped if (strlen($codeline) >= 2) { if ($characters[0] . $characters[1] == '--') { $iscomment = true; if (isset($characters[2])) { if ($characters[2] !== " ") { $characters[1] .= " "; $words[0] = str_ireplace('--', '-- ', $words[0]); } } } else { $iscomment = false; } } /******************************************************************* * REPLACE NOCOUNT WITH COMMENT *******************************************************************/ if (stripos($codeline, "SET NOCOUNT ON") !== false) { //$codeline = str_ireplace("SET NOCOUNT ON","-- SET NOCOUNT ON",$codeline); $words[0] = "-- " . $words[0]; $iscomment = true; } // if it is a comment do nothing if (!$iscomment) { // add ')' to end of store proc declarations if (strtolower($codeline) == 'as begin' || (strtolower($codeline) == 'as' && strtolower($query[$currentline + 1]) == 'begin')) { $words[$lastword] = ') '; // . $words[$lastword]; $startparameters = false; $needsbegin = true; } /******************************************************************* * CHANGE ALTER TO CREATE *******************************************************************/ // checks for th words ALTER PROCEDURE or ALTER FUNCTION if (strtolower($words[0]) == 'alter' && (strtolower($words[1]) == 'procedure' || strtolower($words[1]) == 'function') ) { $words[0] = 'CREATE'; $words[$lastword] .= '('; $startparameters = true; } /******************************************************************* * DECLARE OF VARIABLES *******************************************************************/ // check if variable is being declared if (strtolower($words[0]) == 'declare') { // word 3 should be the data type // THE FIRST PART IS FOR IN SP VARIABLE DECLARAIONS $data_type = $words[2]; $data_size = get_size_from_variable($data_type); $in_var_declare = true; // if the data size is like VARCHAR(MAX) then the function will return datatype and size. else size will be 0 $size_return = get_size_from_variable($data_type); $data_size = $size_return[0]; $data_type = $size_return[1]; $converted_var = convert_variable($data_size, $data_type); if ($converted_var !== false) { $words[2] = convert_variable($data_size, $data_type); } if ($converted_var == false && $mode == 'debug') { //echo "<b>/* VARIABLE IS NOT CONVERTED */ </b>"; } // this statement will be true if the first character is a comma OR // declare has been called or its the start of the parameters } elseif ( ( // if first or last character is a comma ($characters[0] == ',' || $characters[$lastcharacter] == ',') || // or if the first and last character from this line isnt a comma but the last one IS // then return true ( ($characters[0] !== ',' || $characters[$lastcharacter] !== ',') && (substr($prevcodeline, strlen($prevcodeline) - 1, 1) == ',') ) ) && ($in_var_declare || $startparameters) ) { // word 2 should now be the data type // THE SECOND PART IS FOR IN SP PARAMETER DECLARATIONS $data_type = $words[1]; // if the data size is like VARCHAR(MAX) then the function will return datatype and size. else size will be 0 $size_return = get_size_from_variable($data_type); $data_size = $size_return[0]; $data_type = $size_return[1]; $converted_var = convert_variable($data_size, $data_type); if ($converted_var !== false) { $words[1] = convert_variable($data_size, $data_type); } } else { // its no longer declaring variables. // there isn't a declare or a comma after the declaration. $in_var_declare = false; } // variable declaration /******************************************************************* * CHECK IN STRING *******************************************************************/ if (contains($codeline, "'" && !$instring) ) { $quote_count = substr_count($codeline, "'"); if ($quote_count % 2 == 0) { // quote count is even. THERE FOR string does not carry over to another line $instring == false; } else { // quote count is not even there for string carries on to another line $instring == true; } }elseif (contains($codeline, "'" && $instring) ) { $quote_count = substr_count($codeline, "'"); if ($quote_count % 2 == 0) { $instring == true; } else { $instring == false; } } /******************************************************************* * ADD SEMI COLON ( *******************************************************************/ // LEAVE THIS DECLARATION HERE. LAST WORD IS REPLACED BY THE DATATYPE. // Add semi colon after declaration if (strtolower($nextlinewords[0]) == 'declare' && strlen($codeline) !== 0 && ($characters[0] == ',' || $prevlinecharacters[$prevlastcharacter] == ',') && !$instring ) { $words[$lastword] .= ';'; } // Add semi colon after declaration if (strtolower($nextlinewords[0]) !== 'declare' && strtolower($prevlinewords[0]) == 'declare' && strlen($codeline) !== 0 && $instring == false ) { $words[$lastword] .= ';'; } /******************************************************************* * GET ALL IF's IN STATEMENT *******************************************************************/ if (stripos($codeline, 'if') !== false) { $wordcounter = 0; $ifwords = array(); while ($wordcounter <= $lastword) { if (strtolower($words[$wordcounter]) == 'if') { $ifcount++; } if ($ifcount >= 1 && strtolower($words[$wordcounter]) == 'begin') { $ifsbegin++; array_push($ifwords, $wordcounter); } $wordcounter++; } if (count($ifwords >= 1)) { foreach ($ifwords as $wordpos) { $words[$wordpos] = 'then'; } } unset($ifwords); } /******************************************************************* * ADD THEN AFTER IF *******************************************************************/ if (stripos($codeline, 'begin') !== false && $ifcount >= 1) { $wordcounter = 0; while ($wordcounter <= $lastword) { if (strtolower($words[$wordcounter]) == 'begin') { $words[$wordcounter] = 'then'; } $wordcounter++; } } if (stripos($codeline, 'begin') !== false && $ifcount >= 1 && $instring == false ) { $wordcounter = 0; while ($wordcounter <= $lastword) { if (strtolower($words[$wordcounter]) == 'begin') { $words[$wordcounter] = "then"; } $wordcounter++; } } /******************************************************************* * GET PARAMETER DEFAULT VALUES *******************************************************************/ if (stripos($codeline, 'begin') !== false && $needsbegin == true ) { // loop through all parameter default values $alldefaults = ""; if ($startparameters == false) { foreach ($parameter_default_values as $defaultvalue) { if ($mode = "debug") { $alldefaults .= "<br> if " . $defaultvalue['var'] . " = NULL then <br> " . $defaultvalue['statement'] . "<br> end if; <br>"; } else { $alldefaults .= "\r\n if " . $defaultvalue['var'] . " = NULL then \r\n " . $defaultvalue['statement'] . "\r\n end if; \r\n"; } } $words[$lastword] .= $alldefaults; } $needsbegin = false; } /******************************************************************* * ADD END IF; IF THERE IS AN IF *******************************************************************/ if (stripos($codeline, 'end') !== false && $ifcount >= 1 && $instring == false ) { $wordcounter = 0; while ($wordcounter <= $lastword && $ifcount >= 1) { if (strtolower($words[$wordcounter]) == 'end') { if ($wordcounter == $lastword && strtolower($nextlinewords[0]) !== 'else') { $words[$wordcounter] = $words[$wordcounter] . " if;"; $ifcount--; } elseif ($wordcounter < $lastword && strtolower($words[$wordcounter + 1]) !== 'else') { $words[$wordcounter] = $words[$wordcounter] . " if;"; $ifcount--; } } $wordcounter++; } } /******************************************************************* * CLEAR DEFAULT VALUES FROM PARAMETER DECLARATION *******************************************************************/ if ($startparameters == true && strpos($codeline, "=") !== false) { // add to list of default parameters if ($characters[$lastcharacter] == ',') { $codeline = substr($codeline, 0, strlen($codeline) - 1); } array_push($parameter_default_values, array("var" => $words[0], "statement" => "set $codeline;")); // remove default value from declaration // init loop variables $equalfound = false; $wordcounter = 0; while ($wordcounter <= $lastword) { if ($words[$wordcounter] == '=') { $equalfound = true; } if ($equalfound) { if (strpos($words[$wordcounter], ',') !== false) $words[$wordcounter] = ','; else $words[$wordcounter] = ''; } $wordcounter++; } } /******************************************************************* * REPLACE TOP n ROWS WITH limit n *******************************************************************/ if (isset($limitofquery)) { if (contains($codeline, "order by")) { if ($characters[$lastcharacter] !== ',' || $nextlinecharacters[0] !== ',') { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } elseif (contains($codeline, "group by") && contains($nextcodeline, "order by") == false) { if ($characters[$lastcharacter] !== ',' || $nextlinecharacters[0] !== ',') { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } elseif (contains($codeline, "where") && contains($nextcodeline, "order by") == false && contains($nextcodeline, "group by") == false ) { if ( (strtolower($words[$lastword]) !== 'and' && strtolower($words[$lastword]) !== 'or' ) && (strtolower($nextlinewords[0]) !== 'and' && strtolower($nextlinewords[0]) !== 'or' ) ) { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } elseif ( ( strtolower($prevlinewords[$prevlastword]) == 'and' || strtolower($prevlinewords[$prevlastword]) == 'or' || strtolower($words[0]) == 'and' || strtolower($words[0]) == 'or' ) && strtolower($words[$lastword]) !== 'and' && strtolower($words[$lastword]) !== 'or' && strtolower($nextlinewords[0]) !== 'and' && strtolower($nextlinewords[0]) !== 'or' && contains($nextcodeline, "order by") == false && contains($nextcodeline, "group by") == false ) { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } elseif (contains($codeline, "from") && contains($nextcodeline, "where") == false) { if ($characters[$lastcharacter] !== ',' || $nextlinecharacters[0] !== ',') { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } } /******************************************************************* * REPLACE ISNULL WITH IFNULL * REPLACE SELECT INTO WITH SELECT *******************************************************************/ $wordcounter = 0; while ($wordcounter <= $lastword) { // isnull(a,b) if (stripos($words[$wordcounter], 'ISNULL(') !== false) { $words[$wordcounter] = str_ireplace("ISNULL(", "ifnull(", $words[$wordcounter]); } // select into var = string if (strtolower($words[$wordcounter]) == 'select' && strtolower($words[$wordcounter + 1]) == 'into') { $words[$wordcounter + 1] = ''; } // getdate() if (stripos($words[$wordcounter], 'getdate()') !== false) { $words[$wordcounter] = str_ireplace("getdate()", "now()", $words[$wordcounter]); } // top n rows if (stripos($words[$wordcounter], 'top') !== false) { $words[$wordcounter] = ''; $limitofquery = $words[$wordcounter + 1]; $words[$wordcounter + 1] = ''; } $wordcounter++; } /******************************************************************* * PROCESS DYNAMIC SQL WITH [NO OUTPUT] *******************************************************************/ if (contains($codeline, "sp_executesql")) { $execute_found = true; } if ( ( strlen($nextcodeline) == 0 || $nextcodeline[0] == " " ) && $execute_found == true ) { } /******************************************************************* * PROCESS STRING CONCATENATION *******************************************************************/ if ($instring) { } } // not is comment // create final output string $finalline = build_code_line_from_words($words); // do final weird character replacements $arrsearch = array( "1" => "[dbo].[", "2" => "]", "3" => "┴", ); $arrreplacement = array( "1" => "dbo.", "2" => $blank, "3" => "''", ); $finalline = str_ireplace($arrsearch, $arrreplacement, $finalline); // final processing before output $query[$currentline] = $finalline; $codeline = $finalline; if ($mode == 'debug') echo $codeline . "<br>"; // increment current line $currentline++; } if ($mode == 'debug') { echo "/// <br> DELIMITER ;"; } else { echo "/// \r\n DELIMITER ;"; }

Do you use an HTML form to do post back or do you use javascript ? Either of them make sure that the method you are using is in fact "POST" and not "GET" they work differently in php. If you feel that the data should be there you can even go as far as checking $_REQUEST[' name of component in form or in query thing ']

I would suggest you start by looking at AJAX to do that. Something to the effect of: <script> function showUser(str) { if (str == "") { $("hoverbar").innerHTML = ""; return; } var postrequest; postrequest = $.post("menulookuptest.php", {q: str}).done(function () { $("hoverbar").innerHTML = result; return; }).fail(function () { alert("an error occured"); }) } </script> as far as the php code goed its a good idea to rather do it like this: <?php $q = $_GET['q'] or die('REQUEST NOT HANDLES'); $con = mysqli_connect("localhost", "root", "PASS", "DBNAME") or die('CANNOT CONNECT TO DB'); mysqli_select_db($con, "ajax_demo") or die('DATABASE NOT SELECTED'); $sql = "SELECT pagetitle, pagedept FROM intranetpage WHERE pagedept = '" . addslashes($q) . "'"; $result = mysqli_query($con, $sql); $finalstring = ''; while ($row = mysqli_fetch_array($result)) { $finalstring .= $row['pagetitle'].'<br>'; } die($finalstring); ?> As far as the code goes I cant see what could make it take long, make your javascript load async. I would even go as far as using ajax for the hover events of the div using $("id").hover(function(){ showUser("id"); })

Hi everybody. This script is not only for people who want to contribute. The company I work for recently embarked on writing a php script to convert from mssql to mysql. I want to share it because NO ONE online has ever posted such a script. So currently the script is very basic , and i will update it as much as possible. This script does the following: Convert Datatypes Checks and fixes IF statements Converts parameters Changes getdate() to NOW() Changes select into Changes ISNULL Changes Top n rows to LIMIT n if any one can improve or add something please feel free to do so. This code is intended as freeware <?php ini_set('max_execution_time', 120); // 120 2 minutes // 180 3 minutes // 240 4 minutes // 300 5 minutes function convert_variable($data_size, $data_type) { if (strtolower($data_type) == 'identity') { $data_type == 'auto_incriment'; } if (strtolower($data_type) == 'smalldatetime') { $data_type == 'datetime'; } if (strtolower($data_type) == 'money') { $data_type == 'decimal(19,4)'; } if (strtolower($data_type) == 'small money') { $data_type == 'decimal(10,4)'; } if (strtolower($data_type) == 'uniqueidentifier') { $data_type == 'binary(16)'; } if (strtolower($data_type) == 'sysname') { $data_type == 'char(256)'; } if ($data_size !== '0') { if (strtolower($data_type) == "nvarchar") { if (intval($data_size) >= 255 || strtolower($data_size) == 'max') { return "text"; } elseif (intval($data_size) <= 254) { return "varchar($data_size)"; } } if (strtolower($data_type) == "varchar") { if (intval($data_size) >= 255 || strtolower($data_size) == 'max') { return "text"; } } } else { return false; } } function get_size_from_variable($var) { $open_bracket_pos = strpos($var, "("); $close_bracket_pos = strpos($var, ")"); // if it wasnt found then no size was spesified if ($open_bracket_pos == false || $close_bracket_pos == false ) { return array('0', $var); } else { // return the size $size = substr($var, $open_bracket_pos + 1, $close_bracket_pos - $open_bracket_pos - 1); $data_type = substr($var, 0, $open_bracket_pos); return array($size, $data_type); } } function contains($haystack, $needle, $matchcase = false) { if (!$matchcase) { if (stripos($haystack, $needle) !== false) { return true; } else { return false; } } if ($matchcase) { if (strpos($haystack, $needle) !== false) { return true; } else { return false; } } } $query = array(); $parameter_default_values = array(); if (isset($_GET['output'])) { $output = $_GET['output']; } else { $output = "none"; } if (isset($_GET['mode'])) { $mode = $_GET['mode']; } else { $mode = "debug"; } if (isset($_GET['techinfo'])) { $techinfo = $_GET['techinfo']; } else { $techinfo = "show"; } /* $output = "none"; $mode = "debug"; $techinfo = "show"; */ echo " /*<br> MODE = $mode <br> OUTPUT = $output <br> SHOW TECHNICAL INFORMATION = $techinfo <br> */ <br> <br> <br> <br> <br>"; $blank = ""; $instring = false; $needsbegin = false; // count of all if statements found $ifcount = 0; // count of all begins after an if has been found $ifsbegin = 0; $file = fopen('files/sp.sql', "r"); while (!feof($file)) { $line = fgets($file); if ($output == 'html') { array_push($query, trim($line) . "<br>"); } else { array_push($query, trim($line)); } } fclose($file); $codelines = count($query) - 1; $currentline = 0; if ($mode == 'debug') { echo "DELIMITER /// <br>"; } else { echo "DELIMITER /// \r\n"; } while ($currentline <= $codelines) { // get query line // SHOULD ALWAYS BE FIRST LINE IN WHILE $codeline = $query[$currentline]; // do prelim weird character replacements $arrsearch = array( "1" => "''", "2" => "=", "3" => " ", ); $arrreplacement = array( "1" => "┴", "2" => " = ", "3" => " ", ); $codeline = str_ireplace($arrsearch, $arrreplacement, $codeline); if ($currentline >= 1) { $prevcodeline = trim($query[$currentline - 1]); } else { $prevcodeline = ''; } if ($currentline < $codelines) { $nextcodeline = trim($query[$currentline + 1]); } else { $nextcodeline = ''; } //$codeline = str_ireplace("''", "┴", $codeline); $codeline = str_ireplace(" ", " ", $codeline); // break down line $characters = str_split($codeline); $words = explode(" ", $codeline); $prevlinecharacters = str_split($prevcodeline); $prevlinewords = explode(" ", $prevcodeline); $nextlinecharacters = str_split($nextcodeline); $nextlinewords = explode(" ", $nextcodeline); // sized of break down $lastcharacter = count($characters) - 1; $lastword = count($words) - 1; $prevlastcharacter = count($prevlinecharacters); $prevlastword = count($prevlinewords) - 1; $nextlastcharacter = count($nextlinecharacters); $nextlastword = count($nextlinewords) - 1; /******************************************************************* * GET COMMENT *******************************************************************/ // checks if the line is a comment. If the whole line is commented out from the begining the line should be skipped if (strlen($codeline) >= 2) { if ($characters[0] . $characters[1] == '--') { $iscomment = true; if (isset($characters[2])) { if ($characters[2] !== " ") { $characters[1] .= " "; $words[0] = str_ireplace('--', '-- ', $words[0]); } } } else { $iscomment = false; } } /******************************************************************* * REPLACE NOCOUNT WITH COMMENT *******************************************************************/ if (stripos($codeline, "SET NOCOUNT ON") !== false) { //$codeline = str_ireplace("SET NOCOUNT ON","-- SET NOCOUNT ON",$codeline); $words[0] = "-- " . $words[0]; $iscomment = true; } // if it is a comment do nothing if (!$iscomment) { // add ')' to end of store proc declarations if (strtolower($codeline) == 'as begin' || (strtolower($codeline) == 'as' && strtolower($query[$currentline + 1]) == 'begin')) { $words[$lastword] = ') '; // . $words[$lastword]; $startparameters = false; $needsbegin = true; } /******************************************************************* * CHANGE ALTER TO CREATE *******************************************************************/ // checks for th words ALTER PROCEDURE or ALTER FUNCTION if (strtolower($words[0]) == 'alter' && (strtolower($words[1]) == 'procedure' || strtolower($words[1]) == 'function') ) { $words[0] = 'CREATE'; $words[$lastword] .= '('; $startparameters = true; } /******************************************************************* * DECLARE OF VARIABLES *******************************************************************/ // check if variable is being declared if (strtolower($words[0]) == 'declare') { // word 3 should be the data type // THE FIRST PART IS FOR IN SP VARIABLE DECLARAIONS $data_type = $words[2]; $data_size = get_size_from_variable($data_type); $in_var_declare = true; // if the data size is like VARCHAR(MAX) then the function will return datatype and size. else size will be 0 $size_return = get_size_from_variable($data_type); $data_size = $size_return[0]; $data_type = $size_return[1]; $converted_var = convert_variable($data_size, $data_type); if ($converted_var !== false) { $words[2] = convert_variable($data_size, $data_type); } if ($converted_var == false && $mode == 'debug') { //echo "<b>/* VARIABLE IS NOT CONVERTED */ </b>"; } // this statement will be true if the first character is a comma OR // declare has been called or its the start of the parameters } elseif ( ( // if first or last character is a comma ($characters[0] == ',' || $characters[$lastcharacter] == ',') || // or if the first and last character from this line isnt a comma but the last one IS // then return true ( ($characters[0] !== ',' || $characters[$lastcharacter] !== ',') && (substr($prevcodeline, strlen($prevcodeline) - 1, 1) == ',') ) ) && ($in_var_declare || $startparameters) ) { // word 2 should now be the data type // THE SECOND PART IS FOR IN SP PARAMETER DECLARATIONS $data_type = $words[1]; // if the data size is like VARCHAR(MAX) then the function will return datatype and size. else size will be 0 $size_return = get_size_from_variable($data_type); $data_size = $size_return[0]; $data_type = $size_return[1]; $converted_var = convert_variable($data_size, $data_type); if ($converted_var !== false) { $words[1] = convert_variable($data_size, $data_type); } } else { // its no longer declaring variables. // there isn't a declare or a comma after the declaration. $in_var_declare = false; } // variable declaration /******************************************************************* * CHECK IN STRING *******************************************************************/ if (stripos($codeline, "'") !== false && $instring == false ) { // check if string doenst also end in this line $charcount = stripos($codeline, "'"); // its in a string $instring = true; if ($mode == "debug" && $techinfo == "show") { echo "<b>instring set to true;</b>"; } // if another ' is found in the same line it will be unset from being in a string while ($charcount <= $lastcharacter && $charcount !== $lastcharacter) { if ($characters[$charcount] == "'") { $instring = false; if ($mode == "debug" && $techinfo == "show") { echo "<b>instring set to false;</b>"; } } $charcount++; } } elseif ($instring == true && stripos($codeline, "'")) { // check if string doenst also end in this line $charcount = stripos($codeline, "'"); // its in a string $instring = false; // if another ' is found in the same line it will be unset from being in a string. while ($charcount <= $lastcharacter) { if ($characters[$charcount] == "'") { $instring = true; } $charcount++; } } /******************************************************************* * ADD SEMI COLON ( *******************************************************************/ // LEAVE THIS DECLARATION HERE. LAST WORD IS REPLACED BY THE DATATYPE. // Add semi colon after declaration if (strtolower($nextlinewords[0]) == 'declare' && strlen($codeline) !== 0 && ($characters[0] = ',' || $prevlinecharacters[$prevlastcharacters] == ',') && $instring == false ) { $words[$lastword] .= ';'; } // Add semi colon after declaration if (strtolower($nextlinewords[0]) !== 'declare' && strtolower($prevlinewords[0]) == 'declare' && strlen($codeline) !== 0 && $instring == false ) { $words[$lastword] .= ';'; } /******************************************************************* * GET ALL IF's IN STATEMENT *******************************************************************/ if (stripos($codeline, 'if') !== false) { $wordcounter = 0; $ifwords = array(); while ($wordcounter <= $lastword) { if (strtolower($words[$wordcounter]) == 'if') { $ifcount++; } if ($ifcount >= 1 && strtolower($words[$wordcounter]) == 'begin') { $ifsbegin++; array_push($ifwords, $wordcounter); } $wordcounter++; } if (count($ifwords >= 1)) { foreach ($ifwords as $wordpos) { $words[$wordpos] = 'then'; } } unset($ifwords); } /******************************************************************* * ADD THEN AFTER IF *******************************************************************/ if (stripos($codeline, 'begin') !== false && $ifcount >= 1) { $wordcounter = 0; while ($wordcounter <= $lastword) { if (strtolower($words[$wordcounter]) == 'begin') { $words[$wordcounter] = 'then'; } $wordcounter++; } } if (stripos($codeline, 'begin') !== false && $ifcount >= 1 && $instring == false ) { $wordcounter = 0; while ($wordcounter <= $lastword) { if (strtolower($words[$wordcounter]) == 'begin') { $words[$wordcounter] = "then"; } $wordcounter++; } } /******************************************************************* * GET PARAMETER DEFAULT VALUES *******************************************************************/ if (stripos($codeline, 'begin') !== false && $needsbegin == true ) { // loop through all parameter default values $alldefaults = ""; if ($startparameters == false) { foreach ($parameter_default_values as $defaultvalue) { if ($mode = "debug") { $alldefaults .= "<br> if " . $defaultvalue['var'] . " = NULL then <br> " . $defaultvalue['statement'] . "<br> end if; <br>"; } else { $alldefaults .= "\r\n if " . $defaultvalue['var'] . " = NULL then \r\n " . $defaultvalue['statement'] . "\r\n end if; \r\n"; } } $words[$lastword] .= $alldefaults; } $needsbegin = false; } /******************************************************************* * ADD END IF; IF THERE IS AN IF *******************************************************************/ if (stripos($codeline, 'end') !== false && $ifcount >= 1 && $instring == false ) { $wordcounter = 0; while ($wordcounter <= $lastword && $ifcount >= 1) { if (strtolower($words[$wordcounter]) == 'end') { if ($wordcounter == $lastword && strtolower($nextlinewords[0]) !== 'else') { $words[$wordcounter] = $words[$wordcounter] . " if;"; $ifcount--; } elseif ($wordcounter < $lastword && strtolower($words[$wordcounter + 1]) !== 'else') { $words[$wordcounter] = $words[$wordcounter] . " if;"; $ifcount--; } } $wordcounter++; } } /******************************************************************* * CLEAR DEFAULT VALUES FROM PARAMETER DECLARATION *******************************************************************/ if ($startparameters == true && strpos($codeline, "=") !== false) { // add to list of default parameters if ($characters[$lastcharacter] == ',') { $codeline = substr($codeline, 0, strlen($codeline) - 1); } array_push($parameter_default_values, array("var" => $words[0], "statement" => "set $codeline;")); // remove default value from declaration // init loop variables $equalfound = false; $wordcounter = 0; while ($wordcounter <= $lastword) { if ($words[$wordcounter] == '=') { $equalfound = true; } if ($equalfound) { if (strpos($words[$wordcounter], ',') !== false) $words[$wordcounter] = ','; else $words[$wordcounter] = ''; } $wordcounter++; } } /******************************************************************* * REPLACE TOP n ROWS WITH limit n *******************************************************************/ if (isset($limitofquery)) { if (contains($codeline, "order by")) { if ($characters[$lastcharacter] !== ',' || $nextlinecharacters[0] !== ',') { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } elseif (contains($codeline, "group by") && contains($nextcodeline, "order by") == false) { if ($characters[$lastcharacter] !== ',' || $nextlinecharacters[0] !== ',') { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } elseif (contains($codeline, "where") && contains($nextcodeline, "order by") == false && contains($nextcodeline, "group by") == false ) { if ( (strtolower($words[$lastword]) !== 'and' && strtolower($words[$lastword]) !== 'or' ) && (strtolower($nextlinewords[0]) !== 'and' && strtolower($nextlinewords[0]) !== 'or' ) ) { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } elseif ( ( strtolower($prevlinewords[$prevlastword]) == 'and' || strtolower($prevlinewords[$prevlastword]) == 'or' || strtolower($words[0]) == 'and' || strtolower($words[0]) == 'or' ) && strtolower($words[$lastword]) !== 'and' && strtolower($words[$lastword]) !== 'or' && strtolower($nextlinewords[0]) !== 'and' && strtolower($nextlinewords[0]) !== 'or' && contains($nextcodeline, "order by") == false && contains($nextcodeline, "group by") == false ) { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } elseif (contains($codeline, "from") && contains($nextcodeline, "where") == false) { if ($characters[$lastcharacter] !== ',' || $nextlinecharacters[0] !== ',') { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } } /******************************************************************* * REPLACE ISNULL WITH IFNULL * REPLACE SELECT INTO WITH SELECT *******************************************************************/ $wordcounter = 0; while ($wordcounter <= $lastword) { // isnull(a,b) if (stripos($words[$wordcounter], 'ISNULL(') !== false) { $words[$wordcounter] = str_ireplace("ISNULL(", "ifnull(", $words[$wordcounter]); } // select into var = string if (strtolower($words[$wordcounter]) == 'select' && strtolower($words[$wordcounter + 1]) == 'into') { $words[$wordcounter + 1] = ''; } // getdate() if (stripos($words[$wordcounter], 'getdate()') !== false) { $words[$wordcounter] = str_ireplace("getdate()", "now()", $words[$wordcounter]); } // top n rows if (stripos($words[$wordcounter], 'top') !== false) { $words[$wordcounter] = ''; $limitofquery = $words[$wordcounter + 1]; $words[$wordcounter + 1] = ''; } $wordcounter++; } } // not is comment // create final output string $finalline = ''; foreach ($words as $word) { $finalline .= $word . " "; } // do final weird character replacements $arrsearch = array( "1" => "[dbo].[", "2" => "]", "3" => "┴", ); $arrreplacement = array( "1" => "dbo.", "2" => $blank, "3" => "''", ); $finalline = str_ireplace($arrsearch, $arrreplacement, $finalline); // final processing before output $query[$currentline] = $finalline; $codeline = $finalline; if ($mode == 'debug') echo $codeline . "<br>"; // increment current line $currentline++; } if ($mode == 'debug') { echo "/// <br> DELIMITER ;"; } else { echo "/// \r\n DELIMITER ;"; }