PHP Form where sql query is made up of elements of database

lazybrown

Hi,

I have a form that I'm creating that has a list of items that have checkboxes attached to them. The list of items is brought back from a database so how do I dynamically create a SQL statement, dependent on what items are available and are chosen?

Thanks

ahundiak

Assume that your select control has a name of say data. Then

/ Get the data /
$data = $_GET['data'];

(validate $data)

/ Build the query /
$sql = "Select whatever from whomever where data = '$data'";

lazybrown

This works if I only have one variable. However, at the moment there are 5 and in the future there may be more or less than this amount.

For example, if someone wants to search the database for a record that contains any of the following, they click the checkbox that is beside it:

Advertising Campaign Management

Internet

Management

Medicine

Science Research

These 5 items are brought back from a table in my database and can be removed or added to dynamically, so at some point in the future, someone might only be given the option of searching on the first 3 - or more.

How do I write code to accomodate the fact that there won't always be the same amount of options?

Thanks again.

diego25

It should be straight-forward:

get values from database
loop through the results
output a checkbox for each result

For the first part, you use mysql_query() and a SELECT SQL statement.

For the second part, it's the usual while ($row = mysql_fetch_array($result)) loop

For the third part, is just print "<input"> etc.

Diego

lazybrown

Thanks for the prompt reply - don't think I explained very well though.

I have the first bit that you mention done. So, I have the code as follows:

<?php
$checknum = 1;
do {

				  ?>
                  <tr> 
                    <td><?php echo $row_JobListRS['JOBCAT']; ?></td>
                    <td width="10"> 
                      <input type="checkbox" name="checkbox<?php echo $checknum; ?>" value="<?php echo $row_JobListRS['JOBCAT']; ?>"></td>
                  </tr>
                  <?php 
				 $checknum = $checknum + 1;
				  } while ($row_JobListRS = mysql_fetch_assoc($JobListRS)); 

				  ?>

This gives me all the options I require and, for each, a uniquely named checkbox.

However, the part I'm having difficulty with is when a user checks 1, 2, or n boxes, how do I create a search string to search for these options, bearing in mind that there won't always be 5 (as there are now) options to choose from?

Thanks again

diego25

What I'd recommend is naming all the checkboxes the same, and with an array name, e.g.

Name them all the same, so whatever the user checks, will then appear in
$_POST['search'], which will be an array. Try it, and then submit it to a script that has

foreach ($_POST['search'] as $option) {
print $option . "<br />";
}

It should print out whatever the user checked.

Diego

lazybrown

Thanks a lot for that - works a treat.

Ronnie

lazybrown

Hello again.

Having got my dynamic variables separated from each other successfully, I was wondering if you could assist me in my sql string? I can't seem to get it to include all the terms in the array.

Would the SQL look something like this?

select * from BC_jobs where BC_jobs.category IN $option

Thanks again for any help.

diego25

The SQL should be something like this:

select * from BC_jobs where BC_jobs.category IN (option1, option2)

So you need to separate each option with one command, and surround the whole thing with parenthesis. So, to join them with a comma, you can use implode():
http://www.php.net/implode
To surround it with parenthesis should be trivial...

Diego