Having an issue with UPDATING SQL Database via PHP Table

mbb87 · August 7, 2012

Trying to Update the MySQL Tables but nothing is being Updated, I'm sure I'm just not seeing the tiny issue, would love some help. Thanks

So its a trade block for a hockey pool, if you want the player on the trade block then you just check the CHECKBOX in the form and submit and it should change the value in the database to value of "1".

FORM:

echo "<table border='1'>";

echo "<tr><th>NAME</th> <th>POS</th> <th>BLOCK</th></tr>";

$counter = 1;

while($row = mysql_fetch_array( $result )) {

echo "<tr><td>"; 

echo "{$row['f_name']}" . " " . "{$row['l_name']}";

echo "</td><td><input name='pl_id[$counter]' type='hidden' value='{$row['pl_id']}'>";

echo "{$row['pos']}";

echo "</td><td><input name='pos[$counter]' type='hidden' value='{$row['pos']}'>";                                   


    echo "<input type='checkbox' name='block[$counter]' size='1' value='1'";

    if($row['block'] == '1')
    {
        echo "checked='checked'";
    }

    echo "></td></tr>";                 

$counter++;

} 
echo "</table>";

SUBMIT PHP PAGE:

mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("mbbcom1_fantrax") or die(mysql_error());

$i = 1;
while ($i < 26) {

$block = $_POST['block'][$i];
$pl_id = $_POST['pl_id'][$i];

$query = mysql_query("UPDATE 'players'
                     SET  `block` = '$block' 
                     WHERE `players`.`pl_id` = '$pl_id'");                  
mysql_query($query); 

$i++; }

echo mysql_close();

Thank you in advance for any help you can provide me.

Drummin · August 7, 2012

You are executing your query twice.

$query = "UPDATE 'players'
                     SET  `block` = '$block' 
                     WHERE `pl_id` = '$pl_id'";                  
mysql_query($query) OR DIE .mysql_error();

mbb87 · August 8, 2012

Nope still doesn't work.

No error messages just like before, just doesn't update anything as of now.

jazzman1 · August 8, 2012

Why do you want to loop the query 26 times :confused:

mbb87 · August 8, 2012

Theirs 26 players on each team.

jazzman1 · August 8, 2012

Okay, but why did you want to update their columns on the same time :confused:

mbb87 · August 8, 2012

Here is the page if that help you understand what I want to do.

http://www.mbb87.com/fantrax/myblock.php?user=92&inblock=1

Psycho · August 8, 2012

NEVER EVER run queries in loops. They are a huge resource hog and will kill your server. There is ALWAYS a better solution. In this case you need to run only two queries: one to update the record that are unchecked and one to update those that are checked.

First off, I would change the form code by removing the $counter and just using the ID of the record as the field index in the input field name. You aren't using the index of the field name for anything anyway. Or you could just remove the named index entirely.

echo "<table border='1'>";
echo "<tr><th>NAME</th><th>POS</th><th>BLOCK</th></tr>";

while($row = mysql_fetch_array( $result ))
{
    $checked = ($row['block'] == '1') ? ' checked="checked"' : '';
    echo "<tr>\n";
    echo "    <td>{$row['f_name']} {$row['l_name']}</td>\n";
    echo "    <td><input name='pl_id[$row['pl_id']]' type='hidden' value='{$row['pl_id']}'>{$row['pos']}</td>\n";
    echo "    <td>\n";
    echo "         <input name='pos[$counter]' type='hidden' value='{$row['pos']}'>"; 
    echo "         <input type='checkbox' name='block[$counter]' size='1' value='1'{$checked}>\n";
    echo "    </td>\n";
    echo "</tr>";                 
} 
echo "</table>";

Then on your processing page you just need to do ONE query like this

//Verify the values are valid integers and put in comma separated string
$checked_players_list = implode(', ', array_map('int', $_POST['pl_id']));

//Run update to set blocked status based on whether the player ID was in the submitted list
$query = "UPDATE 'players' SET  `block` = `players`.`pl_id` IN ({$checked_players_list})";
$result = mysql_query($query);

EDIT: Corrected one mistake in code above (imploded IDs) and revised to only need ONE query to update all the records!

Psycho · August 8, 2012

OK, I just realized that you were using a hidden field for the ID. That's completely unnecessary and would not work with the code I provided. You only need to use the checkbox field in your form. The hidden fields are not needed. Use this for your form along with the processing logic I provided above.

echo "<table border='1'>";
echo "<tr><th>NAME</th><th>POS</th><th>BLOCK</th></tr>";

while($row = mysql_fetch_array( $result ))
{
    $checked = ($row['block'] == '1') ? ' checked="checked"' : '';
    echo "<tr>\n";
    echo "    <td>{$row['f_name']} {$row['l_name']}</td>\n";
    echo "    <td>{$row['pos']}</td>\n";
    echo "    <td><input type='checkbox' name='pl_id[{$row['pl_id']}]' value='{$row['pl_id']}'{$checked}></td>\n";
    echo "</tr>";                 
} 
echo "</table>";

Psycho · August 8, 2012

-- DELETED --

mbb87 · August 8, 2012

Where are you getting "$checked_players_list"?

EDIT: Never mind, I see it now but still nothing gets updated.

Didnt think it was that confusion, but no one seems to be able to figure it out. I really hope to hearing from you some more. If you have any questions please fire them away. I REALLY WANT TO FIX/GET IT TO WORK

THANKS AGAIN FOR YOUR TIME.

Psycho · August 8, 2012

Where are you getting "$checked_players_list"?

EDIT: Never mind, I see it now but still nothing gets updated.

Didnt think it was that confusion, but no one seems to be able to figure it out. I really hope to hearing from you some more. If you have any questions please fire them away. I REALLY WANT TO FIX/GET IT TO WORK

THANKS AGAIN FOR YOUR TIME.

Put some error/debugging handling into your code to see where the problem is. Change the processing code to this:

//Verify the values are valid integers and put in comma separated string
$checked_players_list = implode(', ', array_map('int', $_POST['pl_id']));

//Run update to set blocked status based on whether the player ID was in the submitted list
$query = "UPDATE 'players' SET  `block` = `players`.`pl_id` IN ({$checked_players_list})";
$result = mysql_query($query);

echo "Debugging info:<br>\n";
echo "POST Data:<pre>" . print_r($_POST, 1) . "</pre>\n";
echo "Checked Players list: {$checked_players_list}<br>\n";
echo "Update Query: {$query}<br>\n";
if($result) {
    echo "Query executed with " . mysql_affected_rows($result) . " affected rows";
} else {
    echo "Query Failed. Error " . mysql_error();
}

What is output to the page?

mbb87 · August 8, 2012

Debugging info:

POST Data:

Array

(

[pl_id] => Array

(

[36] => 36

[31] => 31

[75] => 75

[17] => 17

[187] => 187

[121] => 121

[89] => 89

[42] => 42

[30] => 30

[116] => 116

)

[submit] => submit

)

Checked Players list:

Update Query: UPDATE 'players' SET `block` = `players`.`pl_id` IN ()

Query Failed. Error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''players' SET `block` = `players`.`pl_id` IN ()' at line 1

DavidAM · August 8, 2012

Checked Players list:

Update Query: UPDATE 'players' SET `block` = `players`.`pl_id` IN ()

Query Failed. Error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''players' SET `block` = `players`.`pl_id` IN ()' at line 1

Did you read and understand that output?

1) The checked players list is empty. I don't think that "int" is a function. I think that line needs to be $checked_players_list = implode(', ', array_map('intval', $_POST['pl_id']));

2) Your query is missing a couple of things. It has SET `block` = `players`.`pl_id` IN () but in your earlier posts, it was SET `block` = $block. Then it is missing the "WHERE" -- it should (probably) be $query = "UPDATE 'players' SET `block` = $block WHERE `players`.`pl_id` IN ({$checked_players_list})";

Programming requires some analytical thought. You shouldn't just cut-and-paste suggestions from people without trying to understand what you are pasting. Psycho really gave you the answer, there; it just had a couple of minor issues. If you read his signature you see the disclaimer that any code he provides may not be perfect. The code I provided above may not be perfect. This is not because we are bad programmers. We just don't have the time, and in some cases, the capability, to build a complete environment to fully test your problem. Often we do not have enough information from you to fully build/test the code. We expect you to make an effort to understand and integrate our suggestions.

mikosiko · August 8, 2012

in addition to all the good pointers already given I'm still seeing the table enclosed in single quotes instead of backtics (or nothing)

$query = "UPDATE 'players' SET  `block` = $block WHERE `players`.`pl_id` IN ({$checked_players_list})";

mbb87 · August 8, 2012

echo "<table border='1'>";
echo "<tr><th>NAME</th><th>POS</th><th>BLOCK</th></tr>";

while($row = mysql_fetch_array( $result ))
{
    $checked = ($row['block'] == '1') ? ' checked="checked"' : '';
    echo "<tr>\n";
    echo "    <td>{$row['f_name']} {$row['l_name']}</td>\n";
    echo "    <td>{$row['pos']}</td>\n";
    echo "    <td><input type='checkbox' name='pl_id[{$row['pl_id']}]' value='{$row['pl_id']}'{$checked}></td>\n";
    echo "</tr>";                 
} 
echo "</table>";

RUNNING IT:

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("mbbcom1_fantrax") or die(mysql_error());

//Verify the values are valid integers and put in comma separated string
$checked_players_list = implode(', ', array_map('int', $_POST['pl_id']));

//Run update to set blocked status based on whether the player ID was in the submitted list
$query = "UPDATE 'players' SET `block` = `players`.`pl_id` IN ({$checked_players_list})";
$result = mysql_query($query);

echo "Debugging info:<br>\n";
echo "POST Data:<pre>" . print_r($_POST, 1) . "</pre>\n";
echo "Checked Players list: {$checked_players_list}<br>\n";
echo "Update Query: {$query}<br>\n";
if($result) {
    echo "Query executed with " . mysql_affected_rows($result) . " affected rows";
} else {
    echo "Query Failed. Error " . mysql_error();
}

where are you guys getting $block ?

Im trying to understand what you guys are doing, but not sure how it will update all the players without a loop. you lost me there.

(I keep searching online for the solution, not trying to be lazy here and just cut and paste)

I REALLY APPRECIATE ALL THE HELP and just trying to understand and get this to work.

mbb87 · August 8, 2012

Shouldn't it be something like this?

//Run update to set blocked status based on whether the player ID was in the submitted list
$query = "UPDATE 'players' SET `block` = '1' WHERE `players`.`pl_id` IN ({$checked_players_list})";
$result = mysql_query($query);

Also in the error codes is says '$checked_players_list' is empty no?

Drummin · August 8, 2012

You are not reading the replies.

1. Fix the back-ticks issue.

$query = "UPDATE `players` SET `block` = `players`.`pl_id` IN ({$checked_players_list})";

2. Change callback function to interval.

$checked_players_list = implode(', ', array_map('intval', $_POST['pl_id']));

mbb87 · August 8, 2012

WOW!!! YOU GUYS ARE AMAZING!!!

ITs working.

I would love to know why though. But i bugged you guys enough, ill try to google it.

Thanks again everyone.

jazzman1 · August 8, 2012

@ mbb87.... CONGRATULATIONS......THOSE GUYS ARE REALLY AMAZING!!!

It's a my solution, I found it few months ago and works perfect ( I can not remember the url of the site).

You can use CASE operator.

// An array containing the category ids as keys and the new positions as values
$display_order = array(
    1 => 4,
    2 => 1,
    3 => 2,
    4 => 3,
    5 => 9,
    6 => 5,
    7 => 8,
    8 => 9
);

$ids = implode(',', array_keys($display_order));
$sql = "UPDATE `tbl_name` SET `id` = CASE id ";
foreach ($display_order as $id => $ordinal) {
    $sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal);
}
$sql .= "END WHERE `id` IN ($ids)";

$result = mysql_query($sql) or die(mysql_error());

mbb87 · August 8, 2012

Alright so it changes all players that are not in the get query as well,

At this point what it does is:

Even though the players are not on that users team it changes them back to 0 and removes other users trade block players.

Ill try to figure it out. maybe add and other WHERE criteria.

mbb87 · August 8, 2012

So here is the final product for everyone that helped me and if anyone wants a suggestions.

Thanks Again!!

										// Get all the data from the "example" table										
									$result = mysql_query("SELECT * FROM `players` WHERE `own` =$user ORDER BY `players`.`l_name` ASC LIMIT 0 , 30") 										
									or die(mysql_error());  
									?>

									<form name="myblock" action="_myblock.php?user=<?php echo $user; ?>" method="POST">										

									<?php

										echo "<table border='1'>";
										echo "<tr><th>NAME</th><th>POS</th><th>BLOCK</th></tr>";

										while($row = mysql_fetch_array( $result ))
										{
											$checked = ($row['block'] == '1') ? ' checked="checked"' : '';
											echo "<tr>\n";
											echo "    <td>{$row['f_name']} {$row['l_name']}</td>\n";
											echo "    <td>{$row['pos']}</td>\n";
											echo "    <td><input type='checkbox' name='pl_id[{$row['pl_id']}]' value='{$row['pl_id']}'{$checked}></td>\n";
											echo "</tr>";                 
										} 
										echo "</table>";

mysql_select_db("mbbcom1_fantrax") or die(mysql_error());


//Verify the values are valid integers and put in comma separated string
$checked_players_list = implode(', ', array_map('intval', $_POST['pl_id']));

//Run update to set blocked status based on whether the player ID was in the submitted list
$query = "UPDATE `players` SET `block` = `players`.`pl_id` IN ({$checked_players_list}) WHERE `own` = $user";
$result = mysql_query($query);

Psycho · August 8, 2012

I made some minor errors in the code I provided (I didn't test it), but the logic is sound. Some of the replies since are getting this conversation on the wrong track, so let me elaborate:

1) The checked players list is empty. I don't think that "int" is a function. I think that line needs to be $checked_players_list = implode(', ', array_map('intval', $_POST['pl_id']));

David was 100% correct here - I meant to use 'intval'. Using that will force all the values to be integers to prevent SQL Injection.

[

Shouldn't it be something like this?

//Run update to set blocked status based on whether the player ID was in the submitted list
$query = "UPDATE 'players' SET `block` = '1' WHERE `players`.`pl_id` IN ({$checked_players_list})";
$result = mysql_query($query);

As one other contributor noted the table name should be in backticks (or none at all). However, the query is fine. The query I provided doesn't need a WHERE clause and using one would actually make it fail to produce the correct results. I actually tested this logic on a table so I know it works. Let me explain how it works because it is not completely obvious how it works but is actually pretty ingenious in my humble opinion.

Here is the query

UPDATE `players`
SET `block` = `players`.`pl_id` IN (1, 5, 9, 10, 11, 12)

The list of IDs will be dynamic based upon those that are checked on the form page. So the query is going to attempt to update all records and set the value of 'block' to `players`.`pl_id` IN (1, 5, 9, 10, 11, 12)

What is happening there is that for each record a condition is done to see if that records id is in the list of supplied ids. The result will be true or false. In MySQL a true/false response is a 1 or 0. So, the result is that the block value is set to '1' for all records in the supplied list and '0' for the rest. I originally supplied two queries to do the 0 and 1 separately and then thought of the above approach. After I tested it I modified my post where I provided that code.

So, you should only need to make the changes to the array_map() function to use 'intval' and then use backticks around the table name:

//Verify the values are valid integers and put in comma separated string
$checked_players_list = implode(', ', array_map('intval', $_POST['pl_id']));

//Run update to set blocked status based on whether the player ID was in the submitted list
$query = "UPDATE `players` SET  `block` = `players`.`pl_id` IN ({$checked_players_list})";
$result = mysql_query($query);

echo "Debugging info:<br>\n";
echo "POST Data:<pre>" . print_r($_POST, 1) . "</pre>\n";
echo "Checked Players list: {$checked_players_list}<br>\n";
echo "Update Query: {$query}<br>\n";
if($result) {
    echo "Query executed with " . mysql_affected_rows($result) . " affected rows";
} else {
    echo "Query Failed. Error " . mysql_error();
}

mbb87 · August 8, 2012

Adding the WHERE actually worked fine.

Without it, all the rows got updated to 0 if not checked even the ones that i original didn't call (GET)

Example:

Mikes Team:

Karlsson

Chara (Checked)

Spezza

(NOT SHOWING UP ON THE QUERY (GET))

Paul's Team:

Ovechkin (CHECKED)

Brodeur

Well running it without the WHERE would make Chara on the block but would removed all other players that are on other teams from the block by changing the block value to 0

But i thank you so much since without you i would still be scratching my head.

Christian F. · August 8, 2012

What is happening there is that for each record a condition is done to see if that records id is in the list of supplied ids. The result will be true or false. In MySQL a true/false response is a 1 or 0. So, the result is that the block value is set to '1' for all records in the supplied list and '0' for the rest.

That's quite a neat and interesting trick, didn't realize it would work this way before. Thanks for explaining it!

Sign In

Having an issue with UPDATING SQL Database via PHP Table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information