Updating Mulitple Rows in MYSQL Database

[mitle]

Hi Forum

 

I have this script below which i thought would update multiple rows in my database, but all it does is refresh and revert back to its original data. Im guessing my problem is in my update script but  I cant seem to find where I have gone wrong. Any help would be greatly appreciated.

<?php
$host="*****";
$username="*****";
$password="*****";
$db_name="*****";
$tbl_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['pageorder']) && is_array($_POST['pageorder'])) {
    $stmt = $db->prepare("UPDATE `$tbl_name` SET `pageorder`=:pageorder WHERE id=:id");
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->bindParam(':pageorder', $pageorder, PDO::PARAM_STR);
    foreach ($_POST['pageorder'] as $id => $pageorder) {
        $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`, `pageorder`, `pagetitle`, `linklabel` FROM `$tbl_name` ORDER BY `pageorder`") as $row) {
    echo '<input type="text" pageorder="pageorder[' . (int)$row['id'] . ']" value="'. htmlspecialchars($row['pageorder']) . '" />';
    echo '<input type="text" pagetitle="pagetitle[' . (int)$row['id'] . ']" value="'. htmlspecialchars($row['pagetitle']) . '" />';
	    echo '<input type="text" linklabel="linklabel[' . (int)$row['id'] . ']" value="'. htmlspecialchars($row['linklabel']) . '" /><br />';
}
echo '<input type="submit" name="submit" value="Update" /></form>';
?> 

[denno020]

To update any information in the database you need to do "UPDATE (table name){columns_names - if only updating a couple of columns, otherwise this can be ommited if a value will be placed into every column} VALUES (values)";

 

Search for a tutorial on using UPDATE, and you should be set .

 

Denno

[mitle]

Hi Denno

 

Thanks for the reply. I have not found any useful tutorials on this at all. Can you provide me any links?

 

Thanks Again

[denno020]

http://www.tizag.com/sqlTutorial/sqlupdate.php

 

http://www.w3schools.com/sql/sql_update.asp

 

Did you honestly search?

[mitle]

Yeah sure did thanks for the links. Ill post the solution when i sort it out.

[QuickOldCar]

Here's the way I use to do either an insert or update

 

First I do a check if it exists, I do not want duplicates.

If already exists, then update the information keeping same id. For my case I want to update the information, but some may not want to, or just do a few fields.

 

If it doesn't exist, then insert as new (id not required because using id auto increment)

 

Of course you would need to change any values you needed different.

$con_add = mysql_connect("server","username","password");
        if (!$con_add) {
            die('Could not connect: ' . mysql_error());
        }
        mysql_select_db("databasename", $con_add);
$query_check = mysql_query("SELECT* FROM table WHERE post_title = '".$post_title."'");
        $check = mysql_num_rows($query_check);

$row = mysql_fetch_array($query_check);
$the_ID = $row['ID'];

if($check > 0) {
            mysql_query("SET NAMES 'utf8'");
            mysql_query("UPDATE table SET post_title='$escaped_post_title', title_2='$escaped_title', post_description='$escaped_description', post_keywords='$escaped_keywords', post_date='$my_date', post_modified='$my_date', post_date_gmt='$my_date', post_status='$the_status', post_name='$post_name', guid='./index.php?post=$post_name', link_rss='$feed_array' WHERE ID='$the_ID'");

        } else {

            mysql_query("SET NAMES 'utf8'");
            mysql_query("INSERT INTO posts (post_title, title_2, post_description, post_keywords, post_date, post_modified, post_date_gmt, post_status, post_name, guid, link_rss)
VALUES ('$escaped_post_title', '$escaped_title', '$escaped_description', '$escaped_keywords', '$my_date', '$my_date', '$my_date', '$the_status', '$post_name', ' ./index.php?post=$post_name', '$feed_array')");

        }
mysql_free_result($query_check);
mysql_close($con_add);