CODEWITHSUNDEEP
mysqlwordpress
Mamunur Rashid
Mamunur Rashid

Reputation: 742

Fetch Data_type from mysql Table

I use this code:

select data_type from information_schema.columns where table_name = '$tableName' and column_name = '$column_name'

But after echo, it's returning 1. But when I run it in phpmyadmin sql it's returning data_type of column.

I want data_type of Mysql Table column (I'm using Wordpress).

Upvotes: 6

Views: 817

Answers (7)

Nuhel
Nuhel

Reputation: 196

This is the right one, I also used it. $sql="SELECT data_type FROM information_schema.columns WHERE table_name = '$tableName' AND column_name = '$column_name' ";

Upvotes: 1

Mamunur Rashid
Mamunur Rashid

Reputation: 742

$sql="SELECT data_type FROM information_schema.columns WHERE table_name = '$tableName' AND column_name = '$column_name' ";

Upvotes: 1

Charles
Charles

Reputation: 4352

It's printing one because that's the return value -- it means there is an error. You're trying to fetch a result set from a string (your query) rather than from the actual result object.

This is what will make it work:

$tableName = mysqli_real_escape_string($tableName);
$columnName = mysqli_real_escape_string($tableName);
$query = "select data_type ";
$query .= "from information_schema.columns ";
$query .= "where table_name = '$tableName' ";
$query .= "and column_name = '$column_name' ";
$vv = $wpdb->query($query); 
$row = mysqli_fetch_row($vv);

In your comment, you said this was your code statement:

$query = "select data_type ";
$query .= "from information_schema.columns ";
$query .= "where table_name = '$tableName' ";
$query .= "and column_name = '$column_name' ";
$vv = $wpdb->query($query); 
$x = mysql_fetch_field ( $query ); 
echo $x; 
var_dump($x);

Upvotes: 1

Samir Karmacharya
Samir Karmacharya

Reputation: 890

you can simply get the datatype as below. where ever you like . you can change different data_type by changing the value of "$tableName" and "$column_name" below

<?php
global $wpdb;$prefix=$wpdb->prefix;
$tableName=$prefix.'posts';
$column_name='post_title';
echo $sql="SELECT data_type FROM information_schema.columns WHERE table_name = '$tableName' AND column_name = '$column_name' ";
  $dataType = $wpdb->get_var($sql);        
echo $dataType;?>

Upvotes: 5

Rick James
Rick James

Reputation: 142298

Apparently too many tools (PHP / PhpAdmin) are getting in the way of going straight to the answer. If you are just looking for the answer, use the mysql commandline tool:

mysql> SELECT data_type FROM information_schema.columns
    ->     WHERE column_name = 'id'
    ->       AND table_schema = 'try'
    ->       AND table_name = 'a';
+-----------+
| data_type |
+-----------+
| int       |
+-----------+
1 row in set (0.01 sec)

If you do need to use the result in PHP, the treat it like any other SELECT -- use the appropriate API calls in mysqli_* or PDO to run the SELECT, then fetch the rows (one row in this case) and look at the columns (one column in this case).

Note that you should include the table_schema = ... in case there is another database with the same table_name and column_name.

Upvotes: 3

Rakesh Sojitra
Rakesh Sojitra

Reputation: 3658

Try this in wordpress. You should use get_var() or get_row() method.

$wpdb->query() returns only (int|false) Number of rows affected/selected or false on error

$dataType = $wpdb->get_var("select data_type from information_schema.columns where table_name = '$tableName' and column_name = '$column_name' ");        
echo $dataType;exit;

Edited :

I have tried in my local like. you should try by setting these two varibale $tableName='wp_posts'; $column_name='post_title'; like below :

$tableName='wp_posts';
$column_name='post_title';
$data = $wpdb->get_var("select data_type from information_schema.columns where table_name = '$tableName' and column_name = '$column_name'"); 
echo $data;

Output

mediumtext

Upvotes: 3

Gerard Roche
Gerard Roche

Reputation: 6381

The values of $tableName and $column_name in your Wordpress are not what you are expecting them to be.

I use this code:

select data_type from information_schema.columns where table_name = '$tableName' and column_name = '$column_name'

But after echo, it's returning 1. But when I run it in phpmyadmin sql it's returning data_type of column.

Because when you are running it in phpmyadmin you are substituting the values for $tableName and $column_name explicitly. Whatever values you are substituting explicitly are what you expect $tableName and $column_name to be inside your Wordpress. But they're not what you are expecting them to be. You'll discover this by explicitly setting the values inside your Wordpress.

select data_type from information_schema.columns where table_name = 'x' and column_name = 'y'

There isn't enough information in your question to figure out exactly what is wrong. All you've shown is a partial of what the code looks like. Nonetheless, the next step in the debugging process will be to explicitly set the variables, and then work backwards from the point of error.

$tableName = 'x';
$column_name = 'y';
// ...
select data_type from information_schema.columns where table_name = '$tableName' and column_name = '$column_name'

Debugging backwards you'll find the bug.

Upvotes: 2

Related Questions