Updating SQL database using Php

[system]

Hi all,

I have been trying to get this to work for days now and still I’m stuck!
Can anyone see the problem here and if so can you please let me know how to resolve it? I havent been learning this for long but this is one of the things I need to do to get a piece of work I am building up to to work.

Thanks all.

[php]<?php
//Start session
session_start();
$_SESSION[‘SESS_LOGIN’];
//Include database connection details
require_once(‘config.php’);

$connection = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
if (mysqli_connect_error()) {
		die(`Connect Error: ` . mysqli_connect_error());
} else {
		echo `Successful connection to MySQL <br />`;

	$employ = $_POST['employer'];
	$address = $_POST['flat'];
    $login_name = $_POST['login_name'] ;
    var_dump($connection);

//    Insert employer and address into database row for logged in user.    

$query = "UPDATE members SET employer = `$employ`, flat = `$address` WHERE     login=`$login_name`";

if (!$result = $connection->query($query)) {
		echo "No rows updated <br />";
	} else {
			echo $result . " row(s) successfully updated<br />";
			}
	}

 
  
?>

[/php]

[richei]

The query is wrong. are used for designating column names, not input

$query = “UPDATE members SET employer = ‘$employ’, flat = ‘$address’ WHERE login=’$login_name’”;

Strings are done with single or double quotes, but you can’t use the same quotes together, like if you start with doubles, then your input would be done with either single quotes (the easy way) or conatated ("’.$value."’). if you really want be correct, use

$query = “UPDATE members SET employer = ‘$employ’, flat = ‘$address’ WHERE login = ‘$login_name’”;

I think the confirmation is wrong too, might need == instead of =, can’t stand PDO personally, so I don’t know if the same rules apply.

[Strider64]

It can be easily done using PDO

I also add using prepared statements (either in mysqli or PDO) will save you headaches in the long run.

Here’s the code that I did (I can’t guarantee it to work for I didn’t test is, but it should be close if it isn’t right).

[php]<?php
//Start session
session_start();

$_SESSION[‘SESS_LOGIN’];

// This could go into a utility file like common.php, come to think of it the above could also;
// Create the database connection as a PDO object:
try {

$db_options = array(
	   PDO::ATTR_EMULATE_PREPARES => false                     // important! use actual prepared statements (default: emulate prepared statements)
	   , PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION           // throw exceptions on errors (default: stay silent)
	   , PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC      // fetch associative arrays (default: mixed arrays)
   ); 		 

$pdo = new PDO('mysql:host=localhost;dbname=your_database_here;charset=utf8', 'root', 'your_password', $db_options);	

} catch (PDOException $e) { // Report the Error!

// You could just do an error message like $errMsg = "Fail to Connect!";
$pageTitle = 'Error!';
include('includes/header.inc.php');
include('views/error.html');
include('includes/footer.inc.php');
exit();

}
// End of common.php (or what have you) file…

// Check to see if edited data has been submitted by the user:
if (isset($_POST[‘submit’]) && $_POST[‘submit’] == ‘enter’) {

$employ = $_POST[‘employer’];
$address = $_POST[‘flat’];
$login_name = $_POST[‘login_name’];

// Update the edited text:
$query = ‘UPDATE members
SET employer = :employer,
flat = :flat
WHERE login=:login’;

// Prepare the Statement:

$stmt = $pdo->prepare($query);

// execute the statement:
$result = $stmt->execute(array(’:employer’ => $employ, ‘:flat’ => $address, ‘:login’ => $login_name));

}

if (isset($result)) {
echo ‘Success!!!
’;
} else {
echo ‘Failure
’;
}
[/php]

As you can see updating in PDO is very easy and one little piece of advice: I find it easier to check to see if something succeeds than fails majority of the time. Also I would go to the horses mouth for further help on mysqli or PDO —> php.net manual