mshallop

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.

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.

Given two tables, name1 and name2, let's put the Rubbles in name1, and the Flintstones in name2 -- overlapping names will be the patriarchs: Barney and Fred: -- -- Table structure for table `name1` -- CREATE TABLE IF NOT EXISTS `name1` ( `id` int(11) NOT NULL auto_increment, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `name1` -- INSERT INTO `name1` (`id`, `fname`, `lname`) VALUES (1, 'Fred', 'Flintstone'), (2, 'Barney', 'Rubble'), (5, 'Betty', 'Rubble'), (6, 'Bam-Bam', 'Rubble'); -- -- Table structure for table `name2` -- CREATE TABLE IF NOT EXISTS `name2` ( `id` int(11) NOT NULL auto_increment, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `name2` -- INSERT INTO `name2` (`id`, `fname`, `lname`) VALUES (1, 'Fred', 'Flintstone'), (2, 'Barney', 'Rubble'), (3, 'Wilma', 'Flintsone'), (4, 'Pebbles', 'Flintstone'); next, create a temporary table of all the names from table: name1, using the mySQL function CONCAT_US. This will collapse the names down into a single cell value, as requested: create temporary table foo as (select CONCAT_WS(' ', fname, lname) AS name from name1); Then, within the same block (since you're using a temporary table), get a list of duplicate names from table: name2 based on the data population of the temp table: foo... select * from name2 where (CONCAT_WS(' ', fname, lname) in (select name from foo)); Hopefully, this is enough to get you started...

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 = () ...

Question - in an application of hundreds of php source scripts, does it make more sense to object-ify your session storage via mySQL instead of the _SESSION functions? Granted, some limited data would still have to be session-ized (at the minimum, the user/group id), but if you're storing several tables worth of data in a session, at what point does it become more efficient (if it does) to load everything from a database instead via class functions? Factor in considerations such as a load-balanced server environment....does this change the equation? Just curious if anyone else has dealt with this.... --Mike

[!--quoteo(post=344455:date=Feb 10 2006, 05:31 AM:name=shenniko)--][div class=\'quotetop\']QUOTE(shenniko @ Feb 10 2006, 05:31 AM) [snapback]344455[/snapback][/div][div class=\'quotemain\'][!--quotec--] Hi Guys need a little help, Pretty new to MySQL and PHP, as i started learing ASP thought Access DB's... Ive created a sort of search function for my members section, so you can search by Username.. How do you create a "LIKE" statement? E.g say i enter Da into the search box, it will bring Everything back with "Da" in the database? All i need is the WHERE tblCharacter.Character LIKE '$Character'"; Or whatever the code is. thanks in advance shenn [/quote] WHERE tblCharacter.Character LIKE '%$Character%'"; if $Character == "da" and this is being submitted from somescript.php... --Mike