select statemt LIKE

[ohgosh]

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!!

[Troy]

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.

[tgreer]

Agreed. That message is unreadable.

[ohgosh]

: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!!

[Troy]

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,

myVar = "Hey, I used a code tag!"
Response.Write myVar
Response.End

To query all rows from a table based on a single column not being null or an empty string, you can do this:

sql = "select * from myTable where myCol is not NULL and myCol <> ''"
'By the way, you should not use * to select columns in production
'code--specifically name your columns.

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'

[ohgosh]

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:

if request.QueryString("statusType") <> "" then

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:

<select name="statusType" id="statusType">
 <%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>

oops.. should i post this in the asp forum as a new thread instead of here?

[Troy]

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:

<select name="cars">
  <option value="">[All Cars]</option>
  <% 'Do your logic to create the rest of the options from db. %>
</select>

When processing the form to build your SQL statement, do something like this:

sql = "select year, model, color from car"
if Response.Form("cars") <> "" then
  sql = sql & " where car_manufacturer = '" & Response.Form("cars") & "'"
end if
sql = sql & " order by car_manufacturer, model, year

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.

[ohgosh]

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!!

[Troy]

If you have a select with options like so:

[All Cars]
Acura
Chevy
Ford
Toyota

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.

<select [b]name="car[]"[/b] [b]MULTIPLE[/b]>
<option value="">[All Cars]<option />
<option value="Acura">Acura<option />
<option value="Chevy">Chevy<option />
<option value="Ford">Ford<option />
<option value="Toyota">Toyota<option />
</select>

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
$cars = $_POST['car'];
// $cars is now an array.
foreach ($cars as $car) {
  echo $car."<br />";
} 
?>

[ohgosh]

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..

[Troy]

I'm so used to people developing against MySQL using PHP. I ignored the fact that in your earlier post, you posted ASP code. Sorry!

So do this in you HTML:

[left]<select [b]name="car"[/b] [b]MULTIPLE[/b]>
  <option value="">[All Cars]<option />
  <option value="Acura">Acura<option />
  <option value="Chevy">Chevy<option />
  <option value="Ford">Ford<option />
  <option value="Toyota">Toyota<option />
</select>[/left]

Then, in your ASP, if the user submits with multiple options selected, you'll receive them as a comma-seperated list.

<%
cars = Split(Request.Form("car"), ",")
for each car in cars
  Response.Write car & "<br />"
next
%>

[ohgosh]

oh that's okie! Thanks for replying too!

The code below means getting the values for selecting more than one items?

<%
cars = Split(Request.Form("car"), ",")
for each car in cars
  Response.Write car & "<br />"
next
%>

i think ive done such statemt, see the code below.

if request.QueryString("statusType") <> "" then
      state = split(request.QueryString("statusType"),"|")
        for each rec in state
          if rsS("statusID") = Cint(rec) then response.Write(" selected")
        next
end if

i passed in "0" value for the ALL option cos the items in the listbox was passed in in INT datatype so if i passed in empty str for ALL option it gave me an error.

Now when i click both ALL and other items in the listbox it doesnt give me any error but instead it will not go to search for ALL status records. I was thinking if there is any code to maybe validate the users in either selecting the items in the listbox or ALL option and not both. :evil:

[Troy]

I think you are getting it, but I'm not sure. The points I'm trying to make are:

1. If you want to allow a user to be able to select more than one item in a SELECT list, you must use the MULTIPLE attribute.

2. In ASP, when a SELECT element is submitted that has more than one OPTION selected, it will come in as a comma-seperated list. How you deal with that is your code decision.

Make sense?

I provided a simple code example to show you how to take the comma-seperated list and put it into an array---something easier to work with programmatically than a comma-seperated list of values.

As for validation to ensure the user either selects the "All" option or selects something else, yes you can write client-side javascript to do this validation, but another option is to not do anything. I mean, if the user selects "All" and something else, just treat is as "All"--ignore the other selections since All covers it all. In your ASP code that accepts the form, if you get the zero value (All) in the comma-seperated list, then just write your SQL query to return all values. Otherwise, you'll make your SQL query narrow down the selection accordingly.

[ohgosh]

Hmm~ i will try to think abt it more seriuosly later.. if there're still problems occuring, i will try to post it again in here..

THANKS!!