Using arrays to read and write from a mysql database

[brandon_elfring]

Hi all this is my first post!  I come to you for a bit of help.  I am a novice php programmer and have built a friend a site where he displays what products he has for sale.  He has an admin section of the website and uses this to add/delete products.  I would like to add a checkbox functionality to this page.  There will be  a checkbox next to each product.  When the box is checked the product will be displayed on the website and when the checkbox is not checked it will not display this product.  I created a test page since I have never done this sort of thing before.

 

I am successful in getting the array from the database and writing to the screen, however I cannot figure out how to check a box and have that write a "1" to my active column in my mysql database making this product display on the webpage.  See my code below. 

 

<?php

include ('connect.php');

if(isset($_POST['save']))

{

$arr = $_POST['$checked'];

if(isset($arr))

{

foreach( $arr as $value)

{

 

@mysql_query("UPDATE products SET active='1' WHERE id='$value'");

}

}

 

else

{

@mysql_query("UPDATE products SET active='0' WHERE id='$value'");

}

}

 

$query = mysql_query("SELECT * FROM products WHERE type='misc'");

if(!$rowcount = mysql_num_rows($query)==0)

{

echo "

<form name='update' method='post' action='test.php'>

<table>

";

$query = mysql_query("SELECT * FROM products WHERE type='misc'");

while($row = mysql_fetch_assoc($query))

{

echo "

<tr>

<td>";

if($row['active']== 1)

{

echo "<input type='checkbox' name='checked[".$row['id']."]' id='".$row['id']."' checked='checked' value='1'>";

}

else

{

echo "<input type='checkbox' name='checked[".$row['id']."]' id='".$row['id']."' value='0'>";

}

echo "

<td>".$row['id']."</td>

<td>".$row['name']."</td>

<td>".$row['price']."</td>

</tr>

";

}

echo "</table>";

echo "<input type='submit' name='save' value='save'>

</form>";

}

?>

[gamblor01]

I had this very same question and you're in luck as there is a very simple solution!  It looks like you're on the right track but complicating things more than they need to be.  See haku's reply to my post here:

 

http://www.phpfreaks.com/forums/index.php/topic,261812.msg1232899.html#msg1232899

 

 

For every checkbox you display in the form you just need to give them all the name of the same array.  Then when the form is submitted (i.e. when you perform the POST to test.php) you can just grab the array out of $_POST and iterate through the values.

[brandon_elfring]

Thank you!  I did see that post and it helped me to get closer but the problem Im having is actually in the foreach statement.  I don't think im doing that correctly.  I know how to see which boxes are checked now but I don't know how to pull the unique id from the checkbox field to specify in my mysql query.(see my logic below the checkbox) My checkboxes now look like the below code.  Which from the example seems to be correct.  I am also storing the unique database id as the id of the checkbox. 

 

echo "<input type='checkbox' name='checked[]' id='".$row['id']."' checked='checked' value='1'>";

 

I don't know how to pull the unique id of the checkbox to be able to specify where to insert a 1 for active.

 

if(isset($_POST['save']))

{

$arr = $_POST['checked'];

$fields = $_POST['field'];

print_r($arr);

 

foreach( $arr as  $value)

{

@mysql_query("UPDATE products SET active='1' WHERE id='$value'");

}

}

[Dennis1986]

Hey there,

 

You don't need to loop and create that many Queries.

Instead just do one Query with the array in it.

 

Here's how:

 

if(isset($_POST['save'])) 
{   
   $arr = $_POST['checked'];
   $fields = $_POST['field'];
   
   mysql_query("UPDATE products SET active='1' WHERE id IN ($arr)") or die(mysql_error());
}

I removed the @ sign because it's not a good practice to ignore errors

"or die(...)" is added in case there's an error, then you'll know why it happens

[brandon_elfring]

Hi and thank you for your response.

I used the code you provided however I am getting this error "Unknown column 'Array' in 'where clause".  I understand the error however since I don't understand fully the  "WHERE id IN ($arr)" im not sure where to correct it.  If im not mistaken this where clause is simply finding the id of the checkbox and inserting a "1" in the active column right? 

 

 

[Dennis1986]

I just noticed in your first reply that you are giving the checkboxes' id the id of the data, however the id attribute in the element isn't being sent with the form.

 

You can only send names and values from forms to php.

 

 

And lastly, the error you are getting has nothing to do with the code I gave you.

It's something else you did to the code before/in/after.

 

Also the problem here is that unchecked checkboxes are NOT sent to PHP.

So you won't be able to uncheck and update those checkboxes in php/mysql.

 

What I'm trying to say is:

Checked checkbox goes to $_POST.

Unchecked checkboxes are ignored and does not.

[brandon_elfring]

You are sooo right!  Here is my fixed code, and it works, but again like you said there is no way to "Uncheck" the box and have that action saved to the database.  Any recommendations?

 

<?php

include ('connect.php');

if(isset($_POST['save']))

{

$arr = $_POST['checked'];

print_r($arr);

 

 

foreach( $arr as $value)

{

mysql_query("UPDATE products SET active='1' WHERE id=$value") or die(mysql_error());

}

}

 

$query = mysql_query("SELECT * FROM products WHERE type='misc'");

if(!$rowcount = mysql_num_rows($query)==0)

{

echo "

<form name='update' method='post' action='test.php'>

<table>

";

$query = mysql_query("SELECT * FROM products WHERE type='misc'");

while($row = mysql_fetch_assoc($query))

{

echo "

<tr>

<td>";

if($row['active']== 1)

{

echo "<input type='checkbox' name='checked[]' id='' checked='checked' value='".$row['id']."'>";

}

else

{

echo "<input type='checkbox' name='checked[]' id='' value='".$row['id']."'>";

}

echo "

<td>".$row['id']."</td>

<td>".$row['name']."</td>

<td>".$row['care']."</td>

<td>".$row['price']."</td>

</tr>

";

}

echo "</table>";

echo "<input type='submit' name='save' value='save'>

</form>";

}

?>

[Dennis1986]

echo "<form method='POST'>";

$query = mysql_query("SELECT * FROM products WHERE type='misc'");
$checkboxes = array();

while($row = mysql_fetch_assoc($query)) {
  $id = $row["id"];
  if ($row['active'] == '1') {
    echo "<input type='checkbox' name='check[]' value='$id' checked='checked' />";
  } else {
    echo "<input type='checkbox' name='check[]' value='$id' />";
  }
  $checkboxes[] = $id;
}

$array= implode(",", $checkboxes);
echo "<input type='text' name='checkboxes' value='$array' />";
echo "</form>";


// Posted...
if (isset($_POST)) {
  $checkboxes = explode("," $_POST["checkboxes"]);
  foreach ($checkboxes as $key => $id) {
    if (in_array($id, $_POST["check"]) {
      Query(.... set active = '1' ...);
    } else {
      Query(.... set active = '0' ...);
    }
  }
}

 

I hope you understand the method

[brandon_elfring]

This makes perfect sense!  Thank you Dennis.  I ended up finding a way to do it, but I think this way may be the better way.  I will implement this code and let you know how it goes.  Again, thank you for all of your help!