Solved

Describe 1 field

Posted on 2013-05-31
4
153 Views
Last Modified: 2013-06-18
Is it possible to only describe one field?

I've got a field called `state` in a table called `users`, so I would have thought I could use the statement:-
Describe `users` where `Field` = 'state';

Open in new window


But all I get back is:-
#1064 - 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 'where `Field` = 'state'' at line 1

If I use the statement:-
Describe `users`;

Open in new window


I get all the rows, but I only want the 1 row returned.

Any ideas how I can complete this?

Thank you
0
Comment
Question by:tonelm54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39210981
http://ss64.com/ora/desc.html

SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME_TO_DESCRIBE'
and columnName = 'state'
0
 

Author Comment

by:tonelm54
ID: 39211002
That seems to point to using it in Oracle, which doesnt seem to work in MySQL.

Sorry, maybe I should have said I was using MySQL.
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39211236
http://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names

Use this query:
SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='yourdatabasename'
    AND `TABLE_NAME`='yourtablename';
    and 'Column_Name' = 'state'

you can look at information_schema to determine if you need other info from it.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39218614
This is kind of old code and needs to be updated to remove the MySQL extension (explanation in the article).  You might tinker around with it to see how it works.  For obvious reasons I do not leave a copy on my server ;-)
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

<?php // RAY_mysql_SHOW_TABLES.php
error_reporting(E_ALL);
echo "<pre>\n"; // READABILITY FOR VAR-DUMPS


// DEMONSTRATE HOW TO GET INFORMATION ABOUT MySQL TABLES AND COLUMNS


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";



// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$dbcx = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $dbcx))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    trigger_error('NO DATA BASE', E_USER_ERROR);
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// FINDING THE NAMES OF OUR TABLES
$sql = "SHOW TABLES";

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE:http://php.net/manual/en/function.mysql-query.php
if (!$res = mysql_query($sql, $dbcx))
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-error.php
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// GET THE LIST OF TABLE NAMES
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
while ($show_tables = mysql_fetch_array($res))
{
    $my_tables[] = $show_tables[0];
}
if (empty($my_tables)) die('NO TABLES');
var_dump($my_tables);





// CHOOSE A TABLE AND GET THE COLUMN NAMES
$table_name = $my_tables[0];

$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = mysql_query($sql, $dbcx))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
if (mysql_num_rows($res) == 0)
{
    echo "<br/>$table_name HAS NO COLUMNS";
}
else
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
    while ($show_columns = mysql_fetch_assoc($res))
    {
        $my_columns[] = $show_columns["Field"];
        var_dump($show_columns);
    }
    var_dump($my_columns);
}





// CHOOSE A TABLE AND GET THE CREATE TABLE STATEMENT FOR IT
$table_name = $my_tables[0];

$sql = "SHOW CREATE TABLE $table_name";
if (!$res = mysql_query($sql, $dbcx))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
while ($show_creates = mysql_fetch_assoc($res))
{
    $my_creates[] = $show_creates["Create Table"];
}
var_dump($my_creates);






// GET A LIST OF ALL THE DATA BASES ON THIS CONNECTION
// MAN PAGE: http://php.net/manual/en/function.mysql-list-dbs.php
if (!$db_list = mysql_list_dbs($dbcx))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB LIST: ";
   echo "<br/> $errmsg <br/>";
   die();
}
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-object.php
while ($row = mysql_fetch_object($db_list))
{
    $db_names[] = $row->Database ;
}
echo "<br/>LIST OF DB NAMES: ";
var_dump($db_names);

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
date diff with Fiscal Calendar 4 76
MS SQL Server time between records 14 52
SSRS: Why is Visual Studio stripping these properties? 2 40
calculate days away 11 28
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question