mysql: create table with variable name
Moderator: General Moderators
mysql: create table with variable name
Greetings, I've been searching for a solution for days now, but with no success.
I'd like to be able to use CREATE statements with parametrized names for tables or db, those names should be taken from query results.
I'll describe my idea, I know the syntax is wrong, just to explain the general idea
$result = 'select * from test';
$sql = 'create table $result (a VAR NOT NULL)';
believe me I've searched a lot but it seems this relates to dynamic mysql and it seems quite an advanced topic, while I'm a newbie. Also, I saw that if I use
$result = 'abc';
in place of the query it works, I can't explain why. What I need is to transform the query result into a pure and simple text maybe.
Can you make an example?
Thanks!
I'd like to be able to use CREATE statements with parametrized names for tables or db, those names should be taken from query results.
I'll describe my idea, I know the syntax is wrong, just to explain the general idea
$result = 'select * from test';
$sql = 'create table $result (a VAR NOT NULL)';
believe me I've searched a lot but it seems this relates to dynamic mysql and it seems quite an advanced topic, while I'm a newbie. Also, I saw that if I use
$result = 'abc';
in place of the query it works, I can't explain why. What I need is to transform the query result into a pure and simple text maybe.
Can you make an example?
Thanks!
Re: mysql: create table with variable name
There is absolutely no good reason for you to do this.
What is the real problem you're trying to solve? Why do you think that creating assorted tables is the solution?
What is the real problem you're trying to solve? Why do you think that creating assorted tables is the solution?
Re: mysql: create table with variable name
Your first query will give you table names, you just have to get them out of the result set & into variables. Once the names are in variables (say $result), wrap your query in double quotes, and the variable will be parsed by PHP before sending to MySQL:
Code: Select all
$result = "teams1";
$sql = "create table $result (a VAR NOT NULL)";//query will be: create table teams1 (a VAR NOT NULL)Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: mysql: create table with variable name
I want to be able to access dynamically to those db or tables without knowing their names.requinix wrote:There is absolutely no good reason for you to do this.
What is the real problem you're trying to solve? Why do you think that creating assorted tables is the solution?
In my application I want to keep all data as much separated and ordered into specific tables as possible. If my user inserts some data I want also a table/db to be created from his insert, therefore I'll be able to access user-specific data while keeping everything in order. I don't expect millions of tables to be thus created, but dozens yes, and I want to automate the insert and retrieval of data so I don't have to manually create tables every time.
Is this a bad idea or technically not feasible?
It works if $result is statically defined, yes, but not if it is the result of a query.pickle wrote:Your first query will give you table names, you just have to get them out of the result set & into variables. Once the names are in variables (say $result), wrap your query in double quotes, and the variable will be parsed by PHP before sending to MySQL:
Code: Select all
$result = "teams1"; $sql = "create table $result (a VAR NOT NULL)";//query will be: create table teams1 (a VAR NOT NULL)
Re: mysql: create table with variable name
Of course not. You can't have the result of one query be in another query without eitherilcischi wrote:It works if $result is statically defined, yes, but not if it is the result of a query.Code: Select all
$result = "teams1"; $sql = "create table $result (a VAR NOT NULL)";//query will be: create table teams1 (a VAR NOT NULL)
A) using sub-queries (which you can't do with create statements) or
B) running the query, getting the result set back, pulling the results into a PHP variable and putting that variable in a subsequent query.
My example was just shorthand for B) because I didn't want to write that all out.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: mysql: create table with variable name
I see some serious misunderstandings here. As requinix tried to explain, the concept of creating separate tables dynamically and not under the developer's control is almost guaranteed to be a very bad idea. I can't think of ANY situation in which that would be useful, and indeed it could be dangerous. Databases should at all times be under the control of the dba (database adminstrator). As the web developer, it is YOU who must design the database in accordance with accepted relational database design criteria. I cannot emphasize too strongly that you are proceeding down a grievously flawed path.
As to the technical issue of why a simple variable is nothing like a query result, as pickle tried to explain, they are entirely different kinds of information. A simple variable is just a string (or a numeric or a boolean, etc.). A query result is a complex packet of data that may contain multi-dimensional arrays and cannot be treated like a simple variable! You must use one of the mysql_fetch functions to even get a single row, which is, itself, an array. Then you must assign the array elements to variables. Even if there should be only one row and one column (thus only one value), it is still an array and must be handled as such.
But the first issue is the more important one. You need to study at least the fundamental concepts of a relational database before you go any further.
As to the technical issue of why a simple variable is nothing like a query result, as pickle tried to explain, they are entirely different kinds of information. A simple variable is just a string (or a numeric or a boolean, etc.). A query result is a complex packet of data that may contain multi-dimensional arrays and cannot be treated like a simple variable! You must use one of the mysql_fetch functions to even get a single row, which is, itself, an array. Then you must assign the array elements to variables. Even if there should be only one row and one column (thus only one value), it is still an array and must be handled as such.
But the first issue is the more important one. You need to study at least the fundamental concepts of a relational database before you go any further.
Re: mysql: create table with variable name
Thanks for the replies, let's set aside coding issues for a moment, I'll post my attempts later.
For now I'd like to explore the more fundamental topic of my db organization even if this might not be right place, I do so because of your objections.
I want to create an online community for a wargame. Players should be able to create matches, any match will consist of a lot of units that have to move, attack etc, in short every single unit will have a set of orders every turn.
I can create pre-defined db called "matches" and put there all the orders, using a column with a match id to differentiate the various matches, but this table would be gigantic! why not letting the users create new tables and db dynamically? after all I've read that in mysql tables and db have no special meaning, they're just a way to organize things in a human readable form, but for mysql itself there's no difference between a lot of db with a few tables each and a few db with lots of tables each.
For now I'd like to explore the more fundamental topic of my db organization even if this might not be right place, I do so because of your objections.
I want to create an online community for a wargame. Players should be able to create matches, any match will consist of a lot of units that have to move, attack etc, in short every single unit will have a set of orders every turn.
I can create pre-defined db called "matches" and put there all the orders, using a column with a match id to differentiate the various matches, but this table would be gigantic! why not letting the users create new tables and db dynamically? after all I've read that in mysql tables and db have no special meaning, they're just a way to organize things in a human readable form, but for mysql itself there's no difference between a lot of db with a few tables each and a few db with lots of tables each.
Re: mysql: create table with variable name
I don't know where you read that, but its wrong, wrong, wrong! All relational databases are based on the "relational model" that was developed by Dr. E. F. Codd around 1960 and it is very specific about how tables and columns MUST be designed, in order for queries to work properly. The SQL language was created to work with databases that are designed in strict accordance with the "relational model" and if you fail to follow those rules, SQL will return unreliable query results, if anything at all. Each table represents either a real-life entity or a many-to-many relationship between 2 entities. There are some very specialized situations in which an experienced database developer may deviate in carefully designed ways, such as when using distributed databases for load balancing or truly gigantic data stores (billions of records) or enormously high numbers of concurrent users (thousands), but MySQL and other widely used relational databases are designed for multi-user, millions of records, datasets, and it would be an extremely rare situation where a competent developer would split up data into tables, and NEVER for such reasons as "for mysql itself there's no difference between a lot of db with a few tables each and a few db with lots of tables each" or "they're just a way to organize things in a human readable form", which is absolutely the REVERSE of the truth. That's a description of a spreadsheet, NOT a relational database! This is why I said there are some fundamental misunderstandings here.
Look, you are free to do whatever you want to do, but as someone who has developed hundreds of commercial database projects and taught database courses in college for more than a decade, I know that you are on a path to data disaster if you continue with these concepts, wherever they came from.
Look, you are free to do whatever you want to do, but as someone who has developed hundreds of commercial database projects and taught database courses in college for more than a decade, I know that you are on a path to data disaster if you continue with these concepts, wherever they came from.
Re: mysql: create table with variable name
What can I say, you've been very convincing. Ok I'll drop my idea and completely change my plans. Do you know good books about those principles and db design? they seem the kind of things you only learn in courses or with experience.califdon wrote:I don't know where you read that, but its wrong, wrong, wrong! All relational databases are based on the "relational model" that was developed by Dr. E. F. Codd around 1960 and it is very specific about how tables and columns MUST be designed, in order for queries to work properly. The SQL language was created to work with databases that are designed in strict accordance with the "relational model" and if you fail to follow those rules, SQL will return unreliable query results, if anything at all. Each table represents either a real-life entity or a many-to-many relationship between 2 entities. There are some very specialized situations in which an experienced database developer may deviate in carefully designed ways, such as when using distributed databases for load balancing or truly gigantic data stores (billions of records) or enormously high numbers of concurrent users (thousands), but MySQL and other widely used relational databases are designed for multi-user, millions of records, datasets, and it would be an extremely rare situation where a competent developer would split up data into tables, and NEVER for such reasons as "for mysql itself there's no difference between a lot of db with a few tables each and a few db with lots of tables each" or "they're just a way to organize things in a human readable form", which is absolutely the REVERSE of the truth. That's a description of a spreadsheet, NOT a relational database! This is why I said there are some fundamental misunderstandings here.
Look, you are free to do whatever you want to do, but as someone who has developed hundreds of commercial database projects and taught database courses in college for more than a decade, I know that you are on a path to data disaster if you continue with these concepts, wherever they came from.
Re: mysql: create table with variable name
The definitive book, used by nearly every Computer Science course in the world, is the 8th edition of Chris Date's book, An Introduction to Database Systems, an admittedly dense book (but I'm partial to it, having taken a seminar from Chris Date many years ago, and Date worked with Dr. Codd in developing the mathematics of the relational model - see Wikipedia). But you can find many tutorials online, some of them even good! I can recommend these, although there are many others that I haven't even taken the time to read, if you want to search for them:
http://www.kirupa.com/developer/php/rel ... design.htm
http://media.wiley.com/product_data/exc ... 141897.pdf
http://www.dreamincode.net/forums/topic ... alization/
http://www.deeptraining.com/litwin/dbde ... esign.aspx
http://parallel.vub.ac.be/documentation/database/tutor/
http://www.youtube.com/watch?v=z2kbsG8zsLM
https://www.e-education.psu.edu/geog863/l5_p3.html
There's a lot to learn and the very best first step you can take is to drain your mind of every concept you have formed about a database! Database design is unlike any other mental activity you have engaged in until now. That's a strong statement, but after years of teaching and coaching students, I think I can safely make it.
Good luck, and when you encounter questions, please feel welcome to return here and ask questions.
http://www.kirupa.com/developer/php/rel ... design.htm
http://media.wiley.com/product_data/exc ... 141897.pdf
http://www.dreamincode.net/forums/topic ... alization/
http://www.deeptraining.com/litwin/dbde ... esign.aspx
http://parallel.vub.ac.be/documentation/database/tutor/
http://www.youtube.com/watch?v=z2kbsG8zsLM
https://www.e-education.psu.edu/geog863/l5_p3.html
There's a lot to learn and the very best first step you can take is to drain your mind of every concept you have formed about a database! Database design is unlike any other mental activity you have engaged in until now. That's a strong statement, but after years of teaching and coaching students, I think I can safely make it.
Good luck, and when you encounter questions, please feel welcome to return here and ask questions.