CODEWITHSUNDEEP
phpmysql
Raja
Raja

Reputation: 33

how to parse Column fields of mysql using php

I am using a query ("SHOW COLUMNS FROM XYZ_table"). It displays the out like

Array ( [Field] => policyID [Type] => int(10) [Null] => YES [Key] => [Default] => [Extra] => ).

I am only concerned with PolicyID with it's type => int and size 10. How can I ignore other irrelevant data ? Here is a code: /* $i has size of this array "$tb_names" */

 while ($i!=0){
                $result = mysql_query("SHOW COLUMNS FROM $tb_names[$j] ");
        if (!$result) {
            echo 'Could not run query: ' . mysql_error();
        exit;
        }
        if (mysql_num_rows($result) > 0) {
            while ($row = mysql_fetch_assoc($result)) {
            $var = print_r($row,true);
            echo $var."<br>";

        }
    }       
            $i--;
            $j++;


    }

Upvotes: 1

Views: 201

Answers (2)

Passionate Coder
Passionate Coder

Reputation: 7294

Hi You can try this query

SELECT column_name,character_maximum_length , data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA =  'databasename'
AND TABLE_NAME =  'tablename';

for specific column 

SELECT column_name, character_maximum_length, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA =  'databasename'
AND TABLE_NAME =  'tablename'
AND column_name =  'columnname';

Without database name

SELECT column_name , data_type ,character_maximum_length FROM information_schema.columns
WHERE table_name = 'tablename';

For int length please use NUMERIC_PRECISION

SELECT column_name , data_type ,NUMERIC_PRECISION FROM information_schema.columns
    WHERE table_name = 'tablename';

Upvotes: 2

pcnate
pcnate

Reputation: 1774

You'll have to modify to suit your usecase, but try something along the lines of this:

$allowedColumns = array(
    'columna',
    'columnb',
    'columnc',
    'columnd',
);

while( $i!=0 ) {
  $result = mysql_query("SHOW COLUMNS FROM $tb_names[$j] ");

  if( !$result ) {
    echo 'Could not run query: ' . mysql_error();
    exit;
  }
  if( mysql_num_rows($result) > 0 ) {
    while( $row = mysql_fetch_assoc($result) ) {
      $columns = array();
      foreach( $row as $column ) {
        if( in_array( $column, $allowedColumns ) ) {
          $columns[] = $column;
        }
      }
      $var = print_r($columns,true);
      echo $var."<br>";
    }
  }       
  $i--;
  $j++;
}

Upvotes: 0

Related Questions