Okay, so I have written this script which allows me to find however it does not work correctly. I am having a programming issue - programming this to work correctly. It does not seem to be working correctly.
This function is declared on each row that shows up within a database full of payments from a bank statement. The $a, $b, $c, $d is simply the type (BAC, TRS, etc), description (35 QUEENS ROAD RENT) and amount (£500.00).
I am trying to create a function that will reconcile payments to a particular user, depending on what addresses/properties are connected to them, their name and how much their rent is.
This is what I have so far:
function reconcile($a,$b,$c,$d) {
//when the user clicks on the text fild, jquery loads the various names needed from the database
//when the user clicks on the selected field, the value is saved via ajax and saved on the server the reconciled field
//break down the inputs into an array
//create SQL query that matches all of these key words
//create keywords database
$keywords = $a . " " . $b . " " . $c . " " . $d;
//remove commas, dashes and other keywords from database that might be a nuisance
//change names like ave to avenue
$keywords = str_replace("'", "", $keywords);
$keywords = str_replace("-", "", $keywords);
$keywords = str_replace(",", "", $keywords);
$keywords = str_replace("A/C", "", $keywords);
$keywords = str_replace("TO", "", $keywords);
$keywords = str_replace("CALL", "", $keywords);
$keywords = str_replace("REF.NO.", "", $keywords);
$keywords = str_replace("TO", "", $keywords);
$keywords = preg_replace("/\s+/", " ", $keywords);
$keywords = str_replace(" ", "", $keywords);
$keywords = addslashes($keywords);
$keywords = mysql_real_escape_string($keywords);
$keywords = explode(" ", $keywords);
//$keywords = remove_from_array($keywords, 2);
//match keywords to keywords database
$matches = array();
//search 5 databases...
$i=0;
foreach($keywords as $keyword) {
//add more fields
//check distinct
//do not allow any keywords that are smaller than 3
if(strlen($keyword) < 3) {
continue;
}
$get = mysql_query("SELECT DISTINCT `users`.`id` FROM `users` LEFT JOIN `addresses` ON `users`.`id` = `addresses`.`user` WHERE (`users`.`fname` LIKE '%$keyword%' OR `users`.`lname` LIKE '%$keyword%') OR (((`addresses`.`flat` LIKE '%$keyword%' OR `addresses`.`address` LIKE '%$keyword%') OR (`addresses`.`district` LIKE '%$keyword%' OR `addresses`.`town` LIKE '%$keyword%')) OR (`addresses`.`county` LIKE '%$keyword%' OR `addresses`.`postcode` LIKE '%$keyword%'));") or die("Error: " . mysql_error());
if(mysql_num_rows($get) > 0) {
while($fetch = mysql_fetch_array($get)) {
list($uid) = $fetch; //$uid = user id
//insert $uid into array
//1 => 3 times
//find number if exists and then add to that number
//search array and echo attempts (if any)
if(in_array($uid, $matches)) {
//add one to the key
$matches[$uid]++;
} else {
array_push($matches, $uid);
}
$i++;
//save search to database
}
//create array for user
//add up relevance for that user
}
}
$relevance = 0;
if($i > $relevance) {
//change javascript function and link with ajax.php
echo "<span onClick=\"javascript:void(0)\" style=\"cursor:pointer;\" class=\"label label-success\">Accept</span>\n\r";
//change to jquery function that allows a drop down of names from the database
echo "<input type=\"text\" style=\"width:140px\" value=\"" . $matches[0] . "\" />\n\r";
}
}
I was thinking of adding an array that does this:
[0] (user id) => 3 (how many times that user id is found through one row of a bank statement)
So for example, if user 3 owned a property at 37 Kings Avenue, and the keyword was 'Avenue', the result would be [3] => 1 and so on for extra users.
The 'accept' button means that the user would accept the system's reconciliation and the text input would show a drop down of other relevant names/all names and addresses.
