/    Sign up×
Community /Pin to ProfileBookmark

Displaying only unique variables in a loop

Copy linkTweet thisAlerts:
Oct 23.2006

Ok – I’ve built a list of businesses that can be sorted by category or city. Some of the businesses are listed in more than one category, so when the list is sorted by city their listing is displayed multiple times.

So how can I set up the loop so that it will only show a listing if it has a unique value for $ad_name in the code below:

[code=php]// SHOW LISTINGS FOR THIS CITY

$query3 = “SELECT * FROM advertisers WHERE city = ‘$city_id2’ ORDER BY name”;
$result3 = mysql_query($query3) or die(‘Query failed: ‘ . mysql_error());

while($row3 = mysql_fetch_array($result3))
{

$ad_id = $row3[‘id’];
$ad_name = $row3[‘name’];
$ad_address = $row3[‘address’];
$ad_city = $row3[‘city’];
$ad_link = $row3[‘link’];
$ad_email = $row3[’email’];
$ad_phone = $row3[‘phone’];

echo ‘<div>’ . $ad_name . ‘</div>’;[/code]

That’s a portion of the code that pulls the listings from the database and starts displaying them.

Is it possible to only show unique listings for each city?

Thanks for your help ?

to post a comment
PHP

17 Comments(s) ↴

Copy linkTweet thisAlerts:
@so_is_thisOct 23.2006 — I haven't worked much with MySQL. Does it support the DISTINCT clause in its SQL SELECT syntax?
Copy linkTweet thisAlerts:
@The_Little_GuyOct 23.2006 — Give this a go:
[code=php]$query3 = "SELECT DISTINCT name FROM advertisers WHERE city = '$city_id2' ORDER BY name"; [/code]
Copy linkTweet thisAlerts:
@jeffy777authorOct 23.2006 — Ok, that works if I only select "name", but I need to get the other fields as well.

I tried adding a couple other fields:
[code=php]$query3 = "SELECT DISTINCT name, id, address FROM advertisers WHERE city = '$city_id2' ORDER BY name";[/code]

....but then it gives me the non-unique rows again as well ? Any ideas?
Copy linkTweet thisAlerts:
@The_Little_GuyOct 23.2006 — Are you talking something like this?
[code=php]if(statement){
$query3 = "SELECT DISTINCT name FROM advertisers WHERE city = '$city_id2' ORDER BY name";
}elseif(statement){
$query3 = "SELECT DISTINCT id FROM advertisers WHERE city = '$city_id2' ORDER BY id";
}[/code]
Copy linkTweet thisAlerts:
@SheldonOct 23.2006 — just a guess in this case

[code=php]
 $query3 = "SELECT DISTINCT(name), id, address FROM advertisers WHERE city = '$city_id2' ORDER BY name";
[/code]
Copy linkTweet thisAlerts:
@jeffy777authorOct 23.2006 — Are you talking something like this?
[code=php]if(statement){
$query3 = "SELECT DISTINCT name FROM advertisers WHERE city = '$city_id2' ORDER BY name";
}elseif(statement){
$query3 = "SELECT DISTINCT id FROM advertisers WHERE city = '$city_id2' ORDER BY id";
}[/code][/QUOTE]

No, the only field that needs to be distinct in this case is "name", but I need the data from the other fields as well (adress, city, etc.) so can display them. It would be nice to do it all in one query if possible.
Copy linkTweet thisAlerts:
@jeffy777authorOct 23.2006 — just a guess in this case

[code=php]
 $query3 = "SELECT DISTINCT(name), id, address FROM advertisers WHERE city = '$city_id2' ORDER BY name";
[/code][/QUOTE]

Strangely, that still displays the non-unique names as well. If I just select "DISTINCT(name)" and no other field it works fine and only shows the unique names, but if I add "DISTINCT(name), id, address" then it shows the non-unique names.
Copy linkTweet thisAlerts:
@SheldonOct 23.2006 — i think you may have to use 2 queries, the first a distinct name, the second pulling all records that match the first's results.
Copy linkTweet thisAlerts:
@jeffy777authorOct 23.2006 — i think you may have to use 2 queries, the first a distinct name, the second pulling all records that match the first's results.[/QUOTE]

That's what I was thinking too, but the weird thing is that it works fine if I take "id" out:

[code=php]$query3 = "SELECT DISTINCT(name), address FROM advertisers WHERE city = '$city_id2' ORDER BY name";[/code]

That query does the job and only returns unique values for "name", but if I add "id" in there it returns everything:

[code=php]$query3 = "SELECT DISTINCT(name), id, address FROM advertisers WHERE city = '$city_id2' ORDER BY name";[/code]

Is it because "id" is the first and primary field in the table?
Copy linkTweet thisAlerts:
@jeffy777authorOct 23.2006 — Alright, I take that back: adding other fields to the query works fine for the first few cities, but then it starts showing non-unique names after that ?

I think I'll have to use an additional query...
Copy linkTweet thisAlerts:
@carlhOct 23.2006 — what version of mysql? if it's 5.0 you can run a subquery
<i>
</i>select name, id, address, etc
from advertisers
where name
= ( select max(name)
from advertisers)
order
by name


--not tested code--

edit: the reason you're still getting dupes is because it is grouping distinct by all the fields, not each field individually
Copy linkTweet thisAlerts:
@The_Little_GuyOct 23.2006 — $query3 = "SELECT DISTINCT name, id, address FROM advertisers WHERE city= '$city_id2' ORDER BY name";
Copy linkTweet thisAlerts:
@jeffy777authorOct 23.2006 — $query3 = "SELECT DISTINCT name, id, address FROM advertisers WHERE city= '$city_id2' ORDER BY name";[/QUOTE]

No, that still returns dupes.

what version of mysql? if it's 5.0 you can run a subquery[/quote]

4.1.21

I just used 2 queries:

[code=php]$query3 = "SELECT DISTINCT(name) FROM advertisers WHERE city = '$city_id2' ORDER BY name";
$result3 = mysql_query($query3) or die('Query failed: ' . mysql_error());

while($row3 = mysql_fetch_array($result3))
{

$ad_name = $row3['name'];
$ad_name = addslashes($ad_name);

$query456 = "SELECT * FROM advertisers WHERE name = '$ad_name' and city = '$city_id2' LIMIT 1";
$result456 = mysql_query($query456) or die('Query failed: ' . mysql_error());[/code]

If someone has a more effcient solution, please let me know ?
Copy linkTweet thisAlerts:
@so_is_thisOct 23.2006 — I seem to remember that DISTINCT and ORDER BY (or GROUP BY) go hand in hand. You need to have the same column list for both.
Copy linkTweet thisAlerts:
@jeffy777authorOct 23.2006 — I seem to remember that DISTINCT and ORDER BY (or GROUP BY) go hand in hand. You need to have the same column list for both.[/QUOTE]

Just tried this and it still returns dupes:

[code=php]$query3 = "SELECT DISTINCT name, id, address FROM advertisers WHERE city= '$city_id2' ORDER BY name, id, address";[/code]
Copy linkTweet thisAlerts:
@so_is_thisOct 23.2006 — Yes, that is true. When I've tried to use DISTINCT in the past, I now remember that I began to wonder of what benefit DISTINCT is supposed to be for this very reason. ? Because, DISTINCT operates on the entire list -- not just the first column in the list. If anybody figures this out, I'd really like to know the solution. ?
Copy linkTweet thisAlerts:
@jeffy777authorOct 23.2006 — Well, the only way I can get the desired results is with 2 queries ([url=http://www.webdeveloper.com/forum/showpost.php?p=656155&postcount=14]see this post[/url])

So thanks for your help everyone. Using DISTINCT on the first query did the job, but if there's still a better method, please let me know ?
×

Success!

Help @jeffy777 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 6.2,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,
)...