Update Loop not working 1

[bazaroff]

This code looks like what I need to happen, but before I modified it to use in my web app. I tried it as is. It did not work but I can't figure out why.

<strong>Update multiple rows in mysql</strong><br>

<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="test_mysql"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);
?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">


<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
<td align="center"><input name="name[]" type="text" id="name" value="<? echo $rows['name']; ?>"></td>
<td align="center"><input name="lastname[]" type="text" id="lastname" value="<? echo $rows['lastname']; ?>"></td>
<td align="center"><input name="email[]" type="text" id="email" value="<? echo $rows['email']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php
// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET name='$name[$i]', lastname='$lastname[$i]', email='$email[$i]' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:update_multiple.php");
}
mysql_close();
?>


In trying to fix it, I changed the " if($Submit)...." to "if($Submit !='false')..." which did seem to execute an update query, but it erased all the data from the table (or more accurately updated every field to "")

I then instead of running an update had the page display at the bottom each looped update query, and it showed the following...

UPDATE test_mysql SET name= '', lastname='', email='' WHERE id='1'
UPDATE test_mysql SET name= '', lastname='', email='' WHERE id='2'
UPDATE test_mysql SET name= '', lastname='', email='' WHERE id='3'
UPDATE test_mysql SET name= '', lastname='', email='' WHERE id='4'
UPDATE test_mysql SET name= '', lastname='', email='' WHERE id='5'...

Any thought or help on this would be greatly appreciated.
Thanks

 

[skiflyer]

Whoever wrote this code had register_globals enabled... you probably (and correctly) do not.

$sql1="UPDATE $tbl_name SET name='$name[$i]', lastname='$lastname[$i]', email='$email[$i]' WHERE id='$id[$i]'";

With register globals enabled the variables $name, $lastname, $email, $id are set from the HTML form fields name, lastname, email, id.

With register globals disabled, they are instead included in $_POST['name'], $_POST['lastname'] etc...

 

[bazaroff]

when i tried swapping $name for $_POST['name'] it returns ARRAY and makes the other field empty.

any suggestions? how should the code look using the POST varialbes.

 

[skiflyer]

$_POST['name'] should equal array... the entire variable should now read

$_POST['name'][$i]

(Assuming the HTML form stuff is correct, I've never tried to use arrays for text fields like that before)

 

[bazaroff]

Thanks, that worked perfect. Now I've found that my work around for the submit button (submit != 'false') isn't good because when i open the form all the POST variables are empty so everything is updated to "".

That also had to be changed to the $_POST['Submit']

Thanks for your help on this.

 

[skiflyer]

You may also want to validate the variables... something like

if (isset($_POST['name'][$i]) && strlen($_POST['name'][$i]) > 5)
{
  accept it...
}
else
{
  redisplay the form with an error message about how it's too short.
}

Or, maybe not depending on your usage.

 

[bazaroff]

i'm working on verifing the values entered by the user now.

what i would like is to have something like

if (entered name != orginail name){
run update}

the only way i can think to maek that work is to include in the table hidden fields for each value that could be updated so that on POST both sets of 'name' can be included in the updating script.

is this the best way to go about it?
Thanks

 

[skiflyer]

I usually prefer to get a fresh copy on the submit and compare to that... that way if the user opens multiple windows you're safer.

Failing that I use $_SESSION to hold the values of the originals. The reason being your suggested method is too easy for a nefarious user to muck things up.

 

[bazaroff]

skiflyer-

thanks for your help on this.

i have the update working when i pull all the data in to one page, but when i break my data set up using limit x, y statements and have next & previous buttons, it falls apart.

any thoughts?

 

[Miros]

Can you submit one set of changes when the user clicks Next, then go on to the next?

Can you made hidden fields to store the changes when the user goes to the next page?

 

[bazaroff]

The short answer to both questions is no.

I am working between three separate scripts and I am tryingt obrnig them together.

Script 1 is ideally going to be the final project. This script queries the databse and returns what can be a rather large data set of members, can number at times well over a 1000. From this page the user can go through the list and would be able to ideally edit certain fields of the list (attended y/n, phone number and email). Because of the size of the lists being pulled, I wanted to break it out into bit sized chunks, thus the paging feature.

Script 2 is my working update script that returns a small data set and when a submit button is pushed executes the update query based on the values in the changable fields. (This is actually the script that started this thread.) This works to a point, when the button is clicked the data is updated, but when the page refreshes it loads back with the pre-edited data. I didn't worry too much about that because the user would ideally move back to the main page with teh button, or move on to the next or previous 10 records.

Script 3 is my working paging script that pulls a data set and uses LIMIT x, y to break up the data into managable chunks. this page displays a table with the data and at the bottom are three buttons, next(ahead 10), previous(back 10) and home (back to the main page).

I have scripts 1 and 3 now together, but I can not get the updating to work. I can post the script thus far if it might help.

Thanks

 

[skiflyer]

If by doesn't work you mean you keep updating the first (Y-X) records.... then it's because you're not storing the ID field with the input fields properly. In your initial post you don't store it at all, which would imply you're just using 0-10, or whatever.

If by doesn't work you mean something else, then I dunno

 

[bazaroff]

Unfortunately it doesn't work by something else. It brings the data in but
I can't seem to grab the ids or the edited field values. It returns an array error when I give the inputs a name?

Below is the posted code....

<?php
session_start(); #2
if ( $_SESSION['login']!="yes" )
{
header("Location: login.php");
exit();
}

include("info.inc");

foreach ($_POST as $field => $value)
{
echo "$field = $value<br>";

# USE THE FOLLOWING TO PULL THE LIST WHERE CLAUSE PIECE 35

$connection = mysql_connect($host, $user, $password)
or die ("Could not connect to the Server");
$db = mysql_select_db($database, $connection)
or die ("Could not select database.");
$sql2 = "SELECT ListWhere FROM `TargetLists` WHERE List = '$value'";
$result3 = mysql_query($sql2)
or die("Couldn't execute query 3.");
$qryvalue = mysql_fetch_array( $result3 );
$_SESSION['list'] = $qryvalue['ListWhere'];
}

/* Data paging script
2006-01-11 by sleipnir214
This script is in the public domain.

CAUTION: This script works on my system -- but it could blow yours to
smithereens. Therefore, no waranty is expressed or implied as to how
safe it will be for you to use.

Use this code with trepidation and circumspection.
*/


//variables for connecting to MySQL

$mysql_host = $host;
$mysql_user = $user;
$mysql_pass = $password;

$mysql_db = $database2;

//set the number of records per page
$records_per_page = 10;


//connect to MySQL
mysql_connect ($mysql_host, $mysql_user, $mysql_pass);
mysql_select_db ($mysql_db);


//find out how many records are in the table
$count_query = "SELECT count(*) from TABLENAME WHERE ".$_SESSION['where']." and ".$_SESSION['list'];
$rh = mysql_query ($count_query);
list ($record_count) = mysql_fetch_array($rh);


//calculate the maximum "page" that can be displayed.
$max_pages = floor($record_count / $records_per_page);


//This logic takes care of reacting to input.
if (isset($_GET['page']))
{
if ($_GET['page'] > 1)
{
if ($_GET['page'] > $max_pages)
{
$current_page = $max_pages;
}
else
{
$current_page = $_GET['page'];
}
}
else
{
$current_page = 1;
}
}
else
{
$current_page = 1;
}

$limit_start = ($current_page - 1) * $records_per_page;


//query the database for the required records

$data_query = 'SELECT ID, concat(firstn, if(length(middlen)>0,concat(" ",middlen," ")," "), lastn) as FullName, (2006 - if(length(dob)>12,left(right(dob,12),4),right(dob,4))) as AGE, Gender as SEX, concat(snum, " ",sname,if(length(apt)<1,"",concat(" ",apt))) as Address, phone, party, City, REPONSE as PC, VOLUNTEER as VOL, EMAIL, sname FROM TABLENAME WHERE '.$_SESSION['where'].' and '.$_SESSION['list'].' ORDER BY city, sname, snum, apt, lastn LIMIT '. $limit_start . ', ' . $records_per_page;

### '.$_SESSION['where'].' and '.$_SESSION['list'].'

$rh = mysql_query ($data_query);

print '<html><body><table width="100%" border="1">';
echo "<thead><tr><th>ID</th><th>Full Name</th><th>Age</th><th>Gender</th><th>Address</th><th>Phone</th><th>Group</th><th>City</th><th>P.C.</th><th>Response</th><th>VOL</th><th>EMAIL</th>";


//output the required records
while ($word_data = mysql_fetch_array($rh))
{
echo "<tr>
<td><input name='id[]' type='hidden' id='id' value= $word_data[0]></td>
<td><font size = '2'>$word_data[1]</td><td align='center'><font size = '2'>$word_data[2]</td><td align='center'><font size = '2'>$word_data[3]</td><td><font size = '2'>$word_data[4]</td>
<td><input name = 'phone[]' type ='text' id = 'phone' value =$word_data[5]>
</td><td align='center'><font size = '2'>$word_data[6]</td><td><font size = '2'>$word_data[7]</td><td align='center'>$word_data[8]</td>";
echo "<td>
<p align='center'>
<select name='response'>
<option selected>Response</option>
<option value='Yes'>Yes</option>
<option value='UN'>Undecided</option>
<option value='N1'>No 1</option>
<option value='N2'>No 2</option>
</select>
</p></TD>
<td>
<p align='center'>
<select name='volunteer'>
<option selected>Volunteer</option>
<option value='Y'>Yes</option>
<option value='U'>Maybe</option>
<option value='N'>No</option>
</select>
</p></TD>
<td><input name = 'email[]' type ='text' id = 'email' value =$word_data[10]></td>
</font></tr>";

}


//this is the logic for the "previous" link display
print '<tr><td> </td><td width="50%" align="center">';
if ($current_page > 1)
{

print "<form action='".$_SERVER['PHP_SELF'] . "?page=" . ($current_page - 1) . "' method='POST'>";
echo "<input type='submit' value='Previous'>";
echo "</form>";
}
else
{
print '&nbsp;';
}
print '</td><td> </td><td> </td>';


//this is the logic for the "next" link display
print '<td width="50%" align="center">';
if ($limit_start + $records_per_page < $record_count)
{
print "<form action='".$_SERVER['PHP_SELF'] . "?page=" . ($current_page + 1) . "' method='POST'>";
echo "<input type='submit' value='Next'>";
echo "</form>";
}
else
{
print '&nbsp;';
}
print "</td><td align = 'center'><form action='ContactCenter.php' method='POST'>";
echo "<input type='submit' value='Home'>";
echo "</form>";
print '</td></tr></table>';

for($i=0;$i< $records_per_page; $i++){

# All I am trying to update here are the email values...

$sql1 = "UPDATE TABLENAME SET email= '".$_POST['email'][$i]."' WHERE ID = ".$_POST['id'][$i];
$result1=mysql_query($sql1);
}

print '<body></html>';
?>

 

[Miros]

Just a suggestion, instead of having your <html>, <head>, and <body> tags output by the script, you might whant to have them outside the script as normal html.

<html><head>
</head><body>
<?php
...
?>
</body></html>

 

[skiflyer]

Dude... you're not putting them in a form, and you have no submit button for that section.

Piece of advice, everytime I have a new form submission to work on, the first thing I do is have it submit to a script that just does

echo '<pre>';
print_r($_POST);

That lets you make sure the HTML is right, and it's sending everything to the receiving script.

Basically you're just missing some HTML tags around your input fields... and you're missing the button that's supposed to make this happen.... if you're next/previous buttons are supposed to instantiate that action, then you just need to put everything in one big form instead of the way it's broken up now.

 

[bazaroff]

Once again thanks for the help on this.

Putting it all in one form solved one problem but then caused another. Now the page pulls and updates the first set of records fine, but the paging navigation buttons no longer work.

Ideally the button do 2 things, first they run the update to any values that were edited. Second they perform the navigation function.

Originally with each button as its own form the form action handled the navigation, so the next button form action = "form.php?page = ($currentpage +1) while the previous button form action = "form.php?page = ($currentpage - 1) while the home button form action = "homepage.php".

I tried to set the 1 form action = $goto, and then in the if statements for the individual buttons set $goto = whatever form action it had as multiple forms, but it wouldn't work.

Any thoguhts?

 

[skiflyer]

Sure this one's easy
PHP Picks up which submit button was pushed... so your HTML looks like

<input type="submit" name="navbutton" value="Previous" />
<input type="submit" name="navbutton" value="Next" />

Then your reception can do one of these

if ($_POST['navbutton'] == "Previous") 
{
  //Do whatever
}
else if ($_POST['navbutton'] == "Next")
{
  //Do whatever
}
else
{
  //Do whatever
}

 

[bazaroff]

I kind already have it doing part of this to run the updates.

Three buttons with three different names and values (doe this make a real difference?)

it currently reads soemtihng like

if($_POST['next']){

// runs update query
// but how can I set the form action to = $_SERVER['PHP_SELF'] . "?page=" . ($current_page + 1)


}
else if($_POST['previous']){

// runs update query

// but how can I set the form action to = $_SERVER['PHP_SELF'] . "?page=" . ($current_page - 1)


}
else if($_POST['home']{

// runs update query
// but how can I set the form action to = "home.php"
}

 

[skiflyer]

Your method "should" be as effective, but depending on your error reporting you might see some output like "Array Value not set on line 117 in formProcess.php"


The if's should read

if (isset($_POST['home']) && $_POST['home']=="whatever"))

If you're going to do it that way... just for safety's sake.

So does that mean you have it working, or that's the way you have it and it's not going?

 

[bazaroff]

my actual problem is the syntax for setting the form action....

echo "<form action = '". $what should this variable be (POST / SESSION ?) ."' >";

// code for the data pull and the inout fields...

<input type = 'submit' name = 'navbut' value = 'next'>

<input type = 'submit' name = 'navbut' value = 'previous'>

<input type = 'submit' name = 'navbut' value = 'home'>

</form>

if(isset($_POST['navbut']) && $_POST['navbut'] == 'next'){

// I imagine that there would need to be a piece here to set the form action variable? how should it go?

//this runs the update
for($i=0;$i< $records_per_page; $i++){
$sql1 = "UPDATE table SET email= '".$_POST['email'][$i]."' WHERE ID = ".$_POST['id'][$i];
$result1=mysql_query($sql1);
}

// the same thing would be for the other buttons