1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 3:27 pm Apr 28, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Updating multiple records for MySQL

         

Balakir

11:27 am on Apr 1, 2010 (gmt 0)

10+ Year Member



*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]

Balakir

9:53 pm on Apr 5, 2010 (gmt 0)

10+ Year Member



I have tried putting

<head>
<?php
echo $js;
?>
</head>

and
<head>
<?php
echo $js = '<script type="text/javascript">

function chek_all(frm1) {

var control = document.getElementById("frm1").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>';
?>
</head>

the first way lets the form load the 2nd way stop the form loading ... am i doing something wrong?

Matthew1980

10:00 pm on Apr 5, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



hi there Balakir,

In a word yes!

Place this:-

echo $js;

here:-

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

echo $js;
?>

As you need to echo the built var after it has been built, and not before ;-p

[EDIT]: Oh, and remove this (in bold):-

<head>

<?php
echo $js;
?>
</head>


Just make sure that all of the javascript function is built, and echoed within the head tag

Hope this helps you,

Cheers,
MRb

[edited by: Matthew1980 at 10:03 pm (utc) on Apr 5, 2010]

Readie

10:03 pm on Apr 5, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



If your <head> tags are above the for loop, then this will not work.

I have personally liked to always structure my pages like this:

<?php

/* PHP STUFF GOES HERE */

/* ----- */

/* ECHO HTML STUFF HERE */

?>

[edit]

Damnit Matt, stop typing faster than me! :P

Balakir

10:32 pm on Apr 5, 2010 (gmt 0)

10+ Year Member



Ok my full code is below,

When you say post in head tags I assume you mean in the head tags within the html tags? 



<?php 
session_start();
 
include "db_connect.php"; 

if($_SESSION['id'])
{

$datesql = "SELECT date FROM event_date_store WHERE eventname = 'Sky'";
$datequery = mysql_query($datesql);
$daterows = mysql_num_rows($datequery);

for($i = 0; $i < $daterows; $i++) {
$formtable1 = '<form action="skypointsupdate.php" method="post" onKeyPress="return event.keyCode!=13" onSubmit="return confirm_entry();"><table id="form" cellspacing="2" align="center"> 
<tr>
<th style="text-align: center;">Date of Last Run<th>
<td style="text-align: center;"><input type="text" name="date" value="' . mysql_result($datequery, $i, "date") . '" /></td>
</tr>';
}

$formtable1 .= "\n" . '</table>'; 

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

$formtable .= '<form name="frm1" action="skypointsupdate.php" method="post" onKeyPress="return event.keyCode!=13" onSubmit="return confirm_entry();"><table id="form" cellspacing="2" align="center">
<tr>
<td style="text-align: center;"><input type="checkbox" name="control_box" onclick="chek_all()" /></td>
<th style="text-align: center;">Username</th> 
<th style="text-align: center;">Points</th> 
<th style="text-align: center;">Points since<p>last pay out</p></th> 
<th style="text-align: center;">Date of last<p>run attended</p></th> 
</tr>';

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

echo $js;

$formtable .= "\n" . '<tr>
<td style="text-align: center;"><input type="checkbox" name="control_box" onclick="chek_all()" /></td>
<td style="text-align: center;" colspan="5"><input name="submit" type="submit" value="Save Changes"> <input name="reset" type="reset" value="Reset Changes"></td>
</tr>';
$formtable .= "\n" . '</table>'; 
echo "<h1 style='text-align: center;'>Update Sky Points</h1>";
echo $formtable1;
echo $formtable;
}
else
{
echo "You don't belong here!";
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Update Sky Points</title>
<script language="JavaScript">
function confirm_entry()
{
var agree=confirm("Are you sure you wish to update the points?");
if (agree){ 
// when ok is pressed the form is submitted
return true;
}else{
// when cancel is pressed nothing happens
return false;
}
}
</script>
<?php echo $js; ?>
</head>

<body>
</body>
</html>


Readie

10:38 pm on Apr 5, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



You are echoing $js twice - remove the first one.

When you say post in head tags I assume you mean in the head tags within the html tags?

Ayup.

Balakir

10:42 pm on Apr 5, 2010 (gmt 0)

10+ Year Member



removed the first echo for $js only one left now is the one above in the head tags. Still does not tick all :(

Readie

10:45 pm on Apr 5, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Can you go to this page, right click => view source

Copy and paste the Javascript function I wrote (though strip out some of the repeating stuff - only need to see one of those)

Balakir

10:47 pm on Apr 5, 2010 (gmt 0)

10+ Year Member



think the link is missing.

Readie

10:54 pm on Apr 5, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



firefox/chrome => "view page source"
safari/IE => "view source"
opera => "source"

Make sure you're clicking on a blank part of the page

Balakir

10:58 pm on Apr 5, 2010 (gmt 0)

10+ Year Member



just realised you meant my page lol


<script type="text/javascript">

function chek_all() {

var control = document.getElementById("control_box").checked;
if(control) {
document.getElementById("Aiesha_Check").checked = false;
document.getElementById("Arcrist_Check").checked = false;
document.getElementById("Ashman_Check").checked = false;
} else {
document.getElementById("Aiesha_Check").checked = true;
document.getElementById("Arcrist_Check").checked = true;
document.getElementById("Ashman_Check").checked = true;
}

</script>

Readie

11:04 pm on Apr 5, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member 



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

Needs to be this: 
<input type="checkbox" id="control_box" name="control_box" onclick="chek_all()" />


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

I also suggest swapping the true/false over in these

Balakir

11:08 pm on Apr 5, 2010 (gmt 0)

10+ Year Member



still the same

Readie

11:10 pm on Apr 5, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Hmm...

Someone else will need to take a look at the code then. As I said, JavaScript really isn't my forté. I can just about manage pre-submit form validation and that's all.

Balakir

11:11 pm on Apr 5, 2010 (gmt 0)

10+ Year Member



nps man thanks for the help :) ill keep looking at it and trying different things. The help is much appreciated.

rocknbil

4:44 am on Apr 6, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Didn't look at the rest of the thread, hope you got that sorted, but the JS is a simple solution posted here [webmasterworld.com]. :-)
This 45 message thread spans 2 pages: 45
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 3:27 pm Apr 28, 2026