Changing MySQL Items With Checkboxes

[ericthomson]

I currently have a page where I have each row in my table listed using:

 

$getinfo="SELECT * FROM tablename WHERE Closed='n'";
$result=mysql_query($getinfo);
while ($row=mysql_fetch_array($result)){

... // list of user information

}

 

That successfully displays my content. What I am trying to do is have a checkbox next to each user (row) that when I check them and hit submit at the bottom, it will change the value of each row on the 'Closed' column to 'y' so it will not be displayed when the page is refreshed. I want to be able to select multiple items to "close". And I would like it to come back to this page. I think I can use this page as the form action and have the code above my while loop to change the Closed column so it then would not be displayed when the while loop is called... is that right?

 

How do I set this up? Not sure where to start the form tag and where to end it, and not sure how to...well...do any of it.

[ManiacDan]

Basic steps:

 

- Wrap the entire table in a form tag

- Name all your checkboxes name="close[123]" where 123 is the ID of the row it's on.

- At the top of this page, after you connect to the database but before you select the rows to build the table:

  > Check to see if $_POST['close'] is set.

    > $query = "UPDATE thisTable SET closed = 'Y' WHERE theTableID IN ( " . mysql_real_escape_string(implode(',', $_POST['close'])) . ")";

    > (handle errors)

- Fetch the rows from the table and display as normal.

[ericthomson]

Thanks a lot, I'll try that. So would I use "close[$row]" for my example? Or would I set a new variable to the index column value of that current $row selection?

[ManiacDan]

$row is an entire row (meaning array), you'd need {$row['id']} or something similar, but I don't know what your query/table looks like.

[ericthomson]

Ok, here is the code I have now, thanks to you:

 

//db connect...
$closequery = "UPDATE sellerinfo SET Closed = 'y' WHERE Index IN (".mysql_real_escape_string(implode(',',$_POST['close'])).")";
$i = 0;
$getinfo="SELECT * FROM sellerinfo WHERE Closed='n'";
$result=mysql_query($getinfo);
echo "<form method='post' action='/seller-list-test.php'>\n";
echo "<table>\n";
while ($row=mysql_fetch_array($result)) {
     $itemindex = $row['Index'];
     echo "<tr>\n";
     if ($i > 0) {
          echo "<td width='25%' valign='middle' style='border-top:thin #000 solid;'>";
     }
     else {
          echo "<td width='25%' valign='middle'>";
     }
     $i++;
     echo "<input type='checkbox' name='close[$itemindex]' value='checked' /> Clear Entry?";
//...

 

The error I'm getting is:

 

Warning: implode() [function.implode]: Invalid arguments passed in /home/byebyew1/public_html/ibuytexashomes/seller-list-test.php on line 109

[spec36]

What is in line # 109 of seller-list-test.php?

[ManiacDan]

Remember back when I wrote my first post and I put "check if $_POST['close'] is set"?  You actually need to do that.

[ericthomson]

Line 109 (was) closequery. Now I put that within the if(isset and the error is gone, but checking the box and clicking submit does nothing. Anything else look wrong? Or do you need the rest of the code?

[ManiacDan]

Queries must be run to have any effect.

[kicken]

//db connect...
$closequery = "UPDATE sellerinfo SET Closed = 'y' WHERE Index IN (".mysql_real_escape_string(implode(',',$_POST['close'])).")";

 

That query should be more like this:

$idList = array_keys($_POST['close']); //Since your ID's are the keys in the array, not the values.
$idList = array_map('intval', $idList); //Ensure all values are integers and not strings (protects from sql injection)
$closequery = 'UPDATE sellerinfo SET Closed='y' WHERE Index IN ('.implode(',', $idList).')'; //Do not use mysql_real_escape_string in this instance.

 

Since your row id is being used as an array key (name="close[xxx]"), you need to extract the keys of the close array.  Using intval on all the keys will ensure they are integers and protect from any sql injection attempts.  invalid values will be converted to 0, assuming you have no rows where index=0, they will essentially be ignored since they wont match any rows. 

 

Since we use intval to protect from sql injectection, mysql_real_escape_string is not needed.  In any case, it's usage there is incorrect as you'd want to apply it to each value in the array, not the whole imploded string.

 

 

[ericthomson]

Ok. That makes sense. I guess the mysql_real_escape_string is one of those intentional mistakes ManiacDan warns about in his disclaimer.

 

It is still not working right so I am guessing there is something in how I am setting up the form and/or table that is wrong. Here is the full code.

 

Thanks for your non-sarcastic help.

 

<?PHP
include 'sellersconfig.php';
include 'opendb.php';
$idList = array_keys($_POST['close']);
$idList = array_map('intval', $idList);
$closequery = "UPDATE sellerinfo SET Close='y' WHERE Index IN ('.implode(',', $idList).')";
$i = 0;
$o = 0;
$getinfo="SELECT * FROM sellerinfo WHERE Closed='n'";
$result=mysql_query($getinfo);
echo "<form method='post' action='/seller-list-test.php'>\n";
echo "<table>\n";
while ($row=mysql_fetch_array($result)) {
$itemindex = $row['Index'];
echo "<tr>\n";
if ($i > 0) {
	echo "<td width='25%' valign='middle' style='border-top:thin #000 solid; padding-top:10px;'>";
}
else {
	echo "<td width='25%' valign='middle'>";
}
$i++;
echo "<input type='checkbox' name='close[$itemindex]' value='checked' /> Clear Entry?";
echo "</td>";
if ($o > 0) {
	echo "<td width='75%' valign='top' style='border-top:thin #000 solid; padding-bottom:10px; padding-top:10px;'>";
}
else {
	echo "<td width='75%' valign='top' style='padding-bottom:10px;'>";
}
$o++;
echo "<i>".$row['Date']."</i><br />";
echo $row['LastName'].", ". $row['FirstName']." - ".$row['PhoneNumber']." - <a href='mailto:".$row['EmailAddress']."'>".$row['EmailAddress']."</a><br />".$row['StreetNumber']." ".$row['StreetName'].", ".$row['City'].", TX ".$row['ZipCode']."<br />";
echo "<strong>Lender: </strong> ";
if ($row['Lender'] != "" || $row['Lender'] != NULL) {
	echo $row['Lender'];
}
else {
	echo "Unspecified";
}
echo "<br />";
echo "<strong>Monthly Payment:</strong> ";
if ($row['Payment'] != "" || $row['Payment'] != NULL) {
	echo $row['Payment'];
}
else {
	echo "Unspecified";
}
echo "<br />";
echo "<strong>Payment Status:</strong> ";
if ($row['PayStatus'] != "" || $row['PayStatus'] != NULL) {
	echo $row['PayStatus'];
}
else {
	echo "Unspecified";
}
echo "<br />";
echo "<strong>Second Mortgage:</strong> ";
if ($row['Second'] != "" || $row['Second'] != NULL) {
	echo $row['Second'];
}
else {
	echo "Unspecified";
}
echo "<br /><br />";
echo "</td>\n</tr>";
}
echo "<tr>\n<td>\n<input name='clearitems' type='submit' value='Clear Items' />\n</td>\n</tr>\n";
echo "</table>";
echo "</form>";
include 'closedb.php';
?>

[ManiacDan]

Ok. That makes sense. I guess the mysql_real_escape_string is one of those intentional mistakes ManiacDan warns about in his disclaimer.

Actually I was just wrong.  Kicken noticed that I gave you the solution for array values, when I had given you the form input for the keys.  That was unintentional.

 

 

Thanks for your non-sarcastic help.

Forgetting to run a query isn't something we can gently point out, so we default back to sarcasm.  Speaking of which, you're still not running $closequery, and it's still not inside the isset() check.  Kicken's code was a snippet, designed to be INSIDE of the isset() check that I told you to write, and FOLLOWED by something that actually makes use of the variable.

 

Execute the query.  If that doesn't work, echo the query to see if it's malformed.  If it is, figure out which variable is causing the problem.  Echo that variable, print_r($_POST), do something to debug this on your own.  You were given the basic structure because (a) I didn't want to actually write the 25 lines of example code for you, and (b) I assumed all you needed was the basic structure, and any error checking, debugging, and running of queries could be done on your own.

[ericthomson]

Listen, we get it. On the internet you actually have some sort of power and we're all relatively happy for you. But sarcasm doesn't have to be the default. If you don't want to be helpful or constructive there are others that can help me. I'm not a programmer and I'm asking for help with my code, not for vague references to programming principles. I have Google for that, I don't need a forum.

 

I've wrapped those 3 lines inside of if(isset($_POST['close'])) and added mysql_query($closequery) inside the if statement. Print_r gives me "Array ( [4] => checked ) 1" (which is the one I had checked).

[ManiacDan]

Learn to think for yourself and the world will be a lot easier for you.  I didn't tell you to execute the query just like nobody tells you to lift the toilet seat before you pee.  If I was on a power trip I'd ban you or something, but I don't care.  I wanted to help, and in fact gave you the framework you needed to get started.  You didn't fully implement the framework that I gave you and you didn't take it to its logical conclusion: executing the query.  Any version of "you didn't actually execute the query" would have sounded sarcastic. 

 

Also, identifying yourself as "not a programmer" here on this professional programmer forum might change the quality of the answers you get.  Like I said earlier, I assumed you had a certain level of skill when I gave you the outline of the changes to your application.  All I know is your name.  I automatically assign you an average level of skill unless told otherwise.

 

As for your actual problem: If you've made the changes kicken suggested to your query, and you've added the actual query() function, it should be working.

 

If it's still not working:

1)  Echo the query itself, copy and paste it here and we'll look at it.

2)  echo mysql_error() after the query is run and you can see the actual MySQL error, which you can use to debug on your own if you know enough SQL.  Otherwise, we'll look.

 

When you say print_r gave you that output, you mean print_r($_POST['close']), right?

[ericthomson]

print_r($_POST['close']) gives me: Array ( [2] => checked [4] => checked ), or whatever numbers I click, so I'm guessing that is working right and the if isset should run...

 

echo $closequery gives me: UPDATE sellerinfo SET Close='y' WHERE Index IN ('.implode(',', Array).')

 

is idList supposed to be returning "Array"?

[Pikachu2000]

Well, there's the problem. You have the entire implode() function within the query string. It needs to be concatenated into the string, or you can store the implode() output in a variable prior to forming the query string, then just use the variable.

[ericthomson]

Like this:

 

if (isset($_POST['close'])){
$idList = array_keys($_POST['close']);
$idList = array_map('intval', $idList);
$implValue = implode(',', $idList);
$closequery = "UPDATE sellerinfo SET Close='y' WHERE Index IN ($implValue)";
mysql_query($closequery);
}

 

I am assuming i did it wrong because it's not working.

[Pikachu2000]

Looks right from here. Does it contain the right values if you echo the query?

[ManiacDan]

That looks right.  The code kicken gave you follows the same pattern, though you may have dropped a quote or something somewhere.

[bspace]

check the field names

 

earlier you gave some code with a field "Closed" is this the field you want to update?

 

[ericthomson]

Ok, good eye. It should be Closed, but that didn't fix it either.

 

I don't get how implode works exactly, but should there be another parameter with the WHERE command? Where Index is what? Or does implode handle that? It returns [X] => checked so is that saying "Where Index [X] is checked"?

[ManiacDan]

Time to check the mysql errors.

[ericthomson]

Echoing my imploderesult I get a value of 4 (or 2,4 etc). So do I need to use something other than IN? Like Index[$imploderesult] or Index=$imploderesult?

 

How do I check the mysql errors?

[mikosiko]

some comment added

 

if (isset($_POST['close'])){
$idList = array_keys($_POST['close']);
$idList = array_map('intval', $idList);
$implValue = implode(',', $idList);
$closequery = "UPDATE sellerinfo SET Closed='y' WHERE Index IN ($implValue)";

       // Here you echo your raw query to validate if it is well formed and syntactically correct
        echo "The query is : " . $closequery . "<br/>".

       // Here you add a basic error control to trap any error during the query execution 
      // die should be replaced for something better while code is in production.. like trigger_error per example
mysql_query($closequery) or die("Query Error : " . mysql_error());
}

[ManiacDan]

Seriously, mysql_error().  Print it, or use the code mikosiko provided to get it.  something is wrong with your syntax, field names, or table names.

 

NOTHING is wrong with the IN clause you were given.  The correct syntax for that is:

WHERE fieldName IN (2,3,4);

 

That is correct.  That is what you supposedly have.  The problem is that MySQL is complaining about other problems somewhere else, like the fact that you keep changing your capitalization or you've misspelled the tablename or something.  Show us the error and we'll have a better shot at figuring out which mistake it is.