asked on

Update Query Failing When Zero(0) Is Used as Part of the Query

Hi,

I'm using an update query via a form to adjust stock due to discrepancies & breakages etc. I've just noticed that the update query fails when a single 0 is used in any part of the update, but not when this is done directly using terminal and mysql.

So for example if I use the following mysql in terminal to revised a dummy codes stock level from -7,684 back to 0 it works as anticipated.

INSERT INTO manualAdj (partID, partDescription, adjustmentCode, revisedQty, qtyPreRevision) VALUES (2710, 'DUMMYCODE_BM_FIRELIGHTERS','DUMMY CODE TIDY',0,-7684);

Open in new window

However, when I process the same data via the following:

if ((isset($_POST['submit']))
			&& (!empty($_POST['partID']))
            && (!empty($_POST['catID']))
            && (!empty($_POST['reason']))
			&& (!empty($_POST['revisedQty']))
			&& (!empty($_POST['originalQty']))
				

            ) 

            {
        
            // YES, THE DATA HAS BEEN POSTED, NOW ESCAPE IT FOR USE IN A QUERY
			$partID 			= $mysqli->real_escape_string($_POST['partID']);
            $catID 				= $mysqli->real_escape_string($_POST['catID']);
            $reason 			= $mysqli->real_escape_string($_POST['reason']);
			$revisedQty 		= $mysqli->real_escape_string($_POST['revisedQty']);
			$originalQty 		= $mysqli->real_escape_string($_POST['originalQty']);
				
        
            $sql = "INSERT INTO manualAdj (partID, partDescription, adjustmentCode, revisedQty, qtyPreRevision) VALUES ('$partID', '$catID', '$reason', '$revisedQty', '$originalQty')";
            if (!$res = $mysqli->query($sql)) trigger_error( $mysqli->error, E_USER_ERROR );

            }

Open in new window

The query fails to update anything at all but only fails when a single zero(0) is included, otherwise it works as anticipated - any idea why this would happen?

J

Julian Hansen

What error is being shown?

Julian Hansen

What does the query look like when you echo it out

I am guessing you are going to see a space somewhere where it is expecting a number

Ridgejp

ASKER

I dont get an error the screen flashes for a split second and just reloads - no error message at all.

What does the query look like when you echo it out

How do I do this?

J

Ridgejp

ASKER

Oh like a "var_dump($results);" ?

Ridgejp

ASKER

Hi Julian,

It seems that it's related to the '!empty' check at the validation stage - if I remove that it processes the zero's(0).

J

Julian Hansen

I mean like this

$sql = "INSERT INTO manualAdj (partID, partDescription, adjustmentCode, revisedQty, qtyPreRevision) VALUES ('$partID', '$catID', '$reason', '$revisedQty', '$originalQty')";
if (!$res = $mysqli->query($sql)) trigger_error( $mysqli->error, E_USER_ERROR );
  echo "Error: " . $mysqli->error . "<br>";
  echo "Query: {$sql}<br>"  ;
}

Open in new window

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial