how to parse Column fields of mysql using php

Question

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++;


    }

What is the problem having extra fields within array? Or you can simply uset() those from array. — Alok Patel
– Alok Patel, Commented Aug 11, 2016 at 6:04
it's a requirement not to get extra fields , I need to parse them — Raja
– Raja, Commented Aug 11, 2016 at 6:06
You must unset them in PHP, transpose the array in PHP or change your SQL to return only what you want included. — pcnate
– pcnate, Commented Aug 11, 2016 at 6:08

Passionate Coder · Accepted Answer · 2016-08-11 06:31:56Z

2

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';

edited Aug 11, 2016 at 6:31

answered Aug 11, 2016 at 6:15

Passionate Coder

7,3042 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Raja Over a year ago

thnak you soo much it helped me going more close to the solution. here is the output: "Array ( [column_name] => policyID [character_maximum_length] => [data_type] => int ) " why doesn't it gives me the lenght which I have defined like 10 in this case

Kevin Over a year ago

@Raja use COLUMN_TYPE instead

Passionate Coder Over a year ago

@Raja Please use NUMERIC_PRECISION for int type length

Raja Over a year ago

@user1234 : yes it worked somehow. But I need to parse it more. like Col_Name TYPE Size PolicyID INT 10

Passionate Coder Over a year ago

@Raja then you shuld use this query

SELECT column_name  AS Col_Name , data_type AS TYPE  ,NUMERIC_PRECISION  AS Size FROM information_schema.columns     WHERE table_name = 'tablename'

|

pcnate · Accepted Answer · 2016-08-11 06:20:48Z

0

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++;
}

answered Aug 11, 2016 at 6:20

pcnate

1,7941 gold badge20 silver badges39 bronze badges

3 Comments

Raja Over a year ago

I am going to try ur sol

pcnate Over a year ago

If it works, mark as answer, if not ask more questions

Raja Over a year ago

I tried ur sol but it ain't working. thanks for the effort.

Collectives™ on Stack Overflow

how to parse Column fields of mysql using php

2 Answers 2

10 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related