Jump to content

Having trouble updating database


vet911
Go to solution Solved by Jacques1,

Recommended Posts

This is the error I'm getting.

Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'xxx', desc2 ='xx', desc3 ='x' WHERE id = '1'' at line 15' in 

This is my code:

require_once('connect.php');

$get_id=$_REQUEST['id'];

$fname = $_POST['fname'];
$lname = $_POST['lname'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$phone = $_POST['phone'];
$large = $_POST['large'];
$lsize = $_POST['lsize'];
$lmatl = $_POST['lmatl'];
$medium = $_POST['medium'];
$msize = $_POST['msize'];
$mmatl = $_POST['mmatl'];
$small = $_POST['small'];
$ssize = $_POST['ssize'];
$smatl = $_POST['smatl'];
$desc1 = $_POST['desc1'];
$desc2 = $_POST['desc2'];
$desc3 = $_POST['desc3'];

$sql = "UPDATE register SET 
fname ='$fname',
lname ='$lname', 
address ='$address', 
city ='$city', 
state ='$state', 
zip ='$zip', 
phone ='$phone',
large ='$large', 
lsize ='$lsize', 
lmatl ='$lmatl', 
medium ='$medium', 
msize ='$msize', 
mmatl ='$mmatl', 
small ='$small, 
ssize ='$ssize', 
smatl ='$smatl',
desc1 ='$desc1', 
desc2 ='$desc2', 
desc3 ='$desc3' 
WHERE id = '$get_id' ";


$dbh->exec($sql);

If you could give me some direction to figure this out it would be appreciated. 

Thanks in advance for your time.

Link to comment
Share on other sites

  • Solution

After 6 years, it's about time you meet Bobby Tables.

 

And what's the matter with all those variables? Why can you not use $_POST directly?

<?php

// create and execute a prepared statement to prevent SQL injection attacks
$registerStmt = $dbh->prepare('
    UPDATE register
    SET
        fname = :fname,
        lname = :lname,
        -- ...
    WHERE
        id = :user_id
');
$registerStmt->execute([
    'fname' => $_POST['fname'],
    'lname' => $_POST['lname'],
    // ...,
    'id' => $get_id,
]);
Link to comment
Share on other sites

Ok so this is what I have now. 

<?php 
	try
{
	{
		/*** mysql hostname ***/
		$hostname = 'localhost';

		/*** mysql username ***/
		$username = 'xxxx';

		/*** mysql password ***/
		$password = '';

		$dbname = 'dblogin';

		$dhb = null;

		$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
		$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
		$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true );

		/***echo a message saying we have connected***/ 
		 echo "connected";

		$get_id=$_REQUEST['id'];
		

$registerStmt = $dbh->prepare('
    UPDATE register
    SET
		fname = :fname,
                lname = :lname,
		address = :address, 
		city = :city, 
		state = :state, 
		zip = :zip, 
		phone = :phone,
		large = :large, 
		lsize = :lsize, 
		lmatl = :lmatl, 
		medium = :medium, 
		msize = :msize, 
		mmatl = :mmatl, 
		small = :small, 
		ssize = :ssize, 
		smatl = :smatl,
		desc1 = :$desc1, 
		desc2 = :$desc2, 
		desc3 = :$desc3 
    WHERE
        id = :id
	');
	


$registerStmt->execute([
	'fname' => $_POST['fname'],
        'lname' => $_POST['lname'],
	'address' => $_POST['address'],
	'city' => $_POST['city'],
	'state' => $_POST['state'],
	'zip' => $_POST['zip'],
	'phone' => $_POST['phone'],
	'large' => $_POST['large'],
	'lsize' => $_POST['lsize'],
	'lmatl' => $_POST['lmatl'],
	'medium' => $_POST['medium'],
	'msize' => $_POST['msize'],
	'mmatl' => $_POST['mmatl'],
	'small' => $_POST['small'],
	'ssize' => $_POST['ssize'],
	'smatl' => $_POST['smatl'],
	'desc1' => $_POST['desc1'],
	'desc2' => $_POST['desc2'],
	'desc3' => $_POST['desc3'],
	'id' => $get_id,
    ]);

    
	}
}
catch(PDOException $e)
{
	echo $e->getMessage();
}
?>

And this is the error message.

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

I counted all the variables and tokens and I think they match, so am I wrong?

Link to comment
Share on other sites

I have to say you are the man. I have looked at that sense this morning when I made the post about being lost. I can honestly say I did not see those dollar signs.

I appreciate all your help. Something else has been in my thoughts, I have a sign up page and after you login it brings you to a form which is blank. I want people to be able to change their own stuff. If they did it right the would have to fill out, name address, city, state, phone before they cold save that page without filling in the rest of the form.

What I'm thinking, is there a way to see if they filled out the partial required form and if so it will load that info and they would be able to fill in the rest or update it at a later time?

Thanks for your help. 

Link to comment
Share on other sites

I'm not sure what you're asking. Yes, you can make some fields mandatory and some fields optional simply by implementing the validation logic accordingly (an empty required field leads to an error, an empty optional field doesn't). And, yes, you can pre-fill the form with existing values that are stored e. g. in the database.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.