Jump to content

Archived

This topic is now archived and is closed to further replies.

CloudBreaker

Simple Problem

Recommended Posts

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>

 

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Try turning on error checking so that any sql errors will be displayed for you.

Share this post


Link to post
Share on other sites

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.tableName
instead 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.

Share this post


Link to post
Share on other sites

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>

Share this post


Link to post
Share on other sites

Please use code tags in the future, it makes it easier to read.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.