[SOLVED] Update 2 tables via checkbox

[realjumper]

Hi,

 

I have the following code.....

 

$selected = implode(",", $_POST['select']); //this would make the array into something like this 1,3,7,8 etc...


if($selected == ''){

echo "Nothing selected!";

}else{

$query8=("UPDATE email SET account_created='Activated' WHERE id in ($selected)");
mysql_query($query8);

}


<table align="center" border="0">
<?php

$query = mysql_query("SELECT * FROM email WHERE sid='$row2[sid]'");
while($r = mysql_fetch_array($query)){
echo "<tr><td>$r[first_name]</td>";
    echo "<td>$r[last_name]</td>";	
echo "<td>$r['email']." </td>";
echo "<td><input type='checkbox' name='select[]' value='".$r['id']."'></td></tr>"; 

//just print a title and a text box. note the [], this creates an array of all selected checkboxes.
}


?>	


<tr>
<td><input type="submit" name="submit" value="Delete Selected Email Addresses"></td>
</tr>
</table>

 

This would work fine, and indeed it does. What my problem is, is that this will only work if the array '$selected' contains numbers only. I now need to update another table in the db, but the id's don't match, so I can't use the 'id' field. What does match both tables though, is 'sid'....but I can't use that because 'sid' contains letters as well as numbers.

 

Is there a way around this do you think?

 

Thanks

[teng84]

you can perform join and update if your two tables have a relationships.

 

and wait your question is not clear to me sorry mate.

[realjumper]

Hiya teng,

 

Yes I know that I can do that, but the array that contains the 'id' numbers is of no use on one of the tables as I cannot join on the id.....because the relationship is not based on the id field. The relationship is based on the 'sid' field, which contains not only numbers, but letters too. When I try this, it won't work....even on a single table, so I'm assuming that the array is not allowed to contain characters other than numbers. If that is so....what can I do?

 

eg

 

sid might be s12345p  <--- and that sid number is common to both tables

[teng84]

array can contain characters and numbers..

 

sorry ! im confused..

can you relate the two tables? so i know how to edit your codes

[realjumper]

Well I haven't got that far yet teng.

 

I don't have a problem with table joins, but to begin with I am trying to update a single table using 'sid' instead of 'id' as in the posted code.

 

What baffles me is that the posted code works just fine....using id.....but the array is empty when using 'sid'

[realjumper]

If I echo out '$selected' when it contains the id number I get this:

 

4544,4666

 

 

If I echo out '$selected' when it contains the sid I get this:

 

AFoot,Utest

 

 

So indeed the array is not empty...sorry....I've been at this awhile.

 

The problem remains though....how to update the table based on the array. It works when it contains 'id', but not when it conatins 'sid'.

 

Anyway....I'm going home so I'll check in later to see if there is something I have missed.

[realjumper]

Just before I go.....I think the problem must be in the update query....

 

$query8=("UPDATE email SET account_created='Activated' WHERE sid in ($selected)");

mysql_query($query8);

 

 

This query is fine if '$selected' contains numbers (plus the comma), ie the 'id' field......but it won't work if '$selected' contains numbers and letters (plus the comma), ie the 'sid' field.

 

So, am I correct in thinking that the query is the problem?

[teng84]

maybe sid is string that needs to have '' on it

 

try to add this

function x($n)
{
    $n = "'".$n."'";
    return $n;
}

$array = array_map("x", $_POST['select']);
$selected = implode(",",$array);

 

 

 

[realjumper]

Okay, I'll try that teng, it sounds good......I really need to go home.....lol!!

 

Do you think that the WHERE condition (WHERE sid in)....in particular the "in" part, is having a problem with characters other than numbers?

 

Thanks....I'll let you know what happens

 

ttfn

[realjumper]

 

maybe sid is string that needs to have '' on it

 

 

You are absolutely correct teng, and your function sorted this out for me. I am very gratefull, thank you so much.

 

The number of times I have been caught out by either using quotes, or not using quotes is amazing!! I must learn to try the obvious first!