Page 1 of 1

PDO mysql multi update need help

Posted: Thu Jan 06, 2011 11:11 pm
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>';
?>

Re: PDO mysql multi update need help

Posted: Fri Jan 07, 2011 11:57 am
by social_experiment
Try

Code: Select all

<?php $stmt = $db->prepare("UPDATE `$tbl_name` SET `name`=:name, `lastname`=:lastname WHERE id=:id"); ?>
Hth.

Re: PDO mysql multi update need help

Posted: Sat Jan 08, 2011 9:48 am
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>';
?> 

Re: PDO mysql multi update need help

Posted: Sun Jan 09, 2011 10:18 am
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

Re: PDO mysql multi update need help

Posted: Sun Jan 09, 2011 10:11 pm
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

Re: PDO mysql multi update need help

Posted: Mon Jan 10, 2011 6:28 am
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");
?>

Re: PDO mysql multi update need help

Posted: Mon Jan 10, 2011 8:15 am
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:

Re: PDO mysql multi update need help

Posted: Mon Jan 10, 2011 11:48 pm
by trying2use
still can not get it to work :crazy:

Re: PDO mysql multi update need help

Posted: Tue Jan 11, 2011 12:14 am
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

Re: PDO mysql multi update need help

Posted: Tue Jan 11, 2011 4:41 pm
by trying2use
I'm done with this code went to something else
Thanks for trying