mysql query driving me mad!

[SetToLoki]

private function AddBusinessData( $user_id, $Forename, $Surname, $BusinessName = "", $AddressOne = "", $AddressTwo = "", $Number = "", $PostCode = "", $City = "", $County = "", $Country = "", $Title = "", $business_id = 0 )
	{
		if( !$this->connected )
		{
			$this->DBConnect( );	
		}

	    //$Forename = $this->mysqli->real_escape_string( $Forename );
	    //$Surname  = $this->mysqli->real_escape_string( $Surname );
	    //$BusinessName  = $this->mysqli->real_escape_string( $BusinessName );
	    //$AddressOne  = $this->mysqli->real_escape_string( $AddressOne );
	    //$AddressTwo = $this->mysqli->real_escape_string( $AddressTwo );
	    //$Number = $this->mysqli->real_escape_string( $Number );
	    //$PostCode = $this->mysqli->real_escape_string( $PostCode );
	    //$City = $this->mysqli->real_escape_string( $City );
	    //$County = $this->mysqli->real_escape_string( $County );
	    //$Country = $this->mysqli->real_escape_string( $Country );
	    //$Title = $this->mysqli->real_escape_string( $Title );

	   


		//UPDATE or INSERT?
		if( $business_id == 0 )
		{
			//Create New Entry
			$sql = "INSERT INTO `smartit`.`businesses` (`businesses`.`user_id`,`businesses`.`BusinessName`,`businesses`.`AddressOne`,`businesses`.`AddressTwo`,`businesses`.`Number`,`businesses`.`PostCode`,`businesses`.`City`,`businesses`.`County`,`businesses`.`Country`,`businesses`.`Forename`,`businesses`.`Surname`,`businesses`.`Title`) VALUES ( '$user_id', '$BusinessName', '$AddressOne', '$AddressTwo', '$Number', '$PostCode', '$City', '$County', '$Country', '$Forename', '$Surname', '$Title' )";
		}
		else
		{
			//Update Current Record
			$sql = "REPLACE INTO `businesses`
					(`$business_id`, `user_id`,`BusinessName`,`AddressOne`,`AddressTwo`,`Number`,`PostCode`,`City`,`County`,`Country`,`Forename`,`Surname`,`Title`)
					VALUES 
					( $business_id, $user_id, '$BusinessName', '$AddressOne', '$AddressTwo', '$Number', '$PostCode', '$City', '$County', '$Country', '$Forename', '$Surname', '$Title' )";
		}

		$this->mysqli->query($sql);
		$insert_id = $this->mysqli->insert_id;
		$error = $this->mysqli->error;
		$this->mysqli->close();
		if( $insert_id > 0 )
		{
			return $insert_id;
		}
		else
		{
			$this->error = "FAILED TO INSERT BUSINESS DATA TO DATABASE <br />".$error."<br />".$sql;
			return false;
		}

	}

 

Example Query

INSERT INTO `smartit`.`businesses` (`businesses`.`user_id`,`businesses`.`BusinessName`,`businesses`.`AddressOne`,`businesses`.`AddressTwo`,`businesses`.`Number`,`businesses`.`PostCode`,`businesses`.`City`,`businesses`.`County`,`businesses`.`Country`,`businesses`.`Forename`,`businesses`.`Surname`,`businesses`.`Title`) VALUES ( '2', '', '', '', '01257-858746', '', '', '', '', 'Jimmy', 'Biggear', 'Mr.' )

 

Hi, this code is driving me nuts. mysqli does not return an error and the query will work if I paste it directly into mysql. I've changed the query over and over trying to get it to work but no matter what I do the function returns false and nothing is inserted into the database. I have a feeling it will be some obvious mistake, just not one that is obvious to me

 

Thanks in advance for any help.

 

Tom

[SetToLoki]

on a side note, I am only worried at this point about getting the INSERT query to work, and I have had to take out the real_escape_strings caus if they are uncommented they return null values for everything?? I am not sure why

 

mysqli->error does not return anything and the only other method I reference in the above function is DBConnect

 

private function DBConnect()
	{
		$mysqli = new mysqli(SERVER, USER, PASSWORD, DATABASE);

		if ( mysqli_connect_errno() ) 
		{
			printf("Connect failed: %s\n", mysqli_connect_error());
			exit();
		}

		$this->mysqli = $mysqli;
		$this->connected = true;
	}

[dgoosens]

hi,

 

there is something wrong here:

$sql = "REPLACE INTO `businesses`
                  (`$business_id`, `user_id`,`BusinessName`,`AddressOne`,`AddressTwo`,`Number`,`PostCode`,`City`,`County`,`Country`,`Forename`,`Surname`,`Title`)
                  VALUES
                  ( $business_id, $user_id, '$BusinessName', '$AddressOne', '$AddressTwo', '$Number', '$PostCode', '$City', '$County', '$Country', '$Forename', '$Surname', '$Title' )";

 

not sure there should be any variables in the first section...

also, make sure you put all the variables in the VALUES between quotes

[ignace]

What does $mysqli->error return?

[SetToLoki]

What does $mysqli->error return?

 

it doesn't return anything and the mysqli->errorno is 0

 

the query runs fine in mysql directly just not via the site

 

just tried changing it to an insert on duplicate key update again query runs in mysql but once I put it in the script it won't run, so I am guessing there is something wrong with the insert_id that is being returned but if I echo $insert_id out it is null.

 

I don't seem to be able to generate any errors from mysql and the query is being asked to run, but it must be the query failing as nothing is being added to the database before the function fails.

[SetToLoki]

hi,

 

there is something wrong here:

$sql = "REPLACE INTO `businesses`
                  (`$business_id`, `user_id`,`BusinessName`,`AddressOne`,`AddressTwo`,`Number`,`PostCode`,`City`,`County`,`Country`,`Forename`,`Surname`,`Title`)
                  VALUES
                  ( $business_id, $user_id, '$BusinessName', '$AddressOne', '$AddressTwo', '$Number', '$PostCode', '$City', '$County', '$Country', '$Forename', '$Surname', '$Title' )";

 

not sure there should be any variables in the first section...

also, make sure you put all the variables in the VALUES between quotes

 

yeah, your right about the variables lol I missed thsat, this it of code has never been asked to run yet, so not hit that problem yet, but thanks for freeing me of the pain later lol!

[ignace]

Try:

 

INSERT INTO `smartit`.`businesses` (`user_id`, `BusinessName`, `AddressOne`, `AddressTwo`, `Number`, `PostCode`, `City`, `County`, `Country`, `Forename`, `Surname`, `Title`) VALUES ( '$user_id', '$BusinessName', '$AddressOne', '$AddressTwo', '$Number', '$PostCode', '$City', '$County', '$Country', '$Forename', '$Surname', '$Title' )

[SetToLoki]

Try:

 

INSERT INTO `smartit`.`businesses` (`user_id`, `BusinessName`, `AddressOne`, `AddressTwo`, `Number`, `PostCode`, `City`, `County`, `Country`, `Forename`, `Surname`, `Title`) VALUES ( '$user_id', '$BusinessName', '$AddressOne', '$AddressTwo', '$Number', '$PostCode', '$City', '$County', '$Country', '$Forename', '$Surname', '$Title' )

 

Same as before query works in query window but not when called by php.

[SetToLoki]

solved: schoolboy error I in the last function I never told the is connected variable to be false after mysqli->close()

 

you laugh now, but it happens to the best of us