select * question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
aniket_dj
Forum Newbie
Posts: 3
Joined: Fri Feb 15, 2008 1:59 pm

select * question

Post by aniket_dj »

Hi all,

I have a db called "core_set" which has a bunch of tables in it.

Basically I want to search for a table named "x" or "xx" or "xxx" inside db "core_set". I am scripting using PHP, but I guess this is purely a (My)SQL question.

I was hoping for something like

select *from db(core_set) where tables_in_core_set like '%x%';

right now 'show tables' returns me the names of tables, but I cant have select *from show tables as such.

Any ideas of how I can get around this?

Thanks!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: select * question

Post by califdon »

aniket_dj wrote:Hi all,

I have a db called "core_set" which has a bunch of tables in it.

Basically I want to search for a table named "x" or "xx" or "xxx" inside db "core_set". I am scripting using PHP, but I guess this is purely a (My)SQL question.

I was hoping for something like

select *from db(core_set) where tables_in_core_set like '%x%';

right now 'show tables' returns me the names of tables, but I cant have select *from show tables as such.

Any ideas of how I can get around this?

Thanks!
Either there is an immense problem with your schema or you are trying to do something quite bizarre. You should never need (nor want to) select all records from all tables in a database. Without Joins or a WHERE clause, that would produce what is called a Cartesian Join, which could result in generating an exponentially exploding result set. And to use Joins or a WHERE clause, you have to know what key fields to Join on. So what you're asking makes no sense. If you are inexperienced with relational databases, I respectfully suggest that you need to read some tutorials first. You might start with http://www.tekstenuitleg.net/en/article ... tutorial/3 or http://php.about.com/od/learnmysql/ss/mysql_3.htm
aniket_dj
Forum Newbie
Posts: 3
Joined: Fri Feb 15, 2008 1:59 pm

Re: select * question

Post by aniket_dj »

califdon wrote:
aniket_dj wrote:Hi all,

I have a db called "core_set" which has a bunch of tables in it.

Basically I want to search for a table named "x" or "xx" or "xxx" inside db "core_set". I am scripting using PHP, but I guess this is purely a (My)SQL question.

I was hoping for something like

select *from db(core_set) where tables_in_core_set like '%x%';

right now 'show tables' returns me the names of tables, but I cant have select *from show tables as such.

Any ideas of how I can get around this?

Thanks!
Either there is an immense problem with your schema or you are trying to do something quite bizarre. You should never need (nor want to) select all records from all tables in a database. Without Joins or a WHERE clause, that would produce what is called a Cartesian Join, which could result in generating an exponentially exploding result set. And to use Joins or a WHERE clause, you have to know what key fields to Join on. So what you're asking makes no sense. If you are inexperienced with relational databases, I respectfully suggest that you need to read some tutorials first. You might start with http://www.tekstenuitleg.net/en/article ... tutorial/3 or http://php.about.com/od/learnmysql/ss/mysql_3.htm
Califdon,

Thank you for your response. I have some knowledge of relational databases, and thanks for the links.

I guess you got my question wrong. I dont want to select fields from all tables, <if I did, I am stupid >. Lemme explain in a little more detail....

show tables would basically gimme a list of tables in a particular db. Now within this resultset from "show tables", I needed to find certain table(names) that matched a string input by a user. I am not interested in the contents of the individual tables as such.

So basically I needed something like

select tablename from (show tables) where tablename like '%<user keyword>%';

Hope you get the point.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: select * question

Post by califdon »

aniket_dj wrote:
califdon wrote:
aniket_dj wrote:Thank you for your response. I have some knowledge of relational databases, and thanks for the links.

I guess you got my question wrong. I dont want to select fields from all tables, <if I did, I am stupid >. Lemme explain in a little more detail....

show tables would basically gimme a list of tables in a particular db. Now within this resultset from "show tables", I needed to find certain table(names) that matched a string input by a user. I am not interested in the contents of the individual tables as such.

So basically I needed something like

select tablename from (show tables) where tablename like '%<user keyword>%';

Hope you get the point.
I see. When you use the expression Select * From, that implies that you want to select all fields from one or more table names following From. No, I don't think you can use SQL to search among table names in a database. You probably need to fill an array from show tables and then search the array elements with php.
aniket_dj
Forum Newbie
Posts: 3
Joined: Fri Feb 15, 2008 1:59 pm

Re: select * question

Post by aniket_dj »

This works

Code: Select all

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'core_set' and TABLE_NAME like '%x%'
Post Reply