CloudBreaker Posted July 3, 2015 Share Posted July 3, 2015 I'm fairly new to PHP. I'm trying to create a database while simultaneaously including tables. My syntax seems correct, however while the database is successfully created the tables are not. See below. Thank you... <!DOCTYPE HTML> <html> <head> <title>Create database</title> </head> <body> <form action="createdatabase.php" method="post"> <table align="center" bgcolor="gray" width="500"> <tr align="center"> <td colspan="6"> <h2>New Users Register Here</h2></td> </tr> <tr> <td align="right"><strong>Database Name:</strong></td> <td> <input type="text" name="db_name" placeholder="Enter the Project Name" /> </td> </tr> <tr align="center"> <td colspan="6"> <input type="submit" name="create_db" value="Create Project"/> </td> </tr> </form> <?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "test"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } if(isset($_POST['create_db'])){ $db_name = $_POST['db_name']; $sql = "CREATE DATABASE $db_name"; if ($conn->query($sql) === TRUE) { echo "Project created successfully"; } else { echo "Error creating database: " . $conn->error; } } $conn->close(); // Create new connection $conn = new mysqli($servername, $username, $password, $db_name); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // sql to create table $sql = "CREATE TABLE rfis( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, rfi_no VARCHAR(30) NOT NULL, rfi_posted date, rfi_needed date )"; // sql to create table $sql = "CREATE TABLE rfis ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, rfi_no VARCHAR(30) NOT NULL, rfi_posted date, rfi_needed date )"; if ($conn->query($sql) === TRUE) { echo "Table created successfully"; } else { echo "Error creating table: " . $conn->error; } $conn->close(); ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/ Share on other sites More sharing options...
requinix Posted July 3, 2015 Share Posted July 3, 2015 When you create the database you don't automatically USE it. Either use fully-qualified table names or call mysqli::select_db (after creating it, of course). Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/#findComment-1515493 Share on other sites More sharing options...
CloudBreaker Posted July 3, 2015 Author Share Posted July 3, 2015 Thanx AM. I'm not sure what you mean by "fully-qualify table names." As you can see, I'm using a form to get the desired name of the new db from the user. Once submitted the new db is successfully created, then the connection is closed. A new connection is created to the new db that was just created, then the syntax is entered in order to create the desired tables, which should work. I'm looking into the select_db. thnx Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/#findComment-1515508 Share on other sites More sharing options...
ginerjm Posted July 3, 2015 Share Posted July 3, 2015 Try turning on error checking so that any sql errors will be displayed for you. Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/#findComment-1515509 Share on other sites More sharing options...
scootstah Posted July 3, 2015 Share Posted July 3, 2015 (edited) I'm not sure what you mean by "fully-qualify table names." He means to prefix the table with the database name. Such as, SELECT * FROM databaseName.tableNameinstead of just SELECT * FROM tableName As you can see, I'm using a form to get the desired name of the new db from the user. Once submitted the new db is successfully created, then the connection is closed. Yes, but when you open the new connection you're not selecting the database that the user created. You can have many databases, you need to tell it which one to use. Edited July 3, 2015 by scootstah Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/#findComment-1515513 Share on other sites More sharing options...
CloudBreaker Posted July 3, 2015 Author Share Posted July 3, 2015 I appreciate the help from all of you. This Milestone has been accomplished. No errors and it works clean. I'm a architect by trade (the kind that designs buildings) so all of this twists my mind in a good way. Again, thanks. Here's the working code: <!DOCTYPE HTML> <html> <head> <title>Create database</title> </head> <body> <form action="createdatabase_tables.php" method="post"> <table align="center" bgcolor="gray" width="500"> <tr align="center"> <td colspan="6"> <h2>New Users Register Here</h2></td> </tr> <tr> <td align="right"><strong>Database Name:</strong></td> <td> <input type="text" name="db_name" placeholder="Enter the Project Name" /> </td> </tr> <tr align="center"> <td colspan="6"> <input type="submit" name="create_db" value="Create Project"/> </td> </tr> </form> <?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "test"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } if(isset($_POST['create_db'])){ $db_name = $_POST['db_name']; $sql = "CREATE DATABASE $db_name"; if ($conn->query($sql) === TRUE) { echo "Project $db_name created successfully"; } else { echo "Error creating database: " . $conn->error; } } // Selecting newly created database "db_name" if(isset($_POST['create_db'])){ $db_name = $_POST['db_name']; mysqli_select_db($conn,$db_name); // Creating tables in new database $sql = "CREATE TABLE rfis ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, rfi_no VARCHAR(30) NOT NULL, rfi_posted date, rfi_needed date )"; if ($conn->query($sql) === TRUE) { echo "Table created successfully"; } else { echo "Error creating table: " . $conn->error; } } $conn->close(); ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/#findComment-1515521 Share on other sites More sharing options...
scootstah Posted July 3, 2015 Share Posted July 3, 2015 Please use code tags in the future, it makes it easier to read. Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/#findComment-1515525 Share on other sites More sharing options...
mikosiko Posted July 3, 2015 Share Posted July 3, 2015 (edited) Are you using this code just as a learning exercise and for one time execution? (hope so) or are you planning using it to create a database and associated table(s) for Each project? If the lastest is the case then IMHO you are using an incorrect approach, you should have only one database and define your tables to manage multiples projects/rfis Edited July 3, 2015 by mikosiko Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/#findComment-1515530 Share on other sites More sharing options...
CloudBreaker Posted July 3, 2015 Author Share Posted July 3, 2015 (edited) Are you using this code just as a learning exercise and for one time execution? (hope so) or are you planning using it to create a database and associated table(s) for Each project? If the lastest is the case then IMHO you are using an incorrect approach, you should have only one database and define your tables to manage multiples projects/rfis mikosiko, I'm using it to create a database for each project that is created. That particular project database will include tables such as an RFI table. You can have hundreds of RFIs in a project with associated files "pdf's" that will need to be uploaded to that particular RFI (an RFI is a Request for Information from a contractor). You'll also have a good number of submittals that will be associated with that project which will also require it's own table and associated file uploads with each submittal. Each project (database) can grow to be huge, and having one database which contains say 50 projects can disrupt workflow if that one database were to become corrupt. . There would also be contractors from different companies who should not be able to view or access the projects they are not working on. I thought about having one database and how I'd go about setting that up, for instance I guess you'd break it down into tables....ex project1_rfis, project1_submittals. However, each rfi will have sub-parameters..."rfi-sender" "date submitted" "field question" etc. Can you imagine how enormous those tables would become over time. Even if I decided to create tables for each separate rfi per project, I'd have hundreds of tables to sift through if something went wrong. If I create a database for each project, it allows me to create one table for all the rfi's in that project along with all the necessary attributes associated with each rfi created. I'm green at this, so if my logic is off... Please advise thanks Edited July 3, 2015 by CloudBreaker Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/#findComment-1515531 Share on other sites More sharing options...
scootstah Posted July 3, 2015 Share Posted July 3, 2015 I guess you'd break it down into tables....ex project1_rfis, project1_submittals. No, you'd just make a "projects" table and then add a "project_id" to RFI's and submittals. Each project (database) can grow to be huge, and having one database which contains say 50 projects can disrupt workflow if that one database were to become corrupt. A valid concern, but there are ways to prevent or mitigate that, such as mirroring and routine backups. These should be in place regardless. There would also be contractors from different companies who should not be able to view or access the projects they are not working on. Well, your contractors wouldn't have access to the database, right? Therefore you can implement these restrictions in the code. Can you imagine how enormous those tables would become over time. I often see new comers worried about large databases. A modern database engine can scale to millions of records with ease. Although multiple databases can be a sound architectural decision in certain circumstances, I don't think this is one of them, based on the reasons you have given. I think you are just setting yourself up for a management nightmare. Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/#findComment-1515535 Share on other sites More sharing options...
CloudBreaker Posted July 3, 2015 Author Share Posted July 3, 2015 Thank you very much for the insight. I can see how it would become a management nightmare now. With one database, I can see that it's about linking tables and the corresponding data together. Time for redo. Good thing didn't get too far. Quote Link to comment https://forums.phpfreaks.com/topic/297157-simple-problem/#findComment-1515541 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.