i wud like to select records which userStatus' values is sth.

ive gt a tbl with a userStatus column, dis column is stored with statusID and the values in dis column, userStatus, are either numbers or nothing inside.

now i wud like to retrieve the userStatus which values are sth inside and hw shd i retrieve? cos in d web form ive gt a listbox showing all the status. now im including an ALL status to select all the status frm the db.

<%sql = "Select * from status ORDER BY statusType ASC"
set rsS = Conn.Execute(sql)

do while not rsS.eof
response.Write("<option value='"&rsS("statusID")&"'")
if request.QueryString("statusType") <> "" then
state = split(request.QueryString("statusType"),"|")
for each rec in state
if rsS("statusID") = Cint("0" & rec) then response.Write(" selected")
next
end if
response.Write(">"&rsS("statusType")&"</option>")
rsS.moveNext
loop
%>
</select>


if request.QueryString("statusType") <> "" then
sqlC = "select * from t_user_account where (userStatus='"&replace(request.QueryString("statusType"),"|","' or userStatus = '")&"')"

set rsC= conn.execute(sqlC)
end if


btw do anyone noe hw to explain such select sql statemt:
select * from user_acc where userStatus like '%_%'

to my knowledge, i understand that userStatus like '%%' is to retrieve evry single rec frm d tbl.

and _ is to select a single char but it shd b coded with like this: '_' then the userStatus = 5, 6 will be retrieved.

so hw abt userStatus like '%_%'?

thanks in advance!!

Are the vowels missing on your keyboard? No, it seems your code snippet contains vowels. Is that txt msg speak? I guess I'm too old (at 33) to interpret. I definitely have no idea what "sth" means.

Agreed. That message is unreadable.

:eek: I am sorry because i am in a hurry and in a haste to solve this error hence i write my words in short form.

Well, i will translate my message again:

I would like to select records which userStatus' values is something and not null or empty strings.

I've got a table with a userStatus column, this column is stored with statusID and the values in this column, userStatus, are either numbers or nothing inside (means empty).

now i would like to retrieve the userStatus which values are something inside and how should i retrieve? cos in the web form i've got a listbox showing all the status. now im including an ALL status to select all the status frm the db without having to select all the status individually from the listbox.

<%sql = "Select * from status ORDER BY statusType ASC"
set rsS = Conn.Execute(sql)

do while not rsS.eof
response.Write("<option value='"&rsS("statusID")&"'")
if request.QueryString("statusType") <> "" then
state = split(request.QueryString("statusType"),"|")
for each rec in state
if rsS("statusID") = Cint("0" & rec) then response.Write(" selected")
next
end if
response.Write(">"&rsS("statusType")&"</option>")
rsS.moveNext
loop
%>
</select>


if request.QueryString("statusType") <> "" then
sqlC = "select * from t_user_account where (userStatus='"&replace(request.QueryString("statusType"),"|","' or userStatus = '")&"')"

set rsC= conn.execute(sqlC)
end if


by the way, do anyone know how to explain such select sql statement:
select * from user_acc where userStatus like '%_%'

I understand that userStatus like '%%' is to retrieve evry single record frm the table.

and _ is to select a single character but it should be coded with like this: '_' then the userStatus = 5, 6 will be retrieved.

so how about userStatus like '%_%'?

thanks in advance!!

Thank you for typing out your words. I'm not trying to hassle you--believe me, but some more tips to help you get good replies here on Daniweb or any forum:

Don't paste so much code to ask a specific question. Your question is regarding how to construct the SQL statement, but you posted a whole section of ASP code.

When you post code, use the code tags. They set your code apart, keep your spacing intact, and provide syntax highlighting for some languages. You can insert code tags using the last icons in the list of icons across the top of the editor. For example,

To query all rows from a table based on a single column not being null or an empty string, you can do this:
The tricky part is that an empty string is not the same thing as NULL, so you have to check for both, unless you've always been careful to insert NULL instead of empty strings.

As you know % is a wildcard indicator. It means anything OR nothing.

'%ed' will match 'fred','red','busted', 'ed'
'%ed%' will match 'sedate', 'educate', 'bed', 'ed'

Oh thank you for giving me tips on using the code tags! As a new user, hence I do not know much of the tags and its functionality. Thanks anyway!

Erm.. That sql statemt you provided is to be placed above this statemt:
Hmm~ my question is not only regarding how to construct the SQL statement. Actually, my main objective is to include a ALL status in the listbox w/o having to select all the status individually from the listbox.

First, how should i add in an ALL option in the listbox when the items in the listbox are retrieved frm the db. What I am thinking is to store all the statusID values in the ALL option when i select ALL from the listbox but I am stuck at this point in making it work. :cry:

Or is there any better way to insert an ALL option in the listbox?

I should again post the code in a more efficient manner:
oops.. should i post this in the asp forum as a new thread instead of here?

This is a rather common thing, to have a dropdown list of options and you want the user to be able to select one or [All Options]. A common way to handle this is like so:
When processing the form to build your SQL statement, do something like this:
You see? We do not put the WHERE clause into the SQL statement if the user selected [All Cars], thus all cars will be included in the recordset.

oh okie.. THANKS!!

how abt setting the listbox to allow users to select either ALL option or the items on the listbox? So that users can only select ALL and search or select one to more items on the listbox but with together with the ALL option?

Sorry for the trouble.. and a MILLION THANKS 4 your HELP!!

If you have a select with options like so:

It does not makes sense to select "[All Cars]" and "Ford", for example.

But, you may have users who would do that. First, to make a SELECT element a "multi-select" where the user can select more than one option, you have to use the MULTIPLE attribute.
Did you notice the select's name? Notice that I added [] after the name "car" to make it "car[]". That is a technique you have to do to allow the element to be passed to your PHP script as an array. Once the user submits the form, you'll have an array named car in your $_POST object.
[PHP]
<?php
$cars = $_POST['car'];
// $cars is now an array.
foreach ($cars as $car) {
echo $car."<br />";
}
?>
[/PHP]

erm.. i don't understand php language.. btw the multiple have already being included in my select tag. i'm using asp lang..

how is tt php array code going to help? i don't understand why array should be created after submittin the form?

THANKS for the sql code to retreive record where status is not empty or null..