We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to select all records with the value attribute of the Select tag

judypage4
judypage4 asked
on
Medium Priority
327 Views
Last Modified: 2012-05-11
I am using Dreamweaver CS5 to redesign a website that was originally done in FrontPage. I am trying to set up a form with a list that will access records from a database. Part of the code is:
<select name="Misc3"  id="Misc3 size="3" >
        <option value="" >Abridged and Unabridged</option>
        <option value="NA">Abridged</option>
        <option value="U">Unabridged</option>
      </select>

The column Misc3 contains only 2 values: NA and U. I want the first option to allow the user to pick both NA and U (all records) with one click (I don't want to use multiple selections because I have other lists where that would be difficult). Everything works fine for Abridged by itself and Unabridged, but I cannot find what to put for value= to make this work for Abridged and Unabridged. I originally trying setting value="%" because this worked in FrontPage, but it does not work here. I have also tried *, All, ALL, and nothing. Thanks for your help!
Comment
Watch Question

It depends on how you use the value afterwards. Do you plug it into an SQL statement as in
SELECT * FROM mytable WHERE myfield = "(value of misc3)";
?
If so, "NA" and "U" will correctly translate to
SELECT * FROM mytable WHERE myfield = "NA";
and
SELECT * FROM mytable WHERE myfield = "U";
respectively. Hence <option value="NA%32 or myfield=%32U"> would translate to
SELECT * FROM mytable WHERE myfield = "NA" or myfield ="U";
as desired.
However, you definitely do not want to go that way: A malevolent user might return a value of "NA%32; truncate table mytable;select %32dummy" (or worse) translating to
SELECT * FROM mytable WHERE myfield = "NA";truncate table mytable;select "dummy";

In short: It depends on how you evaluate the form.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
you can try this

<select name="Misc3"  id="Misc3 size="3" >
        <option value="NA, U" >Abridged and Unabridged</option>
        <option value="NA">Abridged</option>
        <option value="U">Unabridged</option>
</select>

Author

Commented:
Thanks for the suggestion - unfortunately, that doesn't work either.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
or what about this
<script>
function selAll(v){
  var sb = document.getElementById("Misc3");
  if (v!="") return;
  for (i=0;i<sb.options.length;i++) sb.options[i].selected=(i==0)?false:true;
}
</script>

<select name="Misc3"  id="Misc3" size="3" multiple onClick="selAll(this.value)">
        <option value="">Abridged and Unabridged</option>
        <option value="NA">Abridged</option>
        <option value="U">Unabridged</option>
</select>

Open in new window

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
"Thanks for the suggestion - unfortunately, that doesn't work either. "

please clarify how do you use the values after... does not work does not mean anything for us :)

Author

Commented:
My last post was for the suggestion by HainKurt.

As for the post from thehagman:

I am using the Select inside a <form> </form> set of tags to select records from a database. The
form uses POST to send this information to a second page, where I use a recordset to display other information from the database using the information from the first page as a filter. The relevant code from the second page (generated by Dreamweaver) is:

...
$var4_rsCatalog = "-1";
if (isset($_POST['Misc3'])) {
  $var4_rsCatalog = $_POST['Misc3'];
}

mysql_select_db($database_connTalkingpages, $connTalkingpages);
$query_rsCatalog = sprintf("SELECT Title,Author,Category,Status,Year,Misc2,Amazon FROM `Catalog` WHERE Category = %s AND Media = %s AND Status = %s AND Misc3 = %s ORDER BY Author", GetSQLValueString($var1_rsCatalog, "text"),GetSQLValueString($var2_rsCatalog, "text"),GetSQLValueString($var3_rsCatalog, "text"),GetSQLValueString($var4_rsCatalog, "text"));

You can find Misc3 in here (along with some other variables). I tried your suggestion, but it didn't work. I can't tell from this code whether it should have, based on your idea. Any other ideas?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
got it, try this:

<select name="Misc3"  id="Misc3 size="3" >
        <option value="All" >Abridged and Unabridged</option>
        <option value="NA">Abridged</option>
        <option value="U">Unabridged</option>
</select>

$query_rsCatalog = sprintf("SELECT Title,Author,Category,Status,Year,Misc2,Amazon FROM `Catalog` WHERE Category = %s AND Media = %s AND Status = %s AND (Misc3 = %s  or %s = 'All') ORDER BY Author", GetSQLValueString($var1_rsCatalog, "text"),GetSQLValueString($var2_rsCatalog, "text"),GetSQLValueString($var3_rsCatalog, "text"),GetSQLValueString($var4_rsCatalog, "text"),GetSQLValueString($var4_rsCatalog, "text"));
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
HainKurt, Thanks for the two suggestions. In each case, I am getting the same result as with all the other things I have tried (sorry I didn't clarify before what this is). If it were working, I would get a repeating table,  with rows corresponding to the rows of the database that meet the criteria in the filters. But in this case, I get a table with one row, with the labels I've provided, and no data returned. I know from checking with the options U and NA individually that I should be getting about 15 rows when I combine them, for one specific combination of the other variables, but I am getting 1 blank row. I tried looking at the source code for the second page when it has executed successfully (e.g., value="U"), but
in that case the source already shows the table results. When it doesn't execute successfully, it is also showing the table results, but with no data - so I am not getting any clues from the source code of the second page after it has been called by the first page (with Submit). Any other suggestions? This just doesn't seem like it should be that unusual a task - filtering the rows of a database by using two or more values of a column in the filter. Thanks!
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
post the value of $query_rsCatalog before executing it... write it to page to see the value...

Author

Commented:
HainKurt, Thanks for the suggestion. I use Dreamweaver to generate the code and am not familiar with how to do what you are suggesting. I can follow it once it has been generated, but don't know how to write it. Can you tell me what to add to do what you are suggesting? Thanks for all your help!
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
is this php code? if yes, add this

print "<div style='border:1px solid red'>".$query_rsCatalog."</div>";
or
echo "<div style='border:1px solid red'>".$query_rsCatalog."</div>";
or
print_r "<div style='border:1px solid red'>".$query_rsCatalog."</div>";

after this line

$query_rsCatalog = sprintf("....");
>>>>>> copy one of the lines above here and run the page, and copy paste the code here

Author

Commented:
First, I reset value="" for Misc3. I had to add php tags around what you sent in order to get any
printout in a red box, so what I used was:
<?php echo "<div style='border:1px solid red'>".$query_rsCatalog."</div>"; ?>


What it returned was the page as usual, with no data in the one row of the table, plus the following in a red box:
SELECT Title,Author,Category,Status,Year,Misc2,Amazon FROM `Catalog` WHERE Category = 'Biography' AND Media = 'CD' AND Status = 'Available' AND Misc3 = NULL ORDER BY Author

I tried setting value="NA OR U", but that just returned an empty table and the SELECT line said:
Misc = 'NA OR U'.

I also tried the other ideas you had suggested earlier. But in those cases, I got back a totally blank second page (no header, sidebar, blank table), and just the words:
Query was empty
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
try this

$query_rsCatalog = sprintf("SELECT Title,Author,Category,Status,Year,Misc2,Amazon FROM `Catalog` WHERE Category = %s AND Media = %s AND Status = %s AND (Misc3 = isNull(%s,Misc3)) ORDER BY Author", GetSQLValueString($var1_rsCatalog, "text"),GetSQLValueString($var2_rsCatalog, "text"),GetSQLValueString($var3_rsCatalog, "text"),GetSQLValueString($var4_rsCatalog, "text"));

and post the query in redbox again...

Author

Commented:
This time I got a completely blank second page, except for the message:
Incorrect parameter count in the call to native function 'isNull'
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
yes but each time I need the value of $query_rsCatalog

try this

$query_rsCatalog = sprintf("SELECT Title,Author,Category,Status,Year,Misc2,Amazon FROM `Catalog` WHERE Category = %s AND Media = %s AND Status = %s AND (Misc3 = isNull('%s',Misc3)) ORDER BY Author", GetSQLValueString($var1_rsCatalog, "text"),GetSQLValueString($var2_rsCatalog, "text"),GetSQLValueString($var3_rsCatalog, "text"),$var4_rsCatalog));

or

$query_rsCatalog = sprintf("SELECT Title,Author,Category,Status,Year,Misc2,Amazon FROM `Catalog` WHERE Category = %s AND Media = %s AND Status = %s AND (Misc3 = '%s' or '%s'='') ORDER BY Author", GetSQLValueString($var1_rsCatalog, "text"),GetSQLValueString($var2_rsCatalog, "text"),GetSQLValueString($var3_rsCatalog, "text"),$var4_rsCatalog, $var4_rsCatalog));

Author

Commented:
Both queries return a blank page, except for:
Incorrect parameter count in the call to native function 'isNull'

Although you say that you need the value of $query_rsCatalog,
there is no red box being printed out. When I look at the source code
for the second page, there is nothing on it except for the same
message listed above.

Is there a reason you dropped GetSQLValueString from var4 at the end of the query and rewrote that part? I tried it both as you wrote it and with the original syntax - both gave the same results (message cited earlier).

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
ok, before executing the query put these lines

echo "<div style='border:1px solid red'>".$query_rsCatalog."</div>";
die();

so each time we can see the value of query to find whats wrong...


Sr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
The solution is the combination of the two - In the first answer you have to change isNull to ifNull, and then it works.

Thanks, HainKurt, for persevering to find a solution!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.