Code to get field value from DB not working

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
robintw
Forum Newbie
Posts: 3
Joined: Sun May 14, 2006 12:10 pm

Code to get field value from DB not working

Post by robintw »

Hi all,

I've got some php code that basically takes a username and password, checks them, and lets you log in. It then lets you edit a large text box, saves it to a DB, or if it is already in the DB for that username, updates it.

I have a session variable called insert which I set to true if when I try and get the text from the DB it is equal to "", ie. the record ain't found, and therefore I know I have to use SQL insert not SQL update. Basically, I'm not quite sure which bit of my code has gone wrong - but it doesn't seem to get anything from the DB for that field at all - regardless if it is there or not.

I've got three files, and I've posted the contents of them below.

auth.php - Authenticates users and passes them to form.php

Code: Select all

<?session_start();
if ( $_GET['check'] == 1 ) {
	//Connection parameters
	$dsn="TestAccess";


	//Create the connection
	$conn=odbc_connect($dsn,"","");
	
	//Get the username and password from the form
	$UserName = $_POST['username'];
	$Password = $_POST['password'];

	$query = odbc_exec($conn,"SELECT * FROM tblAuth WHERE UserName='".$UserName."';");
	
	//Get the username and password from the DB
	$DBUserName = odbc_result($query, "UserName");
	$DBPassword = odbc_result($query, "Password");
	
	//If passwords are equal...
	if ($Password == $DBPassword) {
		//Set some session vars with the users details
		$_SESSION['user'] = $UserName;
		$_SESSION['pass'] = $Password;
		//Redirect to form.php
		header("Location: form.php");
		exit;
	}
	else {
		//Whoops - user entered wrong password!
		echo "Sorry, your username and password did not match. Please try again";
	}
}
else{?>
<form action="auth.php?check=1" method="post">
 Username: <input type="text" name="username"/><BR>
 Password: <input type="text" name="password"/><P>
 <input type="submit" />
</form>
<?
}
?>
form.php - Displays the form and tries to fill the textarea with what is already in the DB. This is where I think the problem is

Code: Select all

<?session_start();
echo "<B>Username: </B>".$_SESSION['user']."<BR>";
echo "<B>Password: </B>".$_SESSION['pass']."<BR>";
echo "<P>";
?>
<form action="process.php" method="post">
 RandomText:<BR>
 <?
	//Connection parameters
	$dsn="TestAccess";

	//Create the connection
	$conn=odbc_connect($dsn,"","");
	
	//Finds the users record
	$query = odbc_exec($conn,"SELECT * FROM tblTest WHERE UserName='".$_SESSION['user']."';");

	//Gets the value in the field TextField
	$TextField = odbc_result($query, "TextField");
	
	//Prints out value for testing purposes
	echo "TextField:<BR>";
	echo $TextField;
	
	//Puts $TextField into TextArea - supposedly!
	echo "<textarea name=\"textfield\" rows=\"20\" cols=\"80\">".$TextField."</textarea><P>";
?>
 <select name="house">
 <option>Hatfield</Option>
 <option>Poulton</Option>
 <option>Benhams</Option>
 <option>The Mount</Option>
 <option>Greenslade</Option>
 </select><P>
 <input type="submit" />
</form>
<?
//Sets a session variable so that I know whether to insert or update
if ( $TextField == "" ) {
	$_SESSION['insert'] = 1;
	echo 1;
}
//If its called with ?success=1 then display message
if ( $_GET['success'] == "1") {
	echo Success;
}
?>
process.php - Processes the output from form.php - does the adding to the DB, and returns success

Code: Select all

<?
session_start();

//Connection parameters
$dsn="TestAccess";
$username="";
$password="";

//Create the connection
$conn=odbc_connect($dsn,$username,$password);

//If the SESSION user var isn't empty - ie. someone is logged in.
if ( !( $_SESSION['user'] == "") ) {	
	//If we have to insert...do it
	if  ( $_SESSION['insert'] = 1 ) {
	$sql =  "INSERT INTO tblTest (UserName, TextField, House) VALUES ('".$_SESSION['user']."','".$_POST['textfield']."','".$_POST['house']."');";
	}
	else {
	//Otherwise UPDATE
		$sql =  "UPDATE tblTest SET UserName='".$_SESSION['user']."', TextField='".$_POST['textfield']."',House='".$_POST['house']."' WHERE UserName='".$_SESSION['user']."';";
		}
}

//Execute the SQL
$result=odbc_exec($conn, $sql);

//Redirect to form.php - showing the success message
header("Location: form.php?success=1");

//Close the connection
odbc_close($conn);
?>
Thanks in advance for any help you can give,

Robin
alex-weej
Forum Newbie
Posts: 19
Joined: Sun May 14, 2006 11:20 am

Post by alex-weej »

Firstly, you can use an SQL "REPLACE" statement to update or insert records, depending on whether keys overlap or not. That should simplify your code muchly.
robintw
Forum Newbie
Posts: 3
Joined: Sun May 14, 2006 12:10 pm

Post by robintw »

Hi

Thanks for the response. I've just tried using that, and as I'm using ODBC to a MS Access DB I can't seem to use it. Access doesn't seem to support the REPLACE command - which is a shame because it looks like it would be very useful.

Does anyone have any other ideas? Hopefully the if statement in process.php will work properly - if only form.php gets the data from the DB properly.

Cheers,

Robin
alex-weej
Forum Newbie
Posts: 19
Joined: Sun May 14, 2006 11:20 am

Post by alex-weej »

In that case, I suggest you do the INSERT/UPDATE check not when the user logs in, but rather just before you go to set the record's value.
robintw
Forum Newbie
Posts: 3
Joined: Sun May 14, 2006 12:10 pm

Post by robintw »

Hi,

How would I check it at that place in the code? The reason I did it earlier was because I'd got a variable out of the DB with the text from the textarea in it (although at the moment nothing appears in that variable - and that is the main problem) so I could do a quick if ... == "" then etc

Robin
Post Reply