PDA

View Full Version : Multiple Update Rows


lacsoncruz
09-19-2013, 06:43 AM
First of all, I use PHP Dreamweaver to make a program with phpMyAdmin Database. This program gets my records in a 1st row blank when I submit the button, and the remaining records in a rows are still there which is 2nd row and below, but the records in a 1st row is still on the database, why should it happen? any idea for me to solve this program, it also won't update my record. this makes me sick, i need help please! :( tnx...

<?php require_once('Connections/tlsc_conn.php');
mysql_select_db($database_tlsc_conn, $tlsc_conn);
$query_Recordset1 = "SELECT * FROM tbl_name";
$Recordset1 = mysql_query($query_Recordset1, $tlsc_conn) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

if(isset($_POST['submit'])) {
$count = count($_POST['id']);
$submit = $_GET['id'];

for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET name='$name[$i]', lastname='$lastname[$i]', email='$email[$i]' WHERE id='$id[$i]'";
$row_Recordset1=mysql_query($sql1);
}

if($row_Recordset1){
header("location:lulu.php");
exit;
}
}
?>
<!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=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<form name="form2" method="post" action="">
<table width="634" border="1">
<tr>
<td>id</td>
<td>name</td>
<td>lastname</td>
<td>email</td>
</tr>
<?php do { ?>
<tr>
<td><?php $id[]=$row_Recordset1['id']; ?><?php echo $row_Recordset1['id']; ?>
<input name="id[]" type="hidden" value="<?php echo $row_Recordset1['id']; ?>" />
</td>
<td>
<input name="name[]" type="text" value="<?php echo $row_Recordset1['name']; ?>">
</td>
<td>
<input name="lastname[]" type="text" value="<?php echo $row_Recordset1['lastname']; ?>">
</td>
<td>
<input name="email[]" type="text" value="<?php echo $row_Recordset1['email']; ?>"> </td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
<p>
<input type="submit" name="submit" value="Submit" />
</p>
</form>
<p>
</body>
</html>
:sad:

edbr
09-19-2013, 09:32 AM
are you trying to update or insert? if you update yu should have a where clause

jmichae3
10-09-2013, 11:08 AM
<?php require_once('Connections/tlsc_conn.php');
mysql_select_db($database_tlsc_conn, $tlsc_conn);

function CalcPageCount($pageSize,$tableRowCount) {
return intval($tableRowCount/$pageSize);
}
function CalcPageOffsetpn($pageNum) {
return $pageNum-1;
}
function CalcPageOffsetpnps($pageSize,$tableRowCount,$pageN um) {
return ($pageNum * $pageSize) % $pageSize; // % is modulus (remainder of division)
}
function CalcPageLimit($pageSize) {
return $pageSize;
}
function GetNumTableRows($dblink) {
global $tbl_name;
$result=0;
$recordset = mysql_query("
SELECT
COUNT(*) AS c
FROM
$tbl_name
", $dblink);
if ($recordset) {
$row=mysql_fetch_assoc($recordset);
$result=intval($row['c']);
}
mysql_free_result($recordset); //always free when done!
}
function DoUpdate($dblink) {
//update a paged set of email records in bulk
global $pageNum, $pageSize, $tableRowCount,$pageCount;
if(isset($_POST['submit'])) {
$query_Recordset1 = "
SELECT
*
FROM
tbl_name
LIMIT
'".CalcPageLimit($tableRowCount)."'
OFFSET
'".CalcPageOffsetpnps($pageSize,$tableRowCount,$page Num)."'
";
$Recordset1 = mysql_query($query_Recordset1, $tlsc_conn) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


$count = count($_POST['id']); //why are you alternating between POST and GET? are you using both?
$idArray = $_GET['id'];

while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) {
$row_Recordset1=mysql_query("
UPDATE
$tbl_name
SET
name='" . mysql_real_escape_string($name[$i]) . "',
lastname='" . mysql_real_escape_string($lastname[$i]) . "',
email='" . mysql_real_escape_string($email[$i]) . "'
WHERE
id='".$id[$i]."'
",$dblink);
}
mysql_free_result($query_Recordset1); //always free when done with query or you get scrambled results!
if($totalRows_Recordset1>0){
//is what you want? goto lulu if an update was done(totalRows_Recordset1>0)?
//or you want to goto lulu if any records in table ($tableRowCount>0)?
header("location:lulu.php");
exit;
}
}
}

function DoShowPageNavigation() {
global $pageNum, $pageSize, $tableRowCount, $pageCount;
//this is rudimentary. I personally would display it as an inline list,
//that's easily changeable with css. see o'reilly css cookbook page 675 or google w3c css selectors
echo "<ul class='pageList'>\n";
for ($pn=1; $pn <= $pageCount; $pn++) {
echo "<li>" . $pn . "</li>\n";
}
echo "</ul>\n";
}
function DoShowForm($dblink) {
global $pageNum, $pageSize, $tableRowCount, $pageCount;
if(isset($_POST['submit'])) {
$query_Recordset1 = "
SELECT
*
FROM
tbl_name
LIMIT
'".CalcPageLimit($pageSize,$tableRowCount)."'
OFFSET
'".CalcPageOffset($pageSize,$tableRowCount)."'
";
$Recordset1 = mysql_query($query_Recordset1, $tlsc_conn) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

?><!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>bulk admin email editing page</title>
<style>
ul.pageList {
margin:0px;
padding:0px;
}
ul.pageList li {
display:inline;
}
ul.pageList li:nth-child(1):before {
content:"Page [ ";
}
ul.pageList li:after {
content:" | ";
}
ul.pageList li:last-child:after {
content:" ]";
}
</style>
</head>
<body>
<form name="form2" method="post" action="processform.php"><!--must have action to do anything !-->
<table width="634" border="1">
<?php
$counter=0;
$headerAboveEveryNRows=5;//header above every 5 rows
while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) {
//displaying the header should be the first thing you do,somakew it the top thing in the while loop and first in count.
if (0==$counter % headerAboveEveryNRows) {
?>
<tr>
<td>id</td>
<td>name</td>
<td>lastname</td>
<td>email</td>
</tr>
<?php
}

?>
<tr>
<td><?php echo htmlentities(stripslashes($row_Recordset1['id'])); ?>
<input name="id[]" type="hidden" value="<?php echo $row_Recordset1['id']; ?>">
</td>
<td><input name="name[]" type="text" value="<?php echo stripslashes($row_Recordset1['name']); ?>"></td>
<td><input name="lastname[]" type="text" value="<?php echo stripslashes($row_Recordset1['lastname']); ?>"></td>
<td><input name="email[]" type="text" value="<?php echo stripslashes($row_Recordset1['email']); ?>"></td>
</tr>
<?php
} //while
?>
</table>
<p><input type="submit" name="submit" value="Submit"></p>
</form>
<?php DoShowPageNavigation(); ?>
</body>
</html>
<?php
mysql_free_result($query_Recordset1); //always free when done with query or you get scrambled results!
}//if submit
}//func DoShowForm

$pageSize=20;//20 rows
if (isset($_GET['page'])) {
$pageNum=intval($_GET['page']);//1-based
} else {
$pageNum=1;//1-based
}
$TableRowCount=GetNumTableRows();
$pageCount=CalcPageCount($pageSize,$tableRowCount) ;
$pageOffset=CalcPageOffsetpnps($pageSize,$tableRow Count,$pageNum);//0-based. I *think* the SQL OFFSET is like a page number, but it may be a 0-based row number.

DoUpdate($tlsc_conn);
DoShowForm($tlsc_conn);
?>
[/QUOTE]

it looks like you are doing bulk admin editing of email addresses and names,by the fact that you are doing [] on your input elements, a standard PHP way of doing arrays of elements.

I am going to add a "page" parameter/variable to $_GET.

several problems you are going to have here:



mysql API is deprecated. use mysqli or PDO::mysql.



be consistent about the way you close your void elements (singletons). if using XHTML or XML, use a / but if not don't.



validate HTML/XHTML by making this the URL for a bookmark,like in the linkx bar: javascript:void(document.location='http://validator.w3.org/check?charset=%28detect+automatically%29doctype=In line&ss=1&group=0&verbose=1&uri='+escape(document.location))



validate css by making this the URL for a bookmark, like in the links bar: javascript:void(document.location='http://jigsaw.w3.org/css-validator/validator?profile=css3&usermedium=all&warning=1&lang=en&uri='+escape(document.location))



you are selecting the ENTIRE TABLE. this doesn't scale well. this could get REALLY HUGE and break the browser and make people upset with long loads and huge pages. page-ify it or something by using LIMIT nrows OFFSET nskip on the end of your SELECT statement. look it up in the mysql manual. http://dev.mysql.com/doc/refman/5.5/en/select.html this means you are also going to have to come up with your own page navigation system. or only update a portion of the table, like a specific ID. you could let them pick an ID to update, and have an edit.php link on that id which shows the row content. and that could be paged. also, this makes MUCH less inpact on the server when it's paged (which keeps your site from being flagged by hosting admins for "oh, you need more expensive hosting! you are using up too much resources!")



arrays don't expand too well within php douvble-quoted strings.



XHTML doesn't work with IE. use HTML 4.01 or HTML5.



assigning to $id[] may have the unintended consequences of appending to the array.



you are always better off starting fromthe beginning of the resultset and processing it whole or just using only the first row in the case of COUNT().



you didn't have a separate query for your form fields.



you don't have an action="processform.php" on your form element. this will result in a dud (non-working) form that's only good for DHTML, not PHP.

jmichae3
10-09-2013, 11:20 AM
change the line with the li tags to
echo "<li><a href=\"processform.php?page=" . $pn . "\">" . $pn . "</a></li>\n";

jmichae3
10-09-2013, 11:23 AM
change $_POST['submit'] to $_POST['Submit'] since you capitalized it in the form. or form will never work.

jmichae3
10-09-2013, 11:28 AM
change if (0==$counter % headerAboveEveryNRows) { to if (0==$counter++ % headerAboveEveryNRows) { so it post-increments after the if statement executes.

jmichae3
10-09-2013, 11:33 AM
edbr is right, at some point at the start,you are goingto have tofind a way todetext that the record doesn't exist and so make an batch-add form that does SQL INSERT statements. http://dev.mysql.com/doc/refman/5.7/en/insert.html

jmichae3
10-09-2013, 09:30 PM
I missed a $ on a tbl_name.

jmichae3
10-09-2013, 10:21 PM
<?php
require_once('dbconnection.php');
mysql_select_db($database_tlsc_conn, $tlsc_conn);

$pageSize=20;//20 rows
if (isset($_GET['page'])) {
$pageNum=intval($_GET['page']);//1-based
} else {
$pageNum=1;//1-based
}
$TableRowCount=GetNumTableRows($tlsc_conn);
$pageCount=CalcPageCount($pageSize,$tableRowCount) ;
$pageOffset=CalcPageOffsetpnps($pageSize,$tableRow Count,$pageNum);//0-based. I *think* the SQL OFFSET is like a page number, but it may be a 0-based row number.

DoUpdate($tlsc_conn);
DoShowForm($tlsc_conn);

function CalcPageCount($pageSize,$tableRowCount) {
return intval($tableRowCount/$pageSize);
}
function CalcPageOffsetpn($pageNum) {
return $pageNum-1;
}
function CalcPageOffsetpnps($pageSize,$tableRowCount,$pageN um) {
return ($pageNum * $pageSize) % $pageSize; // % is modulus (remainder of division)
}
function CalcPageLimit($pageSize) {
return $pageSize;
}
function GetNumTableRows($dblink) {
global $tbl_name;
$result=0;
$recordset = mysql_query("
SELECT
COUNT(*) AS c
FROM
$tbl_name
", $dblink);
if ($recordset) {
$row=mysql_fetch_assoc($recordset);
$result=intval($row['c']);
}
mysql_free_result($recordset); //always free when done!
}//GetNumTableRows

function DoUpdate($dblink) {
//update a paged set of email records in bulk
global $pageNum, $pageSize, $tableRowCount,$pageCount;
if(isset($_POST['Submit'])) {
$query_Recordset1 = "
SELECT
*
FROM
$tbl_name
LIMIT
'".CalcPageLimit($pageSize)."'
OFFSET
'".CalcPageOffsetpnps($pageSize,$tableRowCount,$page Num)."'
";
$Recordset1 = mysql_query($query_Recordset1, $tlsc_conn) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


$count = count($_POST['id']);
$i=0;
while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) {
$row_Recordset1=mysql_query("
UPDATE
$tbl_name
SET
name='" . mysql_real_escape_string($name[$i]) . "',
lastname='" . mysql_real_escape_string($lastname[$i]) . "',
email='" . mysql_real_escape_string($email[$i]) . "'
WHERE
id='".$id[$i++]."'
",$dblink);
}
if ($totalRows_Recordset1>0) {
mysql_free_result($query_Recordset1); //always free when done with query or you get scrambled results!
header("location: updateprocessed.php");
exit;
} //if
}//if
}//DoUpdate

function DoShowPageNavigation() {
global $pageNum, $pageSize, $tableRowCount, $pageCount;
//this is rudimentary. I personally would display it as an inline list,
//that's easily changeable with css. see o'reilly css cookbook page 675 or google w3c css selectors
//need to pare this downto resonable amountof page numbers...
echo "<ul class='pageList'>\n";
for ($pn=1; $pn <= $pageCount; $pn++) {
echo "<li><a href=\"processform.php?page=" . $pn . "\">" . $pn . "</a></li>\n";
}
echo "</ul>\n";
}
function DoShowForm($dblink) {
global $pageNum, $pageSize, $tableRowCount, $pageCount, $_POST;
$headerAboveEveryNRows=5;//header above every 5 rows

if(isset($_POST['Submit'])) {
$query_Recordset1 = "
SELECT
*
FROM
tbl_name
LIMIT
'".CalcPageLimit($pageSize)."'
OFFSET
'".CalcPageOffsetpnps($pageSize,$tableRowCount,$page Num)."'
";
$Recordset1 = mysql_query($query_Recordset1, $tlsc_conn) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

?><!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>bulk admin email editing page</title>
<style>
ul.pageList {
margin:0px;
padding:0px;
}
ul.pageList li {
display:inline;
}
ul.pageList li:nth-child(1):before {
content:"Page [ ";
color:orange;
}
ul.pageList li:after {
content:" | ";
color:orange;
}
ul.pageList li:last-child:after {
content:" ]";
color:orange;
}
</style>
</head>
<body>
<?php DoShowPageNavigation(); ?>
<form name="form2" method="post" action="processform.php"><!--must have action to do anything !-->
<table width="634" border="1">
<?php
$counter=0;
while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) {
//displaying the header should be the first thing you do,so make it the top thing in the while loop and first in count.
if (0==$counter++ % headerAboveEveryNRows) {
?>
<tr>
<td>id</td>
<td>name</td>
<td>lastname</td>
<td>email</td>
</tr>
<?php
}

?>
<tr>
<td><?php echo htmlentities(stripslashes($row_Recordset1['id'])); ?>
<input name="id[]" type="hidden" value="<?php echo $row_Recordset1['id']; ?>">
</td>
<td><input name="name[]" type="text" value="<?php echo stripslashes($row_Recordset1['name']); ?>"></td>
<td><input name="lastname[]" type="text" value="<?php echo stripslashes($row_Recordset1['lastname']); ?>"></td>
<td><input name="email[]" type="text" value="<?php echo stripslashes($row_Recordset1['email']); ?>"></td>
</tr>
<?php

} //while
?>
</table>
<p><input type="submit" name="submit" value="Submit"></p>
</form>
<?php DoShowPageNavigation(); ?>
</body>
</html>
<?php
mysql_free_result($query_Recordset1); //always free when done with query or you get scrambled results!
}//if submit
}//func DoShowForm

//don't have any characters after tthe following close php tag!
?>