SQL query on information_schema

I'm trying to get a print out of the table structure of my
"_fakerockridge" database using information_schema. In PHPAdmin I
run thr SQL query but I get an error. Do I have to add all the tables
in information_schema? I got this example from the MYsql manual. It
seems like it should work. I just renamed the database to my database
name.

SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = '_fakerockridge'
ORDER BY table_name DESC;

I get the empty set:




LIMIT 0 , 30

JRough wrote:
> I'm trying to get a print out of the table structure of my
> "_fakerockridge" database using information_schema. In PHPAdmin I
> run thr SQL query but I get an error. Do I have to add all the tables
> in information_schema? I got this example from the MYsql manual. It
> seems like it should work. I just renamed the database to my database
> name.
>
> SELECT table_name, table_type, engine
> FROM information_schema.tables
> WHERE table_schema = '_fakerockridge'
> ORDER BY table_name DESC;
>
> I get the empty set:
>
>
>
>
> LIMIT 0 , 30

An empty result is not an error. It is a perfectly correct result where the
WHERE clause is not satisfied.

When I get a result like this, I remove WHERE clause (or just comment it out
with a #) and run the query to see what could possibly be in the WHERE
clause.

On May 22, 12:58 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> JRough wrote:
> > I'm trying to get a print out of the table structure of my
> > "_fakerockridge" database using information_schema. In PHPAdmin I
> > run thr SQL query but I get an error. Do I have to add all the tables
> > in information_schema? I got this example from the MYsql manual. It
> > seems like it should work. I just renamed the database to my database
> > name.
>
> > SELECT table_name, table_type, engine
> > FROM information_schema.tables
> > WHERE table_schema = '_fakerockridge'
> > ORDER BY table_name DESC;
>
> > I get the empty set:
>
> > LIMIT 0 , 30
>
> An empty result is not an error. It is a perfectly correct result where the
> WHERE clause is not satisfied.
>
> When I get a result like this, I remove WHERE clause (or just comment it out
> with a #) and run the query to see what could possibly be in the WHERE
> clause.

Hi,
thanks you are right.
I commented out the WHERE clause and I did get a result however it
doesn't
appear to be one of the databases in the window in the SQL view of
PHPAdmin.
How do I clear it out?
tia,
VIEWS SYSTEM VIEW MyISAM
USER_PRIVILEGES SYSTEM VIEW MEMORY
TRIGGERS SYSTEM VIEW MyISAM
TABLE_PRIVILEGES SYSTEM VIEW MEMORY
TABLE_CONSTRAINTS SYSTEM VIEW MEMORY
TABLES SYSTEM VIEW MEMORY
STATISTICS SYSTEM VIEW MEMORY
settings BASE TABLE MyISAM
SCHEMA_PRIVILEGES SYSTEM VIEW MEMORY
SCHEMATA SYSTEM VIEW MEMORY
ROUTINES SYSTEM VIEW MyISAM
rental_rate_view VIEW NULL
rental_rate_view VIEW NULL
PROFILING SYSTEM VIEW MEMORY
users BASE TABLE MyISAM
settings BASE TABLE MyISAM
request BASE TABLE MyISAM
payments BASE TABLE MyISAM
news BASE TABLE MyISAM
increments BASE TABLE MyISAM
help BASE TABLE MyISAM
feedbacks BASE TABLE MyISAM
durations BASE TABLE MyISAM
countries BASE TABLE MyISAM
counters BASE TABLE MyISAM
categories_plain BASE TABLE MyISAM
categories BASE TABLE MyISAM
bids BASE TABLE MyISAM
auctions BASE TABLE MyISAM
adminusers BASE TABLE MyISAM

JRough wrote:
> On May 22, 12:58 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> JRough wrote:
>> > I'm trying to get a print out of the table structure of my
>> > "_fakerockridge" database using information_schema. In PHPAdmin I
>> > run thr SQL query but I get an error. Do I have to add all the
>> > tables in information_schema? I got this example from the MYsql
>> > manual. It seems like it should work. I just renamed the
>> > database to my database name.
>>
>> > SELECT table_name, table_type, engine
>> > FROM information_schema.tables
>> > WHERE table_schema = '_fakerockridge'
>> > ORDER BY table_name DESC;
>>
>> > I get the empty set:
>>
>> > LIMIT 0 , 30
>>
>> An empty result is not an error. It is a perfectly correct result
>> where the WHERE clause is not satisfied.
>>
>> When I get a result like this, I remove WHERE clause (or just
>> comment it out with a #) and run the query to see what could
>> possibly be in the WHERE clause.
>
> Hi,
> thanks you are right.
> I commented out the WHERE clause and I did get a result however it
> doesn't
> appear to be one of the databases in the window in the SQL view of
> PHPAdmin.
> How do I clear it out?
I have no idea what you are talking about????

On May 22, 12:58 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> JRough wrote:
> > I'm trying to get a print out of the table structure of my
> > "_fakerockridge" database using information_schema. In PHPAdmin I
> > run thr SQL query but I get an error. Do I have to add all the tables
> > in information_schema? I got this example from the MYsql manual. It
> > seems like it should work. I just renamed the database to my database
> > name.
>
> > SELECT table_name, table_type, engine
> > FROM information_schema.tables
> > WHERE table_schema = '_fakerockridge'
> > ORDER BY table_name DESC;
>
> > I get the empty set:
>
> > LIMIT 0 , 30
>
> An empty result is not an error. It is a perfectly correct result where the
> WHERE clause is not satisfied.
>
> When I get a result like this, I remove WHERE clause (or just comment it out
> with a #) and run the query to see what could possibly be in the WHERE
> clause.

Hi Sorry,
I commented out the WHERE clause
and I got the information_schema on all of the databases, ow which
there are 3.
They are in alphabetical order which isn't very helpful since it mixes
the tables in the databases together.
How can I get the WHERE clause to pull up the schema for only one
database?
Maybe that can't happen with information_schema it is only set up to
do all databases?
If I can't get the schema on only one how can I order the tables in
order of each of the 3 databases?

thanks,

JRough wrote:
> On May 22, 12:58 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> JRough wrote:
>> > I'm trying to get a print out of the table structure of my
>> > "_fakerockridge" database using information_schema. In PHPAdmin I
>> > run thr SQL query but I get an error. Do I have to add all the
>> > tables in information_schema? I got this example from the MYsql
>> > manual. It seems like it should work. I just renamed the
>> > database to my database name.
>>
>> > SELECT table_name, table_type, engine
>> > FROM information_schema.tables
>> > WHERE table_schema = '_fakerockridge'
>> > ORDER BY table_name DESC;
>>
>> > I get the empty set:
>>
>> > LIMIT 0 , 30
>>
>> An empty result is not an error. It is a perfectly correct result
>> where the WHERE clause is not satisfied.
>>
>> When I get a result like this, I remove WHERE clause (or just
>> comment it out with a #) and run the query to see what could
>> possibly be in the WHERE clause.
>
> Hi Sorry,
> I commented out the WHERE clause
> and I got the information_schema on all of the databases, ow which
> there are 3.
> They are in alphabetical order which isn't very helpful since it mixes
In alphabetical order of what?

> the tables in the databases together.
Are you sure that you know how to normalise databases? You seem to lack the
basic knowledge of SQL and how a RDBMS works.
The output order of a query is inderterminate unless a specific ORDER BY
clause is coded.


> How can I get the WHERE clause to pull up the schema for only one
> database?
Put the name of one of the databases listed in the TABLE_SCHEMA column in
the where clause

> Maybe that can't happen with information_schema it is only set up to
> do all databases?
No it is not.

> If I can't get the schema on only one how can I order the tables in
> order of each of the 3 databases?
Please read about SQL.

>
> thanks,

On May 22, 3:17 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> JRough wrote:
> > On May 22, 12:58 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > wrote:
> >> JRough wrote:
> >> > I'm trying to get a print out of the table structure of my
> >> > "_fakerockridge" database using information_schema. In PHPAdmin I
> >> > run thr SQL query but I get an error. Do I have to add all the
> >> > tables in information_schema? I got this example from the MYsql
> >> > manual. It seems like it should work. I just renamed the
> >> > database to my database name.
>
> >> > SELECT table_name, table_type, engine
> >> > FROM information_schema.tables
> >> > WHERE table_schema = '_fakerockridge'
> >> > ORDER BY table_name DESC;
>
> >> > I get the empty set:
>
> >> > LIMIT 0 , 30
>
> >> An empty result is not an error. It is a perfectly correct result
> >> where the WHERE clause is not satisfied.
>
> >> When I get a result like this, I remove WHERE clause (or just
> >> comment it out with a #) and run the query to see what could
> >> possibly be in the WHERE clause.
>
> > Hi Sorry,
> > I commented out the WHERE clause
> > and I got the information_schema on all of the databases, ow which
> > there are 3.
> > They are in alphabetical order which isn't very helpful since it mixes
>
> In alphabetical order of what?
>
> > the tables in the databases together.
>
> Are you sure that you know how to normalise databases? You seem to lack the
> basic knowledge of SQL and how a RDBMS works.
> The output order of a query is inderterminate unless a specific ORDER BY
> clause is coded.
>
> > How can I get the WHERE clause to pull up the schema for only one
> > database?
>
> Put the name of one of the databases listed in the TABLE_SCHEMA column in
> the where clause
>
> > Maybe that can't happen with information_schema it is only set up to
> > do all databases?
>
> No it is not.
>
> > If I can't get the schema on only one how can I order the tables in
> > order of each of the 3 databases?
>
> Please read about SQL.
>
>
>
> > thanks,

I do know how to do a SELECT query and ORDER BY.
I did put in the database name in the WHERE clause and that is when I
got the empty set.
When I leave out the WHERE clause I get all of the database tables in
the information_schema.
The problem seems to be in PHPAdmin. There is a drop down list of all
the databases and the information_schema.
When I select information_schema in PHPAdmin it ignores the WHERE
clause.

Janis

On May 22, 3:17 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> JRough wrote:
> > On May 22, 12:58 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > wrote:
> >> JRough wrote:
> >> > I'm trying to get a print out of the table structure of my
> >> > "_fakerockridge" database using information_schema. In PHPAdmin I
> >> > run thr SQL query but I get an error. Do I have to add all the
> >> > tables in information_schema? I got this example from the MYsql
> >> > manual. It seems like it should work. I just renamed the
> >> > database to my database name.
>
> >> > SELECT table_name, table_type, engine
> >> > FROM information_schema.tables
> >> > WHERE table_schema = '_fakerockridge'
> >> > ORDER BY table_name DESC;
>
> >> > I get the empty set:
>
> >> > LIMIT 0 , 30
>
> >> An empty result is not an error. It is a perfectly correct result
> >> where the WHERE clause is not satisfied.
>
> >> When I get a result like this, I remove WHERE clause (or just
> >> comment it out with a #) and run the query to see what could
> >> possibly be in the WHERE clause.
>
> > Hi Sorry,
> > I commented out the WHERE clause
> > and I got the information_schema on all of the databases, ow which
> > there are 3.
> > They are in alphabetical order which isn't very helpful since it mixes
>
> In alphabetical order of what?
>
> > the tables in the databases together.
>
> Are you sure that you know how to normalise databases? You seem to lack the
> basic knowledge of SQL and how a RDBMS works.
> The output order of a query is inderterminate unless a specific ORDER BY
> clause is coded.
the 3 databases?
>
> Please read about SQL.

The problem seems to be in PHPAdmin. There is a drop down list that
has the 3 databases plus the information_schema database.
If I choose information_schema then when I run the first query above
with the WHERE clause I get the empty set. IF I comment out the WHERE
clause then I get all the tables in all the databases. I only want
the tables from one database.
I also tried the query below and it did the same as the first one. It
gave me an empty set.

SELECT SCHEMA_NAME AS `Database`
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME ='_fakerockridge';

I will read about SQL but first I have to get past this.

tia,
janis

JRough wrote:
>
> I do know how to do a SELECT query and ORDER BY.
If you know about ORDER BY, why were you complaining about the order of
returned rows and asking "how can I order the tables in order of each of the
3 databases?"

> I did put in the database name in the WHERE clause and that is when I
> got the empty set.
In that case you did not put the database name in in the correct format.

> When I leave out the WHERE clause I get all of the database tables in
> the information_schema.
So do a DISTINCT query on only the table_schema column and see what the
database is ACTUALLY called.

> The problem seems to be in PHPAdmin. There is a drop down list of all
> the databases and the information_schema.
> When I select information_schema in PHPAdmin it ignores the WHERE
> clause.
You are supposed to be running an SQL query in an SQL query box, not listing
the results by using a drop down.

On May 22, 4:23 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> JRough wrote:
>
> > I do know how to do a SELECT query and ORDER BY.
>
> If you know about ORDER BY, why were you complaining about the order of
> returned rows and asking "how can I order the tables in order of each of the
> 3 databases?"
>
> > I did put in the database name in the WHERE clause and that is when I
> > got the empty set.
>
> In that case you did not put the database name in in the correct format.
>
> > When I leave out the WHERE clause I get all of the database tables in
> > the information_schema.
>
> So do a DISTINCT query on only the table_schema column and see what the
> database is ACTUALLY called.
>
> > The problem seems to be in PHPAdmin. There is a drop down list of all
> > the databases and the information_schema.
> > When I select information_schema in PHPAdmin it ignores the WHERE
> > clause.
>
> You are supposed to be running an SQL query in an SQL query box, not listing
> the results by using a drop down.

Yeah, oh yeah, oh yeah!
I got some where, it turns out the distinct worked. what was wrong
was the database name in PHPAdmin was an alias to the real database
name or a shortcut or something. I put in the real name and the first
query worked.

thanks,