CODEWITHSUNDEEP
mysqlisnull
thecore7
thecore7

Reputation: 484

Mysql returns null for rows that doesn't exist

I have the following code:

while ($row = mysql_fetch_array($result)){    
    $que ='select SUM(price) from prices_adverts where advert_id="7" and room_type_id="54" and (date >= "2013-09-20" AND date <"2013-09-21") order by price';    
    $que ='select SUM(price) from prices_adverts where advert_id="7" and room_type_id="55" and (date >= "2013-09-20" AND date <"2013-09-21") order by price';    and etc    

    $res=mysql_query($que) or die();    
    $rw=mysql_fetch_row($res);    
    $price= $rw['0'];    
}

this returns sum for some records that have prices in the database and NULL for $price for the records dont exist /when a room doesnt has price for specific dates it doesn't exist in the table / So my question is how I can get result for records that exist only??? I do not need NULL values for prices and is it possible to access $price outside while ? How? Please help, thanks

May I explain what exactly I need, this may help you to help Me :) Above I am looping hotels rooms to check how much would cost the room for specific period. Than I need to draw button outside loop which will divert visitor to reservation page. But if a hotel has no room prices available for the dates, I wish to have no button for reservation. That's why I need to figure out is there at least 1 room with prices in the hotel or not.. Hope this helps

########################################################Update

first query: I am taking all London hotels id-s

select id from adverts where town="London" limit 0, 5

than

for($i=0;$i<$num_rows;$i++){    
$row=mysql_fetch_row($result);    
echo echo_multy_htl_results($row[0]);    
}

this function echo_multy_htl_results is:

select a.article_title, a.town, a.small_image, a.plain_text, a.star_rating, a.numberrooms, rta.room_type_id, rt.bg_room_type,a.longitude, a.latitude, a.link_name, a.id from adverts a, rooms_to_adverts rta,room_types rt where a.id = rta.advert_id and rta.advert_id="3" and rta.room_type_id=rt.id and rt.occupants>="1" group by rt.bg_room_type order by rt.occupants ASC

it gets info for the html hotel square and also room_types_id-s and that it comes the cod already added.. What would you suggest ?

Upvotes: 0

Views: 1732

Answers (4)

ppeterka
ppeterka

Reputation: 20736

The solution to the immediate problem at hand can be this query:

select  SUM(price) 
from prices_adverts 
where advert_id="7" 
    and room_type_id="54"  -- notice, we are filtering on room type here
    and (date >= "2013-09-20" AND date <"2013-09-21") 
group by room_type_id -- this makes no rows appear when there are no rows found in this case
order by price

It returns 1 row, when there were a corresponding rows, and 0 rows, when there were none.

However, your problem seems to be of a different nature. Your scheme of operation seems to be like this:

  1. query rows from the DB (room_type_ids)
  2. put them in a loop
  3. for each iteration run a query

This is bad. Databases are very good at solving these kinds of problems, using JOINs, and the other appropriate clauses. I'd suggest using these features, and turning things around in your head. That way, you could issue one query returning all data you need. I believe this might be such a query, providing all the room type IDs with their summed prices:

select room_type_id, SUM(price) 
from prices_adverts 
where advert_id="7"  -- notice: no filtering for room_type_id this time
    and (date >= "2013-09-20" AND date <"2013-09-21") 
group by room_type_id
order by price

This query lists all room_type_ids that have records, and does not list those that don't, and beside each different type_id, it has the summed price. You can see the results in this SQL fiddle. (the data types are obviously off, this is just to show it in operation)

EDIT To have the advert IDs similar to the room_type_ids too:

select advert_id, room_type_id, SUM(price) 
from prices_adverts 
where  (date >= "2013-09-20" AND date <"2013-09-21") 
   -- notice: no filtering for room_type_id or advert id this time
group by advert_id, room_type_id
order by price

This will have three columns: advert_id, room_type_id and the summed price...

Upvotes: 1

AdrianBR
AdrianBR

Reputation: 2588

You need to use HAVING

select SUM(price) 
from prices_adverts 
where advert_id="7" and room_type_id="54" and (date >= "2013-09-20" AND date <"2013-09-21")
having sum(price) is not null 
order by sum(price)

Upvotes: 0

MrSimpleMind
MrSimpleMind

Reputation: 8637

You could use

sum(case when price is null then 0 else price end)

or 

sum(isnull(price,0))

or

just add in your where clause `price is not null` to exclude them.

Upvotes: 0

Michael Laffargue
Michael Laffargue

Reputation: 10314

Maybe by adding AND price IS NOT NULL ?

Upvotes: 1

Related Questions