Mysql Update Command Failing

[vba]

Hi,

 

I am using the standard Update command as per below:

 

$conn = db_conn_Write();

 

$myQuery = "update vb_products

set price = '".$pr1."',

active = '".$cb1."',

specialprice = '".$pr2."',

specialactive = '".$cb2."',

uom = '".$uom."'

where vb_prod_id = '".$vb_prod_id."'";

 

$result = $conn->query($myQuery);

 

Below here is the connection function for the database:

 

function db_conn_Write() {

 

$result = new mysqli("localhost", "username", "password", "table");

 

if (mysqli_connect_error()) {

$text = 'Could not connect to database server [W-m]';

throw new Exception($text);

exit();

} else {

return $result;

}

}

 

This all works fine on my windows development site at home (same versions of PHP and MYSQL), however when I ftp the files to the production environment, all the screens work fine the only failure (no visual error messages) is the actual update to the database.

 

When running the update command as listed in PHPMyAdmin the data updates.

 

Any clues would be much appreciated.

 

Thanks.

[OJB]

You might not be seeing any visual failings because you aren't catching your exception and dealing with it....

 

you need a try and catch i think.. like so:

 

 

>try
{
$result = new mysqli("localhost", "username", "password", "table");
if(mysqli_connect_error())
{
	$text = 'Could not connect to database server [W-m]';
	throw new Exception($text);
}

}
catch(Exception $e)
{
var_dump($e->getMessage());
}

 

plus shouldn't it be 'schema' not 'table' in the mysqli construct?

[vba]

Hi OJB, Yes you are correct "Schema" just using wrong terminology but right item.

 

Well I have still been looking at this and I have added in enough "Try/catch" to make the code twice as long and made sure that I can make them all fail on the Windows system but still no failures on the Production system. I did notice that the default of MyISAM had been set by the MySQL server on all the tables and have change them all to InnoDB. But still without success. The storage engine of the MYSQL server is set to MyISAM, but I cannot change that as it is set by TCH(so hopefully I should not have to and changing the tables should be enough).

 

Any ideas?

 

Thanks

 

vba

 

You might not be seeing any visual failings because you aren't catching your exception and dealing with it....

 

you need a try and catch i think.. like so:

 

 

>try
{
$result = new mysqli("localhost", "username", "password", "table");
if(mysqli_connect_error())
{
	$text = 'Could not connect to database server [W-m]';
	throw new Exception($text);
}

}
catch(Exception $e)
{
var_dump($e->getMessage());
}

 

plus shouldn't it be 'schema' not 'table' in the mysqli construct?

[vba]

Can we use InnoDB for the schemas and also the engine. I read something about SET storage_engine = InnoDB for a session however cannot find the PHP method of doing so. However I could be barking up the wrong tree on needing the storage engine set to InnoDB to get the writing happening.

 

I can read all I want, just cannot write.

[vba]

Well problem solved.

 

Ended up being just before the update.

 

I was assigning a session variable(array) to a variable(array). This normally works however there was a small problem which windows lets you get away with and unix does not.

 

//set the new array as type array

$new_array = array();

//assign the session array to the new array

$new_array = $_SESSION['new_array'];

 

The problem with this is the names, they are both called new_array. Windows recognises them as different and unix does not.

 

Basically the first command to set the new array as type array empties the session array under unix.

 

Once I changed the $new_array to $diff_array it all works fine under unix and windows.

 

So it is all now.

[OJB]

That seems very very odd indeed. I cannot see why Unix would reset a session variable because it has the same name as a local variable.

 

How big is the array being stored in session? If I ever store arrays in session which I dont have to do very often I usually serialize them first too:

 

 

$_SESSION['new_array'] = serialize($whatever_array);

 

 

Then when you want to retrieve that array from session:

 

$diff_array = unserialize($_SESSION['new_array']);

 

 

 

I have to say I have never experienced an issue with $_SESSION variables being overwritten though. Very odd. I can't see a reason why that would possibly happen, but I am glad you got it working!

[click]

Sounds like register globals may be turned on on the Unix box. If so, be sure to research the security implications of running with register globals enabled if you don't know. I believe register globals is being removed from PHP6 due to the number of security exploits it allowed.

Edited July 4, 2009 by click

[vba]

Well,

 

the $_SESSION['new_array'] that we are utilising has the potential to store an array of up to 30 primary elements as well as each element being an array of 6 sub elements ( 2 x floats(4,2), 2 x tinyint(1), 1 x int(11) & 1 x varchar(5)). An aray of arrays. However, it is typically only going to have up to a max of 5 primary elements being updated at any time. By the way, we tried it with all 30 being updated and it worked quite well.

 

To fault find we placed print_r($_SESSION['new_array']) & print_r($new_array) statements everywhere to isolate where the array was being emptied. Basically when we used $new_array = array(), it emptied $_SESSION['new_array']. Just that "1 line of code" stopped us!

 

Our windows dev is slightly behind the unix prod environment - MySQL v5.0.51b (5.0.81), PHP v 5.2.6 (5.2.9).

----------------------------------------

 

That seems very very odd indeed. I cannot see why Unix would reset a session variable because it has the same name as a local variable.

 

How big is the array being stored in session? If I ever store arrays in session which I dont have to do very often I usually serialize them first too:

 

 

$_SESSION['new_array'] = serialize($whatever_array);

 

 

Then when you want to retrieve that array from session:

 

$diff_array = unserialize($_SESSION['new_array']);

 

 

 

I have to say I have never experienced an issue with $_SESSION variables being overwritten though. Very odd. I can't see a reason why that would possibly happen, but I am glad you got it working!

[vba]

Will need to investigate whether register globals are turned on in the Unix box - thanks

-----------------------

 

Sounds like register globals may be turned on on the Unix box. If so, be sure to research the security implications of running with register globals enabled if you don't know. I believe register globals is being removed from PHP6 due to the number of security exploits it allowed.