Jump to content

Using mysqli extension - populating member class associative arrays (long)

mshallop

By mshallop
in MySQL Help

 Share

Recommended Posts

mshallop Newbie

mshallop

Posted
Posted

This is my first attempt, upgrading to the mysqli extension from mysql.  I'm running into a problem which I'm sure has been encountered before, but I lack the experience with mysqli to be able to figure out why I've created this issue.

 

I've created a member class which contains arrays that are, by design, associative arrays representing mysql tables and/or queries via their column names.

 

Using mysqli, I have to access the result sets using result_metadata() (to return column names) and call_user_func_array() to bind the results for processing in a foreach loop.

 

I'm going to do this several times within the same initialization function, but I've hit a pothole on the second array-population sequence...So from this point, I'm confining this issue only to where the second sequence processing ends.

 

I have two arrays declared in my method as public vars: users() and profile() and I plan to access the data, for example, as:

 

$user->users['lname'] or $user->profile['address']

 

The first sequence where the query fetches the table data and populates the structure executes flawlessly.  The data is stored into the $user->users structure without any issue.

 

The problem arises when I execute the second query sequence -- what gets stored into the second array is a combination of:  (contents of first query result set + (contents of second result set - minus first parameter of second result set))

 

I've got a logger class, not shown here, which logs simple messages to a table.  I'm logging the query string in the second sequence, and each key-value pair values as the foreach loop is processed.  In this output, I see where data from the first result set is being processed/stored before the data from the second result set.

 

I believe I am missing something in my call-sequence for the mysqli process that's causing this the data redundancy in the second array, but my (re)searches haven't been able to kick-out anything obvious.

 

Any help is appreciated - please let me know if you need more data.

 

Thank you.

 

--mike

 

cls_user.inc


<?php
include_once($_SERVER['DOCUMENT_ROOT'] . '/classes/env_config.inc');
include_once($_SERVER['DOCUMENT_ROOT'] . '/classes/error_logging.class.php');


class user {
public $id;
public $lastupdate;
public $hasadmin;

public $users = array();
public $profile = array();
public $client_profile = array();

# legacy member vars
var $SA=false;
var $username;
var $race;
var $sex;
var $address2;
var $roles;
var $SALARY;
var $STAFF_CALENDAR_OTHER_MODIFY;
var $IsSubUser;
var $ParentUser;


function __construct($id) {
	$this->id = $id; # constructor function
}

function UpdateFlags() {
	$logger = new LogObject();
	$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
	if (mysqli_connect_errno()) {
		printf("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error());
		$logger->logMessage("Cannot connect to mysql: " . mysqli_connect_error(), DEBUG);
		exit;
	}
	$query = "SELECT *
			    FROM users
			    WHERE id = ?";
	$statement = $mysqli->stmt_init();
	$statement->prepare($query);
	$statement->bind_param('i', $this->id);
	$statement->execute();
	if (mysqli_errno($mysqli)) {
		$logger->logMessage(mysqli_error($mysqli));
		return (false);
	}
	$meta = $statement->result_metadata();
	while ($field = $meta->fetch_field()) {
		$params[] = &$row[$field->name];
	}
	call_user_func_array(array($statement, 'bind_result'), $params);
	$statement->fetch();
	foreach ($row as $key => $value) {
		$this->users[strtolower($key)] = $value;
	}
	$statement->close();
	unset($params);
	$meta->close();
	unset($meta);
	unset($statement);
	if (isset($this->users['usertype']) and $this->users['usertype'] !=2) {
		$query2 = "SELECT users.UID, users.IS_SUPERVISOR AS supervisor, profile.FNAME, profile.LNAME, profile.MI, profile.STAFF,
				           profile.EXEMPT,	profile.SALARY_IS_HOURLY, profile.SECRETLEVEL, profile.HOURS_START,
				           profile.HOURS_END, profile.SKILL_LEVEL, profile.COMFORT_LEVEL, profile.USER_LEVEL,
				           profile.EMAIL, profile.ADDRESS, profile.CITY, profile.STATE, profile.ZIP,
				           profile.TIMEZONE, profile.canViewWorkLocator
				    FROM	profile
				    		  INNER JOIN users ON users.ID=profile.UID
				    where profile.UID= ?";
		$logger->logMessage($query2);
		$statement = $mysqli->stmt_init();
		$statement->prepare($query2);
		$statement->bind_param('i', $this->id);
		$statement->execute();
		if (mysqli_errno($mysqli)) {
			$logger->logMessage(mysqli_error($mysqli));
			return(false);
		}
		$meta = $statement->result_metadata();
		while ($field = $meta->fetch_field()) {
			$params[] = &$row[$field->name];
		}
		call_user_func_array(array($statement, 'bind_result'), $params);
		$statement->fetch();
		foreach ($row as $key => $value) {
			$this->profile[strtolower($key)] = $value;
			$logger->logMessage("(PROFILE)key: $key = ($value)");
		}
		$this->profile['supervisor'] = (isset($this->profile['supervisor'])) ? $this->profile['supervisor'] : false;
		# clean-up zip code
		$this->profile['zip'] = rtrim($this->profile["zip"], " -");
		//----- added 2009-06-23: -----
		$this->profile['canviewworklocator'] = isset($this->profile['canViewWorkLocator']) ? $this->profile['canViewWorkLocator'] : 0;

		if (!empty($this->profile['zip']) || ((strtoupper($this->profile['state'])=='IL')
													||  (strtoupper($this->profile['state'])=='WI')
													||  (strtoupper($this->profile['state'])=='IN'))) {
			$this->profile['ischicago'] = true;
		} else {
			$this->profile['ischicago'] = false;
		}
		$statement->close();

# IRL, I'm not looking at the code past this point until I can figure out
# why the second structure isn't populating correctly....

		$query="SELECT count(users.ID) AS cnt
			FROM users
				INNER JOIN profile ON profile.UID=users.ID
			WHERE
				profile.LNAME = ?
				AND profile.FNAME = ?
				AND profile.MI = ?
				AND users.USERTYPE=1
				AND users.STATUS=0";
		$statement = $mysqli->stmt_init();
		$statement->prepare($query);
		$statement->bind_param('sss', addslashes($this->profile['lname']), addslashes($this->profile['fname']), $this->profile['mi']);
		$statement->execute();
		if (mysqli_errno($mysqli)) {
			$logger->logMessage(mysqli_errno($mysqli));
			return (false);
		}
		$statement->bind_result($count);
		$statement->fetch();
		$statement->close();

		$this->hasadmin = ($count) ? true : false;
	} else {
		$query="SELECT city, state, secret, secret_authorized, timezone
			   FROM client_profile
			   WHERE uid = ?";
		$statement = $mysqli->stmt_init();
		$statement->prepare($query);
		$statement->bind_param('i', $this->id);
		$statement->execute();
		if (mysqli_errno($mysqli)) {
			$logger->logMessage(mysqli_error($mysqli));
			return (false);
		}
		$meta = $statement->result_metadata();
		while ($field = $meta->fetch_field()) {
			$params[] = &$row[$field->name];
		}
		@call_user_func_array(array($statement, 'bind_result'), $params);
		$statement->fetch();
		foreach ($row as $key => $value) {
			$tmp[strtolower($key)] = $value;
		}
		$this->client_profile = $tmp;
	}
	$this->lastupdate = strtotime(date("Y-m-d H:i:s"));
}

function needsupdate() {
	$d1 = strtotime(date("Y-m-d H:i:s"));
	if (($d1 - $this->lastupdate)>=60) {
		return true;
	} else {
		return false;
	}
}

}
?>

 

Class Invocation:

<?php

$classDir = $_SERVER['DOCUMENT_ROOT'] . '/classes/';
include_once($classDir . "cls_user.inc");

$user = new user(65950);

$user->UpdateFlags();
echo "USERS:<br/>";
var_dump($user->users);
echo "<br/><br/><hr/><br/>";
echo "PROFILE:<br/>";
var_dump($user->profile);
echo "<br/><br/><hr/><br/>";
echo "CLIENT PROFILE:<br/>";
print_r($user->client_profile);
?>

 

Output:

USERS:
array(96) { ["id"]=> int(65950) ["cdate"]=> string(19) "2009-01-18 15:01:14" ["mdate"]=> string(19) "2009-08-17 11:03:07" ["uid"]=> string(9) "testTerp4" ["timecards"]=> string(1) "0" ["pwd"]=> string(9) "testTerp4" ["rpwd"]=> NULL ["usertype"]=> int(3) ["flags"]=> int(0) ["status"]=> int(0) ["supervisor"]=> int(0) ["interviewed"]=> int(0) ["interviewedby"]=> NULL ["interviewnotes"]=> NULL ["groups_view"]=> int(0) ["groups_create"]=> int(0) ["groups_modify"]=> int(0) ["groups_delete"]=> int(0) ["clients_view"]=> int(0) ["clients_create"]=> int(0) ["clients_modify"]=> int(0) ["clients_delete"]=> int(0) ["practitioners_view"]=> int(0) ["practitioners_create"]=> int(0) ["practitioners_modify"]=> int(0) ["practitioners_delete"]=> int(0) ["administrators_view"]=> int(0) ["administrators_create"]=> int(0) ["administrators_modify"]=> int(0) ["administrators_delete"]=> int(0) ["assignments_view"]=> int(0) ["assignments_create"]=> int(0) ["assignments_modify"]=> int(0) ["assignments_delete"]=> int(0) ["invoices_view"]=> int(0) ["invoices_create"]=> int(0) ["invoices_modify"]=> int(0) ["invoices_delete"]=> int(0) ["webcontent_view"]=> int(0) ["webcontent_create"]=> int(0) ["webcontent_modify"]=> int(0) ["webcontent_delete"]=> int(0) ["rates_view"]=> int(0) ["rates_create"]=> int(0) ["rates_modify"]=> int(0) ["rates_delete"]=> int(0) ["buzz_view"]=> int(0) ["buzz_create"]=> int(0) ["buzz_modify"]=> int(0) ["buzz_delete"]=> int(0) ["sysinfo_view"]=> int(0) ["pay_clients"]=> int(0) ["pay_practitioners"]=> int(0) ["practitioners_levels_view"]=> int(0) ["practitioners_levels_modify"]=> int(0) ["notes_history_view"]=> int(0) ["notes_history_create"]=> int(0) ["notes_assignment_view"]=> int(0) ["notes_assignment_create"]=> int(0) ["notes_client_view"]=> int(0) ["notes_client_create"]=> int(0) ["lastpasswordchange"]=> string(19) "2009-06-30 15:07:44" ["lastavailchange"]=> string(19) "2004-01-01 00:00:00" ["mailout_generic"]=> int(0) ["mailout_prac_jobs"]=> int(0) ["hasreadtoc"]=> int(1) ["bugs_view"]=> int(0) ["bugs_modify"]=> int(0) ["bugs_delete"]=> int(0) ["aim"]=> NULL ["set_salary"]=> string(1) "0" ["set_staff"]=> string(1) "0" ["hire_date_view"]=> string(1) "0" ["start_date_view"]=> string(1) "0" ["leave_date_view"]=> string(1) "0" ["hire_date_modify"]=> string(1) "0" ["start_date_modify"]=> string(1) "0" ["leave_date_modify"]=> string(1) "0" ["al_view"]=> string(1) "0" ["al_modify"]=> string(1) "0" ["pl_view"]=> string(1) "0" ["pl_modify"]=> string(1) "0" ["set_exempt"]=> int(0) ["staff_calendar_view"]=> string(1) "0" ["staff_calendar_self_modify"]=> string(1) "0" ["staff_calendar_other_modify"]=> string(1) "0" ["lastlogin"]=> int(1258665697) ["accounttype"]=> NULL ["cert_is_current_date"]=> NULL ["cert_is_current_by"]=> NULL ["cert_is_current"]=> string(1) "0" ["is_supervisor"]=> string(1) "0" ["lastip"]=> NULL ["signupip"]=> NULL ["externalid"]=> string(0) "" ["role"]=> int(4) }


PROFILE:
array(116) { ["id"]=> int(65950) ["cdate"]=> string(19) "2009-01-18 15:01:14" ["mdate"]=> string(19) "2009-08-17 11:03:07" ["uid"]=> string(9) "testTerp4" ["timecards"]=> string(1) "0" ["pwd"]=> string(9) "testTerp4" ["rpwd"]=> NULL ["usertype"]=> int(3) ["flags"]=> int(0) ["status"]=> int(0) ["supervisor"]=> string(1) "0" ["interviewed"]=> int(0) ["interviewedby"]=> NULL ["interviewnotes"]=> NULL ["groups_view"]=> int(0) ["groups_create"]=> int(0) ["groups_modify"]=> int(0) ["groups_delete"]=> int(0) ["clients_view"]=> int(0) ["clients_create"]=> int(0) ["clients_modify"]=> int(0) ["clients_delete"]=> int(0) ["practitioners_view"]=> int(0) ["practitioners_create"]=> int(0) ["practitioners_modify"]=> int(0) ["practitioners_delete"]=> int(0) ["administrators_view"]=> int(0) ["administrators_create"]=> int(0) ["administrators_modify"]=> int(0) ["administrators_delete"]=> int(0) ["assignments_view"]=> int(0) ["assignments_create"]=> int(0) ["assignments_modify"]=> int(0) ["assignments_delete"]=> int(0) ["invoices_view"]=> int(0) ["invoices_create"]=> int(0) ["invoices_modify"]=> int(0) ["invoices_delete"]=> int(0) ["webcontent_view"]=> int(0) ["webcontent_create"]=> int(0) ["webcontent_modify"]=> int(0) ["webcontent_delete"]=> int(0) ["rates_view"]=> int(0) ["rates_create"]=> int(0) ["rates_modify"]=> int(0) ["rates_delete"]=> int(0) ["buzz_view"]=> int(0) ["buzz_create"]=> int(0) ["buzz_modify"]=> int(0) ["buzz_delete"]=> int(0) ["sysinfo_view"]=> int(0) ["pay_clients"]=> int(0) ["pay_practitioners"]=> int(0) ["practitioners_levels_view"]=> int(0) ["practitioners_levels_modify"]=> int(0) ["notes_history_view"]=> int(0) ["notes_history_create"]=> int(0) ["notes_assignment_view"]=> int(0) ["notes_assignment_create"]=> int(0) ["notes_client_view"]=> int(0) ["notes_client_create"]=> int(0) ["lastpasswordchange"]=> string(19) "2009-06-30 15:07:44" ["lastavailchange"]=> string(19) "2004-01-01 00:00:00" ["mailout_generic"]=> int(0) ["mailout_prac_jobs"]=> int(0) ["hasreadtoc"]=> int(1) ["bugs_view"]=> int(0) ["bugs_modify"]=> int(0) ["bugs_delete"]=> int(0) ["aim"]=> NULL ["set_salary"]=> string(1) "0" ["set_staff"]=> string(1) "0" ["hire_date_view"]=> string(1) "0" ["start_date_view"]=> string(1) "0" ["leave_date_view"]=> string(1) "0" ["hire_date_modify"]=> string(1) "0" ["start_date_modify"]=> string(1) "0" ["leave_date_modify"]=> string(1) "0" ["al_view"]=> string(1) "0" ["al_modify"]=> string(1) "0" ["pl_view"]=> string(1) "0" ["pl_modify"]=> string(1) "0" ["set_exempt"]=> int(0) ["staff_calendar_view"]=> string(1) "0" ["staff_calendar_self_modify"]=> string(1) "0" ["staff_calendar_other_modify"]=> string(1) "0" ["lastlogin"]=> int(1258665697) ["accounttype"]=> NULL ["cert_is_current_date"]=> NULL ["cert_is_current_by"]=> NULL ["cert_is_current"]=> string(1) "0" ["is_supervisor"]=> string(1) "0" ["lastip"]=> NULL ["signupip"]=> NULL ["externalid"]=> string(0) "" ["role"]=> int(4) ["fname"]=> string(9) "testTerp4" ["lname"]=> string(9) "testTerp4" ["mi"]=> string(0) "" ["staff"]=> string(1) "0" ["exempt"]=> string(1) "0" ["salary_is_hourly"]=> string(1) "1" ["secretlevel"]=> int(2) ["hours_start"]=> string( "08:00:00" ["hours_end"]=> string( "17:00:00" ["skill_level"]=> int(0) ["comfort_level"]=> int(0) ["user_level"]=> int(0) ["email"]=> string(29) "robert.troutman@watkinsit.com" ["address"]=> string(9) "1 Main St" ["city"]=> string(10) "Washington" ["state"]=> string(2) "DC" ["zip"]=> string(10) "20006-0004" ["timezone"]=> int(0) ["canviewworklocator"]=> int(0) ["ischicago"]=> bool(true) }


CLIENT PROFILE:
Array ( )

 

Sample db-logger output for start of second sequence:

SELECT users.UID, users.IS_SUPERVISOR AS supervisor, profile.FNAME, profile.LNAME, profile.MI, profile.STAFF,
                              profile.EXEMPT,    profile.SALARY_IS_HOURLY, profile.SECRETLEVEL, profile.HOURS_START,
                              profile.HOURS_END, profile.SKILL_LEVEL, profile.COMFORT_LEVEL, profile.USER_LEVEL,
                              profile.EMAIL, profile.ADDRESS, profile.CITY, profile.STATE, profile.ZIP,
                              profile.TIMEZONE, profile.canViewWorkLocator
                       FROM    profile
                                 INNER JOIN users ON users.ID=profile.UID
                       where profile.UID= ?
(PROFILE)key: ID = (65950)
(PROFILE)key: CDATE = (2009-01-18 15:01:14)
(PROFILE)key: MDATE = (2009-08-17 11:03:07)
(PROFILE)key: UID = (testTerp4)
...
(PROFILE)key: ROLE = (4)
(PROFILE)key: supervisor = (0)
(PROFILE)key: FNAME = (testTerp4)
(PROFILE)key: LNAME = (testTerp4)
(PROFILE)key: MI = ()
...

Link to comment
Share on other sites
mshallop Newbie

mshallop

Posted
  • Author
Posted

Since you can't preview a prepared query, I can't say with certainty what's being executed by the mysql engine.

 

The issue can be distilled down to:

-- query1 executes

-- query1 results are bound and stored in array 1

 

-- query2 executes

-- query1 results are stored in array 2

-- query2 results are bound and stored with a wee bit of overlap in array 2

 

The query preparation, execution, and fetch & store code (php), are identical for query1 and query2 processing.

 

Interesting note:  I can effect the output by removing the line "unset($params);" -- what happens is that the associative array names are created in array2, but the content is null.

 

All of the above is leading me to suspect that the issue is internal, a memory addressing snafu, that is probably the result of a procedural error in how I'm creating the arrays. 

 

Finding authoritative information outside of php.net for this process has been difficult; I am sure I'm overlooking something - I just don't know what, yet.

 

Short of completely resetting the $mysqli instantiation between queries, I'm at a complete loss.  I want to use the mysqli extension -- this process just simply works using mysql.

Link to comment
Share on other sites
mshallop Newbie

mshallop

Posted
  • Author
Posted

I stumbled across a solution to the problem -- using the code found posted as a mysqli class as a user comment to the php reference page, I was able to correctly populate the array objects in my class as associative arrays relevant to the table/queries.

 

How it's different (in that the code works) from iteratively calling the same functions in-line escapes me -- for now, I'm happy that the results are as-expected.

 

Should anyone be interested and not intimidated by this code, I'll happily post it.  I remain frustrated over why something so easily accomplished using the mysql extension is so obfuscated using the "improved" mysqli extension.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.