select and then perform task (PHP and MySQL)

[Jochem]

hi,

I have a little problem, and I don't see a solution. I hope some of
you see a workable solution for my problem. Below I will explain what
I would like to do.

I got a variable number of rows in a table on my HTML page; those rows
are a direct output of my MySQL database. Very row as an ID number. I
would like to put a checkbox in front of every row in the HTML page,
and below some bottoms to functions like delete.

I am thinking of giving the checkboxes the id from the database as
their name. Once this form is submitted it goes to the page that needs
to process it. Here my problem starts. Putting in a query that is
something like: "DELETE FROM table WHERE id = $_POST["x"];" is not the
problem. But since it is possible to check more than one checkbox to
preform the function for several items simultaneously, I have no clue
about how to build the SQL query. Is there anyone, who has a clue how
to solve it.

Similar applications are used in many different webmail applications
(like Operamail and GMail). Just to delete all the selected mails in
once.

Looking forward to hear any suggestion.

Thank you!

Regards,
Jochem Donkers

[Steve]

Re: select and then perform task (PHP and MySQL)


Construct your SQL statement with the list of IDs to delete using this
syntax:

'DELETE FROM table WHERE id IN ( 1, 3, 7, 12, 19 )'

where the list in parentheses is built dynamically from the checked
items array $_POST[ 'X' ].

---
Steve

[Jochem]

Re: select and then perform task (PHP and MySQL)

> 'DELETE FROM table WHERE id IN ( 1, 3, 7, 12, 19 )'

Anybody a suggestion how to build the variables in the IN statement.
My point of departure is that I have a form in which I define the
variables (id) with a checkbox. Thus, this is my input:

<input type="checkbox" name="id" value="21" />

Is there a possibility to 'empty' the pass on with some contruction
like:

$query = "DELETE FROM table WHERE id IN (";
while (list($id) - $_POST)){
$query .= "$id";
}
$query .= ")";

$result = mysql_query($qu ery) or die();

I am just not sure how to do the 'while' thing.

Hope somebody has a suggestion. Thanks in advance!

Jochem

[Steve]

Re: select and then perform task (PHP and MySQL)






Jochem wrote:[color=blue][color=green]
> > 'DELETE FROM table WHERE id IN ( 1, 3, 7, 12, 19 )'[/color]
>
> Anybody a suggestion how to build the variables in the IN statement.
> My point of departure is that I have a form in which I define the
> variables (id) with a checkbox. Thus, this is my input:
>
> <input type="checkbox" name="id" value="21" />
>
> Is there a possibility to 'empty' the pass on with some contruction
> like:
>
> $query = "DELETE FROM table WHERE id IN (";
> while (list($id) - $_POST)){
> $query .= "$id";
> }
> $query .= ")";
>
> $result = mysql_query($qu ery) or die();
>
> I am just not sure how to do the 'while' thing.
>
> Hope somebody has a suggestion. Thanks in advance!
>
> Jochem[/color]



Jochem:

You are nearly there. Two things need to be done. First, you need to
tell PHP that your checkboxes form a group, by naming them as an array,
thus:

....
<input type="checkbox" name="id[]" value="20" />
<input type="checkbox" name="id[]" value="21" />
<input type="checkbox" name="id[]" value="22" />
....

Note the square brackets appended to the name. PHP will pass these to
you in the POST variable $_POST[ 'id' ] which will be an array of all
the checkbox values that were ticked.

Secondly, you iterate through this array to build your query...

$strSQL = 'DELETE FROM table WHERE id IN (';
$blnFirst = True;

if( isset( $_POST[ 'id' ] ) )
{
foreach( $_POST[ 'id' ] as $strID )
{
if( $blnFirst )
{
$strSQL .= $strID; // no comma
$blnFirst = False;
}
else
{
$strSQL .= ', ' . $strID;
}
}
$strSQL .= ')';
}
else
{
// ...oops, none selected...
}


---
Steve

[Michael Fesser]

Re: select and then perform task (PHP and MySQL)

.oO(Steve)
[color=blue]
>Secondly, you iterate through this array to build your query...
>
>$strSQL = 'DELETE FROM table WHERE id IN (';
>$blnFirst = True;
>
>if( isset( $_POST[ 'id' ] ) )
>{
>foreach( $_POST[ 'id' ] as $strID )
>{
>if( $blnFirst )
>{
>$strSQL .= $strID; // no comma
>[...][/color]

It might be easier to use implode() instead:

// check if something was submitted at all
$values = isset($_POST['id']) && !empty($_POST['id'])
// very basic validation, non-numeric values will become 0
? implode(',', array_map('intv al', $_POST['id']))
// no empty IN operator allowed
: 'NULL';

$strSQL = "DELETE FROM table WHERE id IN ($values)";

Micha