Phani Phani - 1 year ago 76
MySQL Question

Query works in phpmyadmin ut not in PHP Script

I know this is very silly question. But I am disappointed with the behaviour of this query. I am updating Customers in Opencart. When I have written and executed an Update query, few fields are being inserted and few are not. Especially I need to update 'status' and 'approved' columns. Please check the below Query.

UPDATE oc_customer SET customer_group_id=1,store_id=0,firstname='',lastname='HEATHER HUME',telephone='9876543210',fax='0',password='f53cbb1352950831a84035d320063383f345cfce',salt='rCF2EquoV',status='1',approved='1',date_added='2016-08-31',discount=62.00 WHERE customer_id='1418'

Please let me know what is wrong with this. It is updating Telephone column and not status,approved.

Below is the structure of my table

`customer_id` int(11) NOT NULL,
`customer_group_id` int(11) NOT NULL,
`store_id` int(11) NOT NULL DEFAULT '0',
`firstname` varchar(32) NOT NULL,
`lastname` varchar(32) NOT NULL,
`email` varchar(96) NOT NULL,
`telephone` varchar(32) NOT NULL,
`cellphone` varchar(32) NOT NULL,
`fax` varchar(32) NOT NULL,
`password` varchar(40) NOT NULL,
`salt` varchar(9) NOT NULL,
`cart` text,
`wishlist` text,
`newsletter` tinyint(1) NOT NULL DEFAULT '0',
`address_id` int(11) NOT NULL DEFAULT '0',
`custom_field` text NOT NULL,
`ip` varchar(40) NOT NULL,
`status` tinyint(1) NOT NULL,
`approved` tinyint(1) NOT NULL,
`safe` tinyint(1) NOT NULL,
`token` text NOT NULL,
`date_added` datetime NOT NULL,
`discount` decimal(8,2) NOT NULL DEFAULT '0.00',
`tax_id` varchar(50) NOT NULL,
`subscribe` varchar(5) NOT NULL

My php code is

$query = "UPDATE oc_customer SET customer_group_id=1,store_id=0,firstname='$first_name',lastname='$last_name',telephone='$phone',fax='$fax',password='$password',salt='$salt',status=".(int)$status.",approved=".(int)$approved.",date_added='$date_added1',discount=$discount WHERE customer_id='$customer_id' ";

$con is my connection variable.No problem with that.

Answer Source

Thanks for your response. There is no problem with the query. The problem is with special characters which we can't see either in our editors or in PhpMyAdmin. This may be helpful for someone who have stuck with same problem, i.e., Query executes in PhpMyAdmin and not in PHP Script.

Please type the query on your own. Please don't copy and paste it from anywhere. Not atleast from your own page again.

Please type everything on your own because copy paste may again copy the invisible special characters into query which again makes your query tough to debug.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download