l7guxq
Last Updated: February 25, 2016
·
23.72K
· nicolaskempfpro
430088 3332822962381 57158174 n

How to convert string to integer (PHP & MySQL)

Here is a type conversion from string to int, the same way, with PHP & MySQL.
Basically, you can change the type of your string by adding 0.

PHP

$myVar = "13";
var_dump($myVar); // string '13' (length=2)
$myVar= $myVar +0; // or $myVar+= 0
var_dump($myVar); // int 13

MySQL

SELECT * FROM stop_times LEFT JOIN stops ON stop_times.stop_id=stops.stop_id WHERE stop_times.trip_id=2755013 ORDER BY stop_times.stop_sequence+0 ASC

In this example, the stop_sequence is stored as a Varchar, without the conversion, you'll have the stop_sequence in the following order : 1, 10, 11, 12, etc.

Note : This is a real use case when dealing with GTFS data (and bad database fields type)

Why using that tips ?

When you're in a PHP file, writing SQL query, it may be hard to know which functions to use : PHP or SQL.

Others ways to do the type conversion

In PHP, you can use the intval() function or cast your variable with (int) :

$myVar = "13";
var_dump($myVar); // string '13' (length=2)
$myVar= intval($myVar);
var_dump($myVar); // int 13

$myVar = "13";
var_dump($myVar); // string '13' (length=2)
$myVar= (int)$myVar;
var_dump($myVar); // int 13

In SQL, you can use the CAST() function :

SELECT * FROM stop_times LEFT JOIN stops ON stop_times.stop_id=stops.stop_id WHERE stop_times.trip_id=2755013 ORDER BY CAST(stop_times.stop_sequence AS UNSIGNED INTEGER) ASC

If you want more details about optimization in PHP : check this post on StackOverflow

Say Thanks
Respond