|
|
|
|
Bookmark this on Delicious
Share on Facebook
 Slashdot It!
Digg  mysql_field_table
Get name of the table the specified field is in
(PHP 4, PHP 5, PECL mysql:1.0)
Example 1439. A mysql_field_table() example<?phpRelated Examples ( Source code ) » mysql_field_table Examples ( Source code ) » From result row get the table name Code Examples / Notes » mysql_field_tablecptnemo
When trying to find table names for a (My)SQL query containing 'tablename AS alias', mysql_field_table() only returns the alias as specified in the AS clause, and not the tablename.
jorge
The function below takes a function and returns the col->table mapping as an array. For example: $query = “SELECT a.id AS a_id, b.id b_id FROM atable AS a, btable b†$cols = queryAlias($query); print_r($cols); Returns: Array ( [a] => atable [b] => btable ) I can't promise it's perfect, but this function never hit production cause I ended up using mysqli methods instead. Enjoy -Jorge /** * Takes in a query and returns the alias->table mapping. * * @param string $query * @return array of alias mapping */ function queryAlias ( $query ) { //Make it all lower, we ignore case $substr = strtolower($query); //Remove any subselects $substr = preg_replace ( ‘/\(.*\)/’, â€, $substr); //Remove any special charactors $substr = preg_replace ( ‘/[^a-zA-Z0-9_,]/’, ‘ ‘, $substr); //Remove any white space $substr = preg_replace(‘/\s\s+/’, ‘ ‘, $substr); //Get everything after FROM $substr = strtolower(substr($substr, strpos(strtolower($substr),‘ from ‘) + 6)); //Rid of any extra commands $substr = preg_replace( Array( ‘/ where .*+$/’, ‘/ group by .*+$/’, ‘/ limit .*+$/’ , ‘/ having .*+$/’ , ‘/ order by .*+$/’, ‘/ into .*+$/’ ), â€, $substr); //Remove any JOIN modifiers $substr = preg_replace( Array( ‘/ left /’, ‘/ right /’, ‘/ inner /’, ‘/ cross /’, ‘/ outer /’, ‘/ natural /’, ‘/ as /’ ), ‘ ‘, $substr); //Replace JOIN statements with commas $substr = preg_replace(Array(‘/ join /’, ‘/ straight_join /’), ‘,’, $substr); $out_array = Array(); //Split by FROM statements $st_array = split (‘,’, $substr); foreach ($st_array as $col) { $col = preg_replace(Array(‘/ on .*+/’), â€, $col); $tmp_array = split(‘ ‘, trim($col)); //Oh no, something is wrong, let’s just continue if (!isset($tmp_array[0])) continue; $first = $tmp_array[0]; //If the “AS†is set, lets include that, if not, well, guess this table isn’t aliased. if (isset($tmp_array[1])) $second = $tmp_array[1]; else $second = $first; if (strlen($first)) $out_array[$second] = $first; } return $out_array; } me
Beware that if you upgrade to MySQL 5 from any earlier version WITHOUT dumping and reloading your data (just by keeping the binary data in MyISAM table files), you might get weird output on the "table" value for mysql_fetch_field and in this function. Weird means that the table name is randomly set or not. This behaviour seems to popup only if the SQL query contains a ORDER BY clause. A bug is already reported: http://bugs.mysql.com/bug.php?id=14915 To prevent the issue, dump and reload all participating tables in your query or do CREATE TABLE tmp SELECT * FROM table; DROP TABLE table; ALTER TABLE tmp RENAME table; on each one via commandline client. spam
<?php /* this function might help in the case described above :-) */ function mysql_field_table_resolve_alias($inQuery,$inResult,$inFieldName) { $theNameOrAlias = mysql_field_table($inResult,$inFieldName); //check, if AS syntax is being used if(ereg(" AS ",$inQuery)) { //catch words in query $theWords = explode(" ",ereg_replace(",|\n"," ",$inQuery)); //find the words preceding and following AS foreach($theWords as $theIndex => $theWord) { if(trim($theWord) == "AS" && isset($theWords[$theIndex-1]) && isset($theWords[$theIndex+1]) && $theWords[$theIndex+1] == $theNameOrAlias ) { $theNameOrAlias = $theWords[$theIndex-1]; break 1; } } } return $theNameOrAlias; } ?> |
Change Language
mysql_affected_rows mysql_change_user mysql_client_encoding mysql_close mysql_connect mysql_create_db mysql_data_seek mysql_db_name mysql_db_query mysql_drop_db mysql_errno mysql_error mysql_escape_string mysql_fetch_array mysql_fetch_assoc mysql_fetch_field mysql_fetch_lengths mysql_fetch_object mysql_fetch_row mysql_field_flags mysql_field_len mysql_field_name mysql_field_seek mysql_field_table mysql_field_type mysql_free_result mysql_get_client_info mysql_get_host_info mysql_get_proto_info mysql_get_server_info mysql_info mysql_insert_id mysql_list_dbs mysql_list_fields mysql_list_processes mysql_list_tables mysql_num_fields mysql_num_rows mysql_pconnect mysql_ping mysql_query mysql_real_escape_string mysql_result mysql_select_db mysql_set_charset mysql_stat mysql_tablename mysql_thread_id mysql_unbuffered_query |
