php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #32736 Select table.field returns wrong result index
Submitted: 2005-04-17 12:23 UTC Modified: 2005-04-18 16:05 UTC
From: bart at mediawave dot nl Assigned: wez (profile)
Status: Wont fix Package: Feature/Change Request
PHP Version: 5.0.4 OS: WinXP
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: bart at mediawave dot nl
New email:
PHP Version: OS:

 

 [2005-04-17 12:23 UTC] bart at mediawave dot nl
Description:
------------
With the SQLite functions:

The problem occurs when selecting fields in a tablename.fieldname manner. Example:

SELECT test.myfield, test.otherfield FROM test

The result array will have ['tablename.fieldname'] as index. This should be just ['fieldname'] as index.

Reproduce code:
---------------
<?php

$dbfile = 'db/test2db.db';

if (!file_exists($dbfile)) {
	$setup = "
		CREATE TABLE test (myfield varchar(50), otherfield varchar(50));
		INSERT INTO test (myfield, otherfield) VALUES ('value1', 'value1');
		INSERT INTO test (myfield, otherfield) VALUES ('value2', 'value2');
	";
}

$connectionId = sqlite_open($dbfile, 0666, $sqliteerror);

if ($setup) 
	sqlite_exec($connectionId, $setup);

$sql = 'SELECT test.myfield, test.otherfield FROM test';

$resultId = sqlite_query($connectionId, $sql);

while ($result = sqlite_fetch_array($resultId, SQLITE_ASSOC))
	print_r($result);

?>

Expected result:
----------------
Array
(
    [myfield] => value1
    [otherfield] => value1
)
Array
(
    [myfield] => value2
    [otherfield] => value2
)

Actual result:
--------------
Array
(
    [test.myfield] => value1
    [test.otherfield] => value1
)
Array
(
    [test.myfield] => value2
    [test.otherfield] => value2
)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-04-17 12:27 UTC] derick@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

Sorry, but this is how SQLite works.
 [2005-04-17 22:57 UTC] bart at mediawave dot nl
Can this be changed to a feature / change request? 

I've been looking around on the web. As far as I can see there are no other SQL system that give results in such a way. This just makes it unnecessarily difficult to write database independent code.

The only advantage I can think of for such a result is when tables that have identical fieldnames need to be joined. But SQL (and SQLite) already has aliases to solve that.

Maybe there should be a new SQLite result type constant for this?
 [2005-04-18 08:41 UTC] derick@php.net
It can be, but don't count on it being changed... assigned to the maintainer.
 [2005-04-18 14:02 UTC] wez@php.net
We leave sqlite in its default state for this kind of thing.
If you want to change the behaviour, you may issue a PRAGMA full_column_names query.

http://sqlite.org/pragma.html


 [2005-04-18 16:05 UTC] bart at mediawave dot nl
WFM... 

sqlite_query($connectionId, 'PRAGMA short_column_names = 1');
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved.
Last updated: Wed Jul 16 23:01:33 2025 UTC