wrong MYSQL statement, very easy!!!

[IanNav]

Hi,

i have a field called date, and a field called delfg.

I'm using this in a php webpage, and it returns no results when i execute it.

When i execute it without the [AND delfg <> '1'] part it works fine.

there is 1 record in table that has the value "1" in the field delfg.

mysql_query("SELECT * FROM practicelist WHERE date >= now() AND delfg <> '1' ORDER BY date ASC")






.....




i've tried it as

$result = mysql_query("SELECT * FROM practicelist WHERE date >= now() AND delfg = '1' ORDER BY date ASC");

and it returns 1 record (which is the correct result)

i've tried

$result = mysql_query("SELECT * FROM practicelist WHERE date >= now() AND delfg != '1' ORDER BY date ASC");

&

$result = mysql_query("SELECT * FROM practicelist WHERE date >= now() AND delfg <> '1' ORDER BY date ASC");

and i get no results....

it must be something real simple i'm getting wrong.


many thanks

Ian

 

[jpadie]

i suspect you should enclose the field name 'date' in backticks as date is a reserved word. you could also change the field name.

$result = mysql_query(" SELECT * 
                                      FROM practicelist 
                                      WHERE [red]`date`[/red] >= now() 
                                      AND 
                                      delfg != '1'
                                      ORDER BY [red]`date`[/red] ASC");

 

[IanNav]

Thanks, i've tried that and it still doesn't work....

the thing is...

It works when i ask it to return record equal to it. but not when i ask not equal to it...

any other suggestions?

 

[jpadie]

but that is right.

you say above that you have precisely 1 record in your database and that record has two fields and the field called delfg has a 1 in it. that means that if you ask for a recordset of all records after now that have a 1 in the field delfg, it would be anticipated that the recordset would contain no records.

there is 1 record in table that has the value "1" in the field delfg.

what result were you expecting?

 

[IanNav]

yes i did say that.

Allow me to correct myself.

there is 1 record in the table that has that flag delfg set to 1.

there however 9 records in the table in total.

when i do the NOT EQUAL to SELECT i hope to return 8 records.

What i am saying is, when i do the SELECT equal to the 1 record it shows, but when i ask to show anything but that one nothing shows...

is this a little clearer? sorry for the haze!

 

[jpadie]

what is the fieldtype of the delfg field?

 

[IanNav]

smallint(6)

 

[jpadie]

what do you get if you try the following

Select * from practicelist where delfg != 1

i.e. do not enquote the value of the comparison and leave out the date test for the time being.

 

[IanNav]

I still get nothing, even when i do it from my MYPHPADMIN window

 

[jpadie]

i'm surprised. i assume the data is not sensitive so can you post the results of the following

select * from practicelist

i will then try and recreate the issue on my server

when you say you are get nothing, does that mean you are getting no errors either? do you have error reporting and error display switched on?

you've asked the question in the wrong forum, to be honest. i'm happy to stay with it for another try as i am convinced the issue is trivial and in order to rule out any php errors, but longer than that i would recomment you post instead in the mysql forum.

can you also try a mysql_num_rows($result) after the query posted in my previous post