Magictallguy Posted April 9, 2013 Posted April 9, 2013 (edited) The topic title speaks for itself. I offer MySQL to MySQLi conversion for any number of files, absolutely free - whilst you wait! My personal site, and my game engine are both running on purely MySQLi, and the only issues I've had are my own lazy-badly formed queries! There are tools online available, so you don't have to use my services, but if you don't want to go through the trouble of either getting the tools or converting it yourself, speak to me! Keep in mind, I can only work with actual code, nothing obfuscated! If you're interested, give me a shout on Skype! magic_tallguy My PDO wrapper is now available with a short tutorial on how to use it. Bear Edited February 28, 2019 by Magictallguy 1 Quote
a_bertrand Posted April 9, 2013 Posted April 9, 2013 Well converting from one lib to the other is one thing but how do you do it? Prepare statement? Or real_escape? Dare to show an example? Quote
Magictallguy Posted April 9, 2013 Author Posted April 9, 2013 (edited) Using MySQL's own Converter! Admittedly, it's not perfect (for example, mysql_result() doesn't have a MySQLi equivalent, but that's fine, I've got a workaround for that.) Now, obviously, you can download the files from MySQL and install it yourself, convert your own code, etc.. I simply remove those steps and do it all for you. Generally, within 15 minutes (family to time ratio providing!) Edited April 9, 2013 by Magictallguy Forgot to close my bracket! Quote
a_bertrand Posted April 10, 2013 Posted April 10, 2013 Well, I doubt it transform the original queries to prepared statements right? And for me that should be done to avoid SQL injection for example. Quote
Magictallguy Posted April 10, 2013 Author Posted April 10, 2013 It doesn't change the query itself, just the function it uses. For example: <?php //Assume you're already connected using the variable $conn as your connection ID mysql_query("Some query", $conn) or die(mysql_error()); ?> Would become <?php mysqli_query( $conn, "Some query") or die(((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false))); ?> As I've stated previously, it can't convert mysql_result, as there is no mysqli_ equivalent, but you can use a mysqli_data_seek Quote
Magictallguy Posted April 10, 2013 Author Posted April 10, 2013 And I've just clicked onto what you meant lol, I'll experiment with MySQL's prepared statements, and see what it pulls back Quote
Magictallguy Posted April 10, 2013 Author Posted April 10, 2013 Had a little bit of free time, and I've tested it - works fine :) Quote
sniko Posted April 10, 2013 Posted April 10, 2013 Had a little bit of free time, and I've tested it - works fine :) Can you provide an example? Quote
Script47 Posted April 10, 2013 Posted April 10, 2013 Can you provide an example? You could just download it and test it? Just saying. :) http://dev.mysql.com/get/Downloads/Contrib/mysqli_converter/mysqli_converter-1.0.zip/from/pick Quote
Magictallguy Posted April 10, 2013 Author Posted April 10, 2013 Can you provide an example? I can, bear with me, just got back home from my fiancé's mums! Gotta shake off the "Meet the Parents" factor lol Quote
Zettieee Posted April 11, 2013 Posted April 11, 2013 MTG converted my whole game in the time it took me to make coffee :) + he converted my classes.php (GRPG) to a more suitable class construction Good service all round. :) Quote
Magictallguy Posted April 11, 2013 Author Posted April 11, 2013 MTG converted my whole game in the time it took me to make coffee :) + he converted my classes.php (GRPG) to a more suitable class construction Good service all round. :) Thank you :) Quote
The Spirit Posted April 12, 2013 Posted April 12, 2013 Good work Magictallguy Heres a good tutorial/ explanation for anyone who wants to know more about mysqli http://codular.com/php-mysqli Quote
Spudinski Posted April 13, 2013 Posted April 13, 2013 It doesn't change the query itself, just the function it uses. For example: <?php //Assume you're already connected using the variable $conn as your connection ID mysql_query("Some query", $conn) or die(mysql_error()); ?> Would become <?php mysqli_query( $conn, "Some query") or die(((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false))); ?> As I've stated previously, it can't convert mysql_result, as there is no mysqli_ equivalent, but you can use a mysqli_data_seek OMG! No,no,no,no.... MY EYES, IT BURNSSS!!! Quote
Magictallguy Posted April 13, 2013 Author Posted April 13, 2013 (edited) OMG! No,no,no,no.... MY EYES, IT BURNSSS!!! Haha, that was a standard query, with no real connection identifier - when used in a class, it is much, much more efficient Edited November 19, 2013 by Magictallguy Spelling correction Quote
Dominion Posted April 13, 2013 Posted April 13, 2013 What advantages does mysqli have in the way you're converting it? Quote
Magictallguy Posted April 13, 2013 Author Posted April 13, 2013 The fact that it's MySQLImproved? I suppose, the only advantages I can think of are that the conversion tool I use is by MySQL themselves, suggesting that their code structure must be to standard (don't quote me on that) Quote
Spudinski Posted April 14, 2013 Posted April 14, 2013 The fact that it's MySQLImproved? I suppose, the only advantages I can think of are that the conversion tool I use is by MySQL themselves, suggesting that their code structure must be to standard (don't quote me on that) There are a few things that are advantageous. Someone on MySQL discussion forums(note: not Oracle/MySQL AB) offering some "str_replace() script" is definitely not one of them. P.S. My previous post holds no relation to the query itself, it's the code(GLOBAL me) that is wrong. Quote
Magictallguy Posted April 14, 2013 Author Posted April 14, 2013 Obviously, that can be changed to a simply $connectionID, or equivalent. Quote
Danny696 Posted March 4, 2014 Posted March 4, 2014 So are you using prepared statement now or not, if so can we see an example please... Quote
Magictallguy Posted March 5, 2014 Author Posted March 5, 2014 So are you using prepared statement now or not, if so can we see an example please... I'm not at the moment. I'm quite happy with mysqli_* procedural style functions. I'll convert my database abstraction layer to PDO and post up if requested :) Quote
Magictallguy Posted February 28, 2019 Author Posted February 28, 2019 As promised, before MWG went down, one PDO wrapper!  <?php /* * DON'T BE A DICK PUBLIC LICENSE * Everyone is permitted to copy and distribute verbatim or modified copies of this license document, and changing it is allowed as long as the name is changed. * * DON'T BE A DICK PUBLIC LICENSE TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION * * Do whatever you like with the original work, just don't be a dick. * * Being a dick includes - but is not limited to - the following instances: * * 1a. Outright copyright infringement - Don't just copy this and change the name. * 1b. Selling the unmodified original with no work done what-so-ever, that's REALLY being a dick. * 1c. Modifying the original work to contain hidden harmful content. That would make you a PROPER dick. * * If you become rich through modifications, related works/services, or supporting the original work, share the love. Only a dick would make loads off this work and not buy the original works creator(s) a pint. * * Code is provided with no warranty. Using somebody else's code and bitching when it goes wrong makes you a DONKEY dick. Fix the problem yourself. A non-dick would submit the fix back. */ // Basic direct access prevention. Define GAME_ENABLE somewhere before this file is included // Uses definitions as database connection details. /* define('DB_HOST', 'localhost'); define('DB_USER', 'root'); define('DB_PASS', 's0meP4ss!'); define('DB_NAME', 'my_db'); */ if (!defined('GAME_ENABLE')) { exit; } class pdo_db extends PDO { protected $last_query; protected $execution; protected $rowsFromFetch; protected $resultFromFetch; protected $resultFromObject; protected $activeTransaction = false; protected $conn; protected $rowCount = 0; protected $transactionCounter = 0; private static $host = DB_HOST; private static $user = DB_USER; private static $pass = DB_PASS; private static $name = DB_NAME; private $db; private $stmt; public static $inst = null; public $binds = []; public $queryCount = 0; public static function getInstance() { if (null == self::$inst) { self::$inst = new self(); } return self::$inst; } public function __construct() { mb_internal_encoding('UTF-8'); mb_regex_encoding('UTF-8'); try { $opts = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT => false, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => 0, PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4;', PDO::MYSQL_ATTR_INIT_COMMAND => 'SET CHARACTER SET utf8mb4;', PDO::MYSQL_ATTR_INIT_COMMAND => 'SET SQL_BIG_SELECTS = 1;', PDO::MYSQL_ATTR_INIT_COMMAND => 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED;', ]; $this->db = new PDO('mysql:host='.static::$host.';dbname='.static::$name.';charset=utf8', static::$user, static::$pass, $opts); } catch (PDOException $e) { if (strpos($e->getMessage(), 'No such file or directory')) { exit('Database disconnected'); } throw new Exception('CONSTRUCT ERROR'."\n".$e->getMessage()); } } public function __destruct() { if (!$this->db) { return null; } $this->db = null; return null; } /** * @param string $query */ public function query($query) { $this->last_query = $query; ++$this->queryCount; try { $this->stmt = $this->db->prepare($query); } catch (PDOException $e) { throw new Exception('QUERY ERROR'."\n".$e->getMessage()."\nQuery was: ".$this->last_query); } } public function bind($param, $value, $type = null) { if (is_null($type)) { switch (true) { case is_int($value): $type = PDO::PARAM_INT; break; case is_bool($value): $type = PDO::PARAM_BOOL; break; case is_null($value): $type = PDO::PARAM_NULL; break; default: $type = PDO::PARAM_STR; break; } } else { switch ($type) { case 'int': $type = PDO::PARAM_INT; break; case 'float': $type = PDO::PARAM_FLOAT; break; case 'str': case 'string': $type = PDO::PARAM_STR; break; case 'null': $type = PDO::PARAM_NULL; break; case 'bool': $type = PDO::PARAM_BOOL; break; } } try { $this->stmt->bindValue($param, $value, $type); } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); throw new Exception('BIND ERROR'."\n".$e->getMessage()); } } public function execute(array $binds = null) { $this->binds = $binds; if (!isset($this->stmt)) { $this->execution = false; } else { try { $this->execution = is_array($binds) && count($binds) > 0 ? $this->stmt->execute($binds) : $this->stmt->execute(); } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); throw new Exception('EXECUTION ERROR'."\n".$e->getMessage()."\nQuery was: ".$this->last_query); var_dump($this->stmt->debugDumpParams()); echo '</pre></p>'; exit(); } } return $this->execution; } public function fetch($shift = false) { if (!isset($this->stmt)) { $this->rowsFromFetch = false; } else { try { $this->execute(); $this->rowsFromFetch = $this->stmt->fetchAll(PDO::FETCH_ASSOC); if ($shift) { $this->rowsFromFetch = array_shift($this->rowsFromFetch); } } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); throw new Exception('FETCH ROW ERROR'."\n".$e->getMessage()); } } if (false === $this->rowsFromFetch || (is_array($this->rowsFromFetch) && !count($this->rowsFromFetch)) || !$this->rowsFromFetch) { return false; } $this->stmt->closeCursor(); return $this->rowsFromFetch; } public function fetchKey($shift = false) { if (!isset($this->stmt)) { $this->rowsFromFetch = false; } else { try { $this->execute(); $this->rowsFromFetch = $this->stmt->fetchAll(PDO::FETCH_BOTH); if ($shift) { $this->rowsFromFetch = array_shift($this->rowsFromFetch); } } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); throw new Exception('FETCH ROW ERROR'."\n".$e->getMessage()); } } if (false === $this->rowsFromFetch || (is_array($this->rowsFromFetch) && !count($this->rowsFromFetch)) || !$this->rowsFromFetch) { return false; } $this->stmt->closeCursor(); return $this->rowsFromFetch; } public function result($col = 0, $asBool = false) { if (true === $col) { $asBool = true; $col = 0; } if (!isset($this->stmt)) { $this->resultFromFetch = null; } else { try { $this->execute(); $this->resultFromFetch = $this->stmt->fetchColumn($col); } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); throw new Exception('FETCH SINGLE ERROR'."\n".$e->getMessage()); } } $ret = $this->resultFromFetch ? $this->resultFromFetch : 0; $this->stmt->closeCursor(); return true === $asBool ? (bool) $ret : $ret; } public function fetch_object() { if (!isset($this->stmt)) { $this->resultFromFetch = null; } else { try { $this->execute(); $this->resultFromObject = $this->stmt->fetch(PDO::FETCH_OBJ); } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); throw new Exception('FETCH OBJECT ERROR'."\n".$e->getMessage()); } } if (!count($this->resultFromFetch)) { return false; } $this->stmt->closeCursor(); return $this->resultFromFetch; } public function affected() { try { return $this->stmt->rowCount(); } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); throw new Exception('AFFECTED ROWS ERROR'."\n".$e->getMessage()); } } public function count($asBool = false) { if (is_array($this->resultFromFetch)) { $this->rowCount = count($this->resultFromFetch); } else { try { $this->rowCount = $this->stmt->fetchColumn(); } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); throw new Exception('NUM ROWS ERROR'."\n".$e->getMessage()); } } $ret = $this->rowCount > 0 ? $this->rowCount : 0; $this->stmt->closeCursor(); return true === $asBool ? (bool) $ret : $ret; } public function insert_id() { try { return $this->db->lastInsertId(); } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); throw new Exception('LAST INSERT ID ERROR'."\n".$e->getMessage()); } } public function colCnt() { try { return $this->stmt->columnCount(); } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); throw new Exception('COLUMN COUNT ERROR'."\n".$e->getMessage()); } } public function query_error() { global $session; $id = array_key_exists('userid', $_SESSION) && ctype_digit($_SESSION['userid']) && $_SESSION['userid'] > 0 ? $_SESSION['userid'] : 0; // ADMIN_IDS is a definition within my configuration file. // It contains, as you'd probably expect, an array of the account IDs of the administrators. Example: // define('ADMIN_IDS', [1, 2]); if (in_array($id, ADMIN_IDS)) { exit('<strong>QUERY ERROR:</strong> '.$this->error.'<br>Query was '.$this->last_query); } else { exit('An error has been detected'); } } public function trans($action) { try { if ('start' == $action) { if (!$this->transactionCounter++) { return $this->db->beginTransaction(); } $this->db->exec('SAVEPOINT trans'.$this->transactionCounter); return $this->transactionCounter >= 0; } elseif ('end' == $action) { if (!--$this->transactionCounter) { return $this->db->commit(); } return $this->transactionCounter >= 0; } else { if (--$this->transactionCounter) { $this->db->exec('ROLLBACK TO trans'.($this->transactionCounter + 1)); return true; } return $this->db->rollback(); } } catch (PDOException $e) { error_log($e->getMessage()); $this->slackenMe($e->getMessage()); exit('<strong>'.strtoupper($action).' TRANSACTION ERROR:</strong> '.$e->getMessage()); } } public function error() { var_dump($this->stmt->debugDumpParams()); } // Helper function(s) public function truncate(array $tables = null, $trans = false) { if (!count($tables)) { return false; } if (!$trans) { $this->trans('start'); } foreach ($tables as $table) { $this->query('TRUNCATE TABLE '.$table.''); $this->execute(); } if (!$trans) { $this->trans('end'); } } public function tableExists($table) { try { $result = $this->db->query('SELECT 1 FROM '.$table.' LIMIT 1'); } catch (Exception $e) { return false; } return false !== $result; } public function columnExists($column, $table) { try { $this->query('SHOW COLUMNS FROM '.$table.' WHERE Field = "'.$column.'"'); $this->execute(); $result = $this->fetch(); } catch (Exception $e) { return false; } return false !== $result; } public function exists($table, $column, $id) { $this->query('SELECT COUNT('.$column.') FROM '.$table.' WHERE '.$column.' = ?'); $this->execute([ $id, ]); return (bool) $this->result(); } public function slackenMe($text, $channel = '#errors', $emoji = ':ghost:') { global $user; $text = (isset($_SERVER['PHP_SELF']) ? $_SERVER['PHP_SELF'].': ' : '').strip_tags($text); try { $data = [ 'channel' => $channel, 'username' => strip_tags($user->username), // gRPG setup. 'text' => $text, 'icon_emoji' => $emoji, 'mrkdwn' => true, ]; $json = json_encode($data); $ch = curl_init('REPLACE_ME_WITH_YOUR_SLACK_HOOK_URL'); curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST'); curl_setopt($ch, CURLOPT_POSTFIELDS, ['payload' => $json]); $result = curl_exec($ch); curl_close($ch); } catch (Exception $e) { // Do nothing } } } $db = pdo_db::getInstance(); And a quick how-to can be found here 1 Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.