Update row in database with php and Microsoft sql

Question

Hi im currently creating a blog, just to learn abit more about php, databases etc. Ive gotten so far that i want to be able to edit my posts. however i struggle to get it working...

this is what my code looks so far:

 <?php
 error_reporting(E_ALL); ini_set('display_errors', 1);
 $head =  $_POST['title'];
 $short = $_POST['short'];
$bread = $_POST['edit'];
$author = $_POST['author'];
$cat = $_POST['cat'];
$id = $_POST['id'];

 $db = new PDO('sqlsrv:server=localhost;Database=blog', '*****', '*********');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = 'UPDATE dbo.blog_posts SET (blog_title, blog_short, blog_post, blog_author, blog_category) VALUES (:head, :short, :bread, :author, :cat) WHERE blogID=$id';

$query = $db->prepare( $sql );
$query->execute( array(':head'=>$head, ':short'=>$short, ':bread'=>$bread, ':author'=>$author, ':cat'=>$cat ) );
header("Location: index.php");
?>

now i get the error msg:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[07002]: [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error' in C:\inetpub\wwwroot\dev\ny\post_edit.php:17 Stack trace: #0 C:\inetpub\wwwroot\dev\ny\post_edit.php(17): PDOStatement->execute(Array) #1 {main} thrown in C:\inetpub\wwwroot\dev\ny\post_edit.php on line 17

line 17 is the execute array

i have also tried:

 <?php
 error_reporting(E_ALL); ini_set('display_errors', 1);
 $head =  $_POST['title'];
 $short = $_POST['short'];
$bread = $_POST['edit'];
$author = $_POST['author'];
$cat = $_POST['cat'];
$id = $_POST['id'];

 $db = new PDO('sqlsrv:server=localhost;Database=blog', '*****', '*****');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = 'UPDATE dbo.blog_posts SET blog_title=(:head), blog_short=(:short), blog_post=(:bread), blog_author=(:author), blog_category=(:cat) WHERE blogID=:id';

$query = $db->prepare( $sql );
$query->execute( array(':head'=>$head, ':short'=>$short, ':bread'=>$bread, ':author'=>$author, ':cat'=>$cat ) );
header("Location: index.php");
?>

that also gives the same error, cant really find how the execute line needs to be changed.

Edit:

When using this code below i only get a blank page again.. :/

 <?php
 error_reporting(E_ALL); ini_set('display_errors', 1);
 $head =  $_POST['title'];
 $short = $_POST['short'];
$bread = $_POST['edit'];
$author = $_POST['author'];
$cat = $_POST['cat'];
$id = $_POST['id'];

 $db = new PDO('sqlsrv:server=localhost;Database=blog', '******', '*****');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = UPDATE `blog_posts` SET `blog_title` = :head, `blog_short` = :short, `blog_post`  = :bread, `blog_author` = :author, `blog_category` = :cat WHERE `blogID` = :id;

$query = $db->prepare( $sql );
$query->execute( array(':id'=>$id, ':head'=>$head, ':short'=>$short, ':bread'=>$bread, ':author'=>$author, ':cat'=>$cat ) );

header("Location: index.php");
?>

Have a look at that: stackoverflow.com/questions/24271964/… - I think you mixup the SQL Update and Insert statements — user5653854
– user5653854, Commented Mar 19, 2016 at 0:33
@LPK Thanks, guess it should look like this = $sql = 'UPDATE dbo.blog_posts SET blog_title=$head blog_short=$short, blog_post=$bread, blog_author=$author, blog_category=$cat WHERE blogID=$id'; but how do i do the execute part of the script then? — per källström
– per källström, Commented Mar 19, 2016 at 0:41

score 1 · Accepted Answer · 2016-03-19 18:01:31Z

1

Change your query from:

UPDATE dbo.blog_posts 
SET (blog_title, blog_short, blog_post, blog_author, blog_category) 
VALUES (:head, :short, :bread, :author, :cat) 
WHERE blogID=$id

To:

UPDATE dbo.blog_posts
SET blog_title = :head,
    blog_short = :short, 
    blog_post  = :bread,
    blog_author = :author,
    blog_category` = :blog_category
WHERE blogID = :id

Then you also have to add :id to your execute array.

edited Mar 19, 2016 at 18:01

answered Mar 19, 2016 at 11:13

user5653854

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

3 Comments

per källström Over a year ago

i tried but i get a blank page, i edited my post to show how i was using the code... thanks for taking your time to help me!

Mihai Over a year ago

backticks are delimiters in mysql he`s using sql server (new PDO('sqlsrv:server..)

user5653854 Over a year ago

Ahhh okey I fixed it

per källström · Accepted Answer · 2016-03-19 12:15:31Z

 <?php
 error_reporting(E_ALL); ini_set('display_errors', 1);
 $head =  $_POST['title'];
 $short = $_POST['short'];
$bread = $_POST['edit'];
$author = $_POST['author'];
$cat = $_POST['cat'];
$id = $_POST['id'];
$postdate = date('Y-m-d H:i:s');

 $db = new PDO('sqlsrv:server=localhost;Database=blog', '*****', '*****');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'UPDATE dbo.blog_posts SET blog_title= :head, blog_short= :short, blog_post= :bread, blog_author= :author, blog_category= :cat, blog_date= :postdate WHERE blogID= :id';


$query = $db->prepare( $sql );
$query->execute( array(':id'=>$id, ':head'=>$head, ':short'=>$short, ':bread'=>$bread, ':author'=>$author, ':postdate'=>$postdate, ':cat'=>$cat ) );

header("Location: index.php");
?>

This solved it for me! thanks for helping!

Collectives™ on Stack Overflow

Update row in database with php and Microsoft sql

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related