mysql query to display records of more than a blank

[busnut]

G'day, one of my fields in my table is titled 'advertising', yet the majority of the records doesn't have anything listed in 'advertising', but several records do, and I want to display those records, but can't seem to find the best way to do so!

 

i was thinking along the lines of 'WHERE advertising > strnlen of 3 characters'

 

But what is the best way to describe it or put it?

[rhodesa]

SELECT * FROM tbl_name WHERE `advertising` NOT NULL and `advertising` != ''

[busnut]

SELECT * FROM tbl_name WHERE `advertising` NOT NULL and `advertising` != ''

thanks, but the code hasn't quite worked . I tried by removing the `, I also tried by putting <> NULL, and did get records, but half of the db which there should only be about 20 odd records from over 2000 displaying

 

Here is what I do have, which also have other items in there aswell - and yes it is probably not the right way of doing it, so if it is wrong, I apologise and would appreciate help for this beginner.

 

$data = mysql_query("SELECT * FROM busfleet WHERE busno = '$find' OR chassisbody LIKE '%$find%' OR depot = '$find' OR active = 'Y' OR SELECT * FROM tbl_name WHERE `advertising` NOT NULL and `advertising` != '' ORDER BY busno +0 ASC, active DESC") or die(mysql_error());

 

As you can see, there are several different things the db is looking for, either looking for a match on busno, or a like for chassisbody or a match on depot, or active = Y or where if advertising is more than a blank or 3 characters or something, it will display.

[rhodesa]

ok...let's back up here...the code i gave was just an example and needed to be modified to fit your case.

 

is the advertising field in the busfleet table?

 

$data = mysql_query("SELECT * FROM busfleet WHERE (busno = '$find' OR chassisbody LIKE '%$find%' OR depot = '$find') AND active = 'Y' AND advertising IS NOT NULL AND advertising != '' ORDER BY busno +0 ASC, active DESC") or die(mysql_error());

[busnut]

Yes, thats correct.

Doing a manual count, the adverterising fleet should display about 20 odd records. Currently dispalys about 1000 odd records.

 

I had to modify a apart of it. When the user does a search for the bus # or chassisbody or depot, it should display both active & non active buses (so Y or N for active), if they want just the active bus list, it looks for only buses that are active (active=Y), or if they want to view the advertising buses, its what buses arent NULL in the advertising AND active=Y.

 

Hopefully that may make abit more sense as to the direction im heading.

 

But are the brackets also to seperate the types of search also?

[busnut]

I've figured out a part of the problem, although I can't figure out the solution as the word 'OR' should really seperate the different types of what it searches for, but here is what i've got:

 

$data = mysql_query("SELECT * FROM busfleet WHERE busno = '$find' OR chassisbody LIKE '%$find%' OR depot = '$find' OR active = 'Y' OR aoa IS NOT NULL AND aoa != '' ORDER BY busno +0 ASC, active DESC") or die(mysql_error());

and I apologise, the advertising field is actually titled aoa.

 

So what did work before adding the aoa search was if somebody entered a bus #, it would display the relevant bus details

if somebody entered a chassis name, it would display the buses of that type of chassis

if somebody entered a depot name, it would display the buses allocated to that depot

 

What i'm wanting it to do is if somebody clicks on the link for advertising buses, it displays teh list of advertising buses (which it does with the above code, HOWEVER, if somebody does a search of either a bus #, chassis or depot, it lists those details as well as the advertising buses too!

Same goes for listing 'active' buses, it lists them, but if they perform a different type of search, it too lists all the active buses aswell.

 

So how is this rectifiable? Or do i have to have seperate scripts for different types of searches?

[fenway]

You're mixing ORs and ANDs without parens.

[busnut]

You're mixing ORs and ANDs without parens.

Is a paren a bracket ( ) ?

[fenway]

paren = ( )

bracket = [ ]

brace = { }