Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : MySQL Functions : mysql_field_table

mysql_field_table

Get name of the table the specified field is in (PHP 4, PHP 5, PECL mysql:1.0)
string mysql_field_table ( resource result, int field_offset )

Example 1439. A mysql_field_table() example

<?php

$query
= "SELECT account.*, country.* FROM account, country WHERE country.name = 'Portugal' AND account.country_id = country.id";

// get the result from the DB
$result = mysql_query($query);

// Lists the table name and then the field name
for ($i = 0; $i < mysql_num_fields($result); ++$i) {
   
$table = mysql_field_table($result, $i);
   
$field = mysql_field_name($result, $i);

   echo  
"$table: $field\n";
}

?>

Related Examples ( Source code ) » mysql_field_table


Code Examples / Notes » mysql_field_table

cptnemo

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


Follow Navioo On Twitter
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
eXTReMe Tracker