looking for advice: checkboxes and mysql

[fogofogo]

Hello all,

I am currently working on a project, and I need some advice on a couple of things. I am working on a system that displays records from a database. I have included a checkbox beside each record that is displayed, and the idea is that when the user selects the check box and hits submit, the record will disappear from the list of records, but must not be deleted from the database.

So my main question is what kind of field will I need to add to my database? Some sort of field that has true/false setting?

And from this I take it I must perform a check to see if the record is true or false in order to determine whether or not to display it?

this is what my code looks like so far - sorry its probably very amateurish

[code]<?php

// this is just to show which record has been selected. It displays the record Id at the top of the page

if(isset($_POST['record']))
{
   $record = $_POST['record'];
   $n        = count($record);
   $i        = 0;

   echo "The record you selected are \r\n" .
        "<ol>";
   while ($i < $n)
   {
      echo "<li>{$record[$i]}</li> \r\n";
      $i++;
   }
   echo "</ol>";
}

//connect to the database
  
$dbhost = 'here:3306 ';
$dbuser = 'coolhan_admin';
$dbpass = 'wordword';


$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');

$dbname = 'coolhan_bigslickreg';
mysql_select_db($dbname);

$result = mysql_query( "SELECT * FROM casinocredit ORDER BY ID ASC" )
or die("SELECT Error: ".mysql_error());

// print out the results here

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";
echo "<form method='post' action=";
echo $_SERVER['PHP_SELF'];
echo ">";

// creating a loop for the records

$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"ID");
$from=mysql_result($result,$i,"dfrom");
$email=mysql_result($result,$i,"demail");
$clubrep=mysql_result($result,$i,"dclubrep");
$amount=mysql_result($result,$i,"damount");

// printing out the records

echo "<b>$id</b><br>From: $from<br>Email: $email<br>Club rep: $clubrep<br>Amount: $amount<br>";
echo "<input name='record[]' type='checkbox' value='$id'>";
echo "<hr><br>";

$i++;
}

echo "<input name='send' type='submit' id='send' value='Send!'></form>"

?>[/code]


If anyone could offer me any advice I greatly appreciate it:)

Thanks

John

[ober]

Are you deleting the record or just removing it from the display? If you're deleting the record from the database, you don't need an extra field. If you're just removing it from the display, all you need is an enum field that can be either 1 or 0.

Keep in mind that when you submit a form with checkboxes, only the ones that are checked will be passed on.

[fogofogo]

[!--quoteo(post=374926:date=May 18 2006, 07:33 AM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 18 2006, 07:33 AM) [snapback]374926[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Are you deleting the record or just removing it from the display? If you're deleting the record from the database, you don't need an extra field. If you're just removing it from the display, all you need is an enum field that can be either 1 or 0.

Keep in mind that when you submit a form with checkboxes, only the ones that are checked will be passed on.
[/quote]


Hi Ober - thanks for that

I just want to hide the record and not delete it. I added a boolean field to the database and set records I want to display to 0. and then changed my SQL statement to say

SELECT * FROM casinocredit WHERE `dadded` = 'false' ORDER BY ID ASC

and it seems to work fine. Thanks.

However my next probelm is how to update the record and change the boolean to 1 (in order to hide it after the check box has been clicked and submitted. Any thoughts?

cheers

[ober]

Since you are using an array for the names, you can do something like this in your processing page:

[code]
foreach($_REQUEST['record'] as $key)
{
    $query = "UPDATE table SET boolean_field = 1 WHERE ID = $key";
    // run query and whatever else
}
[/code]

[anatak]

Hello,

I just want to say something about having a boolean on the record for hiding/showing.

imagine you have more than one user accessing your database
user 01 makes a selection and hides records 1 and 3 and 5
user 02 makes a selection but the records 1 and 3 and 5 are hidden so maybe he does not see the results he wanted.

just a thought.

to hide the records
you have to run an UPDATE statement for every record.
something like
you make your checkbox in your form like this
[code]
<input name='record[$id]' type='checkbox' value='$id'>
[/code]

and where you process your form you do something like this
[code]
foreach ($_POST['record'] as $id => $val)
{
UPDATE STATEMENT;
UPATE [table] set [name of boolean column]=1 where [name of idcolumn]=$id;
}
[/code]


hope this helps

[fogofogo]

[!--quoteo(post=374939:date=May 18 2006, 08:40 AM:name=anatak)--][div class=\'quotetop\']QUOTE(anatak @ May 18 2006, 08:40 AM) [snapback]374939[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hello,

I just want to say something about having a boolean on the record for hiding/showing.

imagine you have more than one user accessing your database
user 01 makes a selection and hides records 1 and 3 and 5
user 02 makes a selection but the records 1 and 3 and 5 are hidden so maybe he does not see the results he wanted.

just a thought.

to hide the records
you have to run an UPDATE statement for every record.
something like
you make your checkbox in your form like this
[code]
<input name='record[$id]' type='checkbox' value='$id'>
[/code]

and where you process your form you do something like this
[code]
foreach ($_POST['record'] as $id => $val)
{
UPDATE STATEMENT;
UPATE [table] set [name of boolean column]=1 where [name of idcolumn]=$id;
}
[/code]
hope this helps
[/quote]


Thats brilliant - thanks