Page 1 of 1
select * question
Posted: Fri Feb 15, 2008 2:00 pm
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!
Re: select * question
Posted: Fri Feb 15, 2008 2:41 pm
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
Re: select * question
Posted: Fri Feb 15, 2008 6:05 pm
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.
Re: select * question
Posted: Fri Feb 15, 2008 8:20 pm
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.
Re: select * question
Posted: Mon Feb 25, 2008 12:15 pm
by aniket_dj
This works
Code: Select all
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'core_set' and TABLE_NAME like '%x%'