Programming Forums
Page 1 of 2 1 2 >
Show 40 post(s) from this thread on one page

Programming Forums (http://www.programmingforums.org/forumindex.php)
-   PHP (http://www.programmingforums.org/forum29.html)
-   -   Select Non distinct in MySQL (http://www.programmingforums.org/showthread.php?t=14669)

davil Dec 3rd, 2007 10:12 AM
Select Non distinct in MySQL
 
Hi all,

I know this isn't exactly PHP but I was wondering if there's a simple MySQL query you can use to get back non-distinct lines... like for example you can use SELECT DISTINCT to only give you back the rows with that unique value in a field or whatever... but I'm looking to do the opposite.. I can filter the data with PHP if needs be but I'd much prefer to only get the correct data from the SQL instead and leave unneeded programming out of it...

I have a field called mac address on my DB and there are duplicate items and I simply want to be able to pull up details for the mac addresses that are reported twice so I can remove old data from the DB....

for example, if I wanted to get back unique mac addresses on the DB only I would do this:

:
  1. $sql = "SELECT DISTINCT `primarymac` FROM `aidahardware` ORDER BY 'datestamp' DESC";
  2. $result = mysql_query($sql) or die(mysql_error());

but how do I do the exact opposite??

Any help is much appreciated as I cannot find the answer I'm looking for through google etc.

davil Dec 3rd, 2007 10:14 AM
Re: Select Non distinct in MySQL
 
I tell a lie, I've just found what I think I'm looking for :

SELECT primarymac, COUNT(*) as count
FROM aidahardware
GROUP BY primarymac
HAVING count > 1

Arla Dec 3rd, 2007 12:10 PM
Re: Select Non distinct in MySQL
 
Was going to recommend exactly that.

Having Count > X is a useful query.

Ancient Dragon Dec 3rd, 2007 12:33 PM
Re: Select Non distinct in MySQL
 
Don't know about MySql but Sybase database the having clause can be somewhat slow, so I would code with the where clause

SELECT primarymac, COUNT(*) as count
FROM aidahardware
where count > 1

Sanjay Aggarwal Dec 4th, 2007 2:55 AM
Re: Select Non distinct in MySQL
 
SELECT primarymac, COUNT(*) as count
FROM aidahardware
GROUP BY primarymac
HAVING count > 1

Sanjay Aggarwal

titaniumdecoy Dec 4th, 2007 3:03 AM
Re: Select Non distinct in MySQL
 
Once again, Sanjay Aggarwal manages to regurgitate information identical to what has already been posted in a thread.

davil Dec 4th, 2007 4:49 AM
Re: Select Non distinct in MySQL
 
Quote:
Originally Posted by Ancient Dragon (Post 137978)
Don't know about MySql but Sybase database the having clause can be somewhat slow, so I would code with the where clause

SELECT primarymac, COUNT(*) as count
FROM aidahardware
where count > 1
I tried that and got the following error:
Unknown column 'count' in 'where clause'

davil Dec 4th, 2007 4:53 AM
Re: Select Non distinct in MySQL
 
Ok so my original code looked right except I wanted everything so this is what worked for me:

:
  1. SELECT *, COUNT(*) AS count FROM aidahardware GROUP BY primarymac HAVING count > 1

davil Dec 4th, 2007 11:06 AM
Re: Select Non distinct in MySQL
 
Well I have this code working great now except for one thing... I can't get a number of how many results coming back from the DB and store it to a variable. see my variable $total_results... what am I doing wrong?
Error message is
supplied argument is not a valid MySQL result resource in non_unique_mac.php


:
  1. <?php
  2. require('config.php');
  3.  
  4. $sql = "SELECT *, COUNT(*) as count FROM `aidahardware` WHERE `primarymac` not like '00-00-00-00-00-00' GROUP BY primarymac HAVING count > 1 ";
  5. $sqltwo = "SELECT COUNT(*) as Num FROM `aidahardware` WHERE `primarymac` not like '00-00-00-00-00-00' GROUP BY primarymac HAVING count > 1 ";
  6.  
  7. $total_results = mysql_result(mysql_query($sqltwo),0);
  8.  
  9. $sqlcake = mysql_query($sql) or die(mysql_error());
  10.  
  11.  
  12. echo "TOTAL RESULTS=$total_results<br>";
  13.  
  14.  
  15. echo "<br><br>";
  16. echo "<table border='1'>";
  17.  
  18. echo "<tr>\n";
  19. echo "<td><b>Primary Mac</a></td>";
  20.  
  21. while($row = mysql_fetch_array($sqlcake)){
  22. echo "</tr><tr>\n";
  23. echo "<td><b><a href='index.php?locate=showmac&mac=$primarymac'>$primarymac</td>";
  24. }
  25.  
  26. echo "</tr>";
  27. echo "</table>\n";
  28.  
  29. ?>

Sane Dec 4th, 2007 12:42 PM
Re: Select Non distinct in MySQL
 
Wow. Slow down. Use int mysql_num_rows ( resource $result ) if you want to get the number of results for a query.


All times are GMT -5. The time now is 3:43 AM.
Page 1 of 2 1 2 >
Show 40 post(s) from this thread on one page

Powered by vBulletin® Version 3.7.0, Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Copyright ©2007 DaniWeb® LLC