Updating multiple records for MySQL

[Balakir]

*typo in heading :( *

Hi All,

I have a database that has 4 fields i use. I want to be able to update the current data for all entries using one form.

The form i have made shows all the entries and allows me to enter new data for each of the fields. The issue I am having just now is that when I submit it my update script seems to set everything to 0 instead of updating it.

Could someone have a look at my code below and provide some help? I am new to php/mysql.

Bala.


Form Code

<form action="update5.php" method="post">
<table id="form">
<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hustlersls_co_u", $con);

$result = mysql_query("SELECT * FROM Sky_Points");

echo "<table border=1>";

while($row = mysql_fetch_array($result))
{
?>
<tr>
<td align="center"><input type="text" name="Username[]" value="<?php echo $row['Username'] ?>" ></td>
<td align="center"><input type="text" name="Points[]" value="<?php echo $row['Points'] ?>" ></td>
<td align="center"><input type="text" name="PointsSinceLastPayOut[]" value="<?php echo $row['PointsSinceLastPayOut'] ?>" ></td>
<td align="center"><input type="text" name="DateofLastRun[]" value="<?php echo $row['DateofLastRun'] ?>" ></td>
</tr>
<?php
}
?>
<?php
mysql_close($con);
?>
<tr>
<td align="center" colspan="4"><br/><input name="submit" type="submit" value="Submit"> <input name="reset" type="reset" value="Reset"></td>
</tr>
</table>
</form>

Update script code

<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hustlersls_co_u", $con);

$Points = $_POST['Points'];
$PointsSinceLastPayOut = $_POST['PointsSinceLastPayOut'];
$DateofLastRun = $_POST['DateofLastRun'];

foreach ($_POST['Username'] as $username)
{
$query = "UPDATE Sky_Points SET Points = '$Points', PointsSinceLastPayOut = '$PointsSinceLastPayOut', DateofLastRun ='$DateofLastRun' WHERE Username = '$username'" ;
mysql_query($query, $con) or die(mysql_error());
}

if (!mysql_query($query,$con)){
die('Error: '. mysql_error());
}

echo "Points have been updated<br />";
echo "<a href='update_multiple5.php'>Return to update table</a>";

mysql_close($con);
?>

[edited by: Balakir at 12:09 pm (utc) on Apr 1, 2010]

[Readie]

Welcome to Webmaster World Balakir

I would suggest doing something like this:

---

$sql = 'SELECT username FROM Sky_Points ORDER BY username ASC';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$out = '<u>Log:</u>';

for($i = 0; $i < $rows; $i++) {
$user = mysql_result($result, $i, "username");
$sql = 'UPDATE Sky_Points SET username = "' . $user . '"';

$Points = $user . '_Points';
$Points = $_POST[$Points];
$PointsSinceLastPayOut = $user . '_PointsSinceLastPayOut';
$PointsSinceLastPayOut = $_POST[$PointsSinceLastPayOut];
$DateofLastRun = $user . '_DateofLastRun';
$DateofLastRun = $_POST[$DateofLastRun];

if(isset($Points) && $points !== "") {
$sql .= ', Points = "' . mysql_real_escape_string($points) . '"';
}
if(isset($PointsSinceLastPayOut) && $PointsSinceLastPayOut !== "") {
$sql .= ', PointsSinceLastPayOut = "' . mysql_real_escape_string($PointsSinceLastPayOut) . '"';
}
if(isset($DateofLastRun) && $DateofLastRun !== "") {
$sql .= ', DateofLastRun = "' . mysql_real_escape_string($DateofLastRun) . '"';
}

$sql .= ' WHERE username = "' . $user . '"';
mysql_query($sql);
if(!mysql_error()) {
$out .= '<br />Updated ' . $username;
} else {
$out .= '<br /><span style="color: #ff0000;">Failed to update ' . $username . '</span>';
}
}
echo $out;

---

Typed this on the fly, untested etc, might have a bug or two.

And build your form with the inputs like:

<input type="text" name="' . $username . '_Points" />

[Balakir]

Hi Readie,

Thanks for that this is what I have tried (think ive done it the way you meant)

This has fixed the updating everything to 0 but it does not update the database. The new code is below any ideas?

Form Code

<form action="update6.php" method="post">
<table id="form">
<?php
$con = mysql_connect("localhost","User","Pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hustlersls_co_u", $con);

$result = mysql_query("SELECT * FROM Sky_Points");

echo "<table border=1>";

while($row = mysql_fetch_array($result))
{
?>
<tr>
<td align="center"><input type="text" name="' . $Username . '_Points" value="<?php echo $row['Username'] ?>" /></td>
<td align="center"><input type="text" name="' . $Points . '_Points" value="<?php echo $row['Points'] ?>" /></td>
<td align="center"><input type="text" name="' . $PointsSinceLastPayOut . '_Points" value="<?php echo $row['PointsSinceLastPayOut'] ?>" /></td>
<td align="center"><input type="text" name="' . $DateofLastRun . '_Points" value="<?php echo $row['DateofLastRun'] ?>" /></td>
</tr>
<?php
}
?>
<?php
mysql_close($con);
?>
<tr>
<td align="center" colspan="4"><br/><input name="submit" type="submit" value="Submit"> <input name="reset" type="reset" value="Reset"></td>
</tr>
</table>
</form>

Update Script Code

<?php
$con = mysql_connect("localhost","User","Pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hustlersls_co_u", $con);

$sql = 'SELECT Username FROM Sky_Points';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$out = '<u>Log:</u>';

for($i = 0; $i < $rows; $i++) {
$user = mysql_result($result, $i, "Username");
$sql = 'UPDATE Sky_Points SET Username = "' . $user . '"';

$Points = $user . '_Points';
$Points = $_POST[$Points];
$PointsSinceLastPayOut = $user . '_PointsSinceLastPayOut';
$PointsSinceLastPayOut = $_POST[$PointsSinceLastPayOut];
$DateofLastRun = $user . '_DateofLastRun';
$DateofLastRun = $_POST[$DateofLastRun];

if(isset($Points) && $points !== "") {
$sql .= ', Points = "' . mysql_real_escape_string($points) . '"';
}
if(isset($PointsSinceLastPayOut) && $PointsSinceLastPayOut !== "") {
$sql .= ', PointsSinceLastPayOut = "' . mysql_real_escape_string($PointsSinceLastPayOut) . '"';
}
if(isset($DateofLastRun) && $DateofLastRun !== "") {
$sql .= ', DateofLastRun = "' . mysql_real_escape_string($DateofLastRun) . '"';
}

$sql .= ' WHERE username = "' . $user . '"';
mysql_query($sql);
if(!mysql_error()) {
$out .= '<br />Updated ' . $username;
} else {
$out .= '<br />Failed to update ' . $username;
}
}
echo $out;
?>

[Matthew1980]

Hi there BalaKir,

Just a quick pointer for you:-

$query = "UPDATE Sky_Points SET Points = '$Points', PointsSinceLastPayOut = '$PointsSinceLastPayOut', DateofLastRun ='$DateofLastRun' WHERE Username = '$username'" ;

Change this to:-

$query = "UPDATE `Sky_Points` SET `Points` = '".$Points."', `PointsSinceLastPayOut` = '".$PointsSinceLastPayOut."', `DateofLastRun` = '".$DateofLastRun."' WHERE `Username` = '".$username."' LIMIT 1" ;

All I have done is join in the vars, as you are building the string using double quote's. And on the end I have specified LIMIT 1 because I think as this is just 1 UPDATE per username, and as you are in a loop, I think this is better done like this. Though that's just my preference, it's not set in stone anywhere ;-p

Also the backticks aren't mandatory, but they are there to distinguish if you have (correct me if I'm wrong) spaces in the column title, I just find that If you get into the habit of using them you are covered for most eventualities ;-p

For the form not passing values, have you tried (I seem to be typing this an awful lot just lately..) using print_r($_POST); on the receiving script to see what IS ACTUALLY being sent from the form? Only reason I ask this is because, on the form you have your inputs like this:-

<input type="text" name="Username[]" value="<?php echo $row['Username'] ?>" ></td>

And I'm not sure why you are using the array operand for this, use it for a radio or check box when you have multiple instance of them and you want to numerically increment them so that you can iterate though them more easily, but (unless I am wrong) for text inputs, the array is there and value value attribute contains the data being sent.

so and your missing the semi-colon from the end of the array var:-

<td align="center"><input type="text" name="Username" value="<?php echo $row['Username']; ?>" ></td>
<td align="center"><input type="text" name="Points" value="<?php echo $row['Points']; ?>" ></td>
<td align="center"><input type="text" name="PointsSinceLastPayOut" value="<?php echo $row['PointsSinceLastPayOut']; ?>" ></td>
<td align="center"><input type="text" name="DateofLastRun" value="<?php echo $row['DateofLastRun']; ?>" ></td>

There you are anyway, the values will now hold a value ;-p Still do the print_r($_POST); to see exactly what's being sent, then you can see any spelling errors etc.

One last thing, $_POST/$_GET sanitising:-

$Points = strip_tags(mysql_real_escape_string($_POST['Points']));
$PointsSinceLastPayOut = strip_tags(mysql_real_escape_string($_POST['PointsSinceLastPayOut']));
$DateofLastRun = strip_tags(mysql_real_escape_string($_POST['DateofLastRun']));
$username = strip_tags(mysql_real_escape_string($_POST['Username']));

For the latter one just alter the foreach loop to compensate:-

foreach($username as $data)//choose something yourself, that's a suggestion :)

And as there is a submit button there on the receiving script, place an if clause to catch the post correctly:-

if(isset($_POST['submit']) && ($_POST['submit'] == "submit"))
{
//process the form data
exit;//just kill the script there, stop's anything downstream being executed ;-p
}
else{
echo "Something went wrong, check spelling etc";
exit;
}

Always worth doing, protect your site & your database, there have been a few posts lately about forum/site attacks code injections, and those two functions are among the most frequently used, though there are more to choose from ;-p

Anyway, have fun with your project, and as Readie say's, Welcome to the forum too ;-p

Cheers,
MRb

[Balakir]

Hi MRb,

Thank you for the reply.

I have made the changes you suggest. The form shows and I can put in the new values etc.

When I submit it the page changes and shows Error: Query was empty.

Any ideas what would cause this?

I added the Print_r($_Post); and it returns this;

Array ( [Username] => Sywen [Points] => 10 [PointsSinceLastPayOut] => 10 [DateofLastRun] => 2010-03-07 [submit] => Submit ) Error: Query was empty

It looks like its only storing the data for one set of fields? as I had entered data for 2 users when I done this.

Another question the code you added for the submit button should that also go on the update script or the form page?

Sorry if these questions are basic.

Bala.

[Readie]

When I told you how to structure the form, my assumption was you were doing it with PHP :P

Build your form like this:

<?php

$sql = 'SELECT username, Points, PointsSinceLastPayOut, DateofLastRun FROM Sky_Points ORDER BY username ASC';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$formtable = '<form action="update5.php" method="post"><table id="form" cellspacing="2">
<tr>
<td style="text-align: center;font-weight: bold;">Username</td>
<td style="text-align: center;font-weight: bold;">Points</td>
<td style="text-align: center;font-weight: bold;">Points since last pay out</td>
<td style="text-align: center;font-weight: bold;">Date of last run</td>
</tr>';

for($i = 0; $i < $rows; $i++) {
$username = mysql_result($result, $i, "username");
$formtable .= "\n" . '<tr>
<td style="text-align: center;">' . ucfirst($username) . '</td>
<td style="text-align: center;"><input type="text" name="' . $username . '_Points" value="' . mysql_result($result, $i, "Points") . '" /></td>
<td style="text-align: center;"><input type="text" name="' . $username . '_PointsSinceLastPayOut" value="' . mysql_result($result, $i, "PointsSinceLastPayOut") . '" /></td>
<td style="text-align: center;"><input type="text" name="' . $username . '_DateofLastRun" value="' . mysql_result($result, $i, "DateofLastRun") . '" /></td>
</tr>';
}
$formtable .= "\n" . '<tr>
<td colspan="4"><input type="submit" value="Submit" /></td>
</tr>
</table></form>';
echo $formtable;

?>

EDIT:

This is assuming you're still using the code I posted above - Matt's given good advice though, where you can see I've written "mysql_real_escape_string($some_variable)" should be changed to "strip_tags(mysql_real_escape_string($some_variable))" :)

The LIMIT 1... It's not necessary but it may well speed things up and reduce the hit on the server

[edited by: Readie at 2:09 pm (utc) on Apr 1, 2010]

[Matthew1980]

[EDIT] Cheers Readie! :)

Hi there BalaKir,

Well doing the print_r($_POST); has told that you are passing the vars to the processing script.

If you need/want to do the $_POST['submit'] checking, do it on the processing script not the form, however if they are the same page then you can use the same code ;-p

if(isset($_POST['submit']) && ($_POST['submit'] == "submit"))
{
//Do the $_POST processing here
exit;// Use the exit so that any html downstream of this won't be displayed on screen
}

As for the query being empty, just echo the $sql var to screen before it's sent to the mysql_query(), and as this is effectivly debugging, place an exit; after the echo, so that the data is literally stopped there.

This will then tell you how your sql is being populated, and will give you an indication of what's available & what's not being properly included in the sql statement.

One other thing, if you have error_reporting(E_ALL); at the top of the script that you are working on, this will echo to screen any notices or error's that php is flagging up, great for debugging, but when you go public ensure that you either comment it out or remove it.

One pointer for future post's try to use things like example.com or example.co.uk when posting links, and for database references and mysql usernames and passwords, try not to put in your actual ones, as this could be giving people details that they use improperly, try exemplifying details instead ;-p Just thought I should point that out.

Query was empty

Is this referring to the query on the update script:-

$sql = 'SELECT Username FROM Sky_Points';

The only thing that sticks out to me is the $sql query itself, you have two separate ones one the go, I think that you may need to individualise them.

First one
mysql_select_db("hustlersls_co_u", $con);

$Firstsql = 'SELECT Username FROM Sky_Points';
$result = mysql_query($Firstsql);
$rows = mysql_num_rows($result);

Try that, I'm not saying as it will cure the issue, but having two var's named the same is bound to cause issues.

Get the query populated first, and go from there, the first point is out the way now, you are passing vars from the form to the script. Now you just have to form the query correctly, before actually sending it to mysql_query.

Hope this helps you a bit anyway,

Cheers,
MRb

[Readie]

The issue here was this:

while($row = mysql_fetch_array($result))
{
?>
<tr>
<td align="center"><input type="text" name="' . $Username . '_Points" value="<?php echo $row['Username'] ?>" /></td>
<td align="center"><input type="text" name="' . $Points . '_Points" value="<?php echo $row['Points'] ?>" /></td>
<td align="center"><input type="text" name="' . $PointsSinceLastPayOut . '_Points" value="<?php echo $row['PointsSinceLastPayOut'] ?>" /></td>
<td align="center"><input type="text" name="' . $DateofLastRun . '_Points" value="<?php echo $row['DateofLastRun'] ?>" /></td>
</tr>
<?php
}
?>

Non-dynamic form input names, as there's calls to PHP variables outside of PHP tags.

[Balakir]

Hi Guys,

Thank you both for the help.

Readie - I now have it working the way you suggested :) didn't think to do the form as PHP but it makes sense.

MRb - Not quite there yet with my first way but getting there thanks to your help :) i'll keep at it as it will be good practice for me and will help me learn more.

Thank you both for the great help and the fast replies. This has been of great help :)

[Matthew1980]

Hi there Readie,

The irony is that the value attributes were correct, BUT missing the semi-colons ; ;-p

I actually prefer dipping in and out, as I think that it more than likely decreases server load, as the bigger the HTML echo's gets, the parser is having more stress stripping the one from the other. And its easier to read methinks.

Anyway, thanks for pointing me to that I had missed it :)

Cheers,
MRb

[Matthew1980]

Hi there Balakir,

Credit to Readie, he did the code. Glad we got you sorted in the end anyway.

Have fun doing the rest of the project anyway

Cheers,
MRb

[Readie]

>> Balakir
You're very welcome :)

>> Matt
I'm just in the habit of declaring everything in variables, with the very last line/s in the PHP file being the echoes - makes things easier with regards to headers, sessions etc in my opinion.

[Matthew1980]

Hi there Readie,

Well each to their own, there is no set way of doing things, just each person's preference, Just remember that more var's means more memory, that goes for arrays too. But I'm sure as you already knew that. Now where is my coffee cup I need a refill :)

Happy Easter to all!
MRb

[Balakir]

lol :) happy easter.

Well I am going to be "attempting" to add a check box column so it only updates those checked. So you guys may hear from me again lol XD

Time now just to make this look how i want and then onto a login script and some type of validation so u can only use this if ure logged in etc :) the joys of coding.

Bala

[Readie]

Checkbox:

<input type="checkbox" name="' . $user . '_check" value="1" />

Snippet of my earlier code, modified:

$check = $username . '_check';
if(isset($_POST[$check]) && $_POST[$check] !== "") {
$sql = 'UPDATE Sky_Points SET username = "' . $user . '"';

$Points = $user . '_Points';
$Points = $_POST[$Points];
$PointsSinceLastPayOut = $user . '_PointsSinceLastPayOut';
$PointsSinceLastPayOut = $_POST[$PointsSinceLastPayOut];
$DateofLastRun = $user . '_DateofLastRun';
$DateofLastRun = $_POST[$DateofLastRun];

if(isset($Points) && $points !== "") {
$sql .= ', Points = "' . mysql_real_escape_string($points) . '"';
}
if(isset($PointsSinceLastPayOut) && $PointsSinceLastPayOut !== "") {
$sql .= ', PointsSinceLastPayOut = "' . mysql_real_escape_string($PointsSinceLastPayOut) . '"';
}
if(isset($DateofLastRun) && $DateofLastRun !== "") {
$sql .= ', DateofLastRun = "' . mysql_real_escape_string($DateofLastRun) . '"';
}

$sql .= ' WHERE username = "' . $user . '"';
mysql_query($sql);
if(!mysql_error()) {
$out .= '<br />Updated ' . $username;
} else {
$out .= '<br />Failed to update ' . $username;
}
}
}

Edit :=> For a login script I suggest looking into sessions

[Balakir]

I added that code for the checkbox Readie.
I have it showing fine and when I try to update without a box ticked it does nothing and returns nothing so thats good.

When I check a box it tells me that I have updated succesfully .. but the update is not actually done.

I'm still looking at it to see if I can figure out whats going wrong but any ideas are appreciated.

I have checked with print_r($_POST); and the array shows all the correct data. I still need to check the other debugging ways MRb suggested when I get a chance.

Bala.

[Readie]

Well that's odd... Could you post your update code please?

[Balakir]

Here you go :).

Update Code:

$check = $username . '_Check';
if(isset($_POST[$check]) && $_POST[$check] !== "") {
$sql = 'UPDATE Sky_Points SET username = "' . $user . '"';

$Points = $user . '_Points';
$Points = $_POST[$Points];
$PointsSinceLastPayOut = $user . '_PointsSinceLastPayOut';
$PointsSinceLastPayOut = $_POST[$PointsSinceLastPayOut];
$DateofLastRun = $user . '_DateofLastRun';
$DateofLastRun = $_POST[$DateofLastRun];

if(isset($Points) && $points !== "") {
$sql .= ', Points = "' . mysql_real_escape_string($Points) . '"';
}
if(isset($PointsSinceLastPayOut) && $PointsSinceLastPayOut !== "") {
$sql .= ', PointsSinceLastPayOut = "' . mysql_real_escape_string($PointsSinceLastPayOut) . '"';
}
if(isset($DateofLastRun) && $DateofLastRun !== "") {
$sql .= ', DateofLastRun = "' . mysql_real_escape_string($DateofLastRun) . '"';
}

$sql .= ' WHERE username = "' . $user . '"';
mysql_query($sql);
if(!mysql_error()) {
$out .= '<br />Updated ' . $username;
} else {
$out .= '<br />Failed to update ' . $username;
}
}
echo $out;
?>

Form Code:

$sql = 'SELECT Username, Points, PointsSinceLastPayOut, DateofLastRun FROM Sky_Points ORDER BY Username ASC';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$formtable = '<form action="update7.php" method="post"><table id="form" cellspacing="2" align="center">
<tr>
<td style="text-align: center;font-weight: bold">Update</td>
<td style="text-align: center;font-weight: bold">Username</td>
<td style="text-align: center;font-weight: bold">Points</td>
<td style="text-align: center;font-weight: bold">Points since last pay out</td>
<td style="text-align: center;font-weight: bold">Date of last run</td>
</tr>';

for($i = 0; $i < $rows; $i++) {
$username = mysql_result($result, $i, "Username");
$formtable .= "\n" . '<tr>
<td style="text-align: center;" ><input type="checkbox" name="' . $user . '_Check" value="1" /></td>
<td style="text-align: center;">' . ucfirst($username) . '</td>
<td style="text-align: center;"><input type="text" name="' . $username . '_Points" value="' . mysql_result($result, $i, "Points") . '" /></td>
<td style="text-align: center;"><input type="text" name="' . $username . '_PointsSinceLastPayOut" value="' . mysql_result($result, $i, "PointsSinceLastPayOut") . '" /></td>
<td style="text-align: center;"><input type="text" name="' . $username . '_DateofLastRun" value="' . mysql_result($result, $i, "DateofLastRun") . '" /></td>
</tr>';
}
$formtable .= "\n" . '</table>';
echo $formtable;

?>

[Readie]

I'm right in assuming you still have this bit above the code you pasted?

<?php
$con = mysql_connect("localhost","User","Pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hustlersls_co_u", $con);

$sql = 'SELECT Username FROM Sky_Points';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$out = '<u>Log:</u>';

for($i = 0; $i < $rows; $i++) {
$user = mysql_result($result, $i, "Username");

Try changing this bit:

if(!mysql_error()) {
$out .= '<br />Updated ' . $username;
} else {
$out .= '<br />Failed to update ' . $username;
}

To this:

if(!mysql_error()) {
$out .= '<br />Updated ' . $username . '<br />' . $sql;
} else {
$out .= '<br />Failed to update ' . $username;
} 

And show me a short snippet

[Matthew1980]

Hi there Balakir,

$check = $username . '_Check';

Shouldn't that be:-

$check = $user.'_Check';

Because on the form you have it defined as:-

<td style="text-align: center;" ><input type="checkbox" name="'.$user.'_Check" value="1" /></td>

I may well be wrong though?

Cheers,
MRb

[Balakir]

Ok new coded added below.

made the change MRb suggested as i think he is right on that. Found a statement with a } missing so added that in.

Now the update script runs each time but fails to give the successfully updated message with the box ticked and still does not update.

The results page shows only "Log:" it does not show the Updated or failed to update parts.

I added in the changed to that part as well Readie but this made no difference.

<?php
$con = mysql_connect("localhost","blah","blah");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hustlersls_co_u", $con);

$sql = 'SELECT Username FROM Sky_Points';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$out = '<u>Log:</u>';

for($i = 0; $i < $rows; $i++) {
$user = mysql_result($result, $i, "Username");
$check = $user . '_Check';
if(isset($_POST[$check]) && $_POST[$check] !== "") {
$sql = 'UPDATE Sky_Points SET username = "' . $user . '"';

$Points = $user . '_Points';
$Points = $_POST[$Points];
$PointsSinceLastPayOut = $user . '_PointsSinceLastPayOut';
$PointsSinceLastPayOut = $_POST[$PointsSinceLastPayOut];
$DateofLastRun = $user . '_DateofLastRun';
$DateofLastRun = $_POST[$DateofLastRun];

if(isset($Points) && $points !== "") {
$sql .= ', Points = "' . mysql_real_escape_string($Points) . '"';
}
if(isset($PointsSinceLastPayOut) && $PointsSinceLastPayOut !== "") {
$sql .= ', PointsSinceLastPayOut = "' . mysql_real_escape_string($PointsSinceLastPayOut) . '"';
}
if(isset($DateofLastRun) && $DateofLastRun !== "") {
$sql .= ', DateofLastRun = "' . mysql_real_escape_string($DateofLastRun) . '"';
}

$sql .= ' WHERE username = "' . $user . '"';
mysql_query($sql) or die(mysql_error());
if(!mysql_error()) {
$out .= '<br />Updated ' . $username;
} else {
$out .= '<br />Failed to update ' . $username;
}
}
}
echo $out;
?>

[Readie]

I edited it since you read it, but before you replied sorry. If mysql_query was failing it would of thrown the "failed to update" so I suggest removing the "or die()" clause

<input type="checkbox" name="' . $user . '_Check" value="1" />

Should be

<input type="checkbox" name="' . $username . '_Check" value="1" />

(They're in different files Matt - I had them the wrong way around on my above example :/)

---

Also, I messed up earlier aswell

if(!mysql_error()) {
$out .= '<br />Updated ' . $username;
} else {
$out .= '<br />Failed to update ' . $username;
}

Should have been

if(!mysql_error()) {
$out .= '<br />Updated ' . $user;
} else {
$out .= '<br />Failed to update ' . $user;
} 

Although there's no chance that is causing this problem.

Sorry it took me so long to reply, t'was dinner time :P

[Balakir]

Ok code fixed to what you said but still the same.

With checkbox ticked or unticked it takes me to the next page and only shows the Log: .. nothing else.

still no idea whats causing this grrr lol

<?php
$con = mysql_connect("localhost","Blah","Blah");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hustlersls_co_u", $con);

$sql = 'SELECT Username, Points, PointsSinceLastPayOut, DateofLastRun FROM Sky_Points ORDER BY Username ASC';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$formtable = '<form action="update7.php" method="post"><table id="form" cellspacing="2" align="center">
<tr>
<td style="text-align: center;font-weight: bold">Update</td>
<td style="text-align: center;font-weight: bold">Username</td>
<td style="text-align: center;font-weight: bold">Points</td>
<td style="text-align: center;font-weight: bold">Points since last pay out</td>
<td style="text-align: center;font-weight: bold">Date of last run</td>
</tr>';

for($i = 0; $i < $rows; $i++) {
$username = mysql_result($result, $i, "Username");
$formtable .= "\n" . '<tr>
<td style="text-align: center;" ><input type="checkbox" name="' . $username . '_Check" value="1" /></td>
<td style="text-align: center;">' . ucfirst($username) . '</td>
<td style="text-align: center;"><input type="text" name="' . $username . '_Points" value="' . mysql_result($result, $i, "Points") . '" /></td>
<td style="text-align: center;"><input type="text" name="' . $username . '_PointsSinceLastPayOut" value="' . mysql_result($result, $i, "PointsSinceLastPayOut") . '" /></td>
<td style="text-align: center;"><input type="text" name="' . $username . '_DateofLastRun" value="' . mysql_result($result, $i, "DateofLastRun") . '" /></td>
</tr>';
}
$formtable .= "\n" . '</table>';
echo $formtable;

?>

<?php
$con = mysql_connect("localhost","Blah","Blah");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hustlersls_co_u", $con);

$sql = 'SELECT Username FROM Sky_Points';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$out = '<u>Log:</u>';

for($i = 0; $i < $rows; $i++) {
$user = mysql_result($result, $i, "Username");
$check = $username . '_Check';
if(isset($_POST[$check]) && $_POST[$check] !== "") {
$sql = 'UPDATE Sky_Points SET username = "' . $user . '"';

$Points = $user . '_Points';
$Points = $_POST[$Points];
$PointsSinceLastPayOut = $user . '_PointsSinceLastPayOut';
$PointsSinceLastPayOut = $_POST[$PointsSinceLastPayOut];
$DateofLastRun = $user . '_DateofLastRun';
$DateofLastRun = $_POST[$DateofLastRun];

if(isset($Points) && $points !== "") {
$sql .= ', Points = "' . mysql_real_escape_string($Points) . '"';
}
if(isset($PointsSinceLastPayOut) && $PointsSinceLastPayOut !== "") {
$sql .= ', PointsSinceLastPayOut = "' . mysql_real_escape_string($PointsSinceLastPayOut) . '"';
}
if(isset($DateofLastRun) && $DateofLastRun !== "") {
$sql .= ', DateofLastRun = "' . mysql_real_escape_string($DateofLastRun) . '"';
}

$sql .= ' WHERE username = "' . $user . '"';
mysql_query($sql;
if(!mysql_error()) {
$out .= '<br />Updated ' . $user . '<br />' . $sql;
} else {
$out .= '<br />Failed to update ' . $user;
}
}
}
echo $out;
?>

[edited by: Balakir at 7:26 pm (utc) on Apr 1, 2010]

[Readie]

Update.php:

$user = mysql_result($result, $i, "Username");
$check = [b]$username[/b] . '_Check'; 

Change to

$user = mysql_result($result, $i, "Username");
$check = [b]$user[/b] . '_Check';

This is the problem with using different variables for the same things in different files, I need to stop it :/

[Balakir]

ok working now lol goddamn silly variable names lol

thank you :)

[Readie]

Excellent :)

Good luck with the rest of your project.

[Balakir]

Hi guys,

Sorry for pulling this back up. I have been trying to add a check box to this that with check or uncheck all of the other boxes.

I believe this needs to be done with a javascript function but so far I can't get it to work.

Is there anyway to get this to work with they code for my form?

[Readie]

It does indeed need to be done with some client side code like JavaScript. You could use the form generation code to generate the JavaScript at the same time as you develop the form and echo them into their respective places.

$js = '<script type="text/javascript">

function chek_all() {

var control = document.getElementById("control_box").checked;
if(control) {';
$js_i = '';
$js_e = '';

for($i = 0; $i < $rows; $i++) {
$username = mysql_result($result, $i, "Username");
$formtable .= "\n" . '<tr>
<td style="text-align: center;" ><input type="checkbox" id="' . $username . '_Check" name="' . $username . '_Check" value="1" /></td>
<td style="text-align: center;">' . ucfirst($username) . '</td>
<td style="text-align: center;"><input type="text" name="' . $username . '_Points" value="' . mysql_result($result, $i, "Points") . '" /></td>
<td style="text-align: center;"><input type="text" name="' . $username . '_PointsSinceLastPayOut" value="' . mysql_result($result, $i, "PointsSinceLastPayOut") . '" /></td>
<td style="text-align: center;"><input type="text" name="' . $username . '_DateofLastRun" value="' . mysql_result($result, $i, "DateofLastRun") . '" /></td>
</tr>';

$js_i .= "\n" . 'document.getElementById("' . $username . '_Check").checked = false;';
$js_e .= "\n" . 'document.getElementById("' . $username . '_Check").checked = true;';
}

$js .= $js_i . "\n" . '} else {' . $js_e . "\n" . '}

</script>';

I'm not 100% certain the JavaScript there will work... I've never been brilliant with JS.

The principle is sound though, just call it with this:

<input type="checkbox" name="control_box" onclick="chek_all()" />

I've intentionally left the "c" out of "check" - it's the sort of thing that might be reserved by the DOM.

[Balakir]

Well that gets the table showing so thats good :)

adding in a check box that onclick runs the the chek_all function does not check them all.

So my guess its the java script is not playing games. gonna have a look around see if i can find a way to make it do it.

[Readie]

Are you echoing $js inside of the <head></head> tags?