Don't show if repeat

[coldfission]

I am running an SQL query through a database that will probably have repeat addresses.

The purpose of this query is to download addresses for printing Labels to send out catalogs.

I need a fast way to make sure no repeat address fields are repeated. I know this is possible by running through the whole query again for each record to see if it has any matches, but that will take forever. I need a fast way to do this.

 

Thanks

[per1os]

<?php
$viewed_addresses = array();

while ($address = mysql_fetch_array($address_qu)) {
      if (!in_array($address['address'], $viewed_addresses)) {
             echo $address['address'];
             $viewed_addresses[] = $address['address'];
       }
}

 

Should be fairly quick.

[Psycho]

The best way is to use GROUP BY or DISTINCT in your query so youwon't have any duplicates in your results to begin with.

 

If you were to provide the query you are using now someone should be able to show how to modify it.

[coldfission]

<?php
$viewed_addresses = array();

while ($address = mysql_fetch_array($address_qu)) {
      if (!in_array($address['address'], $viewed_addresses)) {
             echo $address['address'];
             $viewed_addresses[] = $address['address'];
       }
}

 

Should be fairly quick.

 

 

That worked great. Not putting tons of stress on the MySQL server is a priority, so this was nice.

[per1os]

The best way is to use GROUP BY or DISTINCT in your query so youwon't have any duplicates in your results to begin with.

 

If you were to provide the query you are using now someone should be able to show how to modify it.

 

That worked great. Not putting tons of stress on the MySQL server is a priority, so this was nice.

 

Yes my way works great, but mjdamato's way would be more efficient to be honest using the Group by and distinct as that would not stress the sql server anymore than it already was. But whichever method you choose either one works fairly well.

[coldfission]

Well the problem with changin the actual query is that the query is dynamic. It changes according to a lot of stuff. I don't want to change it if i don't have to, because it would end up becoming a huge mess and head ache.

[Psycho]

Well the problem with changin the actual query is that the query is dynamic. It changes according to a lot of stuff. I don't want to change it if i don't have to, because it would end up becoming a huge mess and head ache.

 

Having a working solution is the most important, but the solution you have is probably more of a stress on your system than doing a proper query. PHP and MySQL (or whichever database you are using) each have their own strengths and it is good practice to leverage those strengths.

 

I have seen people take a list of results from a query, dump it all into an array and then use PHP to sort the results. All that when a simple ORDER BY in the query would handle it very efficiently.

 

In any event, if you were to post the code you use to build your query I would be happy to take a look. It probably won't be very difficult to add in the necessary code as it would, most likely, just get appended at the end. If not, no loss.

 

Also, please mark the thread as solved if you don't plant to pursue this.

[coldfission]

SELECT SQL_CALC_FOUND_ROWS orders.orders_id AS ID, last_modified AS modified, customers_company AS organization, customers_name AS name, customers_street_address AS address, customers_city AS city, customers_state AS state, customers_postcode AS zip, customers_country AS country, customers_telephone AS phone, customers_email_address AS email, payment_method, value AS total, 'tackwholesale.com' AS source FROM orders
LEFT JOIN orders_total
ON orders.orders_id=orders_total.orders_id
WHERE orders_status=3 AND class='ot_total'
UNION ALL
SELECT ID, modified, NULL AS organization, CONCAT(f_name, ' ', l_name) AS name, address, city, state, zip, country, phone, email, payment_method, total, 'tack-wholesale.com' AS source FROM tack_orders
UNION ALL
SELECT NULL AS ID, NULL AS modified, NULL AS organization, CONCAT(f_name, ' ', l_name) AS name, street AS address, city, zip, state, country, phone, email, NULL AS payment_method, NULL AS total, 'Catalog Request' AS source FROM cat_req_static
UNION ALL
SELECT ID, modified, NULL AS organization, name, address, city, state, zip, country, phone, email, payment_method, total, 'eBay Payment' AS source FROM ebay_payments
ORDER BY modified DESC

 

Now that i actually think about it, i could just add it to the end and be done with it. This is just a sample query, because it is generated by a different page. One page needs to sort our repeat "email" and the other needs to sort out repeat "address".

[Psycho]

Two things you could try:

 

1) Remove the ALL switch for the UNION statements

 

or 2) Try adding this line just before the ORDER BY

 

GROUP BY address

[coldfission]

What would be the difference between using the ALL switch and not using the ALL switch in this situation? I ran it with the GROUP BY address and the ALL switch on, and it seemed to be fine. But, if you convince me that it is not fine, i will do a search string thing to get rid of it.

[Psycho]

Do you even know what the ALL switch is doing? Don't just copy and paste code, learn what it does. I had never used UNION before, but a quick search in the manual ( http://dev.mysql.com/doc/refman/5.0/en/union.html

) showed that ALL is the exact opposite of what you should be doing.

 

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements

 

So, the ALL switch is forcing your query to return duplicate rows. So, that is counter to what you are trying to achieve, is it not? However, just removing the ALL switch may not solve your problem depending on the actual structure and content of your tables. So, you may still need the GROUP BY clause.