Advice on how to check for duplicate results in query

[sfraise]

I'm working on a directory component and have kind of a tricky thing I need to do.

 

The list of results pop through ajax on the left side along with it's google map number tag, and the google map pops on the right side. To make it even trickier the results are popped through a radius search by proximity.

 

The problem is if there are multiple results with the same address you end up having all of the number tags on the map buried under the last tag with that address, plus it makes for kind of a long list.

 

What I need to do is run a check to see if the previous result is the same address, and if yes not pop that result in a new table cell, rather stick it in a jquery drop down div so that all of the following results with the same address are stuffed in the jquery drop down in the same table cell under the first result with that address.

 

In a perfect world all of the listings with the same addresses would be submitted in order so I could just do a check for the previous itemid's address, but of course that's not the case. I thought about running a query to grab ALL of the addresses and then check the item's address against it to see if it matches and if yes echo the jquery div instead of the <td>, but obviously that won't work because that will cause the first item with that address to be put in the div also. Plus I don't know if that's a very efficient way to go about it anyway.

 

Here's kind of the basic thing I'm working with:

$query = "SELECT data_txt FROM jos_sobi2_fields_data WHERE fieldid = 76 AND itemid = $mySobi->id";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$myaddress = $row['data_txt'];
}
echo "<td $style>";

 

Any ideas?

[titan21]

Not sure I am totally understanding you but you could amend your SQL to include "LIMIT 1" so that it only returns one record.

 

P.S - I think this thread needs to be moved to SQL Help - but I'm not sure how to do that!

[sfraise]

I can't just limit it to 1 as I need all of the other listings with the same address to show as well, they're not duplicate listings, they just have the same address. What this is is a directory of AA and NA meetings and many times there are multiple meetings for different things at different times at the same location which is why we have the issue of the duplicate addresses.

 

I'm not sure the sql section is the best place for this as this is more of a way to handle the sql with php and perhaps regex as opposed on how to do the actual query.

 

If you want to get a better idea of what I'm talking about you can go to the dev site at http://www.erecoverydev.com, log in with user test and password test123. Click the resource center link in the top menu, then click on the find a meeting link on the left menu. Do a search for within 200 miles of 55403, this should pop some results. You'll see what I'm talking about with multiple listings with the same address.

[JasonLewis]

You can use either DISTINCT or GROUP BY. Both will achieve what you want. I'd go with GROUP BY though:

 

SELECT data_txt FROM jos_sobi2_fields_data WHERE fieldid = 76 AND itemid = $mySobi->id GROUP BY data_txt

 

No reason to do this in PHP, you're better off restricting the query like above.

[sfraise]

Well it's actually a bit more complex than that.

The query I listed there for $myaddress was just a query to pull that item's address in order to check against the results.

The actual listing results are popped by a pretty extensive set of code spread across several files to incorporate the regular search, radius search, and google map and fired through ajax.

 

What I'm hoping to do is figure out a way to set an if/else statement to show the <td> if it's unique or the first result of multiple addresses, else if it's the 2nd or subsequent listing with the same address to echo the <div id="jquerydiv">. But the key here is to try and somehow accomplish this without having to rewrite the actual search code because man that thing is a complex beast.

[JasonLewis]

You could add the address to an array, and use in_array to see if the address has already been used.

 

$query = "SELECT data_txt FROM jos_sobi2_fields_data WHERE fieldid = 76 AND itemid = $mySobi->id";
$result = mysql_query($query) or die(mysql_error());

// An array of unique addresses
$unique = array();

while($row = mysql_fetch_array($result)){
    if(in_array($row['data_txt'], $unique)){
        // This is not a unique address, add it to the div or whatever
    }else{
        // Address is unique, new row or something and add it to the array.
        $unique[] = $row['data_txt'];
}

[sfraise]

Excellent Jaysonic, I'll give it a shot and see what I come up with.

[sfraise]

My head's a bit more clear today and was able to think of the best way to do this.

I decided the best way to go about it was to do a query for the item's address, then do a second query pulling all of the rows that match the item's address and then count the number of rows returned. If the number = 1 then echo a td, else echo a div.

 

Now I just have to be able to handle the 1st listing with multiple addresses differently than all of the other ones in order for that one to be in a td while the rest fall under divs. Any good ideas on how to go about that?

 

Here's what I have so far that's working as I want:

$myquery = "SELECT data_txt FROM jos_sobi2_fields_data WHERE fieldid = 76 AND itemid = $mySobi->id";
$myresult = mysql_query($myquery) or die(mysql_error());
while($row = mysql_fetch_array($myresult)){
$myaddress = $row['data_txt'];
}
$query = "SELECT data_txt FROM jos_sobi2_fields_data WHERE data_txt = '$myaddress'";
$result = mysql_query($query) or die(mysql_error());
$numrows = mysql_num_rows($result);
while($row = mysql_fetch_array($result)) {
if($numrows == 1) {
echo "<td $style>"; }
elseif($numrows > 1) {
echo "<div id=\"jquerydiv\">"; }
}