Comparing Strings from 2 MySQL Tables

[14pulsars]

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?

[bluejay002]

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?

[14pulsars]

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.

[14pulsars]

It seemed to work pretty well, but I get some duplicate entries. Is there a way I can only pull unique matches?

[bluejay002]

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.