Multiple Selections with Listbox

[chrisb]

I am using a listbox as one of the criteria for a report. I'd like to allow me/users to select multiple options; however, this isn't working. When I select one option, it works; but when I select multiple criteria using the shift key, the report comes up blank.

[admin]

chrisb,

What is the listbox's hash variable returning?

Steven

[chrisb]

The list box hash variable is returning the id for a table used to create. The first column in the list box is bidder_id, as is the field name. Here's the PHP code:

Code: Select all

CREATE TABLE #TABLE_ID# 
SELECT bidder_id, bid_number, CONCAT(bid_firstName, ' ', bid_lastname) FROM bidder
WHERE bidder_id = '#bidder_id#'

I'm trying to create a report that will print a separate page for each database row. I can get reports to work fine with a single entry report, but when I use the list box to select multiple rows, I seem to break things.

[massiws]

chrisb,
when you select more then one field with listbox, the returned value is something like "bidder_id1,bidder_id2,bidder_id3".
You have to change the SQL code accordingly.

Max

[chrisb]

You'll have to pardon me--I'm not really experienced with this.

I've tried using the WHERE with IN so that the SQL select will pick out the rows that include those from the list in what the hash variable returns.
I started with the above where the the bidder_id column would match one or more than the list box selected. When I used IN, nothing came back. When I used = as above, I could get one to come back, but I couldn't get more than one to come back.

[massiws]

chrisb,
you have to check returning values from listbox before construct your SQL:

Code: Select all

/* Create an array of all returning values in Listbox */
$listbox =  '#bidder_id#';
$ids = explode(',', $listbox);

/* Create SQL with first value in Listbox */
$sql = "CREATE TABLE #TABLE_ID#
  SELECT bidder_id, bid_number, CONCAT(bid_firstName, ' ', bid_lastname) FROM bidder
  WHERE bidder_id = '" . $ids[0] . "'";

/* Delete first element and add any further Listbox values */
array_shift($ids);
foreach ($ids as $id) {
  $sql .= " OR bidder_id = '" . $id . "'";
}

/* nuDebug($sql);  uncomment this line to check SQL statement in Setup -> Debug */
nuRunQuery($sql); 

Hope this helps,
Max

[achaahaa]

when you select more then one field with listbox, the returned value is something like "bidder_id1,bidder_id2,bidder_id3".
You have to change the SQL code accordingly.

[admin]

chrisb,

I have updated the wiki a bit hopefully this helps.

http://wiki.nubuilder.net/index.php/Add ... istbox_Tab

Steven