14pulsars Posted June 20, 2008 Share Posted June 20, 2008 I'm trying to match up a list of client names with billing details. The problem is that the info is spread over two different data sets. I loaded the two data sets into two DB tables, and finding matches by Last Name. I want to categorize any matches into an array for printing and organizing, and the rest into another array, for the same reason. Here is the code: <?php $dbh=mysql_connect ("localhost", "DB_Name", "Password") or die ('I cannot connect to the database because: ' . mysql_error()); mysql_select_db ("database"); $missingUsers = mysql_query("SELECT * FROM `members-unknowns`") or die (mysql_error()); $OSCOrder = mysql_query("SELECT * FROM `1SC-Orders`") or die (mysql_error()); $OscData = mysql_fetch_array( $OSCOrder ); $miData = mysql_fetch_array( $missingUsers ); $successArray = array(); $failArray = array(); foreach ($OscData as $Osckey => $Oscvalue){ foreach ( $miData as $mikey => $mivalue){ if (strcasecmp($Osckey['LastName'],$mikey['LastName']) == 0) { $successArray[] = array( 'FirstName' => $mikey['FirstName'], 'LastName' => $mikey['LastName'], 'UserName' => $mikey['UserName'], 'CCNum' => $Osckey['CCNum'], 'CCExp' => $Osckey['ExpireDate'] ); } else { $failArray[] = array( 'FirstName' => $mikey['FirstName'], 'LastName' => $mikey['LastName'], 'UserName' => $mikey['UserName'] ); } } } mysql_close(); //print_r($successArray); For Debug Purposes print "<p>These are good:</p>"; foreach ($successArray as $key => $value) { print $value['FirstName']." ".$value['LastName']." | ".$value['UserName']." | ".$value['CCNum']."<br />\n"; } ?> Here's a sample of the output, though: These are good: | | | | | | | | | | | | I I | I | I | | | | Pretty much alot of empty spaces, and some other things I can't account for. What am I doing wrong? Quote Link to comment Share on other sites More sharing options...
bluejay002 Posted June 20, 2008 Share Posted June 20, 2008 i think it would be better if you do that using query. instead of: SELECT * FROM `members-unknowns` SELECT * FROM `1SC-Orders` you can use inner join: SELECT umembers.FirstName, umembers.LastName, umembers.UserName, uorders.CCNum, uorders.ExpireDate FROM `members-unknowns` umembers INNER JOIN `1SC-Orders` uorders ON umembers.LastName = uorders.LastName also, instead of lastname, use the primary key instead, say, user_id or whatever. a lot of people can have the same name. did it work? Quote Link to comment Share on other sites More sharing options...
14pulsars Posted June 20, 2008 Author Share Posted June 20, 2008 I had to use the last name since it's the only real point of commonality across the data sets. The billing details don't have User ID's, and some users used variations in their fist names (Dan/Danny), and rather than having to account for that, I thought it easier to just compare last names, and eliminate any mismatches based on first name manually. I'll try what you suggested and get back to you. Quote Link to comment Share on other sites More sharing options...
14pulsars Posted June 20, 2008 Author Share Posted June 20, 2008 It seemed to work pretty well, but I get some duplicate entries. Is there a way I can only pull unique matches? Quote Link to comment Share on other sites More sharing options...
bluejay002 Posted June 20, 2008 Share Posted June 20, 2008 use this: SELECT DISTINCT umembers.FirstName, umembers.LastName, umembers.UserName, uorders.CCNum, uorders.ExpireDate FROM `members-unknowns` umembers INNER JOIN `1SC-Orders` uorders ON umembers.LastName = uorders.LastName i added DISTINCT. no user_id's? make one if possible. last name is never a good idea, many people have the same last name, first name, even the whole name. during the design, you should have included id's since it is unique and faster compared to string. happy programming. Quote Link to comment Share on other sites More sharing options...
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.