Home » Database » DB » Bug #4272

[2005-05-03 18:05 UTC] douglas dot day at calendarsystems dot com

Description:
------------
If I call tableInfo() with a "fully-qualified" table name (ie "dbname.tablename"), it won't work. This is because the MySQL wrapper uses the mysql_list_fields(), which does not allow this, and is a deprecated function.

PHP suggests using "SHOW COLUMNS FROM table" instead. I've included a partial patch in the following section (It doesn't handle passing a result resource to tableInfo(), only table names).

Reproduce code:
---------------
$info = $db->tableInfo("dbname.tablename");

//
// Partial Patch
// (only handles passing a table name)
//
function tableInfo($table, $mode = null)
{
if (is_string($table)) {
/*
* Probably received a table name.
* Create a result resource identifier.
*/
$id = @mysql_query("SHOW COLUMNS FROM {$table}", $this->connection);
$got_string = true;
}
else return null;

if (!is_resource($id)) {
return $this->mysqlRaiseError(DB_ERROR_NEED_MORE_DATA);
}

if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
$case_func = 'strtolower';
} else {
$case_func = 'strval';
}

$count = @mysql_num_fields($id);
$res = array();

if ($mode) {
$res['num_fields'] = $count;
}

for ($i = 0; $i < $count; $i++) {
$this->fetchInto($id, $v, DB_FETCHMODE_ORDERED);

$res[$i] = array('table' => $table);
$res[$i]["name"] = $v[0];
$res[$i]["type"] = $v[1];
if ($len = strpos($res[$i]["type"], "(")) {
$res[$i]["len"] = (int)substr($res[$i]["type"],$len+1);
$res[$i]["type"] = substr($res[$i]["type"],0,$len);
}
else $res[$i]["len"] = null;

// Get some flags
$res[$i]["flags"] = array();
if (empty($v[2]) || $v[2] == "NO")
$res[$i]["flags"][] = "not_null";
if ($v[3] == "PRI")
$res[$i]["flags"][] = "primary_key";
if (isset($v[5]))
$res[$i]["flags"][] = $v[5];
// FIXME: get "unique_key", "multiple_key", "blob", "unsigned", "zerofill", "binary", "enum", and "timestamp"

if ($mode & DB_TABLEINFO_ORDER) {
$res['order'][$res[$i]['name']] = $i;
}
if ($mode & DB_TABLEINFO_ORDERTABLE) {
$res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
}
}

// free the result only if we were called on a table
if ($got_string) {
@mysql_free_result($id);
}
return $res;
}

Expected result:
----------------
Table info

Actual result:
--------------
mysql_list_fields() returns with an error saying "dbname.dbname" does not exist.

[2005-05-03 22:52 UTC] douglas dot day at calendarsystems dot com

Thanks. I'll see if I can get that working in the next couple of days, and I'll repost it here when I'm sure it's working.

[2005-05-04 15:21 UTC] douglas dot day at calendarsystems dot com

Okay, I've been working with the code to find a good solution. Looks like the only function that's being used that's deprecated is mysql_list_fields(). So, instead of rewriting a bunch of stuff and probably losing performance along the way, I replace mysql_list_fields() with this query: "SELECT * FROM {$table} LIMIT 1" and then pull info from that. This fixes the problem, and entails very few changes in the code.

Also, I reordered the if/elseif/else statements to determine what $result really is. I was encountering buggy behavior with it -- it was recognizing a string as a resource object, which clearly broke things. This implementation is less error-prone.

Index: mysql.php
===================================================================
RCS file: /repository/pear/DB/DB/mysql.php,v
retrieving revision 1.117
diff -u -r1.117 mysql.php
--- mysql.php 29 Mar 2005 15:03:26 -0000 1.117
+++ mysql.php 4 May 2005 08:19:17 -0000
@@ -932,17 +932,10 @@
* Probably received a table name.
* Create a result resource identifier.
*/
- $id = @mysql_list_fields($this->dsn['database'],
- $result, $this->connection);
+ $id = @mysql_query("SELECT * FROM {$result} LIMIT 1", $this->connection);
$got_string = true;
- } elseif (isset($result->result)) {
- /*
- * Probably received a result object.
- * Extract the result resource identifier.
- */
- $id = $result->result;
- $got_string = false;
- } else {
+ }
+ else if (is_resource($result)) {
/*
* Probably received a result resource identifier.
* Copy it.
@@ -950,6 +943,13 @@
*/
$id = $result;
$got_string = false;
+ } else {
+ /*
+ * Probably received a result object.
+ * Extract the result resource identifier.
+ */
+ $id = $result->result;
+ $got_string = false;
}

if (!is_resource($id)) {
@@ -989,6 +989,7 @@
if ($got_string) {
@mysql_free_result($id);
}
+
return $res;
}

Thanks,
-Doug