Do mysql_list_fields for a Join Query

Question

I am looking to populate an array of all columns that are being viewed in a query lets say

    $sql='SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
    FROM tutorials_tbl a, tcount_tbl b
    WHERE a.tutorial_author = b.tutorial_author';


    function getColoumns($sql) {
         $result = mysql_query("SHOW COLUMNS FROM (". $sql."));
  if (!$result) {
    echo 'Could not run query: ' . mysql_error();
  }
  $fieldnames=array();
  if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
      $fieldnames[] = $row['Field'];
    }
  }

  return $fieldnames;
    }

I cant seem to get it right. Anyone out there who can help me out. Thanks in Advance.

Besides the obvious missing `"`, any error messages? Can you describe your problem in more detail? — Sirko, Jul 31 '13 at 12:51
OOPs sorry for that , it should be .$sql) i had tried earlier, this was just how I wrote for the question. It is qiving a mysql_fetch_array() expects parameter 1 to be resource, boolean given. — Njuguna Mureithi, Jul 31 '13 at 13:06

score 1 · Accepted Answer · edited May 23 '17 at 10:31

1

SHOW COLUMNS does not allow for a subselect statement according to the MySQL documentation.

You could instead go for a combination of the original query, mysql_num_fields() and mysql_field_name():

function getColoumns($sql) {
  $result = mysql_query( $sql );
  if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    // return from function here. don't know what value you need.
    return array()
  }

  $fieldnames = array();
  $fieldCount = mysql_num_fields($result);
  for( $i=0; $i<$fieldCount; $i++ ) {
    $fieldnames[] = mysql_field_name( $result , $i );
  }

  return $fieldnames;
}

^{Besides, take a look at Why shouldn't I use mysql_* functions in PHP? and/or make sure the query is not malicious!}

edited May 23 '17 at 10:31

Community

1
1

answered Jul 31 '13 at 13:16

Sirko

72,589
19
149
183

Could not run query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT a.tutorial_id, a.tutorial_author,b.tutorial_count FROM tuto' at line 1 – Njuguna Mureithi Jul 31 '13 at 13:37
@GeoffreyMureithi Sorry, didn't read your question carefully enough. See the updated answer. – Sirko Jul 31 '13 at 13:47
Thanks It worked, And I think you forgot the **return array();** – Njuguna Mureithi Jul 31 '13 at 13:55

Do mysql_list_fields for a Join Query

1 Answers1