Dreamweaver Club Forums

Dreamweaver Club Forums (http://www.dreamweaverclub.com/forum//index.php)
-   PHP (http://www.dreamweaverclub.com/forum//forumdisplay.php?f=188)
-   -   Multiple Update Rows (http://www.dreamweaverclub.com/forum//showthread.php?t=46164)

lacsoncruz 09-19-2013 06:43 AM

PHP Multiple Update function not updating and gets the 1st row blank
 
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 Code:

<?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;
    }   
 }
?>

HTML Code:

<!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 Code:

<?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,$pageNum) {
    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,$pageNum)."'
            "
;
        
$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,$tableRowCount,$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:
    Code:

    javascript:void(document.location='http://validator.w3.org/check?charset=%28detect+automatically%29doctype=Inline&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:
    Code:

    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
PHP Code:

echo "<li><a href=\"processform.php?page=" $pn "\">" $pn "</a></li>\n"


jmichae3 10-09-2013 11:23 AM

change
PHP Code:

$_POST['submit'

to
PHP Code:

$_POST['Submit'

since you capitalized it in the form. or form will never work.

jmichae3 10-09-2013 11:28 AM

change
PHP Code:

if (0==$counter headerAboveEveryNRows)  { 

to
PHP Code:

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 Code:

<?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,$tableRowCount,$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,$pageNum) {
    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,$pageNum)."'
            "
;
        
$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,$pageNum)."'
            "
;
        
$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!
?>



All times are GMT. The time now is 10:57 PM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Copyright 2006 DreamweaverClub.com