Using mysql distinct to get all fields

[Exoon]

Hello,

 

Ive got a mysql database but i used a script to add a bunch of file names but it entered with a few errors and theres quite a few that got entered 4-5 times.

 

I can get all the names fine using "SELECT DISTINCT name FROM games" but how can i export all the other fields not just the names. I want to select everything but only DISTINCT on the names

 

 

[gristoi]

SELECT DISTINCT(name), field2, field3 FROM games

[Exoon]

Hi,

 

I tried that but dosen't seem to work. It just selects my whole database

 

SELECT DISTINCT(name) ,id,console FROM games

[Psycho]

You don't. If you want data from ALL the records, then you select all the records. It is a simple process to filter out the unique values in a particular field in the processing logic.

 

however, I'm a little confused. You say

...but it entered with a few errors and theres quite a few that got entered 4-5 times.

 

So, if these were in error, why do you need the data from these records, but not the names. Seems like you would want to run a query to remove the records in error.

[Exoon]

Yes i do want to remove records in error i want to find names which are the same and if they are the same, delete the other entry's and keep the one good entry.

[Psycho]

Give me a few minutes and I'll give you a script. I hope all the records have a unique ID.

[Exoon]

yes they have a unique ID.

[Psycho]

Not tested, so there may be syntax errors, but I comment out the line that would actually do the delete so there is no harm in running it to test. I also added code to see the results before actually executing the delete operation. Once you fix any syntax errors and are confident of the results, uncomment the line that does the actual delete operation.

 

<?php

//Get list of all records
$query = "SELECT id, name FROM games";
$result = mysql_query($result) or die(mysql_error());

$saveAry = array();
$deleteAry = array();
$saveHTML = '';
$deleteHTML = '';
//Process records into save/delete lists based upon duplicate names
while($row = mysql_fetch_assoc())
{
    if(!in_array($row['name'], $saveAry))
    {
        $saveAry[$row['id']] = $row['name'];
        $saveHTML .= "<tr><td>{$row['id']}</td><td>{$row['name']}</td></tr>\n";
    }
    else
    {
        $deleteAry[$row['id']] = $row['name'];
        $deleteHTML .= "<tr><td>{$row['id']}</td><td>{$row['name']}</td></tr>\n";
    }
}

$query = "DELETE FROM games WHERE id IN (" .implode(',', array_keys($deleteAry)) . ")";
//$result = mysql_query($result) or die(mysql_error());
//RUN THE SCRIPT WITH THE LINE ABOVE COMMENTED OUT TO TEST THE RESULTS
//ONCE YOU ARE SATISFIED WITH THE RESULTS RUN AGAIN WITH THE LINE UNCOMMENTED


echo "<b>Delete Query:</b><br>$query<br>\n";
echo "<table>\n"; 
echo "<tr><th colspan=\"2\">SAVED RECORDS</th></tr>\n";
echo $saveHTML;
echo "<tr><th colspan=\"2\">DELETED RECORDS</th></tr>\n";
echo $deleteHTML;
echo "</table>\n";
?>

[Exoon]

Hi,

 

Thanks for the code. I changed the field names to what they actually are, as i was just shorting it down when i first posted. Can you check it will still work ok with what ive added.

 

Im getting this error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #14' at line 1

 

<?php

//Get list of all records
$query = "SELECT r_id, r_name FROM roms WHERE r_console = 'NES'";
$result = mysql_query($result) or die(mysql_error());

$saveAry = array();
$deleteAry = array();
$saveHTML = '';
$deleteHTML = '';
//Process records into save/delete lists based upon duplicate r_names
while($row = mysql_fetch_assoc())
{
    if(!in_array($row['r_name'], $saveAry))
    {
        $saveAry[$row['r_id']] = $row['r_name'];
        $saveHTML .= "<tr><td>{$row['r_id']}</td><td>{$row['r_name']}</td></tr>\n";
    }
    else
    {
        $deleteAry[$row['r_id']] = $row['r_name'];
        $deleteHTML .= "<tr><td>{$row['r_id']}</td><td>{$row['r_name']}</td></tr>\n";
    }
}

$query = "DELETE FROM roms WHERE r_id IN (" .implode(',', array_keys($deleteAry)) . ")";
//$result = mysql_query($result) or die(mysql_error());
//RUN THE SCRIPT WITH THE LINE ABOVE COMMENTED OUT TO TEST THE RESULTS
//ONCE YOU ARE SATISFIED WITH THE RESULTS RUN AGAIN WITH THE LINE UNCOMMENTED


echo "<b>Delete Query:</b><br>$query<br>\n";
echo "<table>\n"; 
echo "<tr><th colspan=\"2\">SAVED RECORDS</th></tr>\n";
echo $saveHTML;
echo "<tr><th colspan=\"2\">DELETED RECORDS</th></tr>\n";
echo $deleteHTML;
echo "</table>\n";
?>

[Psycho]

That' my mistake. Use the $query variable in the mysql_query() function.

 

Wrong

$result = mysql_query($result) or die(mysql_error());

 

Right

$result = mysql_query($query) or die(mysql_error());

 

You need to make the change on the first line of code as well as the line I commented towards the bottom

[Exoon]

I changed what you mentioned, The results i get are as follows:

 

 

 

Delete Query:

DELETE FROM roms WHERE r_id IN ()

SAVED RECORDS

DELETED RECORDS

 

[Psycho]

Then I would have to assume you have no matching records to the initial SELECT query. Add this after the first query is run

echo "Records returned from SELECT query: " . mysql_num_rows($result) . "<br>\n";

[Exoon]

Now i get:

 

 

Records returned from SELECT query: 10788

Delete Query:

DELETE FROM roms WHERE r_id IN ()

SAVED RECORDS

DELETED RECORDS

[Psycho]

Ah, hell

 

Change this

while($row = mysql_fetch_assoc())

 

To this

while($row = mysql_fetch_assoc($result))

[Exoon]

Ok i get a massive list now. This is only going to delete the roms which are under Deleted right? Because that looks perfect if so.

 

Will wait for your reply before i run it.

 

Thanks for the help upto now.

[Exoon]

I just tried running it with that line uncommented and now i get

 

Records returned from SELECT query: 10788

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

 

I added $query in the brackets like you previously asked. Heres the code:

 

<?php

//Get list of all records
$query = "SELECT r_name FROM roms WHERE r_console = 'NES'";
$result = mysql_query($query) or die(mysql_error());
echo "Records returned from SELECT query: " . mysql_num_rows($result) . "<br>\n";
$saveAry = array();
$deleteAry = array();
$saveHTML = '';
$deleteHTML = '';
//Process records into save/delete lists based upon duplicate r_names
while($row = mysql_fetch_assoc($result))
{
    if(!in_array($row['r_name'], $saveAry))
    {
        $saveAry[$row['r_id']] = $row['r_name'];
        $saveHTML .= "<tr><td>{$row['r_id']}</td><td>{$row['r_name']}</td></tr>\n";
    }
    else
    {
        $deleteAry[$row['r_id']] = $row['r_name'];
        $deleteHTML .= "<tr><td>{$row['r_id']}</td><td>{$row['r_name']}</td></tr>\n";
    }
}

$query = "DELETE FROM roms WHERE r_id IN (" .implode(',', array_keys($deleteAry)) . ")";
$result = mysql_query($query) or die(mysql_error());
//RUN THE SCRIPT WITH THE LINE ABOVE COMMENTED OUT TO TEST THE RESULTS
//ONCE YOU ARE SATISFIED WITH THE RESULTS RUN AGAIN WITH THE LINE UNCOMMENTED


echo "<b>Delete Query:</b><br>$query<br>\n";
echo "<table>\n"; 
echo "<tr><th colspan=\"2\">SAVED RECORDS</th></tr>\n";
echo $saveHTML;
echo "<tr><th colspan=\"2\">DELETED RECORDS</th></tr>\n";
echo $deleteHTML;
echo "</table>\n";
?>

[Adam]

Hi,

 

I tried that but dosen't seem to work. It just selects my whole database

 

SELECT DISTINCT(name) ,id,console FROM games

 

Sorry a bit off-topic here, but just FYI - 'distinct' only returns unique rows in the resulting data set. If each row has a different ID, and you select the ID as part of the statement, then they're not classed as distinct and both will be returned.

[Psycho]

Echo the delete query to the page

$query = "DELETE FROM roms WHERE r_id IN (" .implode(',', array_keys($deleteAry)) . ")";
echo $query;
//$result = mysql_query($query) or die(mysql_error());

 

[Exoon]

Records returned from SELECT query: 10788

DELETE FROM roms WHERE r_id IN ()Delete Query:

DELETE FROM roms WHERE r_id IN ()

[Psycho]

This is all simple debugging. I would hope you could figure out what to do next. The query is failing because there are no IDs identified. That would indicate to me that the $deleteAry may have no values. You should have picked up on that previously since the delete query was already echo'd to the page.

 

Try echoing count($deleteAry) to the page to verify it has values. If it does, then do a print_r($deleteAry) to see if it has the keys/values that you expect.

[Exoon]

Hi,

 

When i do count it comes back with 1.

 

When i print_r it comes back with Array ( [] => Zombie Hunter )

 

In the list of all the things though it is fine. Under "Deleted" It shows a list of all the duplicates.

 

I don't really understand this code my php knowledge is very basic.

[Psycho]

OK, I see the problem. The first query was modified to ONLY get the name - not the ID. So when the code runs through the loop to generate the delete array the ID field is empty. Each iteration of the loop is overwriting the previous value assigned to the array item with a null key. You should have noticed this in the display of record on the page. No matter change the first query to this:

$query = "SELECT r_id, r_name FROM roms WHERE r_console = 'NES'";

[Exoon]

Hi,

 

Its got rid of pretty much all of them. However theres still a few duplicates, Isnt a major problem just wondering if anything can be done to get rid of the remaining duplicates.

 

 

Thanks for all your help upto now!

[Psycho]

I don't see any reason why the code I provided would NOT remove duplicates values, so my *guess* would be that those two records which both show "8 Eyes" are not really duplicates. For example, one may have additional spaces before, after or even between words. When rendered in HTML they will look exactly the same. Or, perhaps a character is displayed using a character code in one value and not the other. Again, they would look the same on the page, but would actually be different. Or, there could be HTML tags within the value which also wouldn't be displayed.

 

You could inspect the HTML source code, but that can be tedious if you have a lot of records to check. One easy way to check if the values are not EXACTLY the same would be to convert everything to HTML entities when displaying it. So, the output on the screed should display what is exactly in the DB, but the actual HTML source would be different. Also, on the initial query you should probably do an ORDER BY ont he name field to help make looking for duplicates easier.

 

1. Change first query to this

$query = "SELECT r_id, r_name FROM roms WHERE r_console = 'NES' ORDER BY r_name";

 

2. Change the following two blocks of code that are used to display the data

while($row = mysql_fetch_assoc($result))
{
    if(!in_array($row['r_name'], $saveAry))
    {
        $saveAry[$row['r_id']] = $row['r_name'];
        $saveHTML .= "<tr><td>{$row['r_id']}</td><td>{$row['r_name']}</td><td><pre>[".htmlenteties($row['r_name'])."]</pre></td></tr>\n";
    }
    else
    {
        $deleteAry[$row['r_id']] = $row['r_name'];
        $deleteHTML .= "<tr><td>{$row['r_id']}</td><td>{$row['r_name']}</td><td><pre>[".htmlenteties($row['r_name'])."]</pre></td></tr>\n";
    }
}

 

echo "<b>Delete Query:</b><br>$query<br>\n";
echo "<table>\n"; 
echo "<tr><th colspan=\"3\">SAVED RECORDS</th></tr>\n";
echo "<tr><th>ID</th><th>DB Value</th><th>Escaped Value</th></tr>\n";
echo $saveHTML;
echo "<tr><th colspan=\"3\">DELETED RECORDS</th></tr>\n";
echo "<tr><th>ID</th><th>DB Value</th><th>Escaped Value</th></tr>\n";
echo $deleteHTML;
echo "</table>\n";

 

Now, rerun the script and review the results. If you see two similar values in the 2nd column check to see if the values are the same in the third column.

 

If you find that there are two values that are visually identical (but not really identical), Just pick the ID of the one you want removed and run a query

DELETE FROM roms WHERE r_id = [THE_ID_YOU_WANT_TO_DELETE]

[Exoon]

Hi,

 

Before i saw your reply i just tried adding rtrim to the r_name and it worked perfectly. Must of been some whitespace when i added them into the DB.

 

Thanks for all your help!