I need help converting mysql to msqli in the program I am using.
This is the database class to connect to database and to insert,select,delete and update.

<?php
final class db{
static $db;

static function config($conf=false){
	#echo '<pre>'.print_r($conf, 1).'</pre>';
	self::$db = mysql_connect($conf['host'], $conf['username'], $conf['password']);
	mysql_select_db($conf['database'], self::$db);
}

static function changedb($database){
	$resp = mysql_select_db($database);
	return($resp);
}

static function select($sql, $args=false){
	
	if($args['pager']){
		$limit = self::load_pager($args['pager']);
		$sql .= 'LIMIT '.$limit;
	}
	
	$result = mysql_query($sql, self::$db);
	if(!$result) trigger_error("MySQL SELECT error: ".mysql_error()." using ".$sql);

	if(@$args['explain']==true) self::explain($sql);
	
	while ($r = mysql_fetch_array($result,MYSQL_ASSOC)){
			$row[] = $r;
	}


	mysql_free_result($result);

	@array_walk_recursive($row, 'de_clean_post_recursive');
	return @$row;
}

static function select_row($sql, $args=false){
		$result = mysql_query($sql, self::$db);
		if(!$result) trigger_error("MySQL SELECT error: ".mysql_error()." using ".$sql);
		
		if(@$args['explain']==true) self::explain($sql);
		
		$row = mysql_fetch_array($result,MYSQL_ASSOC);			
		mysql_free_result($result);

		@array_walk_recursive($row, 'de_clean_post_recursive');					
		return @$row;
}

static function query($sql)
{
	$result = mysql_query($sql, self::$db);
	if(!$result) trigger_error("MySQL UPDATE error: ".mysql_error().'<pre>'.$sql.'</pre><br>');
	#if($_SESSION['access'] == 'admin') recache($sql);
}

static function explain($sql){
	
	trigger_error("SQL: ".$sql);
	$explain = mysql_query("EXPLAIN {$sql}", self::$db);
	if ($row = mysql_fetch_assoc($explain)) { 
		foreach ($row as $key => $value) { 
			trigger_error("$key: $value\n"); 
		}
	} 

}

}
?>

USAGE:
I use this to select a row:
<?
$sql = "SELECT * FROM user
WHERE userid = {$_SESSION['userid']}";
$user = db::select_row($sql);
?>

And this to select multiple rows
<?
$sql = "SELECT * FROM user";
$users = db::select($sql);
?>

To update:
<?
$sql = "UPDATE user SET user_name = '".($post['user_name'])."'
WHERE userid = '{$_SESSION['userid']}' LIMIT 1";
db::query($sql);
?>

To insert:
<?
$sql = "INSERT INTO user( ...,..,...,..) VALUES(..,..,..,..')";
db::query($sql);
?>

To delete:
<?
db::query('DELETE FROM user WHERE userid = "'.intval($GET['id']).'" LIMIT 1');
?>
OR
<?
$sql = "DELETE FROM user WHERE userid = "'.intval($GET['id']).'" LIMIT 1";
db::query($sql);
?>

I tried changing all mysql to mysqli but I can't connect to the server. Thank you for any help.

    It seems to me that your database class interface suffers from a serious design flaw in that it does not accommodate the use of prepared statements with parameters to which variables/values can be bound. Have you considered just switching over to use mysqli directly, or if you do need a wrapper, using something PDO, or if you must, redesigning to have an interface that fully supports prepared statements?

    As for "can't connect to the server": are you sure that you have determined that mysqli_connect fails? What is the relevant code that you tried to change it to?

    laserlight
    So do you think I need to re-code it? Is there no other way to convert this to mysqli?
    I tried to change all mysql     to mysqli_ like this code.

    mysql_select_db($conf['database'], self::$db); to mysqli_select_db($conf['database'], self::$db);

        Yes, I think that you need to change the design itself. You might not even need this database class to begin with.

        What is the exact error message?

        laserlight Error, it is not connecting to the database. Do you have any guide where I can begin with this (maybe procedural approach)? I am a beginner, but I want this old program to be upgraded.

            "Error, it is not connecting to the database."

            I don't see how you could get that error message as it doesn't match any of the error output strings or prefixes that you showed in your code.

            Try:

            self::$db = mysqli_connect($conf['host'], $conf['username'], $conf['password']) or die('Error: could not connect to the database');

            If this error message does not appear, then you are mistaken, or rather the error message that was printed is wrong.

              About how many calls are there to the db class in this old program? How many of these are calls to the changedb() method?

              The reason that's important, and why laserlight mentioned prepared queries, is because a feature (magic quotes) was removed from php that provided (some) protection against sql injection in string data. Any old code you convert needs to address sql injection and the surest and simplest way (least amount of added code) of doing so is to use prepared queries and to use the php PDO extension (the interface for mysqli prepared queries was not designed to easily and quickly handle executing queries through a common function/method.) This however will require changing the calls to the database class for any query has dynamic data value(s) being put directly into it, into a prepared query, with place-holder(s) in the sql query statement for each value, and with the data value(s) being supplied as an addition array parameter in the calls.

              mysql_select_db($conf['database'], self::$db); to mysqli_select_db($conf['database'], self::$db);

              If that's the exact code change you made, you cannot just add an i after the mysql. The parameter order was changed between the msyql and mysqli extensions and you have to actually learn what the differences are when converting code between the two.

                Write a Reply...