0

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 ?

Improve this question
1
  • What programming language is that? Commented Sep 19, 2013 at 12:36

4 Answers 4

Reset to default
1

Maybe by adding AND price IS NOT NULL ?

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

php and I tried this AND price IS NOT NULL but still returns row with NULL value for records that doesnt exist..
1

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...

Improve this answer

6 Comments

Ppeterka 66, thank you for helping me. I tested your query and when I echo $num_rows I get 1 and 0 for both queries.. And something else, this while is because I am looping the advert_id-s, not only room_type_id.. advert_id is also variable passed from previous query
this is how it looks like: $que = 'select SUM(price) from prices_adverts where advert_id="'.$id.'" and room_type_id="'.$row['6'].'" and (date >= "'.$checkinDate.'" AND date <"'.$checkoutDate.'") having sum(price) is not null order by sum(price)';
@thecore7 I think you should paste a larger chunk of your code, including the previous query to be able to provide a good solution. In the meanwhile, I updated my answer, to have a version that has advert_id in the group too.
Ppeterka 66, what you advice is turning the logic for selecting advert_id. In previous query I am findind all adverts_id-s for those hotels located in London for example and room_type_id for room that can accommodate 3 visitors.. So thats why in the above query I have all proper hotels id-s and the proper room id-s and now I am checking each room for prices.. but some rooms have no prices.. do you understand why I can't change the logic / query?
There is no such thing as code can not be changed. Especially in this case. I still recommend you to post the code for one level more, to see thee outer loop zou do, with the querz zou do to acquire that data. I'm sure there is a better way of solving what you want by taking a step back, and looking at the whole issue rather than an issue immediately blocking you...
|
0

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.
Improve this answer

1 Comment

price is not null would work if there is price at all, but when such record doesnt exist it returns NULL
0

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)
Improve this answer

6 Comments

Adrian your suggestion is near what I need. $num_rows returned 1 0 for each query, but how I can say outsid of while if($num_rows > 0){echo '<input type..>';}
if (empty($results)) { echo 'No results found'; }else {echo your result}
outside while it gives me num_rows of the last query only, so this is not valid for my purpose if you read my edit
Can I sum the Num_rows from all queries and if > 0 to draw the button? would it be visible outside while this sum of num_row?
you can have a single query. Just use "where room_type_id in (55,56,57,etc)"
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.