PDO mysql multi update need help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
trying2use
Forum Newbie
Posts: 6
Joined: Thu Jan 06, 2011 11:06 pm

PDO mysql multi update need help

Post by trying2use »

I can update name but how do I update lastname from the input statement below?
I have multiple row in mysql table
need help with the code
Please HELP!!! Thanks alot in advance

Code: Select all

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

// Connect to server and select databse.
$db = new PDO('mysql:host=' . $host . ';dbname=' . $db_name, $username, $password);

// If there is an update, process it.
if (isset($_POST['submit'], $_POST['name']) && is_array($_POST['name'])) {
    $stmt = $db->prepare("UPDATE `$tbl_name` SET `name`=:name WHERE id=:id");
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->bindParam(':name', $name, PDO::PARAM_STR);
    foreach ($_POST['name'] as $id => $name) {
        $stmt->execute();
    }
    echo '<h1>Updated the records.</h1>';
}

// Print the form.
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF']) . '" method="post">';
foreach ($db->query("SELECT `id`, `name`, `lastname` FROM `$tbl_name` ORDER BY `name`") as $row) {
    echo '<input type="text" name="name[' . (int)$row['id'] . ']" value="'
        . htmlspecialchars($row['name']) . '" /><input type="text" lastname="lastname[' . (int)$row['id'] . ']" value="'
        . htmlspecialchars($row['lastname']) . '" /><br />';;
   
}
echo '<input type="submit" name="submit" value="Update" /></form>';
?>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: PDO mysql multi update need help

Post by social_experiment »

Try

Code: Select all

<?php $stmt = $db->prepare("UPDATE `$tbl_name` SET `name`=:name, `lastname`=:lastname WHERE id=:id"); ?>
Hth.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
trying2use
Forum Newbie
Posts: 6
Joined: Thu Jan 06, 2011 11:06 pm

Re: PDO mysql multi update need help

Post by trying2use »

When I try
<?php $stmt = $db->prepare("UPDATE `$tbl_name` SET `name`=:name, `lastname`=:lastname WHERE id=:id"); ?>
I get Undefined index: lastname
I've tried the code below but nothing updates

Code: Select all

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

// Connect to server and select databse.
$db = new PDO('mysql:host=' . $host . ';dbname=' . $db_name, $username, $password);

// If there is an update, process it.
if (isset($_POST['submit'], $_POST['name']) && is_array($_POST['name'])) {
    $stmt = $db->prepare("UPDATE `$tbl_name` SET `name`=:name `lastname`=:lastname WHERE id=:id");
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->bindParam(':name', $name, PDO::PARAM_STR);
    $stmt->bindParam(':lastname', $lastname, PDO::PARAM_STR);
    foreach ($_POST['name'] as $id => $name) {
        $stmt->execute();
    }
    echo '<h1>Updated the records.</h1>';
}

// Print the form.
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF']) . '" method="post">';
foreach ($db->query("SELECT `id`, `name`, `lastname` FROM `$tbl_name` ORDER BY `name`") as $row) {
    echo '<input type="text" name="name[' . (int)$row['id'] . ']" value="'
        . htmlspecialchars($row['name']) . '" /><input type="text" lastname="lastname[' . (int)$row['id'] . ']" value="'
        . htmlspecialchars($row['lastname']) . '" /><br />';
   
}
echo '<input type="submit" name="submit" value="Update" /></form>';
?> 
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: PDO mysql multi update need help

Post by social_experiment »

Code: Select all

<?php $stmt = $db->prepare("UPDATE `$tbl_name` SET `name`=:name `lastname`=:lastname WHERE id=:id"); ?> 
In this code you forgot the comma
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
trying2use
Forum Newbie
Posts: 6
Joined: Thu Jan 06, 2011 11:06 pm

Re: PDO mysql multi update need help

Post by trying2use »

I inserted the comma now I'm getting a notice Array to string conversion in line 15
how do I post lastname I can update name but lastname erases and does not update
seems like I missing a post but i don't know how to do that.
Thanks
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: PDO mysql multi update need help

Post by social_experiment »

Have you tried using 2 statements instead of one?

Code: Select all

<?
$stmt1 = $db->prepare("UPDATE `$tbl_name` SET `name`=:name WHERE id=:id");
$stmt2 = $db->prepate("UPDATE `$tbl_name` SET `lastname`=:lastname WHERE id=:id");
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
trying2use
Forum Newbie
Posts: 6
Joined: Thu Jan 06, 2011 11:06 pm

Re: PDO mysql multi update need help

Post by trying2use »

I've tried $stmt = $db->prepare("UPDATE `$tbl_name` SET `name`=:name WHERE id=:id");
$stmt = $db->prepare("UPDATE `$tbl_name` SET `lastname`=:lastname WHERE id=:id");
but got Invalid parameter number: number of bound variables does not match number of tokens :banghead:
trying2use
Forum Newbie
Posts: 6
Joined: Thu Jan 06, 2011 11:06 pm

Re: PDO mysql multi update need help

Post by trying2use »

still can not get it to work :crazy:
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: PDO mysql multi update need help

Post by social_experiment »

I don't know much about PDO but i got a query going with the code below, it updates perfectly.

Code: Select all

<?php
$query = "UPDATE image_tbl SET file1='10101010.jpg', file2='10101010.jpg', 
		file3='10101010.jpg', file4='10101010.jpg' WHERE id='1'";		
		$affected = $dbc->exec($query);
		echo $affected .' rows changed';
?>
Hth
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
trying2use
Forum Newbie
Posts: 6
Joined: Thu Jan 06, 2011 11:06 pm

Re: PDO mysql multi update need help

Post by trying2use »

I'm done with this code went to something else
Thanks for trying
Post Reply