script not working to update/delete database records

webguync · June 17, 2009

I have two scripts I am trying. one is supposed to update records in the MySQL database and the other is supposed to delete a record, after checking it off in a checkbox. neither is working. The problem isn't with the connection to the database or displaying the records in an html table. This all works fine.

here are the scripts.

*****update records*****

<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // 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();
?>

*****delete records*****

<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // 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=mysql_num_rows($result);

?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF"> </td>
<td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
<?
// Check if delete button active, start this
if($delete){
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);
}

// if successful redirect to delete_multiple.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete.php\">";
}

elseif(!$result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete.php\">";
}
}
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

any ideas on why the scripts might not be working or how I can debug?

UQ13A · June 17, 2009

try this

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

Not tested im kind of a new at php ::)

pkedpker · June 17, 2009

// Check if button name "Submit" is active, do this

if($Submit){

will that work?

Submit is using form method "POST" (same with delete)

<form name="form1" method="post" action="">

meaning it should be like isset($_POST['submit'])

also if($delete){ has to be isset($_POST['delete')

then a bad coding practice..

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

(same goes to delete also done the same bad way)

// if successful redirect to delete_multiple.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete.php\">";
}

elseif(!$result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete.php\">";
}

is outside the main if statement that has the submit/delete.. meaning it will never be called when submit never works.. (sounds good but.. why still parse it? kinda stupid waste of speed

try this

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

Not tested im kind of a new at php ::)

no he did that query correctly thats how I'd do it without any AND's..

webguync · June 17, 2009

how should I do this syntax. Currently I am getting an error

if isset($_POST['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);
}

also what would this be the best way to display a successful update?

[php

if($result1){

echo ("Update successfull");

}

[/code]

pkedpker · June 17, 2009

this works for me.. look for my news updates

mysql_query("UPDATE news SET title='$title', news='$news', name='$name', date='$date' WHERE id=$id") or die(mysql_error());

looks exactly like yours minus the loops etc..

I ment look here is what I ment with bad if statements.. (i know im a bad talker but im a good coder)

BAD:

if isset($_POST['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){
echo ("Update successfull");
}

vs

GOOD

if isset($_POST['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){
echo ("Update successfull");
}
}

BTW HERE this might look ugly then your old one but if it doesn't work then something up with your computer seriously check it.

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

to

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

webguync · June 17, 2009

I am getting an error. here is my latest

<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // Database name
$tbl_name=""; // 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 isset($_POST['submit']{
for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET name='".$name[$i]."', lastname='".$lastname[$i]."', email='$email[$i]'"; 
$result1=mysql_query($sql1);

if($result1){
echo ("Update successfull");
}
}
mysql_close();
?>

pkedpker · June 17, 2009

I know I didn't complete it and pressed Post too quickly I edited it after look up at my new edited one..

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

webguync · June 17, 2009

still get an error. It's in this part

if isset($_POST['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){
echo ("Update successfull");
}
}

pkedpker · June 17, 2009

you see the blue colors are what u do.. red are static..

the last one WHERE id='$id[$i]' isn't blue.. so yah thats a bit screwed up :-\

try

<?php //color check
$sql1="UPDATE $tbl_name SET name='".$name[$i]."', lastname='".$lastname[$i]."', email='".$email[$i]."' WHERE id='".$id[$i]."'"; 
?>

webguync · June 18, 2009

still having problems, getting a blank white page. Here is my code

<?php
ini_set("display_errors","1");
ERROR_REPORTING(E_ALL);
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // 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 isset($_POST['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){
echo ("Update successfull");
}
}
}
mysql_close();
?>

webguync · June 18, 2009

anyone see a problems w/ the code I posted?

EchoFool · June 18, 2009

Yes your not diagnosing your SQL

change your queries:

<?php
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>

To:

<?php
$result = mysql_query("SELECT * FROM $tbl_name") or die(mysql_error());
?>

<?php
$sql1="UPDATE $tbl_name SET name='".$name[$i]."', lastname='".$lastname[$i]."', email='".$email[$i]."' WHERE id='".$id[$i]."'"; 
$result1=mysql_query($sql1);
?>

To

 <?php
$result1= mysql_query("UPDATE $tbl_name SET name='".$name[$i]."', lastname='".$lastname[$i]."', email='".$email[$i]."' WHERE id='".$id[$i].'"") or die(mysql_error());
?>

Once done tell me if you get any new errors

webguync · June 18, 2009

not sure where the error is, b/c It isn't displaying, but I am still not getting a display. My latest code is.

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // Database name
$tbl_name=""; // 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");

$result = mysql_query("SELECT * FROM $tbl_name") or die(mysql_error());

// 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 isset($_POST['submit']{
for($i=0;$i<$count;$i++){

$result1= mysql_query("UPDATE $tbl_name SET name='".$name[$i]."', lastname='".$lastname[$i]."', email='".$email[$i]."' WHERE id='".$id[$i]."") or die(mysql_error());
if($result1){
echo ("Update successfull");
}
}
}
mysql_close();
?>

Hybride · June 18, 2009

if isset($_POST['submit']{

is missing parenthesis.

if (isset($_POST['submit'])){

webguync · June 18, 2009

thanks, I don't get an error anymore, but the update still doesn't work. When I make a change in the form and hit submit, it just goes back to it's original data.

EchoFool · June 18, 2009

Your update is below your select table so any data shown before an update wont show till the next reload don't forget?

webguync · June 18, 2009

it's not updating at all though. Refreshing doesn't do anything.

djjjozsi · June 20, 2009

i'm using this code to update tables, lets try that:

<?php

/*

Updater code for multiple records
Types: checkbox , textfield, textarea

directives used:
magic qoutes= disabled
register globals=OFF
*/

/* For the following details, ask your server vendor  */
$dbhost = "xxxxxxxxxx";
$dbuser = "xxxxxxxxxx";
$dbpass = "xxxxxxxxxx";
$dbname = "xxxxxxxxxx";

function stripslashes2( $string ) {
    if(get_magic_quotes_gpc()) {
        return stripslashes($string);
    } else {
        return $string;
    }
}


mysql_pconnect( $dbhost, $dbuser, $dbpass ) or die ( "Unable to connect to MySQL server" );
mysql_select_db( "$dbname" );
mysql_query( "SET NAMES utf8" ); // Set this to latin2 if you're using latin2 collacation in your database

$tablename = "tablename";
$idfield = "id";
/*mysql field name /      Fieldd title, field type, is required?*/
$fields["name"]=    array( "name", "textfield", "required" );
$fields["lastname"]=    array( "lastname", "textfield", "required" );
$fields["email"]=    array( "email", "textfield", "required" );

/*
-- 
-- Table structure for table `tablename`
-- 

CREATE TABLE IF NOT EXISTS `tablename` (
`id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL ,
  `lastname` varchar(255) NOT NULL ,
  `email` varchar(255) NOT NULL ,

  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

*/


if ( !empty( $_POST["Update"] ) ) {
    if ( empty( $_POST["keys"] ) )
        die( "Update identifer is empty" );

    $rows = 1;
    foreach( $_POST["keys"] AS $ids ) {
        $sql = "update $tablename ";
        foreach( $fields AS $key => $val ) {
            if ( empty( $_POST["$key"]["$ids"] ) AND !empty( $val[2] ) )
                $errors[] = "$val[0] in the $rows row is requied";

            $updated_value = isset( $_POST["$key"]["$ids"] )? mysql_real_escape_string( stripslashes2($_POST["$key"]["$ids"]) ):'';
            $set[] = sprintf( "`%s`='%s'" , $key , $updated_value );
        }
        $main[] = $sql . " SET " . implode( "," , $set ) . sprintf( " WHERE $idfield='%d'" , $ids ) ;
        unset( $set );
        $rows++;
    }

    if ( empty( $errors ) ) {
        foreach( $main AS $query ) {
            mysql_query( $query ) or die( mysql_error() );
        }
    header("Location: updater.php?ok=1");
    }
    
}

?><!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>Multile updater</title>
</head>
<body><?php
if ( !empty( $_GET["ok"] ) )
    print "Updated succesfully";

$sqlquery = "select * from $tablename";
$result_query = mysql_query( $sqlquery ) or die( mysql_error() . " error in query:" . htmlspecialchars( $sqlquery ) );

if(!empty($errors))
{  array_unique( $errors );
  print implode( "<br />" , $errors );
}

echo '<form action="updater.php" method="post" name="Form">';
print "<table><tr><th> # </th>";

foreach( $fields AS $key => $val )
print "<th>$val[0]</th>";
print "</tr>";

if ( mysql_num_rows( $result_query ) > 0 ) {
    $rownumber = 1;
    while ( $rows_query = mysql_fetch_assoc( $result_query ) ) {
        print "<tr>\n";
        print "<td>$rownumber.</td>";
        print "<input type=\"hidden\" name=\"keys[]\" value=\"{$rows_query[$idfield]}\">";
        foreach( $fields AS $key => $value ) {
            if ( $value[1] == "textfield" )
                echo "<td><input name=\"{$key}[{$rows_query[$idfield]}]\" type=\"text\" value=\"" . ( isset( $rows_query["$key"] )?htmlspecialchars( $rows_query["$key"] ):"" ) . "\"></td>\n";
            elseif ( $value[1] == "checkbox" ) {
                echo "<td><input name=\"{$key}[{$rows_query[$idfield]}]\" type=\"checkbox\" value=\"1\"" . ( !empty( $rows_query["$key"] )?" checked=\"checked\"":"" ) . "></td>\n";
            } elseif ( $value[1] == "textarea" ) {
                echo "<td> <textarea name=\"{$key}[{$rows_query[$idfield]}]\" cols=\"45\" rows=\"5\" wrap=\"virtual\">" . ( isset( $rows_query["$key"] )?htmlspecialchars( $rows_query["$key"] ):"" ) . "</textarea></td>\n";
            }
        }
        print "</tr>\n";
        $rownumber++;
    }
} else
    echo "No result in query table ...";

print "</table>\n";
echo '<input name="Update" type="submit" value="Update">
</form>
';

?>

</body></html>

webguync · June 22, 2009

that seems to be working for updating, so thanks. Could I add another column with check marks to delete also or would I need a separate form for that?

djjjozsi · June 22, 2009

nop. its using arrays to define the field types. You can simple add a new field type, called: delete_checkbox. i've done with this code:

<?php

/*
Generated by:
http://phpcode.hu/teszt/multiple_updater/

Updater code for multiple records
Types: checkbox , textfield, textarea

directives used:
magic qoutes= disabled
register globals=OFF
*/

/* For the following details, ask your server vendor  */
$dbhost = "xxxxxxxxxx";
$dbuser = "xxxxxxxxxx";
$dbpass = "xxxxxxxxxx";
$dbname = "xxxxxxxxxx";

function stripslashes2( $string ) {
    if(get_magic_quotes_gpc()) {
        return stripslashes($string);
    } else {
        return $string;
    }
}


mysql_pconnect( $dbhost, $dbuser, $dbpass ) or die ( "Unable to connect to MySQL server" );
mysql_select_db( "$dbname" );
mysql_query( "SET NAMES utf8" ); // Set this to latin2 if you're using latin2 collacation in your database

$tablename = "tablename";
$idfield = "id";
/*mysql field name /      Fieldd title, field type, is required?*/
$fields["name"]=    array( "name", "textfield", "required" );
$fields["lastname"]=    array( "lastname", "textfield", "required" );
$fields["email"]=    array( "email", "textfield", "required" );
$fields["DeleteField"]=    array( "Delete Field", "delete_checkbox", "" );

/*
-- 
-- Table structure for table `tablename`
-- 

CREATE TABLE IF NOT EXISTS `tablename` (
`id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL ,
  `lastname` varchar(255) NOT NULL ,
  `email` varchar(255) NOT NULL ,

  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

*/


if ( !empty( $_POST["Update"] ) ) {
    if ( empty( $_POST["keys"] ) )
        die( "Update identifer is empty" );

    $rows = 1;
    $deleted=0;
    $updated=0;
    foreach( $_POST["keys"] AS $ids ) {

        if(empty($_POST["delete"][$ids]))
        {
        $sql = "update $tablename ";
        foreach( $fields AS $key => $val ) {
            if ( empty( $_POST["$key"]["$ids"] ) AND !empty( $val[2] ) )
                $errors[] = "$val[0] in the $rows row is requied";

            $updated_value = isset( $_POST["$key"]["$ids"] )? mysql_real_escape_string( stripslashes2($_POST["$key"]["$ids"]) ):'';
            $set[] = sprintf( "`%s`='%s'" , $key , $updated_value );
        }
        $main[] = $sql . " SET " . implode( "," , $set ) . sprintf( " WHERE $idfield='%d'" , $ids ) ;
        unset( $set );
        $rows++;
        $updated++;

        }
        else
        {
        $main[]=sprintf("DELETE from $tablename WHERE $idfield='%d'" , $ids );
        $deleted++;
        }
    }

    if ( empty( $errors ) ) {
        foreach( $main AS $query ) {
            mysql_query( $query ) or die( mysql_error() );
        }
    } else {
        array_unique( $errors );
        print implode( "<br />" , $errors );
    }

    // header("Location: updater.php?ok=1");

    if(!empty($updated))
    print "Updated row(s): $updated ";

    if(!empty($deleted))
    print "Deleted row(s): $deleted ";


}

?><!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>Multile updater</title>
</head>
<body><?php
if ( !empty( $_GET["ok"] ) )
    print "Updated succesfully";

$sqlquery = "select * from $tablename";
$result_query = mysql_query( $sqlquery ) or die( mysql_error() . " error in query:" . htmlspecialchars( $sqlquery ) );

echo '<form action="updater.php" method="post" name="Form">';
print "<table><tr><th> # </th>";

foreach( $fields AS $key => $val )
print "<th>$val[0]</th>";
print "</tr>";

if ( mysql_num_rows( $result_query ) > 0 ) {
    $rownumber = 1;
    while ( $rows_query = mysql_fetch_assoc( $result_query ) ) {
        print "<tr>\n";
        print "<td>$rownumber.</td>";
        print "<input type=\"hidden\" name=\"keys[]\" value=\"{$rows_query[$idfield]}\">";
        foreach( $fields AS $key => $value ) {
            if ( $value[1] == "textfield" )
                echo "<td><input name=\"{$key}[{$rows_query[$idfield]}]\" type=\"text\" value=\"" . ( isset( $rows_query["$key"] )?htmlspecialchars( $rows_query["$key"] ):"" ) . "\"></td>\n";
            elseif ( $value[1] == "checkbox" ) {
                echo "<td><input name=\"{$key}[{$rows_query[$idfield]}]\" type=\"checkbox\" value=\"1\"" . ( !empty( $rows_query["$key"] )?" checked=\"checked\"":"" ) . "></td>\n";
            } elseif ( $value[1] == "textarea" ) {
                echo "<td> <textarea name=\"{$key}[{$rows_query[$idfield]}]\" cols=\"45\" rows=\"5\" wrap=\"virtual\">" . ( isset( $rows_query["$key"] )?htmlspecialchars( $rows_query["$key"] ):"" ) . "</textarea></td>\n";
            } elseif ( $value[1] == "delete_checkbox" ) {
                echo "<td><input name=\"delete[{$rows_query[$idfield]}]\" type=\"checkbox\" value=\"1\"></td>\n";




        }
        print "</tr>\n";
        $rownumber++;
    }
} else
    echo "No result in query table ...";

print "</table>\n";
echo '<input name="Update" type="submit" value="Update">
</form>
';

?>

</body></html>

webguync · June 22, 2009

I get an error with that code. What all do I need to add besides

$fields["DeleteField"]=    array( "Delete Field", "delete_checkbox", "" );

I can just add to the previous code which I know works.

Also, I noticed that you get the update successful even if you didn't update anything (whenever you click the button). Is there a way to check to see if anything was updated prior to printing that msg, and if not stating "you haven't updated anything"?

djjjozsi · June 22, 2009

hello,

if you see the whole code i posted you will see that the code has updated to be able to handle the delete_checkbox option when the form is building.

This code is counting then how many rows has updated, and how many of them has deleted.

now i created a generator for this code , here:

http://phpcode.hu/teszt/multiple_updater

If you then have a parse error in the generated code, tell me.

webguync · June 22, 2009

yea, I get an error, but the error reporting code I add to the script, doesn't tell me where the error is unfortunately.

djjjozsi · June 22, 2009

i found the problem now i've uploaded a wrong modification.

Try to refresh the code now.

webguync · June 22, 2009

it looks like the code deletes now, but no longer updates. is it supposed to do both or do I need to use two forms (one to delete and one to update)?

here is my code

<?php

/*
Generated by:
http://phpcode.hu/teszt/multiple_updater/

Updater code for multiple records
Types: checkbox , textfield, textarea

directives used:
magic qoutes= disabled
register globals=OFF
*/

/* For the following details, ask your server vendor  */
$dbhost = "localhost";
$dbuser = "";
$dbpass = "";
$dbname = "";

function stripslashes2( $string ) {
    if(get_magic_quotes_gpc()) {
        return stripslashes($string);
    } else {
        return $string;
    }
}


mysql_pconnect( $dbhost, $dbuser, $dbpass ) or die ( "Unable to connect to MySQL server" );
mysql_select_db( "$dbname" );
mysql_query( "SET NAMES utf8" ); // Set this to latin2 if you're using latin2 collacation in your database

$tablename = "test_mysql";
$idfield = "id";
/*mysql field name /      Fieldd title, field type, is required?*/
$fields["id"]=    array( "id", "textfield", "required" );
$fields["name"]=    array( "Name", "textfield", "required" );
$fields["lastname"]=    array( "Last Name", "textfield", "required" );
$fields["email"]=    array( "email", "textfield", "required" );
$fields["delete"]=    array( " Delete", "delete_checkbox", "" );


if ( !empty( $_POST["Update"] ) ) {
    if ( empty( $_POST["keys"] ) )
        die( "Update identifer is empty" );

    $rows = 1;
    $deleted=0;
    $updated=0;
    foreach( $_POST["keys"] AS $ids ) {

        if(empty($_POST["delete"][$ids]))
        {
            $sql = "update $tablename ";
            foreach( $fields AS $key => $val ) {
            if($val[1]!="delete_checkbox")
            {
                if ( empty( $_POST["$key"]["$ids"] ) AND !empty( $val[2] ) )
                    $errors[] = "$val[0] in the $rows row is requied";

                $updated_value = isset( $_POST["$key"]["$ids"] )? mysql_real_escape_string( stripslashes2($_POST["$key"]["$ids"]) ):'';
                $set[] = sprintf( "`%s`='%s'" , $key , $updated_value );
            }
        }
        $main[] = $sql . " SET " . implode( "," , $set ) . sprintf( " WHERE $idfield='%d'" , $ids ) ;
        unset( $set );
        $rows++;
        $updated++;

        }
        else
        {
        $main[]=sprintf("DELETE from $tablename WHERE $idfield='%d'" , $ids );
        $deleted++;
        }
    }

    if ( empty( $errors ) ) {
        foreach( $main AS $query ) {
            mysql_query( $query ) or die( mysql_error() );
        }
    } else {
        array_unique( $errors );
        print implode( "<br />" , $errors );
    }

    // header("Location: Update_Delete?ok=1");

    if(!empty($updated))
    print "Updated row(s): $updated ";

    if(!empty($deleted))
    print "Deleted row(s): $deleted ";


}

?><!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>Multile updater</title>
</head>
<body><?php
if ( !empty( $_GET["ok"] ) )
    print "Updated succesfully";

$sqlquery = "select * from $tablename";
$result_query = mysql_query( $sqlquery ) or die( mysql_error() . " error in query:" . htmlspecialchars( $sqlquery ) );

echo '<form action="delete.php" method="post" name="Form">';
print "<table><tr><th> # </th>";

foreach( $fields AS $key => $val )
print "<th>$val[0]</th>";
print "</tr>";

if ( mysql_num_rows( $result_query ) > 0 ) {
    $rownumber = 1;
    while ( $rows_query = mysql_fetch_assoc( $result_query ) ) {
        print "<tr>\n";
        print "<td>$rownumber.</td>";
        print "<input type=\"hidden\" name=\"keys[]\" value=\"{$rows_query[$idfield]}\">";
        foreach( $fields AS $key => $value ) {
            if ( $value[1] == "textfield" )
                echo "<td><input name=\"{$key}[{$rows_query[$idfield]}]\" type=\"text\" value=\"" . ( isset( $rows_query["$key"] )?htmlspecialchars( $rows_query["$key"] ):"" ) . "\"></td>\n";
            elseif ( $value[1] == "checkbox" ) {
                echo "<td><input name=\"{$key}[{$rows_query[$idfield]}]\" type=\"checkbox\" value=\"1\"" . ( !empty( $rows_query["$key"] )?" checked=\"checked\"":"" ) . "></td>\n";
            } elseif ( $value[1] == "textarea" ) {
                echo "<td> <textarea name=\"{$key}[{$rows_query[$idfield]}]\" cols=\"45\" rows=\"5\" wrap=\"virtual\">" . ( isset( $rows_query["$key"] )?htmlspecialchars( $rows_query["$key"] ):"" ) . "</textarea></td>\n";
            } elseif ( $value[1] == "delete_checkbox" ) {
                echo "<td><input name=\"delete[{$rows_query[$idfield]}]\" type=\"checkbox\" value=\"1\"></td>\n";
            }



        }
        print "</tr>\n";
        $rownumber++;
    }
} else
    echo "No result in query table ...";

print "</table>\n";
echo '<input name="Update" type="submit" value="Update">
</form>
';

?>

</body></html

Sign In

script not working to update/delete database records

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information