Jump to content

Changing MySQL Items With Checkboxes


ericthomson

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

//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.

 

 

Link to comment
Share on other sites

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';
?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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"?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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"?

Link to comment
Share on other sites

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());
}

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.