Create a database and table

Beginners - Learning PHP
#1

Trying to create a database and table. When I go to phpAdmin from XAMPP it does not show the database or table I’m trying to create. Was wondering if anyone had any ideas about what I was doing wrong.

<?php 
$host = "localhost";
$username = "root";
$password = " ";
$db = "database";
$conn = mysqli_connect($host, $username, $password, $db);
$sql = "CREATE DATABASE IF NOT EXISTS newsad_hw2;   ";
$result = mysqli_query($conn, $sql);

if(!$result){
    echo "Database creation failed";
}
else{
    echo "Connection established, database created";
}

$sql2 = "CREATE TABLE IF NOT EXISTS tbl (id INT PRIMARY KEY AUTO_INCREMENT, 
        animal_name VARCHAR(50) NOT NULL,
        comments VARCHAR(255) NOT NULL,
        photo BLOB NOT NULL);";
$query = mysqli_query($conn, $sql2);

if(!$query){
    echo "Table creation failed";
}else{
    echo "Table created";
}


?>
#2

Two problems:

  1. Unless your DB password is a space, get rid if it in $password. Mysql will think you are using a password when you are not.
  2. You are connecting to one DB ($db =“database”) but trying to work with another. Remove the optional $db parameter.
#3

Forgot to change newsad_hw2 to database.
Will also change the password I will let you know if I get it working. Thanks for your help

#4

Fixed the problems you talked about but still did not create the database or table.

<?php 
$host = "localhost";
$username = "root";
$password = "";
$db = "newsad_hw2";
$conn = mysqli_connect($host, $username, $password, $db);
$sql = "CREATE DATABASE IF NOT EXISTS newsad_hw2;   ";
$result = mysqli_query($conn, $sql);

if(!$result){
    echo "Database creation failed";
}
else{
    echo "Connection established, database created";
}

$sql2 = "CREATE TABLE IF NOT EXISTS tbl_newsad (id INT PRIMARY KEY AUTO_INCREMENT, 
        animal_name VARCHAR(50) NOT NULL,
        comments VARCHAR(255) NOT NULL,
        photo BLOB NOT NULL);";
$query = mysqli_query($conn, $sql2);

if(!$query){
    echo "Table creation failed";
}else{
    echo "Table created";
}


?>
#5

No you didn’t. Read #2 again.

#7

Was able to create the table by switching the variable $db in the conn statement with the actual variable. Thanks for your help.

#8

Lets see your revised code.

#9 
<?php 
$host = "localhost";
$username = "root";
$password = "";
$conn = mysqli_connect($host, $username, $password, "newsad_hw2");
$sql = "CREATE DATABASE IF NOT EXISTS newsad_hw2;   ";
$result = mysqli_query($conn, $sql);

if(!$result){
    echo "Database creation failed";
}
else{
    echo "Connection established, database created";
}

$sql2 = "CREATE TABLE IF NOT EXISTS tbl_newsad (id INT PRIMARY KEY AUTO_INCREMENT, 
        animal_name VARCHAR(50) NOT NULL,
        comments VARCHAR(255) NOT NULL,
        photo BLOB NOT NULL);";
$query = mysqli_query($conn, $sql2);

if(!$query){
    echo "Table creation failed";
}else{
    echo "Table created";
}


?>
#10

That is still wrong. If you turned on error reporting you would see. Try it without an existing database named newsad_hw2. I already told you what to do in #2 but you didn’t listen to me.

#11

I have removed newsad_hw2 from the database. However, when I did that and ran the code again the database did not create again.

#12

I am done here. I told you EXACTLY what to do.

#13

Well, unless you are a serial database creator, (wanted by the FBI) I can’t really see the need to programme database creation. You can’t want hundreds or thousands of dbs!

Create a db (or two) manually, on the MySQL command line. Create a user with almost all privileges. Then login to that db as that user, and make any tables you need.

As for tables: lately I’ve made some Python to create tables in my database according to certain text files. As a raw amateur, I am amazed that it works, but it works!

Python creates the SQL command, then spits out the SQL command line and pauses. I copy and paste the SQL command in phpMyAdmin, try it, see if it works. Lately, it does just what I want, but I am never too confident, so I try first.

For example, I named a column: both

You can’t do that, both is a MySQL reserved word, but pmysql told me the error, and I changed both to both_

When I know everything works, I delete the table and let Python carry on. If and when I am certain everything is ok, I won’t bother testing.

I use Python pymsql module to connect to the db. Works great, also fetching data! Just as fast as connecting to my localhost. I am always amazed!

#14

@Lacy, it appears that you don’t have php’s error related settings setup so that php will help you by reporting and displaying all the errors it detects. Php’s error_reporting should be set to E_ALL and display_errors should be set to ON. You also don’t have consistent and complete error handling for all the database statements that can fail - connection, query, prepare, and execute. Your photo column is also defined as a BLOB data type, but you are (correctly) putting the filename into it, which is character/text data.

When you are learning, developing, and debugging code/query(ies), you should display all php errors and all database statement errors. When running code on a live/public server, you should log all these errors.

You can accomplish this by simply changing php’s error related settings, which should be in the php.ini on your system, so that you can change them in a single location, and by using exceptions for database statement errors and in most cases simply let php catch and handle any exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) You would then remove any existing database statement error handling logic in your code since it will no longer get executed upon an error, simplifying your code.

A reply in a different php help forum already gave you the line of code needed to enable exceptions for errors for the mysqli extension, so I won’t bother to repeat it here.

Your installation/initialization code to create any database/table(s) should be separate from your application code. This will simply your application code.

Sponsor our Newsletter | Privacy Policy | Terms of Service