Bug #14915 | mysql_result(..., "tablename.fieldname") + Upgrade 4.1 -> 5.0 | ||
---|---|---|---|
Submitted: | 14 Nov 2005 9:36 | Modified: | 10 Sep 2008 14:07 |
Reporter: | Mark Plomer | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
Version: | 5.0.15 | OS: | Linux (Linux (not relevant)) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[14 Nov 2005 9:36]
Mark Plomer
[14 Nov 2005 9:42]
Mark Plomer
When i wrote "rowname", i meant "fieldname" ...
[14 Nov 2005 9:44]
Mark Plomer
[i would not change the Subject before ...]
[14 Nov 2005 10:32]
Mark Plomer
I forgot to mention the error-message, that is displayed, when calling the mysql_result(..., "tablename.fieldname"): "tablename.fieldname not found in MySQL result index 8 in ....php"
[23 Nov 2005 21:15]
Thomas Keller
Hi there! I can confirm this behaviour, though I updated also from 4.0.25 to 5.0.16 on a FC2 machine. The same behaviour popped up on a 4.1.9 to 5.0.16 upgrade on my local FC4 installation. In both cases the binary tables weren't touched during upgrade. I used PHP's mysql_fetch_field to retrieve info about each field in my query. The query contains one join and uses aliases. Like the post author already told, the table name isn't set for each returned field, unless the ORDER BY part is ommitted. Here is a small PHP code snippet: <?php error_reporting(E_ALL); include "../admin/globals.inc.php"; include "../lib.inc.php"; // does a simple mysql_connect and mysql_select_db for our db $link = mysqlConnect(); $sql = 'SELECT SQL_CALC_FOUND_ROWS tb.*, t0.* FROM users as tb LEFT JOIN users_online AS t0 ON 1 AND t0.user_id=tb.user_id ORDER BY `actphrase` asc'; $res = mysql_query($sql, $link) or die(mysql_error()); $fieldCount = mysql_num_fields($res); for ($i=0; $i<$fieldCount; $i++) { echo $i," ",mysql_field_table($res, $i),"<br>"; } ?> The code above works flawlessly in 4.0.x and 4.1.x, but breaks in 5.0.16 as it only partially returns the table, not always. The fieldname after which is ordered is irrelevant. I'm now trying to dump and restore my db and see if this helps to fix the issue.
[23 Nov 2005 22:03]
Thomas Keller
Indeed, after doing CREATE TABLE tmp SELECT * FROM table; DROP TABLE table; ALTER TABLE tmp RENAME table; for all included tables in my query via commandline client, the problem vanished.
[5 Apr 2006 19:06]
Devin Butts
This problem still exists for me. The TableName.FieldName doesn't work. I'm using MySQL 5.0.18-Debian_1. I tried the above, recreating the table with no success. I'm having to create an array of field references vs field index to reference fields with the same name from different tables in one result because of an inner join. The problem exists for me when using both PHP4 and PHP5. (Other key words for search: Table dot Field Table Name dot Field Name)
[25 Jul 2006 7:43]
Hartmut Holzgraefe
Yet another condition: - only the last column for each table is affected I tracked things down to this difference in the 4.1 and 5.0 .frm files defining the user table: -00002170 05 4a ff 00 05 00 00 00 00 00 00 00 fd 08 00 00 |.J..............| +00002170 05 4a ff 00 05 00 00 00 00 00 00 00 0f 08 00 00 |.J..............| ^^ Once i change this byte from 0xFD to 0xFF in the user.frm file taken from 4.1 the problem with the last column of the user tabel goes away but still persists for the animals table
[25 Jul 2006 11:06]
Hartmut Holzgraefe
C test case and data files (SQL and MyISAM)
Attachment: bug_14915-1.0.tar.bz2 (application/x-tar, text), 188.43 KiB.
[25 Jul 2006 11:09]
Hartmut Holzgraefe
Added a C example to show that: - it is not a PHP specific problem - it is mysql_fetch_field() which is to blame (although the real problem is on the server side)
[30 Aug 2006 10:24]
Konstantin Osipov
This is a duplicate of Bug#14897 "ResultSet.getString("table.column") sometimes doesn't find the column", which is fixed in 5.0-runtime and will hopefully make it into 5.0.25. I tested the bug against 4.1, 5.0 and 5.0 runtime, and in 5.0 I get the following metadata: | animal.id | animal.user_id | .name | user.id | .name | whereas in 5.0-runtime (with the fix for Bug#14897) and 4.1 the result is correct: | animal.id | animal.user_id | animal.name | user.id | user.name |
[10 Sep 2008 13:50]
Richard Bagnall
The following is failing for me. I seem unable to reference the field value using the field name 'firstname', though using the index '0' works? Can anyone advise this perplexed newbie please? $strSQL = "SELECT firstname FROM users WHERE id = ".$_GET["id"]; //echo($strSQL); $result = mysql_query( $strSQL ) or die(strSQLError($strSQL)); //echo($strSQL); if ($result){ $nrows = mysql_num_rows($result); $row = mysql_fetch_row($result); //echo( $row[0] ); $strFirstName = $row['firstname']; }
[10 Sep 2008 14:07]
Mark Plomer
This is absolutely the wrong place to ask such questions. But have a look at mysql_fetch_assoc(), instead of mysql_fetch_row().
[10 Sep 2008 14:10]
Richard Bagnall
Sorry & Thanks.
[24 Dec 2008 10:52]
max ali
maxhex