Page 1 of 1

[Solved] Stuck In Prepared Statement code

Posted: Fri Sep 03, 2010 1:29 pm
by ChrisBull
Hello, once again i am stuck, once again the problem i am stuck on seems to not be documented on at all any where.

So, i have some code in a class that creates an object of another class and passes some data to this class. The newly created class then runs a stored procedure to a database. The example i am about to give is for inserting some user deatails into the database to create a new user.

This is the function that controls it.

Code: Select all

public function addUserDetails()
	{
		$userFirstName = $this->_filtered['userFirstName'];
		$userLastName = $this->_filtered['userLastName'];
		$userMail = $this->_filtered['userMail'];
		$userPassword = $this->_filtered['userPassword'];
		$query = "INSERT INTO userDetails (userFirstName, userLastName, userMail, userPassword) VALUES (?, ?, ?, ?)";
		$tempVal = $this->_DBConnection->runQuery($query, array(&$userFirstName, &$userLastName, &$userMail, &$userPassword), false);
		if($this->_DBConnection->getLastID() == false) {
			mail("bugs@site.co.uk", "Major Error In Creating User", "Please Check Tables For Creating A User. Error in 'register_CreateUser.php' on 'line 79'. Paramaters(UserName = $userFirstName $userLastName, UserMail = $userMail)", "From: code@site.co.uk");
			return false;
		}
		else $this->_userID = $this->_DBConnection->getLastID();
		$query2 = "INSERT INTO userActive (userID, userLastSeen, userHash) VALUES (?, ?, ?)";
		$userLastSeen = date("M jS Y");
		$this->_userHash = md5(uniqid(rand(), true));
		$tempVal2 = $this->_DBConnection->runQuery($query2, array(&$this->_userID, &$userLastSeen, &$this->_userHash), false);
		if($this->_DBConnection->getLastID() == false || $this->_DBConnection->getLastID() != $this->_userID) {
			mail("bugs@site.co.uk", "Major Error In Creating User", "Please Check Tables For Creating A User. Error in 'register_CreateUser.php' on 'line 88'. Paramaters(UserName = $userFirstName $userLastName, UserMail = $userMail)", "From: code@site.co.uk");
			return false;
		}
		$this->_DBConnection->close();
		return true;	
	}
This is the function that it is calling.

Code: Select all

public function runQuery($query, $functionArray = array(), $needResults = true)
	{
		$stmtParam = "";
		$parameters = array();
		$results = array();
		foreach ($functionArray as $v) {
			$stmtParam .= "s";
		}
		array_unshift($functionArray, $stmtParam);
		$stmt = $this->_dbConnection->prepare($query);
		call_user_func_array(array($stmt, 'bind_param'), $functionArray);
		$stmt->execute();
		if($needResults) {
			$meta = $stmt->result_metadata();
			while ( $field = $meta->fetch_field() ) {
				$parameters[] = &$row[$field->name];
			}
			call_user_func_array(array($stmt, 'bind_result'), $parameters);
			while ( $stmt->fetch() ) {
				$x = array();  
				foreach( $row as $key => $val ) {
					$x[$key] = $val;
				}
				$results[] = $x; 
			}
			return $results;
		} else return true;
	}
So what happens at the moment is that basically nothing, nothing is created in the database. I do however get the first error email of the two.

So i really do not now what is going on. Sorry that this is a long piece of code, i have tried to solve it myself but it has beaten me.

Many Thanks
Chris

Re: Stuck In stored Procedure Code.

Posted: Fri Sep 03, 2010 3:47 pm
by Jonah Bron
Try echoing all the arguments passed in runQuery().

Re: Stuck In stored Procedure Code.

Posted: Fri Sep 03, 2010 4:11 pm
by ChrisBull
I guess i should have posted this as well, I have another function that runs before 'addUserDetails' that checks if the userMail has already been used in the database, It calls the same function as 'addUserDetails' but this one works correctly.

Code: Select all

public function isMailUnique()
	{
		$checkMail = $this->_filtered['userMail'];
		$query = "SELECT count(*) FROM userDetails WHERE userMail=?";
		$tempVal = $this->_DBConnection->runQuery($query, array(&$checkMail));
		if($tempVal[0]['count(*)'] != 0) {
			return false;
		} else
			return true;
	}
When you say echo the arguments do mean the $query and the array of data? Also, i'm not sure if im meant to be putting a '&' before the variable names.
On the first block of code, i have this bit of code -
if($this->_DBConnection->getLastID() == false) {
the function getLastID is this bit of code
return $this->_dbConnection->insert_id;


I'm not sure if this is what is wrong, but it is returning as false when it should have a number.

Re: Stuck In stored Procedure Code.

Posted: Fri Sep 03, 2010 4:56 pm
by Jonah Bron
echo $query, $functionArray, and $needResults

Re: Stuck In stored Procedure Code.

Posted: Fri Sep 03, 2010 5:59 pm
by ChrisBull
OK, this is the echo's from the function that works correctly,
$query = SELECT count(*) FROM userDetails WHERE userMail=?
$functionArray = Array
$needResults = 1

And from the failing function,
$query = INSERT INTO userDetails (userFirstName, userLastName, userMail, userPassword) VALUES (?, ?, ?, ?)
$functionArray = Array
$needResults =

The last $needResults is empty.

I changed the opening of the runQuery function to this,

Code: Select all

public function runQuery($query, $functionArray = array(), $needResults)
so that the last value is required, but $needResults cam up blank in the echo again.

Re: Stuck In stored Procedure Code.

Posted: Fri Sep 03, 2010 7:27 pm
by Weirdan
I don't see any stored procedures here. Did you mean 'prepared statements'?

Re: Stuck In stored Procedure Code.

Posted: Sat Sep 04, 2010 4:09 am
by ChrisBull
ooops yeah

Re: Stuck In Prepared Statement code

Posted: Sat Sep 04, 2010 2:56 pm
by ChrisBull
Ive also checked the inside of the arrays and they are all fine but still doesnt work

Re: Stuck In Prepared Statement code

Posted: Sat Sep 04, 2010 7:59 pm
by Jonah Bron
Are you sure you're echoing right at the beginning of runQuery()?

Re: Stuck In Prepared Statement code

Posted: Sat Sep 04, 2010 8:25 pm
by mikosiko
also will be interesting to know the complete structure of the table userDetails... that to validate if your insert in the addUserDetail function is correct

Re: Stuck In Prepared Statement code

Posted: Sun Sep 05, 2010 5:10 am
by ChrisBull
this is the code i was using to get the variables. i couldn't use echo directly because this class is getting called by a class which is called by another class, i would have to change a lot of code to get the returns working.

Code: Select all

public function runQuery($query, $functionArray = array(), $needResults)
	{
		mail("bugs@site.co.uk", "Major Error In Creating User", "$query, $functionArray, $needResults", "From: code@site.co.uk");
The structure of the sql table is,

Field Type Collation Attributes Null Default Extra
userID int(11) No auto_increment
userFirstName varchar(30) latin1_general_ci No
userLastname varchar(30) latin1_general_ci No
userMail varchar(100) latin1_general_ci No
userPassword varchar(32) latin1_general_ci No

Re: Stuck In Prepared Statement code

Posted: Sun Sep 05, 2010 10:06 am
by ChrisBull
now i'm really confused,
if you look at my addUserDetails function it is getting stukk on the first query at the if statement.

Code: Select all

if($this->_DBConnection->getLastID() == false) {
The wierd thing is that when i removed the if statement and ran this instead,

Code: Select all

echo $this->_DBConnection->getLastID();
It echoes 0 and the data is still not entered into the database. But the second part of this function, the other query works perfectly and inserts a new row in the other table.

Re: Stuck In Prepared Statement code

Posted: Sun Sep 05, 2010 10:35 am
by ChrisBull
OK, I seem incredibly stupid now, sorry for wasting all of your time...
It turns out that where i had this line,

Code: Select all

$userPassword = $this->_filtered['userPassword'];
i should have had,

Code: Select all

$userPassword = $this->_filtered['userPassword1'];
8O Anyway, 3 days of stuggling im just glad its over, and now to post my next question....