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
– RiggsFolly, Commented Dec 4, 2015 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
– Bahamit, Commented Dec 4, 2015 at 11:25
You could simply remove prepare and execute and replace them with ->query But again the manual is your friend. its in many languages — RiggsFolly
– RiggsFolly, Commented Dec 4, 2015 at 11:26
Thank you, I am going to favorite the manual from here on out :) — Bahamit
– Bahamit, Commented Dec 4, 2015 at 11:32
You may also want to refer to this stackoverflow.com/questions/15754502/… — Nelson Owalo
– Nelson Owalo, Commented Dec 4, 2015 at 11:38

Saty · Accepted Answer · 2015-12-04 11:12:44Z

2

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

answered Dec 4, 2015 at 11:12

Saty

22.5k7 gold badges35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RiggsFolly · Accepted Answer · 2015-12-04 11:20:28Z

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 4, 2015 at 11:20

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

answered Dec 4, 2015 at 11:08

Nelson Owalo

2,41423 silver badges39 bronze badges

9 Comments

RiggsFolly Over a year ago

Hope you dont mind, just remove the unnecessary double quotes around prepare($sqlUPDATE)

Nelson Owalo Over a year ago

@RiggsFolly - i didnt see that, ok, lemme go through that once more

Saty Over a year ago

lastLatt= :lastLatt WHERE name= :name;" semicolumn missing

RiggsFolly Over a year ago

Also fixed the missing/misplaced semi colon

Nelson Owalo Over a year ago

@Saty appreciated, never sifted through the SO's code, just pointed out mistakes. I never realised he was so off...

|

Collectives™ on Stack Overflow

PHP/MySQL PDO/Update Rows

2 Answers 2

Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related