Update database error, probably simple, much like me!

[herghost]

Hi all,

 

Wonder if any one can help me with this:

 

This is meant to be a simple form to update a email address on a mysql database. This is what the page looks like:

 

<?php
//Start session
session_start();

//Include database connection details
require_once('config.php');


//Array to store validation errors
$errmsg_arr = array();

//Validation error flag
$errflag = false;

//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
	die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
	die("Unable to select database");
}

//Function to sanitize values received from the form. Prevents SQL injection
function clean($str) {
	$str = @trim($str);
	if(get_magic_quotes_gpc()) {
		$str = stripslashes($str);
	}
	return mysql_real_escape_string($str);
}

//Sanitize the POST values

$email= clean($_POST['email']);

//Input Validations


if($email == '') {
	$errmsg_arr[] = 'Please enter an email ';
	$errflag = true;
}




//If there are input validations, redirect back to the registration form
if($errflag) {
	$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
	session_write_close();
	header("location: register-form.php");
	exit();
}

//Create INSERT query
$qry = "REPLACE INTO members('$email')";
$result = @mysql_query($qry);

//Check whether the query was successful or not
if($result) {
	header("location: register-success.php");
	exit();
}else {
	die("Query failed");
}
?>

 

All I am getting is a query failed message when the uer trys to update the email address and I cannot see why?! Any ideas?

[revraz]

I would do a couple of things.

 

First, remove the @ in front of your query to see your error.

Second, use mysql_error after the query to report a error.

Third, echo $qry to ensure $email does have a value stored in it.

Forth, try using the SET parameter:

 

$qry = "REPLACE INTO members SET colname = '$email'";

 

Fifth, use UPDATE instead of REPLACE INTO

 

$qry = "UPDATE members SET colname = '$email'";

[herghost]

Thanks for your reply revraz, I have done what you have suggested using the Update syntax and on the surface it seems to work, the script completes and I get my update succesful page, however the data in the database hasnt actually changed?!

 

Also, you have asked me to remove the @ sign before my query, maybe I am going blind but I cant see one I shall be using your hints in future scripts

 

Any ideas on why the database has not updated?

 

 

[revraz]

The @ is here

 

//Create INSERT query

  $qry = "REPLACE INTO members('$email')";

  $result = @mysql_query($qry);  <----

 

Also, do the third step I posted above to see if your variable is populated.  If so, verify that variable matches the value in the column.

[herghost]

Thanks again

 

Found it!

 

I have discovered what the problem seems to be, the script is creating a new user in the data basea nd just populating the email field, I also added my auth.php file which is just information for the sessions, however it appears to be ignoring that and just adding a new user.

 

I am right in assuming that in the

 

qry = "UPDATE members SET email = '$email'";

 

I have to add something like

qry = "UPDATE members SET colname = '$email' WHERE (noidea what to put here? something to do with the sessions?)";

Thanks again for all this, very much needed and appreicaited

 

Dave

[revraz]

The WHERE clause should be the ROW ID of that user.  When they log in, set their ROW ID in a session variable.  With just the code you posted, not really sure where you are having them log in at.

[herghost]

Thanks once again, I have done as you suggested and added this:

 

$qry = "REPLACE INTO members SET email = '$email' WHERE user = '$user'" ;

 

and assigned it like this:

 

$user= clean($_POST['$_SESSION['SESS_MEMBER_ID']']);

 

However, I am now getting this error:

 

Parse error: parse error, expecting `']'' in C:\wamp\www\cvsite\include\emailchange.php on line 39

 

With line 39 being the above line ie. $user= clean

 

Thanks again

[revraz]

I think you want this.

 

$user= clean ($_SESSION['SESS_MEMBER_ID']);

[herghost]

another 'query failed' message, nothing else

 

I really must have screwed something up somewhere, perhaps I should just start again with this page. I dont suppose you know of any tuturials in this kind of thing? Or provide me with a working example?

 

I really thought this bit would be easy!

 

The user is already signed into the 'member area' and I can display there email from the database, why cant I bloody change it!!

 

Thanks for all your help so far.

 

[revraz]

Lets see your updated code.

 

Also, be sure to echo your variables.  That will tell you what they contain.

[revraz]

Also, post your DB structure, as well as some sample data that it contains.

[herghost]

Thanks mate,

 

here is my latest code:

 

<?php require('auth.php');



//Include database connection details
require_once('config.php');


//Array to store validation errors
$errmsg_arr = array();

//Validation error flag
$errflag = false;

//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
	die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
	die("Unable to select database");
}

//Function to sanitize values received from the form. Prevents SQL injection
function clean($str) {
	$str = @trim($str);
	if(get_magic_quotes_gpc()) {
		$str = stripslashes($str);
	}
	return mysql_real_escape_string($str);
}

//Sanitize the POST values

$email= clean($_POST['email']);
$member = ($_SESSION['SESS_MEMBER_ID']);


//Input Validations


if($email == '') {
	$errmsg_arr[] = 'Please enter an email ';
	$errflag = true;
}




//If there are input validations, redirect back to the registration form
if($errflag) {
	$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
	session_write_close();
	header("location: register-form.php");
	exit();
}

//Create INSERT query
$qry = "REPLACE INTO members SET email = '$email' WHERE member_id = '$member'" ;
$result = mysql_query($qry);

//Check whether the query was successful or not
if($result) {
	header("location: ../register-success.php");
	exit();
}else {
	die("Query failed");
	echo mysql_errno($email) . ": " . mysql_error($email). "\n";
}
?>

 

and the database structure:

 

 Host: localhost
Database: cvsite
Generation Time: Mar 06, 2009 at 06:23 PM
Generated by: phpMyAdmin 3.1.1 / MySQL 5.1.30-community-log
SQL query: SELECT * FROM `members` LIMIT 0, 30 ;
Rows: 1
member_id 	firstname 	lastname 	login 	passwd 	postcode 	email
2 	Dave 	Grix 	admin 	dc23053d9ada11806420839fd42aa9c9 	NULL 	admin@jollyhosting.com