mySQL Database Guidance


I am in the process of updating website that uses a mySQL database. I now see that I have to migrate from mysql to mysqli which is ok. Learning new things is good. However in the past I used a database constructor to connect to the database which returned a database resource I then used in turn used to run various and sundry queries against the database (at different times).

I am a bit confused about the improved version of mySQL/

I researched the subject and am coming up with the impression that one no longer uses constructors and has to open and close the database each time a query is run. All the examples I find seem to imply this is true, but this just does not feel right to me. But it may very well be that I am misunderstanding things. If any one can provide me with some guidance or point my to a good resource I can study I would really appreciate it.

Thank you.

If you’re talking Persistent Connections you may want to put your OOP hat on and use PDO instead of mysqli_

Persistent connection support was introduced in PHP 5.3 for the mysqli extension. Support was already present in PDO MYSQL and ext/mysql.

not sure what examples you found but generally (unless you’re using persistent connections) you’d open a connection at script start using new MySQLi(…) (you may manually close that on script end, but usually PHP takes care of that itself) and then pass that around to create queries or prepared statements.

Thank you for the response.


I was looking at the example in the link in your post ‘Stop using mysql’. With some differences.

My tentative code based on the original mysql is as follows:
Also I am using a constants file to define the constants)

class Database
//class variables
public $link_id; //database resource

//class constructor
function Database()
	//function description:  open database
	//arguments:  none
	//returns:  int defining database resource

	//echo '<br />file:  database.php | class constructor | function:  Database()';

	//establish connection with database
	$link_id = @mysqli_connect(DB_SERVER, DB_USER, DB_PASSWORD) or
	message_server(CRITICAL_ERROR, __LINE__, __FILE__, mysqli_connect_errno(), mysqli_connect_error(), 'Unable to establish connection with server:  ' . DB_SERVER);

	open database
	@mysqli_select_db($link_id, DB_DATABASE) or
	message_database(CRITICAL_ERROR, __LINE__, __FILE__, mysqli_errno($link_id), mysqli_error($link_id), 'Unable to open requested database:  ' . DB_DATABASE);

//function description:  return integer defining site status
//arguments:  none
//returns:  int defining site status
function getCurrentSiteStatus()
	echo '<br /> file:  database.php | function:  getCurrentSiteStatus()';
	$sql_query = 'SELECT config_value FROM ' . TABLE_CONFIG . ' WHERE config_name = "site_status"';
	echo '<br />$sql_query = ' . $sql_query;
	$result = mysqli_query($link_id, $sql_query) or
	message_config(GENERAL_ERROR, __LINE__, __FILE__, mysqli_errno($link_id), mysqli_error($link_id), 'Error accessing <b>config</b> database table');

	$num_rows = mysqli_num_rows($result);
	echo '<br />file:  database.php | function:  getSiteStatus() | Number of rows retrieved = ' . $num_rows;
	if ( $num_rows != 1 )
		message_die(GENERAL_ERROR, __LINE__, __FILE__, 'Incorrect number of rows retrieved from table <b>config</b>');	
	$site_status = mysql_fetch_row($result);
	$site_status = $site_status[0];
	echo '<br />file:  database.php | function:  getSiteStatus() | $site_status  = ' . $site_status;

	return $site_status;



The problem I am having is the second function appears to know nothing about the variable “$link_id” and throws an error.

In your example you have two different <php> tags and somehow the second tag knows about “$db”. I do not see how it is happening.

I know I am doing something really silly, just cannot figure out what it is.

Thank you.

of course, $link_id is a local variable to the first function/method (and besides that, $link_id is not a simple ID, it’s the connection object itself).

some more notes:

  1. use code tags when posting code

  2. MySQLi itself is already a class (well, rather a set of classes)

  3. your Database class mixes responsibilities. it creates the DB connection and queries for specific data. it should only do one of these (judging from the name the former, but that is already covered by the MySQLi classes). if it should be the latter, pass in the database connection as parameter (cf. Dependency Injection)

  4. never use the @ operator. the MySQLi constructor doesn’t emit errors anyways.

  5. mysqli_select_db() is superfluous as you can pass in the database name as 4th parameter to mysqli_connect().

  6. class methods should always use a visibility (public, protected or private)

Thank you for the response.