Solved

Mysql query - PHP

Posted on 2013-10-30
5
403 Views
Last Modified: 2013-10-30
Here is my data:
---------------------------------------
2013-10-30 06:04:31      545      55      130.5      131.2      28.6      226      23.3      123.7      122.3      50739      1.3405      5      5      
2013-10-30 06:11:07      396      55      148.6      150.2      36.2      226      19.5      133.6      135.7      50740      1.9535      5      5      
2013-10-30 06:17:10      363      55      158.2      159.3      45.3      226      22.3      129.4      132.8      50740      2.065      5      5      
2013-10-30 06:18:06      56      55      159.7      161.9      46.8      226      20.3      141.7      142.7      50741      2.066      3      1      
2013-10-30 06:21:15      189      55      165.9      168.1      52.8      226      22.5      124.2      129.2      50741      2.071      3      1      
2013-10-30 07:34:25      57      55      191.1      192.4      109.3      227      20.3      152.2      156.1      50742      1.526      5      5      
2013-10-30 07:35:11      56      55      191.8      193.4      109.7      226      22.5      169.1      161.7      50743      1.553      5      5
--------------------------------------------------------------------

Say all my headers are h1, h2, h3, etc for above.

In my column ( h11 ) where i have duplicate numbers like '50741' twice, i only want to return the result once instead of twice.

Here is my query:
$query = "SELECT * FROM datalog WHERE DATE(hes_datetime) = '$hes_date' ORDER by hes_datetime ASC";

This query works but I dont want to count h11 twice if it is same number.

I have tried distinct but cannot seem to get it to work.
0
Comment
Question by:keith1001
5 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39612708
>> In my column ( h11 ) where i have duplicate numbers like '50741' twice, i only want to return the result once instead of twice.
whcih one you want to return? first one or second one?

also what are the primary key columns of your table?
0
 

Author Comment

by:keith1001
ID: 39612732
I want the first one returned.

Sorry but i have id (h0) which is primary key .
0
 
LVL 9

Accepted Solution

by:
Derek Jensen earned 350 total points
ID: 39612745
Would a GROUP BY (h11) do the trick? That should return unique values for that column.
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 150 total points
ID: 39612866
I think bigdogman may be right.  The query would look something like this:

$query = "SELECT * FROM datalog WHERE DATE(hes_datetime) = '$hes_date' GROUP BY h11 ORDER by hes_datetime";
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39612896
try this query
$query = "SELECT t1.* FROM datalog t1 INNER JOIN (SELECT h11,MAX(h1) AS max_h1 FROM datalog GROUP BY h11) t2 ON t1.h11 = t2.h11 AND t1.h1 = t2.max_h1 WHERE DATE(hes_datetime) = '$hes_date' ORDER by hes_datetime ASC";

Open in new window

I assume h1 is the date column in your table. Otherwise change the column name with date column name.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question