Problem with multiple form selections 1

meeble · May 15, 2003

I have a web form:

<form method=post action="timesheet_view2.php">
<table width="760" border="0" cellspacing="3" cellpadding="3" align="left">
<tr>
<td width="200" class="heading3">Staff Name</td>
<td width="560" class="heading4"><SELECT multiple NAME="name">
<option>jamesf4218</option>
<option>Pete</option>
<option>Alex Warbler</option>
</SELECT></td></tr>
<tr>
<td width="200" class="heading3">Job Number</td>
<td width="560" class="heading4"><select multiple name="jobnumber">
<option>acd000</option>
<option>acd001</option>
<option>CAU032/401</option>
<option>MOT023/102</option>
</select>
</td>
</tr>
<tr>
<td width="200" class="heading3">Client</td>
<td width="560" class="heading4"><select multiple name="client">
<option>foo</option>
<option>blah</option>
<option>meep</option>
</td>
</tr>
<tr>
<td width="200" class="heading3">Description of Job/Task</td>
<td width="560" class="heading4">
<textarea cols="19" rows="5" name="description">
</textarea>
</td>
</tr>
<tr>
<td width="200" class="heading3">Stage</td>
<td width="560" class="heading4"><select multiple name="stage">
<option>N/A</option>
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>
</select></td>
</tr>
<tr>
<td width="200"class="heading3">Start Date</td>
<td width="560" class="heading4">
<input type="text" value="15-05-03" size=12 name="firstdate"></td>
</tr>
<tr><td width="200"class="heading3">Finish Date</td><td width="560" class="heading4">
<input type="text" size=12 name="lastdate" value="15-05-03"></td>
</tr>
<tr>
<td width="200" class="heading3">Time (in Hrs i.e 4 or 4.5)</td><td width="560" class="heading4">
<input type=text name=timetaken size=12></td>
</tr>
<tr>
<td>
<input class="button" type="submit" value="Submit..."></td>
</tr></table>
<br clear=all>
</form>
</table>

When the user chooses their criteria and click submit, the following script searches the database and brings up the records based on the criteria.

function englishToAmericanDate($str)
{
$elements = explode("-", $str);
$americandate = $elements[2]."-".$elements[1]."-".$elements[0];
return $americandate;
}
$americandate = englishToAmericanDate($firstdate);
$americandate2 = englishToAmericanDate($lastdate);

$db = mysql_connect("localhost", "root&quot

;

mysql_select_db("acwh",$db);

if ($name == "&quot

{$name = '%';}

if ($jobnumber == "&quot

{$jobnumber = '%';}

if ($client == "&quot

{$client = '%';}

if ($description == "&quot

{$description = '%';}

if ($stage == "&quot

{$stage = '%';}

if ($timetaken == "&quot

{$timetaken = '%';}

$result = mysql_query ("SELECT * FROM timesheets WHERE name LIKE '%$name%' AND jobnumber LIKE '%$jobnumber%' AND client LIKE '%$client%' AND description LIKE '%$description%' AND stage LIKE '%$stage%' AND timetaken LIKE '%$timetaken%' AND entrydate BETWEEN '".$americandate."' AND '".$americandate2."' order by entrydate desc",$db);

if ($myrow = mysql_fetch_row($result)) {

do {

echo "<table width=\"760\" border=\"0\" cellspacing=\"3\" cellpadding=\"3\" align=\"left\">
<tr><td nowrap width=\"120\" class=\"nound2\">",$myrow[1], "</td><td nowrap width=\"100\" class=\"nound2\">",$myrow[2], "</td>
<td nowrap width=\"120\" class=\"nound2\">",$myrow[3], "</td><td nowrap width=\"220\" class=\"nound2\">",$myrow[4], "</td><td nowrap width=\"50\" class=\"nound2\">",$myrow[5], "</td><td nowrap width=\"85\" class=\"nound2\">",$myrow[6], "</td><td nowrap width=\"65\" class=\"nound2\">",$myrow[7], "</td></tr></table><br clear=all>";

$sum += $myrow[7];

} while($myrow = mysql_fetch_row($result));

} else {print "<font color=\"red\">Sorry, no records were found!</font><br>";}

echo "<table width=\"760\" border=\"0\" cellspacing=\"3\" cellpadding=\"3\" align=\"left\"><tr><td colspan=\"2\"><hr color=\"#003366\"></td></tr><tr><td nowrap valign=\"top\" align=\"right\" width=\"740\">The total number of hours is</td><td nowrap valign=\"top\" class=\"nound3\" align=\"right\" width=\"20\"> ", $sum, "</td></tr></table>";

This all works fine. However, if the user tries to choose multiple selections in the same field on the form, it doesn't work. For example, if the users chooses jamesf4218 only, the computer returns all records for jamesf4218. But if the user chooses jamesf4218 and Pete then it just returns the records for pete.

How do I change this please?

Many thanks

James

vbkris · May 15, 2003

u can workaround this only by using javascript. this is peculiar only to multiple selection box (works fine with check boxes).

loop throught the contents of the drpdown save the values that are seleced to a hidden field and retrieve the hidden field valu in the second page.

Known is handfull, Unknown is worldfull

meeble · May 15, 2003

>>>>>>>>>>>>u can workaround this only by using javascript. this is peculiar only to multiple selection box (works fine with check boxes).

This isn't the case. I believe that you can put the values into an array using the [] notation and display the records using FOREACH. I just need some help on how to do this. I find the manual a bit complicated......

sleipnir214 · May 15, 2003

If you need multiple selections in a select box, change the name of the element to "foo[]" instead of just "foo". PHP will then be able to provide any number of selected options and will provide them to you in an array -- even if only one option was selected.

If the use of "[]" element-naming notation is in the manual, I've never been able to find it.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

meeble · May 15, 2003

I know to make the form elements, ie name="jobname[]" but I don't know how, on the script page, to pull records from the database based on the values in the arrays

DRJ478 · May 15, 2003

Ok, here's the deal:

Code:

<select multiple name=&quot;stage[]&quot;>
<option>N/A</option>
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>
</select>

The square brackets in the name pass the posted value(s) as an array.

What you need to do in composing your SQL statement is make sure that the multiple selections are combined with an OR statement.

Alternatively you can use the FIND_IN_SET(str,strlist) SQL function:

Code:

$myList = implode(',',$_POST[stage]);
$SQL = &quot;SELECT * FROM myTable WHERE FIND_IN_SET(mycolumn,'$myList')&quot;;

meeble · May 15, 2003

Hello,

I thought I'd try with the 'stage' field so I changed the code to this:

$myList = implode(',',$_POST[stage]);

$result = mysql_query ("SELECT * FROM timesheets WHERE name LIKE '%$name%' AND jobnumber LIKE '%$jobnumber%' AND client LIKE '%$client%' AND description LIKE '%$description%' AND FIND_IN_SET(stage,'$myList') AND timetaken LIKE '%$timetaken%' AND entrydate BETWEEN '".$americandate."' AND '".$americandate2."' order by entrydate desc",$db);

But this draws out no results and I get the error message:

PHP Warning: Bad arguments to implode() in c:\inetpub\

http://wwwroot\new\timesheet_view2.php

on line 45

DRJ478 · May 15, 2003

Troubleshooting:

1. Did you add the [] in the HTML tag?
2. Are you using method="post"?
3. print out all posted variables to the screen to inspect what's posted:
print_r($_POST);

Let's go from there.

meeble · May 15, 2003

Hello,

I have changed the code for all 4 fileds so now the code is:

$myname = implode(',',$_POST[name]);
$myjobnumber = implode(',',$_POST[jobnumber]);
$myclient = implode(',',$_POST[client]);
$mystage = implode(',',$_POST[stage]);

$result = mysql_query ("SELECT * FROM timesheets WHERE FIND_IN_SET(name,'$myname') AND FIND_IN_SET(jobnumber,'$myjobnumber') AND FIND_IN_SET(client,'$myclient') AND description LIKE '%$description%' AND FIND_IN_SET(stage,'$mystage') AND timetaken LIKE '%$timetaken%' AND entrydate BETWEEN '".$americandate."' AND '".$americandate2."' order by entrydate desc",$db);

This just gives the error messages:

PHP Warning: Bad arguments to implode() in c:\inetpub\

http://wwwroot\ac\new\timesheet_view2.php

on line 45 PHP Warning: Bad arguments to implode() in c:\inetpub\

http://wwwroot\ac\new\timesheet_view2.php

on line 46 PHP Warning: Bad arguments to implode() in c:\inetpub\

http://wwwroot\ac\new\timesheet_view2.php

on line 47 PHP Warning: Bad arguments to implode() in c:\inetpub\

http://wwwroot\ac\new\timesheet_view2.php

on line 48

And print_r($_POST); just gives print_r(Array);

What am I doing wrong?

Cheers

James

meeble · May 15, 2003

Hmm, I think the problem might be if I don't select any options in the fields. Before, if I didn't select anything it looked through EVERYTHING. Now, if I don't select something it selects NOTHING and comes up with the error.

If I select options in all 4 fields it seems to work. What do I do if I don't want to select any options in a particular drop down list?

Cheers

James

DRJ478 · May 15, 2003

Instead of just writing out the SQL statement assuming that there will be all creiteria present it is advisable to compile the statement according to the state of the passed arrays.
The procedure for each array is similar:
1. if nothing was selected there is no $_POST[] for fieldname.
2. Only tag on the 'FIND_IN_SET()' when there is something in the array.
3. If the array is empty omit the condition.

meeble · May 15, 2003

Well, I tried that and the code I have now is:

$db = mysql_connect("localhost", "root&quot

;

mysql_select_db("acwh",$db);

if ($description == "&quot

{$description = '%';}

if ($timetaken == "&quot

{$timetaken = '%';}

if(is_array($_POST[name]) )
{
$myname = implode(',',$_POST[name]);
}
else $myname = "";

if(is_array($_POST[jobnumber]) )
{
$myjobnumber = implode(',',$_POST[jobnumber]);
}
else $myjobnumber = "";

if(is_array($_POST[stage]) )
{
$mystage = implode(',',$_POST[stage]);
}
else $mystage = "";

if(is_array($_POST[client]) )
{
$myclient = implode(',',$_POST[client]);
}
else $myclient = "";

$result = mysql_query ("SELECT * FROM timesheets WHERE FIND_IN_SET(name,'$myname') AND FIND_IN_SET(jobnumber,'$myjobnumber') AND FIND_IN_SET(client,'$myclient') AND description LIKE '%$description%' AND FIND_IN_SET(stage,'$mystage') AND timetaken LIKE '%$timetaken%' AND entrydate BETWEEN '".$americandate."' AND '".$americandate2."' order by entrydate desc",$db);

This gets rid of the error messages but brings up no records whatever I choose.

What is wrong?

Cheers

James

DRJ478 · May 15, 2003

You are still making the assumption that you have to use the FIND_IN_SET.
Do it like this for each field:

Code:

if(is_array($_POST[client])){
   $myclient = implode(',',$_POST[client]);
   $condition .= &quot;AND FIND_IN_SET(client,'$myclient') &quot;;
}
[code]
The later assemble the SQL statement with the part that never changes first:
[code]
$SQL = &quot;SELECT * FROM timesheets WHERE timetaken LIKE '%$timetaken%' AND entrydate BETWEEN '&quot;.$americandate.&quot;' AND '&quot;.$americandate2.&quot;' $condition order by entrydate desc&quot;;
$result = mysql_query($SQL,$db) OR die(mysql_error());

This way it will only apply the conditions if there is a selection made.
Just be aware that all these are AND, that means the record must match all the criteria.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Problem with multiple form selections 1

meeble

Programmer

vbkris

Programmer

meeble

Programmer

sleipnir214

Programmer

meeble

Programmer

DRJ478

IS-IT--Management

meeble

Programmer

DRJ478

IS-IT--Management

meeble

Programmer

meeble

Programmer

DRJ478

IS-IT--Management

meeble

Programmer

DRJ478

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor