mysql_field_seek
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;
}
mysql_field_table
(PHP 4, PHP 5, PECL mysql:1.0)
mysql_field_table — Pobiera nazwę tabeli w której znajduje się dane pole
Opis
string mysql_field_table
( resource $wynik
, int $ofset_pola
)
Zwraca nazwę tabeli w której znajduje się dane pole.
Parametry
- wynik
-
Wynik w postaci zmiennej typu resource, które jest przetwarzane. Wynik ten pochodzi z wywołania funkcji mysql_query().
- przesunięcie_pola
-
Liczbowe przesunięcie pola. przesunięcie_pola zaczyna się od wartości 0. Jeśli przesunięcie_pola nie istnieje, wygenerowane zostanie ostrzeżenie poziomu E_WARNING.
Zwracane wartości
Zwraca nazwę tabeli w przypadku powodzenia.
Przykłady
Example #1 Przykład użycia mysql_field_table()
<?php
$query = "SELECT konto.*, panstwo.* FROM konto, panstwo WHERE konto.nazwa = 'Polska' AND konto.panstwo_id = panstwo.id";
// Pobiera wynik z bazy danych
$result = mysql_query($query);
// Wyświetla nazwę tablicy, a następnie nazwę pola
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";
}
?>
Notatki
Informacja: Dla zachowania kompatybilności wstecznej, możliwe jest użycie poniższych aliasów: mysql_fieldtable()
add a note
User Contributed Notesmysql_field_table
jorge at rhst dot net
29-Jul-2007 11:57
29-Jul-2007 11:57
spam at blondella dot de
03-Oct-2006 01:09
03-Oct-2006 01:09
<?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;
}
?>
me at thomaskeller dot biz
23-Nov-2005 10:15
23-Nov-2005 10:15
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.
cptnemo
15-Aug-2004 04:18
15-Aug-2004 04:18
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.