Programming Forums > Web Development and Design > PHP

davil · Dec 3rd, 2007, 9:12 AM

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:

 php Syntax (Toggle Plain Text) 
$sql = "SELECT DISTINCT `primarymac` FROM `aidahardware` ORDER BY 'datestamp' DESC";
$result = mysql_query($sql) or die(mysql_error());
$sql = "SELECT DISTINCT `primarymac` FROM `aidahardware` ORDER BY 'datestamp' DESC";
$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, 9:14 AM

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, 11:10 AM

Was going to recommend exactly that.

Having Count > X is a useful query.

Ancient Dragon · Dec 3rd, 2007, 11:33 AM

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, 1:55 AM

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

Sanjay Aggarwal

titaniumdecoy · Dec 4th, 2007, 2:03 AM

Once again, Sanjay Aggarwal manages to regurgitate information identical to what has already been posted in a thread.

davil · Dec 4th, 2007, 3:49 AM

Quote:

Originally Posted by Ancient Dragon

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, 3:53 AM

Ok so my original code looked right except I wanted everything so this is what worked for me:

 sql Syntax (Toggle Plain Text) 
SELECT *, COUNT(*) AS count FROM aidahardware GROUP BY primarymac HAVING count > 1
SELECT *, COUNT(*) as count FROM aidahardware GROUP BY primarymac HAVING count > 1

davil · Dec 4th, 2007, 10:06 AM

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

 php Syntax (Toggle Plain Text) 
<?php
require('config.php');
 
$sql = "SELECT *, COUNT(*) as count FROM `aidahardware` WHERE `primarymac` not like '00-00-00-00-00-00' GROUP BY primarymac HAVING count > 1 ";
$sqltwo = "SELECT COUNT(*) as Num FROM `aidahardware` WHERE `primarymac` not like '00-00-00-00-00-00' GROUP BY primarymac HAVING count > 1 ";
 
$total_results = mysql_result(mysql_query($sqltwo),0);
 
$sqlcake = mysql_query($sql) or die(mysql_error());
 
 
echo "TOTAL RESULTS=$total_results<br>";
 
 
echo "<br><br>";
echo "<table border='1'>";
 
echo "<tr>\n";
echo "<td><b>Primary Mac</a></td>";
 
while($row = mysql_fetch_array($sqlcake)){
echo "</tr><tr>\n";
echo "<td><b><a href='index.php?locate=showmac&mac=$primarymac'>$primarymac</td>";
}
 
echo "</tr>";
echo "</table>\n";
 
?>
<?php
require('config.php');

$sql = "SELECT *, COUNT(*) as count FROM `aidahardware` WHERE `primarymac` not like '00-00-00-00-00-00' GROUP BY primarymac HAVING count > 1 ";
$sqltwo = "SELECT COUNT(*) as Num FROM `aidahardware` WHERE `primarymac` not like '00-00-00-00-00-00' GROUP BY primarymac HAVING count > 1 ";

$total_results = mysql_result(mysql_query($sqltwo),0);

$sqlcake = mysql_query($sql) or die(mysql_error());


echo "TOTAL RESULTS=$total_results<br>";


echo "<br><br>";
echo "<table border='1'>";

echo "<tr>\n";
echo "<td><b>Primary Mac</a></td>";

while($row = mysql_fetch_array($sqlcake)){
echo "</tr><tr>\n";
echo "<td><b><a href='index.php?locate=showmac&mac=$primarymac'>$primarymac</td>";
}

echo "</tr>";
echo "</table>\n";

?>

Sane · Dec 4th, 2007, 11:42 AM

Wow. Slow down. Use int mysql_num_rows ( resource $result ) if you want to get the number of results for a query.

Dec 3rd, 2007, 9:12 AM	#1
davil Newbie Join Date: Nov 2007 Posts: 27 Rep Power: 0	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: php Syntax (Toggle Plain Text) $sql = "SELECT DISTINCT `primarymac` FROM `aidahardware` ORDER BY 'datestamp' DESC"; $result = mysql_query($sql) or die(mysql_error()); $sql = "SELECT DISTINCT `primarymac` FROM `aidahardware` ORDER BY 'datestamp' DESC"; $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.

Dec 3rd, 2007, 9:14 AM	#2
davil Newbie Join Date: Nov 2007 Posts: 27 Rep Power: 0	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

Dec 3rd, 2007, 11:10 AM	#3
Arla Hobbyist Programmer Join Date: Mar 2005 Posts: 227 Rep Power: 4	Re: Select Non distinct in MySQL Was going to recommend exactly that. Having Count > X is a useful query.

Dec 3rd, 2007, 11:33 AM	#4
Ancient Dragon PFO God In Training Join Date: Jun 2005 Location: near St Louis, MO. (USA) Posts: 550 Rep Power: 4	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

Dec 4th, 2007, 1:55 AM	#5
Sanjay Aggarwal Newbie Join Date: Oct 2007 Posts: 12 Rep Power: 0	Re: Select Non distinct in MySQL SELECT primarymac, COUNT(*) as count FROM aidahardware GROUP BY primarymac HAVING count > 1 Sanjay Aggarwal

Dec 4th, 2007, 2:03 AM	#6
titaniumdecoy Expert Programmer Join Date: Nov 2005 Posts: 856 Rep Power: 3	Re: Select Non distinct in MySQL Once again, Sanjay Aggarwal manages to regurgitate information identical to what has already been posted in a thread.

Dec 4th, 2007, 3:53 AM	#8
davil Newbie Join Date: Nov 2007 Posts: 27 Rep Power: 0	Re: Select Non distinct in MySQL Ok so my original code looked right except I wanted everything so this is what worked for me: sql Syntax (Toggle Plain Text) SELECT , COUNT() AS count FROM aidahardware GROUP BY primarymac HAVING count > 1 SELECT , COUNT() as count FROM aidahardware GROUP BY primarymac HAVING count > 1

Dec 4th, 2007, 10:06 AM	#9
davil Newbie Join Date: Nov 2007 Posts: 27 Rep Power: 0	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 php Syntax (Toggle Plain Text) <?php require('config.php'); $sql = "SELECT , COUNT() as count FROM `aidahardware` WHERE `primarymac` not like '00-00-00-00-00-00' GROUP BY primarymac HAVING count > 1 "; $sqltwo = "SELECT COUNT() as Num FROM `aidahardware` WHERE `primarymac` not like '00-00-00-00-00-00' GROUP BY primarymac HAVING count > 1 "; $total_results = mysql_result(mysql_query($sqltwo),0); $sqlcake = mysql_query($sql) or die(mysql_error()); echo "TOTAL RESULTS=$total_results<br>"; echo "<br><br>"; echo "<table border='1'>"; echo "<tr>\n"; echo "<td><b>Primary Mac</a></td>"; while($row = mysql_fetch_array($sqlcake)){ echo "</tr><tr>\n"; echo "<td><b><a href='index.php?locate=showmac&mac=$primarymac'>$primarymac</td>"; } echo "</tr>"; echo "</table>\n"; ?> <?php require('config.php'); $sql = "SELECT , COUNT() as count FROM `aidahardware` WHERE `primarymac` not like '00-00-00-00-00-00' GROUP BY primarymac HAVING count > 1 "; $sqltwo = "SELECT COUNT() as Num FROM `aidahardware` WHERE `primarymac` not like '00-00-00-00-00-00' GROUP BY primarymac HAVING count > 1 "; $total_results = mysql_result(mysql_query($sqltwo),0); $sqlcake = mysql_query($sql) or die(mysql_error()); echo "TOTAL RESULTS=$total_results<br>"; echo "<br><br>"; echo "<table border='1'>"; echo "<tr>\n"; echo "<td><b>Primary Mac</a></td>"; while($row = mysql_fetch_array($sqlcake)){ echo "</tr><tr>\n"; echo "<td><b><a href='index.php?locate=showmac&mac=$primarymac'>$primarymac</td>"; } echo "</tr>"; echo "</table>\n"; ?>

Dec 4th, 2007, 11:42 AM	#10
Sane Programming Guru Join Date: Apr 2005 Location: Waterloo, Ontario Posts: 1,888 Rep Power: 5	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.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Connecting MySQL and PHP	titaniumdecoy	PHP	10	Feb 25th, 2008 7:47 PM
MySql	paulchwd	Other Web Development Languages	8	Feb 8th, 2007 9:17 PM
Better way to Select from MySQL?	Xeoncross	PHP	3	Oct 3rd, 2006 3:48 PM
SQL select distinct	OpenLoop	Other Programming Languages	2	Apr 10th, 2006 8:30 PM
Tutorial - Using MySQL in C#	Darkhack	C#	12	Jan 17th, 2006 9:28 AM

Programming Forums > Web Development and Design > PHP

Select Non distinct in MySQL