CODEWITHSUNDEEP
mysqlselectnotnull
Toniq
Toniq

Reputation: 5016

Mysql select var if not null

Some of the fields (url, poster, page) are null in my table (because this value as added later). When selecting all with following query, I will get null for these fields (url, poster, page), while the rest of the values are correct (sums). This value (url, poster, page) is the same for all with same media_id.

How could I get these values being not null (if they are set in at least 1 row) in my query? Note that I dont need WHERE IS NOT NULL here.

enter image description here

Query:

"SELECT media_id, title, poster, url, page, SUM(c_play) AS total_play, SUM(c_time) AS total_time, SUM(c_download) AS total_download FROM {$statistics_table} GROUP BY media_id, title ORDER BY $order $dir"

Thsi is just a single result example:

 "media_id": "41",
    "title": "Beach sunset",
    "poster": null,
    "url": null,
    "page": null,
    "total_play": "5",
    "total_time": "42",
    "total_download": "2",

Upvotes: -1

Views: 31

Answers (1)

XraySensei
XraySensei

Reputation: 192

I suggest using MAX function:

SELECT 
    media_id, 
    title, 
    MAX(poster) AS poster, 
    MAX(url) AS url, 
    MAX(page) AS page, 
    SUM(c_play) AS total_play, 
    SUM(c_time) AS total_time, 
    SUM(c_download) AS total_download 
FROM 
    {$statistics_table} 
GROUP BY 
    media_id, 
    title 
ORDER BY 
    $order $dir

This way, you ensure that you get the non-null values for url, poster, and page if they are set for any of the rows with the same media_id.

Upvotes: 1

Related Questions