当前位置: 动力学知识库 > 问答 > 编程问答 >

MySQL SUBSTRING_INDEX Integrate with sqlite - PHP PDO

问题描述:

I am using PHP Data Objects to link the database. Below query is working fine with mysql.

$con = new PDO("mysql:host=localhost; dbname=company; charset=utf8", "root","root");

$data_value = $_GET['value'];

$sql = "SELECT SUBSTRING_INDEX(c_value, ',', 1) as c_index, SUBSTRING_INDEX(c_value, ',', -1) as c_name FROM `default_values` where category = 'Nationality'";

$smt = $con->prepare($sql);

$smt->execute();

$row = $smt->fetchAll(PDO::FETCH_OBJ);

print json_encode($row);

But when I connect to sqlite, I have to change the query to below code.

$con = new PDO('sqlite:d:/company.sqlite');

$data_value = $_GET['value'];

$sql = "SELECT substr(c_value,1, pos-1) as c_index, substr(c_value,pos+1) as c_name FROM (SELECT *, instr(c_value,',') as pos FROM default_values) WHERE category = 'Nationality';";

$smt = $con->prepare($sql);

$smt->execute();

$row = $smt->fetchAll(PDO::FETCH_OBJ);

print json_encode($row);

See Working MySQL Fiddle

See Working Sqlite Fiddle

Is there any way that we can do this with PHP-PDO, regardless of the database connection. I mean only change the db connection and without changing queries.

Thank You,

Supun

网友答案:

MySQL SUBSTRING_INDEX() returns the substring from the given string before a specified number of occurrences of a delimiter.

The substring returned from the left of the final delimiter when the specified number is a positive number and from right of the final delimiter when the specified number is a negative number.

If the specified number is greater than the number of occurrence of delimiter, the returned substring will be the total string. If the specified number is 0, nothing will be fetched from the given string.


The substr function can be used in the following versions of SQLite: SQLite 3.8.6, SQLite 3.8.x, SQLite 3.7.x, SQLite 3.6.x

For example:

sqlite> SELECT substr('TechOnTheNet.com', 5);
    -> 'OnTheNet.com'

sqlite> SELECT substr('TechOnTheNet.com', 5, 2);
    -> 'On'

sqlite> SELECT substr('TechOnTheNet.com', 1, 4);
    -> 'Tech'

sqlite> SELECT substr('TechOnTheNet.com', -3, 3);
    -> 'com'

sqlite> SELECT substr('TechOnTheNet.com', -3, 1);
    -> 'c'`
分享给朋友:
您可能感兴趣的文章:
随机阅读: