?
Solved

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

Posted on 2011-04-19
20
Medium Priority
?
289 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!
0
Comment
Question by:judypage4
  • 11
  • 8
20 Comments
 
LVL 20

Expert Comment

by:thehagman
ID: 35433695
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.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35434829
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>

0
 

Author Comment

by:judypage4
ID: 35434861
Thanks for the suggestion - unfortunately, that doesn't work either.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:HainKurt
ID: 35434923
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

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35434930
"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 :)
0
 

Author Comment

by:judypage4
ID: 35434947
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?
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35434994
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"));
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 2000 total points
ID: 35435012
or this

<select name="Misc3" id="Misc3" size="3" >
        <option value="">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 = isnull(%s,Misc3)) ORDER BY Author", GetSQLValueString($var1_rsCatalog, "text"),GetSQLValueString($var2_rsCatalog, "text"),GetSQLValueString($var3_rsCatalog, "text"),GetSQLValueString($var4_rsCatalog, "text"));
0
 

Author Comment

by:judypage4
ID: 35437939
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!
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35438511
post the value of $query_rsCatalog before executing it... write it to page to see the value...
0
 

Author Comment

by:judypage4
ID: 35441489
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!
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35442491
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
0
 

Author Comment

by:judypage4
ID: 35443125
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
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35443637
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...
0
 

Author Comment

by:judypage4
ID: 35444379
This time I got a completely blank second page, except for the message:
Incorrect parameter count in the call to native function 'isNull'
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35444442
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));

0
 

Author Comment

by:judypage4
ID: 35445054
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).

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35445534
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...


0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 35445545
oops, I guess we should use ifnull function instead of isnull :) try the queries with ifnull...
0
 

Author Closing Comment

by:judypage4
ID: 35445795
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!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
In this tutorial viewers will learn how add a scalable full-width header using CSS3. Create a new HTML document with an internal stylesheet. Set a tiled background.:  Create a new div and name it Header. Position it with position:absolute at the top…
In this tutorial viewers will learn how to style transparent/translucent elements using alpha transparency in CSS Start with a normal styled element, such as a div.: Define its "background-color" property as "rgba (255, 255, 255, .5): The numbers in…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question