Page 1 of 1

problems wit multiple where...=$_POST[...] in sql statement

Posted: Thu Mar 27, 2008 6:32 am
by imthinking
hI,

I hav been having problems wit running sql statement wit multiple where...=$_ POST[...]

Here is my sql statement:

select city_name,count(ticket_id) from airport_t inner join flight_t
on airport_t.airport_code=flight_t.arrival_airport_code inner join
flight_schedule_t on flight_schedule_t.flight_number=flight_t.flight_number
inner join ticket_t on flight_schedule_t.schedule_id=ticket_t.schedule_id
where ticket_t.price_class_type='$_POST[price_class]' and member_type='$_POST[member_type]' and passenger_type='$_POST[passenger_type]' group by city_name order by count(ticket_id)
desc limit 0,3;

its not returning any results, as i guess sql cannot run null values for where clause..

I m wondering if anyone has a way around it like adding the where...=$_POST[..] clauses to the sql statement as and when it the $_POST[..] is not null? Thanks

Re: problems wit multiple where...=$_POST[...] in sql statement

Posted: Thu Mar 27, 2008 8:39 am
by Strike
I don't Get Exactly What you need but here is how i wold write this code :

Code: Select all

mysql_query('SELECT city_name,count(ticket_id) FROM airport_t inner join flight_t
on airport_t.airport_code=flight_t.arrival_airport_code inner join
flight_schedule_t on flight_schedule_t.flight_number=flight_t.flight_number
inner join ticket_t on flight_schedule_t.schedule_id=ticket_t.schedule_id
where ticket_t.price_class_type='.$_POST['price_class'].' and member_type='.$_POST['member_type'].' and passenger_type='.$_POST['passenger_type'].' group by city_name order by count(ticket_id)
desc limit 0,3');
 

Re: problems wit multiple where...=$_POST[...] in sql statement

Posted: Thu Mar 27, 2008 10:28 am
by onion2k
Echo the SQL and see what it's actually doing. You'll see some unescaped values I expect. Use mysql_real_escape_string() to fix them. Also, you'll find it easier to debug your code if you write it neatly ... one long line of code is a lot harder to find problems in than a string broken up into several lines.

Re: problems wit multiple where...=$_POST[...] in sql statement

Posted: Thu Mar 27, 2008 8:30 pm
by imthinking
Sorry abt the messy statement...i made it better below

select city_name,count(ticket_id)

from airport_t
inner join flight_t on airport_t.airport_code=flight_t.arrival_airport_code
inner join flight_schedule_t on flight_schedule_t.flight_number=flight_t.flight_number
inner join ticket_t on flight_schedule_t.schedule_id=ticket_t.schedule_id

where ticket_t.price_class_type='$_POST[price_class]'
and member_type='$_POST[member_type]'
and passenger_type='$_POST[passenger_type]'

group by city_name
order by count(ticket_id)
desc limit 0,3;


anyway i m setting a reporting system, and i hav a form whereby the user can select 3
things namely the price_class of the airplane e.g. economy , choose a particular member_type and lastly choose a passenger_type e.g. child under 3 different dropdown list

I was hoping to post all of the 3 selections that the user has made into the sql statement, so that the sql can return the result for tat particular combination of selection.

However, when i run it, no results are returned and there are no error msg as well....hence i
hav no idea how to go abt doing it..

hope u guys can help..thks

Re: problems wit multiple where...=$_POST[...] in sql statement

Posted: Thu Mar 27, 2008 9:12 pm
by califdon
As onion2k suggested, echo the SQL so you can see what is actually being sent to MySQL. That will often clarify what the problem is. Also, do you have error reporting turned on? Unless you do, it's hard to know whether you have an error that prevents either PHP or MySQL from processing your code, or whether there is simply no data being returned due to the details of your query.

If you have access to phpMyAdmin or similar facility, try running the query directly (yeah, I know, it's a lot of typing, but it can often immediately reveal the problem).