Update Row using php pdo

[cwd]

I create a news section on my website and want the ability to update it if needed. I had it working using the old mysql method, but want to change it by using PDO.

I’ve tried a couple methods, but just seem to be missing something.
My database connection is stored in my header, that’s why it is not shown.

On method #1 The form pulls in data for me to update, but its pulling from the wrong row / id.

Here is method #1

[php]<?php
$post_title = “”;
$description = “”;
$id = isset($_POST[‘ud_id’]);

$query = $db->query(“SELECT title, description FROM htp_news WHERE id=’$id’”);

$post_title = $db->query(‘SELECT title FROM htp_news’)->fetchColumn();
$description = $db->query(‘SELECT description FROM htp_news’)->fetchColumn();

?>

">

Title: <input type=“text” name=“ud_title” value="<?php echo "$post_title"; ?>">

News Details:

<?php echo "$description"; ?>

<input type="submit" value="Update">
<input type="button" value="Cancel" onclick="window.location = '/admin'">
</div>

[/php]

[hr]

Here is method #2 which I like better because I’m using only on query but it is not pulling in any info into my form.

Method #2

[php]

<?php $sth = $db->prepare("SELECT id, title, description FROM htp_news WHERE id = :id"); $sth->bindValue(':id', isset($_POST['ud_id']), PDO::PARAM_INT); $sth->execute(); $row = $sth->fetch(PDO::FETCH_ASSOC); ?>

Title:

News Details:

<?php echo $row[0]['description']; ?>

<input type="submit" value="Update">
<input type="button" value="Cancel" onclick="window.location = '/admin'">
</div>

[/php]

With method #2 I also tried to bind the title and description, but still didn’t help bring in the data from my database row.

Any help would be appreciated. Thanks

[JimL]

Method 1

Vulnerable to sql injection, why are the 2 last queries there?

Method 2

[php]$sth->bindValue(’:id’, isset($_POST[‘ud_id’]), PDO::PARAM_INT);[/php]

You bind the return value of the isset function to the query. That means the query will see id 0 or 1 no matter what number you send in

[cwd]

Method #1
I had the extra queries there because that’s the only way I knew to get that variable.

Method #2
So are you saying I should not use isset? If I don’t I get an index notice.
Sorry not understanding fully.

[JimL]

Method 1
The first query should give you the data you want

Method 2
Then you arent posting the data you expect, you probably don’t want to run rhe query at all if the post variable is not set

You are atm running this query if the post variable is not set
"SELECT id, title, description FROM htp_news WHERE id = 0

And this if it is set
"SELECT id, title, description FROM htp_news WHERE id = 1

So it doesnt care about the actual value of the posted variable

[cwd]

So on method #1

[php]$post_title = “”;
$description = “”;
$id = isset($_POST[‘ud_id’]);

$query = $db->query(“SELECT title, description FROM htp_news WHERE id=’$id’”);

$post_title = // ? What would I do here?
$description = // ? What would I do here?[/php]

[JimL]

$query should be named $result, or similar

Check what it contains

But remember that you must refactor it to use prepared statement / bound variables

[cwd]

LOL I don’t know I feel like such a moron ??? . I’ve been trying to figure this out since 9 last night.
I think I should just stick with front end, but unfortunately I have to get this figured out.
Maybe I’ll just stick with old mysql that was so much easier for me. Well maybe not easier, but I had all this working at least.

Method #1

[php] <?php
$post_title = “”;
$description = “”;
$id = isset($_POST[‘ud_id’]);

$result = $db->query(“SELECT id title, description FROM htp_news WHERE id=’$id’”);

?>

<div class="row">

Title: <input type=“text” name=“ud_title” value="<?php echo $result = ("title"); ?>">

News Details:

<?php echo $result = ("description"); ?>

[/php]

I realize that just echos out the word title and description, but idk, Im so frustrated.

[JimL]

Do this after the query

[php]echo ‘

’;

var_dump($result);

die();[/php]
It will show you a nicely formatted output of the content of the variable $result

[cwd]

Okay after the var dump I saw that it wasn;t assigning an ID to the query.

So I got the query to assign an id by doing this:

[php]$id = ($_GET[‘id’]);[/php] or this [php] $id = isset($_GET[‘id’]) ? $_GET[‘id’] : NULL;[/php]

Now what would be the next step?

Echo out the title and description on my form? How would I do this?

[cwd]

Okay I got the first part working by using this code:

[php] <?php
$id = isset($_GET[‘id’]) ? $_GET[‘id’] : NULL;
$sth = $db->prepare(“SELECT id, title, description FROM htp_news WHERE id = ‘$id’”);

$sth->bindValue(’:id’, isset($_GET[‘ud_id’]), PDO::PARAM_INT);
$sth->execute();

$row = $sth->fetch(PDO::FETCH_ASSOC);

?>

Title:

News Details:

<?php echo $row ['description']; ?>

[/php]

Any thoughts to make it better?

[JimL]

You arent using the variable from the bind value line, so just remove it.

Also you must change to a parameterized query, atm you are vulnerable to sql injection

[cwd]

Like this:

[php]<?php
$id = isset($_GET[‘id’]) ? $_GET[‘id’] : NULL;
$sth = $db->prepare(“SELECT id, title, description FROM htp_news WHERE id = ‘$id’”);

$sth->bindValue(’:id’, isset($_GET[‘ud_id’]));
$sth->execute();

$row = $sth->fetch(PDO::FETCH_ASSOC);

?>[/php]

[JimL]

you shouldn’t insert the variable directly into the query
you are mixing up bindParam with bindValue
you are still binding a true/false value, which will not work for what you are doing
what’s the difference between $_GET[‘id’] and $_GET[‘ud_id’]?

have a look here

Why You Should Be Using PHP's PDO for Database Access

Guessing you should do something like this:

[php] <?php
$id = isset($_GET[‘id’]) ? $_GET[‘id’] : NULL;

$sth = $db->prepare(“SELECT id, title, description FROM htp_news WHERE id = :id”);
$sth->bindParam(’:id’, $id, PDO::PARAM_INT);
$sth->setFetchMode(PDO::FETCH_OBJ);
$sth->execute();

$article = $sth->fetch();
echo ‘

Article: ‘.$article->title.’ (’.$article->id.’)

’.$article->description;[/php]

Not sure what the ud_id variable is

[cwd]

The the ud_id variable was in the input line in my form. I removed it.

Here is my final working code. Please let me know if there is anything I can do better here.

[php]<?php

$id = isset($_GET[‘id’]) ? $_GET[‘id’] : NULL;
$sth = $db->prepare(“SELECT id, title, description FROM htp_news WHERE id = :id”);
$sth->bindParam(’:id’, $id, PDO::PARAM_INT);
$sth->setFetchMode(PDO::FETCH_OBJ);
$sth->execute();

$row = $sth->fetch();

?>

Title:

News Details:

<?php echo $row->description; ?>

<input type="submit" value="Update">
<input type="button" value="Cancel" onclick="window.location = '/admin'">
</div>

[/php]

Here is the action script “update-news.php” this I’m using to add the info into my database.

[php]<?php
include($_SERVER[‘DOCUMENT_ROOT’] . “/includes/database.php”);
// new data
$id = $_POST[‘id’];
$title = $_POST[‘title’];
$description = $_POST[‘description’];
// query
$sql = “UPDATE htp_news SET title=?, description=? WHERE id=?”;
$sth = $db->prepare($sql);
$sth->execute(array($title,$description,$id));
echo “The post has been updated.

Update another position.
”;
?>[/php]

Thanks for your help. Much appreciated.