[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Describe 1 field

Posted on 2013-05-31
4
Medium Priority
?
159 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
  • 2
4 Comments
 
LVL 41

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 41

Accepted Solution

by:
Kyle Abrahams earned 2000 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 111

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Suggested Courses

612 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