Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Why cannot charater returned by Mysql


  • Please log in to reply
6 replies to this topic

#1 dungpt29

dungpt29

  • Members
  • 45 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:05:12 AM

Posted 28 January 2013 - 09:45 PM

My laptop is installed 64-bit Windows 7 Home Premium operating system.

I am trying to buid a website based on:
1) apache_2.2.14-win32-x86-no_ssl
2) php-5.2.11-Win32
3) mysql-essential-5.1.40-winx64
4) Zend Framework (ZF) 1.12.0

I create a table in database named city containing list of cities as the following:

CREATE TABLE `mydb`.`city` (
`city_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`city_name` CHAR(100) UNICODE NOT NULL,
`city_code` VARCHAR(5) NOT NULL,
PRIMARY KEY (`city_id`)
)
ENGINE = InnoDB;


e.g
------------------------
city_code | city_name |
------------------------
499 | Paris |
422 | Atlanta |
------------------------

My function connects to Mysql server and selects data in table 'city' based on ZF as the following:

class Application_Model_NewAccount
{
public function list_city($city_code)
{

$options = array(Zend_Db::AUTO_RECONNECT_ON_UNSERIALIZE => true);
$params = array(
'host' => 'localhost',
'username' => 'username',
'password' => 'password',
'dbname' => 'mydb',
'options' => $options);

$db = Zend_Db::factory('PDO_Mysql', $params);

$select = $db->select()
->from('city', array('city_code', 'city_name'))
->where('city_code = ?', $city_code);

$stmt = $select->query();
$rows = $stmt->fetchAll();
$db->closeConnection();

return $rows;
}
}

The function displays city code as the following:

class AccountController extends Zend_Controller_Action
{
public function newAction()
{
$newacc = new Application_Model_NewAccount();
$city_code = '499';
$cities = $newacc->list_city($city_code);
$result = $cities[0]['city_code'];
$this->view->city_code = $result;
D
}

And I put the following command that displays data in browser in file new.phtml that is a view in MVC pattern:
<?php echo $this->city_code?>

During the test I found that:
My website runs without error only if city_code field contains digit characters.
If city_code field contains digit characters led by 0 (zero) such as: 010, 000 or alphabetical characters such as: PRS, ATL, Mysql returns incorrect data that is not similar to one in Mysql database. For example:

-----------+---------------------------------+
city_code | city code displayed in browser |
---------------------------------------------+
010 | 8 |
PRS | nothing |
-----------+---------------------------------+

I cannot locate the cause that is in PHP or Mysql.
Please help me fix this error!

BC AdBot (Login to Remove)

 


#2 Billy O'Neal

Billy O'Neal

    Visual C++ STL Maintainer


  • Malware Response Team
  • 12,304 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Redmond, Washington
  • Local time:03:12 PM

Posted 28 January 2013 - 11:50 PM

You are getting hit by the perils of dynamic typing. PHP interprets an integer with a leading zero as one in octal (base 8) format. Check for leading zeros and remove them before conversion to integer and/or passing to MySQL.

Billy3
Twitter - My statements do not establish the official position of Microsoft Corporation, and are my own personal opinion. (But you already knew that, right?)
Posted Image

#3 dungpt29

dungpt29
  • Topic Starter

  • Members
  • 45 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:05:12 AM

Posted 30 January 2013 - 12:05 AM

Hi Billy,

Can you help me write a PHP function that identifies whether a variable with leading zero or not and remove that leading zero before use?

Edited by dungpt29, 30 January 2013 - 12:09 AM.


#4 Billy O'Neal

Billy O'Neal

    Visual C++ STL Maintainer


  • Malware Response Team
  • 12,304 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Redmond, Washington
  • Local time:03:12 PM

Posted 30 January 2013 - 12:12 AM

Erm, you should be able to do that.

if ($input[0] === '0') { 
    // ...
}

Twitter - My statements do not establish the official position of Microsoft Corporation, and are my own personal opinion. (But you already knew that, right?)
Posted Image

#5 dungpt29

dungpt29
  • Topic Starter

  • Members
  • 45 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:05:12 AM

Posted 30 January 2013 - 02:51 AM

But the difficult point is that the variable contains alphabetical characters is not displayed in browser despite without leading zero.
For example:

<?php
$e = "AAA"; // No leading zero
$this->view->city_code = $e;

Display in view
<?php echo $this->city_code?>// Instead of displaying "AAA" the browser displays no data
                             // because PHP considers $e as an octal number not as string type

What can you explain about this, Billy?

#6 Billy O'Neal

Billy O'Neal

    Visual C++ STL Maintainer


  • Malware Response Team
  • 12,304 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Redmond, Washington
  • Local time:03:12 PM

Posted 30 January 2013 - 02:56 AM

Oh, well if that's the case it isn't even a number. So if your MySQL table has numbers in it that where clause you put in is never going to match.

Billy3
Twitter - My statements do not establish the official position of Microsoft Corporation, and are my own personal opinion. (But you already knew that, right?)
Posted Image

#7 dungpt29

dungpt29
  • Topic Starter

  • Members
  • 45 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:05:12 AM

Posted 30 January 2013 - 07:45 AM

Yes, I understand what you mean. Thank Billy for your answers!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users