Page 1 of 1

SELECT query returns no results for unknown reasons.

Posted: Thu Oct 09, 2008 9:07 pm
by rabw
Hi everyone, I'm fairly new to this but have been making good progress until tonight.

I'm doing a simple PHP/MySQL SELECT query as I have done many many times now, only now I have added a new column to my 'bookings' table called 'deleted'. I have made a page where I can submit through and 'delete' a booking (actually puts a 'Y' in the deleted column). Obviously I now want to add a bit on my SQL query's that list my bookings that says something like...

Code: Select all

WHERE deleted!='Y'
However when I do this, I get absolutely no results! I currently have one booking out of about 30 with a Y, and adding this results in no results! Using:

Code: Select all

WHERE deleted='Y'
returns 1 result as expected.

To summarise, here's a snippet of what does not work:

Code: Select all

$query  = "SELECT title, shortdesc, customerid, quotedate, quotetime, bookingid, frompostcode, topostcode, subcontractorid, podtime, poddate, podname, readytoinvoice, payamount FROM bookings WHERE ownerid='".$userid."' AND subcontractorid!='' AND podname!='' AND deleted!='Y'";
$result = mysql_query($query);
And here's the code which returns the one deleted result (the opposite of what I want!):

Code: Select all

$query  = "SELECT title, shortdesc, customerid, quotedate, quotetime, bookingid, frompostcode, topostcode, subcontractorid, podtime, poddate, podname, readytoinvoice, payamount FROM bookings WHERE ownerid='".$userid."' AND subcontractorid!='' AND podname!='' AND deleted='Y'";
$result = mysql_query($query);
If anyone could give me a pointer I'd be eternally grateful, this is driving me mad now!
Thanks.

Re: SELECT query returns no results for unknown reasons.

Posted: Fri Oct 10, 2008 3:18 am
by aceconcepts
SQL does not make use of != in order to those records "not equal to".

Instead it uses "<>", so your query should look like this:

Code: Select all

 
$query  = "SELECT title, shortdesc, customerid, quotedate, quotetime, bookingid, frompostcode, topostcode, subcontractorid, podtime, poddate, podname, readytoinvoice, payamount FROM bookings WHERE ownerid='".$userid."' AND subcontractorid<>'' AND podname<>'' AND deleted<>'Y'";
$result = mysql_query($query);
 
Also, be care how you name your db table fields. One person who posted in this forum yesterday had a problem because he used "primary" as a field name. Primary is a word used by SQL. I tend to use a naming convention based on the field's data type i.e. first name would be a string so i would name my field "strFirstName".

Make sense?

Re: SELECT query returns no results for unknown reasons.

Posted: Fri Oct 10, 2008 6:41 am
by rabw
Thanks for the reply... however != has worked as expected on all of my other columns. I've just given it a go using <> and I get exactly the same result. When I use '<>' on my other "does not equal" values and '=' for deleted it comes up with the one result as expected though... :(

Also similarly if I try to select from that table specifying ONLY deleted<>'Y' I get no results, and with deleted='Y' get one, so it isn't because of anything else I'm specifying! :roll:

I also tried setting the default column value to 'N' so I could use deleted='N', but that didn't help either...

My deleted column is column 35, don't see why this should make any difference.

I saw that topic and checked out the list of reserved words/names. 'delete' is one, but 'deleted' is not. I also tried changing the column name to something totally different and still got the same result. Is this a bug with MySQL? I added the column using MySQL Admin as I have done with many other columns before with no problems.

Re: SELECT query returns no results for unknown reasons.

Posted: Fri Oct 10, 2008 6:46 am
by rabw
Wooooo, I've just tried 'AND deleted IS NULL' and it works! :D

Annoying that I cannot explain the difference in behaviour to my other columns, but at least it works!

Thanks

Re: SELECT query returns no results for unknown reasons.

Posted: Thu Oct 23, 2008 6:47 pm
by rabw
rabw wrote:Annoying that I cannot explain the difference in behaviour to my other columns
I can now... I was inserting a blank (not NULL) previously for the other columns. Duuuuh sorry newbie mistake, but may help someone else starting out to know.