PHP/MySQL question

[kspooner13]

I have a database that has two tables. In each of these tables is a column "name" . For the life of me, and my terrible programming experience, I can not get my "webapp" to sift through the records and show me which ones are not similar. I've tried while loops, i've tried foreach's. So i've come here to see if anyone can help...

What I have are computers listed, in table a with names such as "ComputerXP" in one table. In another table I have devices listed in table b, under "computerxp.some.info" I want to find out which records are not similar to each other and echo them out. The only things that match them are a location ID which im using to filter out some of the rows.

Notes: This is not my database, i have no ability to change it, just pull information, otherwise i would have done this differently.

 

[PTNL]

Few questions...

1 - Are you strictly dealing with the presentation layer, or do you have access/rights to run your own query against the database?
2 - Can you provide some sample/mock data that highlights the problem? We'd need something accurate enough to tell if a provided database query or business-/web-tier loop would work.
3 - What, specifically, have you tried? Please post sample code and/or queries, and specify the accuracy of the current data being returned from your attempts (ie: whether what you are seeing should or should not be considered).
4 - I think I understand what you mean in your matching description; is it just "see if string 'A' exists somewhere in string 'B'" ?

 

[kspooner13]

Few questions...

1 - Are you strictly dealing with the presentation layer, or do you have access/rights to run your own query against the database?
2 - Can you provide some sample/mock data that highlights the problem? We'd need something accurate enough to tell if a provided database query or business-/web-tier loop would work.
3 - What, specifically, have you tried? Please post sample code and/or queries, and specify the accuracy of the current data being returned from your attempts (ie: whether what you are seeing should or should not be considered).
4 - I think I understand what you mean in your matching description; is it just "see if string 'A' exists somewhere in string 'B'" ?

1. I can run my own query to the database. I just can't (and don't want) to insert, or delete any records.
4. That is exactly what im trying to do!

I'll post some code later on, but #4 is exactly it. I just can't seem to find that solution that plagues me so.

Table 1:
1 - computer , 192.111.111.1 ,
2 - my_name , 152.251.22.11

Table 2:
1 - computer.name.info , 192.111.111.2
2 - some_thing , 152.251.255.2

I want to take row 1 of table one, and compare the name to all of table 2 records and if there is no similar record, echo that computer name. If there is a similar record, move to the next row in table 1.

 

[PTNL]

There's a few different ways I can think of to solve this:

1) Do matching on web server -- Fetch the distinct values from Table1, and all of the rows from Table2. Use various substring/parsing functions in PHP as you for-each over the results from Table02. Load the substring matches you find into an array, and iterate over that array in your presentation layer.
2) Dynamic SQL in business tier -- Fetch values from Table01, and build a SQL query in memory with concatonated LIKE (or INSTR) functions, while paying extra attention to the values being loaded in the iterations for SQL injection opportunities. Second database hit will return the matches.

There's probably other variances of the above approaches, but it still comes down to where the matching logic occurs. Personally, I'd use #1 -- let the DB just dump out two SELECT statements and place the matching burden on the web server. A few aspects that haven't been covered include: the number of rows in Table1 and Table2, and the frequency that this matching logic would be performed.

Edit:
Thinking a little more, I also thought of this:
3) Maintain a third table that persists the relationships/matches between tables' 1 and 2. Data is added/removed via database triggers, unless there is a clear reason to do it in the business tier.

This last suggestion obviously goes against your stated limitations, but if the previous options have been both implemented and proven to be insufficient *and* the servers are not strapped for horsepower or throughput, then it's worth having the conversation with your boss... well, that among other possibilities.

 

[kspooner13]

Do you have some sample code I could see? I've looked at foreach's and ran some tests before i posted, but i couldn't get anything to match.

SQL injection wouldn't happen, unless im the one doing it. There is no user input aside from a drop down box.

I could create a new database with the information on my local xampp webserver which wouldn't be a problem. I just can't find some sample code on the internet to point me in the right direction of how to match everything. I've tried strstr(), strpos(), and similar_text(), but should i do a while() and nest a foreach inside?

I do appreciate your insight on this, as well as your help.

 

[kspooner13]

Here is my latest, and terrible code. I know its outdated, but its internal, and after i get it working, i clean it up. So please by nice on my formatting/use of it

it runs off an ajax call to "reload the page" with the information for companyid.

$c2 = mysql_query("select name, ipaddress, locationid from networkdevices WHERE locationid='".$location_id."'");
	$computer_query2 = mysql_query("select name, locationid, computerid FROM computers WHERE locationid='".$location_id."'");
$fetch_state = mysql_fetch_array($computer_query2);

$gotname = $fetch_state;
var_export($gotname);
$filter = mysql_fetch_array($c2);

while ( list($key, $value) = each($filter));
{
	echo "$key => $value \r\n";
}


	foreach($gotname as $f_name => $val)
	{
		

		foreach ($filter as $name) {
			
			
				
				$get_pct =similar_text($f_name, $name, $pct);
				if ($get_pct < 10) {
					
					echo $f_name ." matches ".$name." at ".$pct."%<br>";
					var_export($gotname);
					var_export($filter);
exit();
				}
			
					
			
		}

 

[PTNL]

I'm not a regular PHP dev, but at a glance I believe that your "similar_text" method is returning a match consideration that you're not expecting. (I'll also assume that your previous "while" dump gives the full range of values you'd expect to consider in the matching logic.)

I suggest looking into strstr or stristr; the latter being case-insensitive. In fact, the manual for stristr gives an example that would work for you.

Edit: You have "exit" in your inner foreach loop; did you mean "break" instead? Have you stepped through the iterations? Also, use the "CODE" tags in your post instead of "PHP" tags; the multi-color formatting is obnoxious.