MYSQL :: Eliminating Duplicate Results From Query

Hide Duplicate Search Results
i have a PHP script directory with an MYSQL database and it has some duplicate records.
the directory has a search functionality and duplicate results are showing up in the search results.
i would like to hide these duplicate results ( but dont want to delete them ).
all the records have a 'unique item' and i would like to filter the results using this 'unique item' ( so that a record with the same 'unique item' should not appear more than once )

please inform me how to filter my search results to avoid duplicates.
i am new to PHP and MYSQL. please inform me the possible code.

Removing Duplicate Data And Formating Results
I have several queries each returning results with duplicated data, such as this:

staff_id      date           hours        duty

1             1/1/04           8-5            C
2             1/1/04           9-6            W

1             2/1/04           9-4            C
2             2/1/04           8-5            W
3             2/1/04           8-5            C

And so on for each week day.

The tables are as follows:

rosters (rid, wknum, startdate,enddate)
date (dateid, date)
staffday (dateid, rosterid, staffid, hours, duty)
staffcontract (staffid, stafftype)
staff (staffid, name)

What I want at the end on screen is:

ID   DATE1  DATE2........ DATE6    DATE7

1     8-5       9-4              8-3         9-6
2     9-6       8-5              7-2         6-2
3     OFF      8-5              8-3         9-6

My question is how do I group/order/sort the results in mysql so that i don'
t have duplication in the returned data as above. I want to avoid complicat
ed if/then/else in my php when building the output table.

Strange Results When Trying To Do A Duplicate Entry Check
My goal with this is to check a database for duplicates using the first 25 characters of the "title" column in a database called "jos_content." The query should find the entries whose titles appear more than once and then the second part should take those entries and mark them for trash (state=-2 is trash).

CREATE TEMPORARY TABLE dupes
SELECT Left(title,25)
FROM jos_content
GROUP BY title
HAVING COUNT(*)>1; # Affected rows: 4

UPDATE jos_content
SET state="-2"
WHERE (title) IN (SELECT Left(title,25) FROM dupes); # MySQL returned an empty result set (i.e. zero rows).

The result for the first part finds entries but I suspect they aren't duplicates becuase the second part returns zero entries. The actual database has about 16 duplicate rows based on the first 25 characters of the title column.

Eliminating Blank Values
I have a database randomly scattered with 'NULL' values, being a
newbie to mysql, I was wondering if there was a single command to
replace all 'null' values with a string 'no data'. all the fields are
set to hold 255 character strings.

Eliminating Double Values From A Table
I need to consolidate data from many adress databases, which i allready sucessfully put into one table1. As Newbie i am reviing the Manuals and tutorials, but in 2 days i learned a lot about the powerfull command, but not how to solve the following question:

Is there a straight forward method to copy data from one table1 into the other table2, but not those rowse, which have same value in Fieldx.

All my Update and INSERT SELECT experiments somehow created more then less data in the target table.

Query Based On Results Of A Previous Query
So far I have managed to construct one query which gives me all individuals that have one of three titles.

based on this I now want to find all the individuals that are affiliated to those listed in the first query ....

ON DUPLICATE KEY UPDATE And Query Cache
After bludgeoning my head against the table for a bit, I figures out what the problem was that had been plaguing me.

When using a INSERT INTO ... ON DUPLICATE KEY UPDATE ...
statemtent, it seemed like the update was not taking place.
After viewing the webpage in question, (and doing the manual select statement), the old data was being returned.

I couldn't figure out why, until FINALLY, I added 'RESET QUERY CACHE' manually after the statement. Then the correct updated info was returned.

Apparently the query cache is not flushed after using the above ON DUPLICATE KEY UPDATE statement, like it does with other insert or updates.

What do I do now. Just not use that statement? Id like to avoid locking tables if possible. And I cant have the website showing the outdated info.

Query Help To Remove Duplicate Data
I have a query that returns: first name, last name, comments.

I would like the result to be something like:

Dan | Hudson | "here is a comment"
| | "here is another comment"
Lisa | Hudson | "my comment"
but currently the name is repeated every time there is a comment. Is there a way to do a group by clause or something that will remove the duplicates?

Dan

Query That Duplicate Row And Increment Date
I have a table EVENT
id date duration
1 2006-01-01 3
2 2006-04-23 5
3 2006-10-01 0

is possible made a query that do it?

id date
1 2006-01-01
1 2006-01-02
1 2006-01-03
1 2006-01-04
2 2006-04-23
2 2006-04-24
2 2006-04-25
2 2006-04-26
2 2006-04-27
2 2006-04-28
3 2006-10-01

URGENT: Please Help Me With My ON DUPLICATE KEY UPDATE Query
Could you please help me do this quickly.

I have a query:

Quote:

mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=a")

What I need it to do is that whne title and brand matches, it is then a duplicate listing. I have set up the unique key for this. When a duplicate listing is found it will then change the groupname value from "1" and update it with "a".

When it then finds the next duplicate listing that has the same title and brand as the duplicate listing that had it's groupname changes to "a", I need it to be change to "b" this time.

So I now have two listings that have a groupname of "a" and "b".

Now when there is more duplicate listings that have the same title and brand as "a" and "b", I now need the groupname to be updated to the actual listings id and not use "a", "b", "c", etc...

How can I do that.

I know how I can do the first one so that it updates to 8, but then how would I do the 2nd and 3rd ones.

Please help, I have to do this urgent as my database is really slow and it is damaging my site as it is taking about a minute or so to do each query.

Also, I would prefer it if it could be done in one single query. I can also use variables in the query and if statements plus other php like

Quote:

mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=$groupname")

Duplicate Yet Diferent Data In Sql Query
Is there a single SQL Query that will do the following?

mysql> SELECT * FROM user;
+----+----------+
| id | username |
+----+----------+
| 1 | Bill |
| 2 | Bob |
| 3 | Hank |
| 4 | Jim |
+----+----------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM email;
+----+-------+------+---------------------------------------+
| id | _from | _to | message |
+----+-------+------+---------------------------------------+
| 1 | 1 | 2 | Blah, blah, blah |
| 2 | 1 | 3 | I told Bob to Blah, Blah, Blah |
| 3 | 3 | 4 | Bill told Bob to Blah, Blah, Blah |
| 4 | 4 | 2 | Did Bill tell you to Blah, Blah, Blah |
+----+-------+------+---------------------------------------+
4 rows in set (0.00 sec)
What SQL Query would result in the following...???
mysql> SELECT ??????????????????????????????;
+----+-------+------+---------------------------------------+
| id | _from | _to | message |
+----+-------+------+---------------------------------------+
| 1 | Bill | Bob | Blah, blah, blah |
| 2 | Bill | Hank | I told Bob to Blah, Blah, Blah |
| 3 | Hank | Jim | Bill told Bob to Blah, Blah, Blah |
| 4 | Jim | Bob | Did Bill tell you to Blah, Blah, Blah |
+----+-------+------+---------------------------------------+
4 rows in set (0.00 sec)

Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is:

The key of one of the record has the format:

Rssss

The key of the second record has the format:

ssss

How can I delete both records from the table?

Ignore Duplicate Query Items
This formcode retrieves faxes from a DB but some are the same and I do not want those in my list... how do I tell it to ignore duplicates?PHP Code:

$sql = "select c.fax as fax ".
    "from #__comprofiler as c ".
    "left join #__users as u on c.user_id = u.id ".
           " where  (c.user_id = u.id) and (u.block =0) and (c.fax != '') and (u.usertype != 'Super Administrator') and (u.name != 'guest') ".
       " order by c.fax";

Help With Updating Duplicate Rows In Mysql Query.
Quote:

SELECT id, count(*) AS numlist FROM products GROUP BY category, name, brands HAVING numlist > 1 ORDER BY id ASC

What it does is find the duplicates. I also have a column called "app" which has a default set to "1". So now what I want to do is that when duplicates are found, I want the first duplicate row in each group to stay as a "1" and the others in the same group to be updated to a "2". I need this done for every group.

How can I do this. I have looked high and low accrossed the web, but can't seem to find any solution.

I have managed to find out how to group them and count the number of listings in each group, but I can't seem to figure out how to do the rest.

Also, if possible, I would like it to be done with one query.

Query Giving Me Results I Don't Want.
query:

SELECT sales_reps.sr_id,
sales_reps.order_id,
sales_reps.name,
sales_reps.job_number,
UNIX_TIMESTAMP( shop_orders.date ) AS date,
sales_reps.stage,
sales_reps.status,
pi.fname AS pfname,
pi.lname AS plname,
sales_reps.date_to_shop,
users.builder,
users.division,
shop_assignments.editor_id,
shopper.fname AS sfname,
shopper.lname AS slname,
ei.shop_result,
ei.good_tape
FROM sales_reps
LEFT JOIN shop_orders ON shop_orders.order_id = sales_reps.order_id
LEFT JOIN users ON users.user_id = shop_orders.builder_id
LEFT JOIN shop_assignments ON shop_assignments.sr_id = sales_reps.sr_id
LEFT JOIN users AS shopper ON shopper.user_id = shop_assignments.shopper_id
LEFT JOIN users AS pi ON pi.user_id = shop_assignments.pi_id
LEFT JOIN exit_interviews AS ei ON ei.shop_id = shop_assignments.shop_id
WHERE sales_reps.status = 'Rejected'
AND (
ei.shop_result != 'Trip x1'
OR ei.shop_result != 'Trip x2'
OR ei.shop_result != 'Non-Reg Trip x1'
OR ei.shop_result != 'Non-Reg Trip x2'
OR ei.shop_result != 'Office Closed'
)
ORDER BY users.builder, users.division, shop_orders.date DESC , sales_reps.stage ASC
It's not filtering out the records with the shop_result field matching what I've listed.

Formatting Query Results?
I have a shell script (sh) that queries a MySQL database and then formats the results to an html page. I realize shell is probably not the best choice for this but it's all I know. My problem is that I need to do a mysql query for every field in every record in order to put it into the html page. This is obviously very slow. This is an example of what I'm doing now:

ProductLine=`mysql -u www -s support <<EOF
SELECT ProductLine FROM Documents WHERE ID = '$ID';
EOF`

DocType=`mysql -u www -s support <<EOF
SELECT DocType FROM Documents WHERE ID = '$ID';
EOF`

DocName=`mysql -u www -s support <<EOF
SELECT DocName FROM Documents WHERE ID = '$ID';
EOF`

FileName=`mysql -u www -s support <<EOF
SELECT FileName FROM Documents WHERE ID = '$ID';
EOF`

Like I said, I am doing this for every field in every record. It works but it is VERY slow. I thought if there was a way to tell the query to insert a special charater between each field then I could use awk to pick out the fields and only do one query per record... something like this:

SELECT ProductLine, DocType, DocName, FileName FROM Documents WHERE ID = '$ID';

I don't know how to take that output and put it into my shell variables.

Send Results From One Query To Another
Is there a way to send a results from one query to another in mysql command line, i mean just like similiar to linux's bash terminal by '|' and 'stdin'?

Saving Query Results
Is there any way to save the results of a query without the quote marks?
When i try to import the text file that I saved the query to, I have to go thru and delete all of the quote marks.

Query Not Returning Any Results
I can't get any results form my query:

SELECT * FROM userfile

There's only one record in the table but I don't get nay results.

The query is written in PERL:

#!/usr/bin/perl -w

use CGI qw(param);
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
my $q = new CGI;
print $q->header();

..........

Excluding Results In A Query
I've got a query like this:

SELECT user.*, foto.*, votes.sum(rating) FROM votes WHERE votes.foto_id = foto.fid AND user.id = votes.vote_to_user_id GROUP BY votes.foto_id ORDER BY sum(rating) DESC LIMIT 0 , 10

This query selects photos with the highest sum of votes.
Now I want to exclude photos from the same user.id in this top10. (Because users have more than one photo) So what do I have to add to my query to prevent this from happening?

Query Results By Pages
I would like to display query results in pages of say 20 results each.

e.g. a table with two columns, serial# and descrition, with a few thousand entries.

What would be the SQL query to find a specific serial number, and display all 20 results within the "page" that row happens to be in?

e.g say serial#=3211 is in row 64, display rows 60->79.

Average Over Results From Query
I would like to write a query in which i calculate the average over the result from an earlier query. I have a transaction header table and a separate transaction lines table. I am trying to count an amount per transaction by summing the lines table, grouped by transactionnumber and would like to calculate the average over all of these sums.

Save Query Results
In other words, how do I build a new table from the results of a query, so I can access pieces of this data later without performing the same query (a very heavy one) over and over again?

Filter Results Of One Query
I'm looking for the syntax that would allow me to filter one querys results using another query i.e.

QUERYA = SELECT * FROM table WHERE id < 100

QUERYB = SELECT * FROM QUERYA WHERE id <> 49

I understand that I can do the above example in one query - I'm just using that to simplify matters.

Order Query Results
Regarding a dummy set of data below,

ID Status
1 Open
2 Closed
3 Expired
4 Closed
5 Open
6 Cancelled
7 Expired
8 Cancelled

I want to view the results but order them according to the status.
i.e. i want to view in order of Open, Closed, Expired, Cancelled
I can only think of a long drawn out way by which i would create the four seperate queries.

Case Insensitive Query Results?
I've been noticing that the MySQL 4.1.19 server on a client's Web server returns case-insensitive results for SELECT statements when using PHP's PDO extension. I didn't notice this happening on my XP dev server with MySQL 5.0, and have never seen it happen on any other server. Does anyone know why this happens and what can be done about it?

Query Is Not Returning Desired Results.
My query is not returning desired results.
I want to structure my query to return the Post Subject, Post Text, and Poster. Instead, it is returning the correct Post Subject, correct Post Text, but the Poster is just repeating the username from the '$_POST[username]' variable. How can I alter the query to return the correct name?

the query
PHP

<?php$conn = mysql_connect( $domain, $user, $password ) or die("Err:Conn");$rs = mysql_select_db($db, $conn) or die("Err:Db");$sql = "SELECT `table_posts_text`.`post_subject`, `table_posts_text`.`post_text`, `table_users`.`username` FROM table_categories, table_forums, table_topics, table_posts, table_posts_text, table_users WHERE username='$_POST[student]' ANDcat_title='$_POST[category]' AND`table_categories`.`cat_id`=`table_forums`.`cat_id` AND `table_forums`.`forum_id`=`table_topics`.`forum_id` AND `table_topics`.`topic_id`=`table_posts`.`topic_id` AND `table_posts`.`post_id`=`table_posts_text`.`post_id`AND `table_topics`.`topic_poster`=`table_users`.`user_id`AND`table_posts`.`poster_id`=`table_users`.`user_id`ORDER BY `table_topics`.`topic_id`, `table_posts`.`post_id`"; $rs=mysql_query($sql,$conn);while($row=mysql_fetch_array($rs)){ echo($row['post_subject'] . $row['post_text'] . $row['username']);}?>

There are six tables involved:
1. table_categories (cat_id medint, cat_title varchar)
2. table_topics (topic_id medint, forum_id smallint topic_title varchar topic_poster medint)
3. table_forums (forum_id smallint, cat_id medint, forum_name varchar)
4. table_posts (post_id medint, topic_id medint, forum_id smallint, poster_id medint)
5. table_posts_text (post_id medint, post_subject varchar, post_text text)
6. table_users (user_id medint, username varchar)

Reversing Query Results (not The Same As ORDER BY)
I am trying to reverse the order in which the results of my query are given.

I am tweaking a gallery. I need to get the previous 3 pics based on the pic I am looking at.

The following code is a simplified version of what I have. The problem is, when I echo the results, the thumbnails are displayed in DESC order (i need them to display in ASC order) but if I order the query using ASC, the query gets the wrong images.

SELECT pic_id
WHERE pic_id < $current_pic_id
ORDER BY pic_id DESC
LIMIT 3
I need the results to show like this:
oldest pic | older pic | old pic | current pic | new pic | newer pic | newest pic

I've got the new pics sorted out but the old pics are causing a little problem...

So basically, is there a way that I can reverse the results of my query?

Query For Displaying Results Except Latest
So I'm looking for a way to pull the results from the database showing all the records EXCEPT for the latest one i.e. if there are 30 recordsets I only want to show numbers 1-29.

Limiting Results In Query Question
I've got a SQL query which i want to return one result from each development in the database. The result i want to be returned is the first image in the database related to a development.

There are currently two developments in my database however all the images that are stored in the database are returned and not one per development.

QUERY:

select development_images.development_id, developments.title, developments.description, development_images.url, development_images.image_title from developments, development_images WHERE developments.id = development_images.development_id order by developments.id DESC

LINK: http://demo2.pixel-room.net/developm...dex.php?page=1

Mixed ORDER BY Query Results
Is there a way to order a query result a specific way?

For instance, I'm querying using

WHERE id IN(23, 25, 19)
I would like the results to stay in that order instead of by ASC or DESC.
Is this possible?

Export Query Results Through Browser
This would seem simply enough but I am struggling with it. I am using PHP 4 and Mysql 5.0 I need to take the results of a query and allow the user to download the results....

Assigning Query Results To A Variable
I would like to Execute this Select Statement...

SELECT MAX(VisitorID) FROM Visitors.

Is there any way to store the result directly into a VB variable??

lngVisitorID = ???

Query Of Similar Results Of An Array
I know about Full-Text searchs, soundex, and subqueries with IN or SOME. But I want to make a query that joins two concepts.

Let's see... I got this array ....

Assigning Query Results To A Variable
I would like to Execute this Select Statement...

SELECT MAX(VisitorID) FROM Visitors.

Is there any way to store the result directly into a VB variable?

Use Query-results As Delayed Insert?
Can I use the results of one query like:
-> SELECT user_id FROM tbl_customers

And use the results to fill the VALUES-statement in another table like:
-> INSERT INTO tbl_postal ('user-id') VALUES (<previous query results>)

If so, could someone complete my second SQL-statement, since I'm staring
at my CRT for over 2 evenings and I cannot get it to work.

Showing Query Results In HTML
In mysql I can find the minimum data from a particular column by doing as follows:

SELECT MIN(Freq_Min) FROM datatable;

and mysql will display the results. Now I want to do the same thing but I want to do it in html, so I wrote the following code. When I open up the webpage stats.php it says that it couldn't execute the query. Can someone help get me on track? .....

Average Query Results Not Correct
This query works, but average results are not correct.

I don't no what the problem is!

Should i use subquery's?

Joining is not working fot this type of query's?

Trouble With Query Results Deleting 0
I'm having trouble with the query results deleting the 0 in numbers, no matter what place it's in. EX: 10 returns 1, 100 returns 1, and even 101 returns 1. It works fine on numbers with no zeros.

Export Query Results To Csv File
Can you please tell me how to export query results in to a csv file?

just simple run a query and get results in a csv file..

Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories.

What I want the query to return is this:

Date | ShopperID | VATable Amount | Non VATable Amount|
2006-3-1 | 802135 | 146.95 | 54.00 |

Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above.

I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product:

+------------+----------+-----------------+---------------------+
| Date | ShopperID| VATable Amount | Non VATable Amount |
+------------+----------+------------+---------------+
| 2006-09-04 | 805284 | 0 | 64.00 |
| 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a
| 2006-09-04 | 805287 | 3.25 | 0 | customer has
| 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products
| 2006-09-04 | 805287 | 4.95 | 0 | - I need total
| 2006-09-04 | 805327 | 0 | 53.95 | under each
| 2006-09-04 | 805335 | 0 | 58.95 | ShopperID
| 2006-09-04 | 805414 | 0 | 64.95 |
| 2006-09-04 | 805414 | 3.25 | 0 |
| 2006-09-04 | 805414 | 0 | 64.00 |
| 2006-09-04 | 805414 | 0 | 69.00 |
| 2006-09-04 | 805423 | 0 | 64.95 |
| 2006-09-04 | 805423 | 0 | 69.00 |
| 2006-09-04 | 805423 | 0 | 64.00 |
| 2006-09-04 | 805423 | 3.25 | 0 |
+------------+-----------+----------------+--------------------+

Heres the query currently getting these results (without the GROUP BY ShopperID).

SELECT Date,orders.ShopperID,
CASE
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag'
THEN items_ordered.price -- If it is an accessory add VAT
ELSE 0
END AS 'VATable Amount',

CASE
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?('
THEN items_ordered.price -- If size 1 - 6 add price to other column
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?('
THEN items_ordered.price -- If size kids 12/13 add price to other column
ELSE 0
END AS 'Non VATable Amount'
FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= &#55614;&#57158;-1-30') AND (Date <=

&#55614;&#57158;-9-31');

Query Showing More Results In PHPMyAdmin Than In My Application
I am trying to run a query on my database, my database class returns 5 results, when I run it on PHPMyAdmin (direct copy), it returns 15 results. I am 100% sure I am copying the exact query. I tried my customer db class and ADODB.

I've tried many search queries, so it's not just this one.

MySQL: 4.0.27-standard
PHP: 4.47

Any ideas would be great appreciated, I am pulling my hair out......

Use A Mapping Table To Get Query Results From Several Tables
Ok, so I have 4 tables...

Projects -> id, project_name, project_type
Variables -> id, variable_name
Symbols -> id, symbol_name
Mapped -> project_id, variable_id, symbol_id

if I had to find

(project_id=5, variable_id=2, symbol_id=3) AND
(project_id=2, variable_id=14, symbol_id=1) AND
(project_id=34, variable_id=78, symbol_id=44)

How could I write a query that would return the results for these all at once?

I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3

Query - Top 20 Results In Ascending Alphabetical Order
I have a table which is a list of categories in an online directory.. each time the category is visited in increases the hits column by one..

I want to get the top 20 results (ie. the top twenty most popular categories) and then sort them into a list alphabetically.

I tihnk I have to use a nested select but when I do this I only get the top 20 results sorted by alphabet not by hits..

Here is the table structure:

CODE#
# Table structure for table `category`
#

CREATE TABLE category (
autoID mediumint(9) NOT NULL auto_increment,
title varchar(100) NOT NULL default '',
hits int(11) NOT NULL default '0',
PRIMARY KEY (autoID)
) TYPE=MyISAM;