How to update data in table using form and a checkbox?

[flametail]

I have a php script that shows all the data in a table. I want it to have check boxes next to each line of this data that it puts out, so I can check the boxes next to certain entries, and when I click submit, the "have" collumn for those entries is set to yes.

Here is most of the script that shows the list:

$query="SELECT * FROM get ORDER BY artist,name ASC";
$result=mysql_query($query);
 
$num=mysql_numrows($result);
 
mysql_close();
?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Artist</font></th>
<th><font face="Arial, Helvetica, sans-serif">Album</font></th>
<th><font face="Arial, Helvetica, sans-serif">Have</font></th>
</tr>
 
<?php
$i=0;
while ($i < $num) {
 
$f1=mysql_result($result,$i,"name");
$f2=mysql_result($result,$i,"artist");
$f3=mysql_result($result,$i,"album");
$f4=mysql_result($result,$i,"have");
?>
 
<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
</tr>
 
<?php
$i++;
}
?>
</body>
</html>

To try to help out whoever helps(becuase I honestly have no idea how to do this) The MySQL query to put Yes in the Have column would be similar to this:

UPDATE get SET have = YES WHERE name = $CheckedRows

[code green]

UPDATE get SET have = 'YES' WHERE name IN ($CheckedRows)

$CheckedRows needs to be a string of the format 'id1','id2','id3'.
So you wil need to loop through the web-form $_POST array, collecting which names were checked.

[flametail]

And how may that be accomplished? Sorry to say I don't really understand arrays and looping through them.

[Markus]

Hi, flametail.

Firstly you need to associate some value with your checkboxes so that you can read this data when your form is submit. At this point I will assume that your rows in your database have some unique ID associated with them. So your checkboxes will look something like:

<input 
  type="checkbox" 
  name="item_have[]" 
  value="<?php echo $items['id']; ?>"
/>
 

Each checkbox should use the same "name" attribute - the reason for this will be discussed later.

Note: you'll have to modify the above code to be output in your loop and also substitute the $row variable with the correct data.

Now that we have some data we can use, we can move on to processing this data.

When your form is submit, that form data is then available in either the $_GET or $_POST superglobal arrays (PHP populates them for you), depending on the the "method" attribute of your form - if you specify no method, it will default to GET. I'll assume you're using POST.

So now let's take a look at what we need to tell PHP to do.

When PHP is processing the POST data of the HTTP request, it looks to see what nodes have the same names (before I mentioned that all the checkboxes should use the same "name attribute"), and then sticks each of them into an array - a very cool thing, I think you'll agree. This leaves us with an array we can access with the $_POST variable, like so:

var_dump($_POST['have_item']);
 

If you stick the above code in a PHP file and then tell your form to submit data to it, you'll see a human-readable representation of the array PHP created for you, hopefully populated with the items you selected!

Now, according to Code Green's post above, we need a piece of data in the format "(1,2,3,...)" to give to MySQL. We can achieve this using the implode PHP function:

// Items that I have, formatted appropriately
$have_items_fm = implode(',', $_POST['have_item']));
 

And that should be roughly all you need to know. Just stick this variable in your MySQL query string between a couple of parentheses, and you're laughing!

Let us know if you have any trouble.

[flametail]

I tried, and I failed. I used GET just to see what was being sent and it was index.php?have_items=1&have_items=2&submit=submit

Dunno if that's right or not, but var_dump only shows 2, or 3 if thats in the list, so always highest number? And it keeps saying implode has improper syntax.

also, can you add the stuff to my code in the first post with a few comments? I want to see if I did it right.

[Markus]

Woops! That's my fault. The input elements should be named the same with square brackets appended! Like this:

Name 1: <input type="text" name="username[]" /><br />
Name 2: <input type="text" name="username[]" />
 

My apologies.

Without sound rude, no, I will not write your code for you. If you'd like to show us what you've tried, then we can point out what's wrong and how you can fix it.

[flametail]

Ok, I have it working now :D

Now, is it possible to only show the checkbox if have=no?

[Markus]

Sure - just modify your SQL SELECT stuff to use a WHERE query.

SELECT blah,blah FROM table WHERE column = 'value'
 

[mad genius]

Here is a JavaScript solution to the problem:

<html>
<body onLoad=chgtable()>
<table border=1>
<tr><td id="tablecell">Unchecked</td></tr>
</table>
<script type="text/javascript">
function chgtable(){
    if(document.getElementById("checkbox").checked == true){
        document.getElementById("tablecell").innerHTML = "Checked"
    }
    else{
        document.getElementById("tablecell").innerHTML = "Unchecked"
    }
}
</script>
<input type=checkbox id="checkbox" onChange=chgtable()><br />
</body>
</html>

[Markus]

Would you care to explain how that is a solution?

[flametail]

Neither of those is a solution. :)

I still want the row of data shown, just not the checkbox.

[Markus]

Sorry about that, flametail. I misread your question.

Your code would look something like (I'm making some assumptions - I haven't seen your code, so I'm not sure what you're doing):

if ( $item['have'] == 1 )
{
  echo "<input type='checkbox' name='have_item[]' value='whatever' />";
}