PHP/MySQL PDO/Update Rows

Question

I'm here trying to update my DB rows without deleting/creating new ones all the time. Currently, my DB creates new entries everytime I run this block of code. Instead of spamming my DB, I just want to change some of the values.

     <?php
try {
    $conn = new PDO("mysql:host=localhost;port=3306;dbname=dbname", Username, password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 }
catch(PDOException $e){
echo "Connection failed: " . $e->getMessage();
}

if(isset($_POST['mUsername']))
{
    $mUsername = urldecode($_POST['mUsername']);
    $mEvent = urldecode($_POST['mEvent']);
    $mChat = urldecode($_POST['mChat']);
    $mlongitude = urldecode($_POST['mlongitude']);
    $mlatitude = urldecode($_POST['mlatitude']);

    $sqlUPDATE = "UPDATE users 
                        SET lastEvent=:lastEvent, lastChat=:lastChat,
                            lastLong=:lastLong, lastLatt=:lastLatt 
                      WHERE name=:name";

$stmt = $conn->prepare($sqlUPDATE);
$stmt->bindParam(':lastEvent', $mEvent);
$stmt->bindParam(':lastChat', $mChat);
$stmt->bindParam(':lastLong', $mlongitude);
$stmt->bindParam(':lastLatt', $mlatitude);
$stmt->bindParam(':name', $mUsername);
$stmt->execute();
}
echo "successfully updated";
?>

My assumption is my final line, the $results area. I believe it's just treating this an a new entry instead of an update. How do I go about just replacing values? some values will not change, like the username, and sometimes longitude/latitude won't need to be changed. Would that have to be a separate query, should I split this in to two scripts? Or could I just enter a blank, null value? Or would that end up overwriting the ACTUAL last coordinates, leaving me with null values? Looking for any help or guides or tutorials. Thank you all in advance.

I am tempted to say RTM! You are preparing a query that has nothing to prepare i.e. No `?` parameters or `names` parameters — RiggsFolly, Dec 04 '15 at 11:06
Sorry, I am very beginner in MySQL\pdo and I took my original (insert) code and tried to modify it to update — Bahamit, Dec 04 '15 at 11:25
You could simply remove `prepare` and `execute` and replace them with `->query` [But again the manual is your friend.](http://php.net/manual/en/book.pdo.php) its in many languages — RiggsFolly, Dec 04 '15 at 11:26
Thank you, I am going to favorite the manual from here on out :) — Bahamit, Dec 04 '15 at 11:32
You may also want to refer to this http://stackoverflow.com/questions/15754502/mysql-creates-a-new-row-instead-of-updating-existing — Nelson Owalo, Dec 04 '15 at 11:38

score 2 · Answer 1 · edited Dec 04 '15 at 11:20

2

When using prepared statements, you should also make a habbit of following the set rules. Use named parameters. Try this:

if(isset($_POST['mUsername']))
{
    $mUsername = urldecode($_POST['mUsername']);
    $mEvent = urldecode($_POST['mEvent']);
    $mChat = urldecode($_POST['mChat']);
    $mlongitude = urldecode($_POST['mlongitude']);
    $mlatitude = urldecode($_POST['mlatitude']);

    $sqlUPDATE = "UPDATE users SET lastEvent= :lastEvent, lastChat= :lastChat, lastLong= :lastLong, lastLatt= :lastLatt WHERE name= :name";
    $q = $conn->prepare($sqlUPDATE);
    $results = $q->execute(array(':name'=>$mUsername, ':lastEvent'=>$mEvent, ':lastChat'=>$mChat, ':lastLong'=>$mlongitude, ':lastLatt'=>$mlatitude));
}

edited Dec 04 '15 at 11:20

RiggsFolly

93,638
21
103
149

answered Dec 04 '15 at 11:08

Nelson Owalo

2,324
18
37

Hope you dont mind, just remove the unnecessary double quotes around `prepare($sqlUPDATE)` – RiggsFolly Dec 04 '15 at 11:17
@RiggsFolly - i didnt see that, ok, lemme go through that once more – Nelson Owalo Dec 04 '15 at 11:18
2

`lastLatt= :lastLatt WHERE name= :name;"` semicolumn missing – Saty Dec 04 '15 at 11:19
2

Also fixed the missing/misplaced semi colon – RiggsFolly Dec 04 '15 at 11:20
@Saty its been noted and fixed by RiggsFolly – Nelson Owalo Dec 04 '15 at 11:23
1

@Saty appreciated, never sifted through the SO's code, just pointed out mistakes. I never realised he was so off... – Nelson Owalo Dec 04 '15 at 11:28
I'm still creating new rows in my database :| – Bahamit Dec 04 '15 at 11:31
@Saty I may have identified the problem, maybe I should be using my primary key as the reference, instead of the username? and I will edit the original post with my full PHP code. – Bahamit Dec 04 '15 at 11:36
Wow! It works! My issue wasn't from the code, it was from my server. For whatever reason the file didn't overwrite itself. Thank you so much for the help guys! – Bahamit Dec 04 '15 at 12:27

score 2 · Accepted Answer · answered Dec 04 '15 at 11:12

lots of syntax error in your code. It is simple to use bindParam

$sqlUPDATE = "UPDATE users 
                        SET lastEvent=:lastEvent, lastChat=:lastChat,
                            lastLong=:lastLong, lastLatt=:lastLatt 
                      WHERE name=:name";// you forget to close statement in your code

$stmt = $conn->prepare($sqlUPDATE);
$stmt->bindParam(':lastEvent', $mEvent);
$stmt->bindParam(':lastChat', $mChat);
$stmt->bindParam(':lastLong', $mlongitude);
$stmt->bindParam(':lastLatt', $mlatitude);
$stmt->bindParam(':name', $mUsername);
$stmt->execute();

read http://php.net/manual/en/pdostatement.bindparam.php

PHP/MySQL PDO/Update Rows

2 Answers2