Create database and tables with PHP

Question

I'm creating a db and table creation script. The db is being created, but the tables are not. When I copy my CREATE TABLE sql directly into phpMyAdmin, it inserts successfully. So I'm not sure what the issue is.

function createdb() {
        include 'DbConnect.php';
        $qry = "CREATE DATABASE chinesegame2";       
        $sql = $mysqli->query($qry);

        $stats = "CREATE TABLE stats (
                    id_stats    INT(10) NOT NULL AUTO_INCREMENT,
                    PRIMARY KEY (id_stats),
                    id_user_fk  VARCHAR(30),
                    level       VARCHAR(255),
                    experience  INT(10),
                    item_a      INT(10),
                    item_b      INT(10),
                    item_c      INT(10)
                )";

        if ($mysqli->query($stats)) {echo "Table stats created successfully";}
        else { printf("Connect failed: %s\n", $mysqli->connect_error);}

   mysqli_close($mysqli);   

}

Result: chinesegame2 database is created, but no tables are created. $mysqli->connect_error only gives "Connect failed:".

Any thoughts why?

Thanks

can you confirm the connection details are correct? Once you have created the DB you need to connect to it. — Nick Maroulis
– Nick Maroulis, Commented Feb 2, 2014 at 20:15

Community · Accepted Answer · 2023-11-21 15:10:29Z

1

Look here: http://www.w3schools.com/php/php_mysql_create.asp

and here https://www.php.net/mysqli_select_db

<?php
$con=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// Create table
$sql="CREATE TABLE Persons(FirstName CHAR(30),LastName CHAR(30),Age INT)";

// Execute query
if (mysqli_query($con,$sql))
  {
  echo "Table persons created successfully";
  }
else
  {
  echo "Error creating table: " . mysqli_error($con);
  }
?>

and:

$sql = "CREATE TABLE Persons 
(
PID INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(PID),
FirstName CHAR(15),
LastName CHAR(15),
Age INT
)";

Also, make sure that you are in that database "HAVE IT SELECTED", or you tell what database to create a table in. And that your connection details are correct as well.

edited Nov 21, 2023 at 15:10

CommunityBot

11 silver badge

answered Feb 2, 2014 at 20:15

Hunter Mitchell

7,35318 gold badges75 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3871 Over a year ago

Oops, I was using the same connect function for other parts of my project which was connecting to the wrong db. Thanks!

symcbean · Accepted Answer · 2014-02-02 20:33:15Z

1

only gives "Connect failed:"

That's the string you defined in your code - not the error message. You are only polling for a connect error when you should be polling the connection for any error - i.e. mysqli->error(). If you do this it will tell you you've not SELECTED a database (and you haven't explicitly specified a database in your CREATE TABLE statement).

Try:

$qry = "CREATE DATABASE chinesegame2";       
$sql = $mysqli->query($qry);
$mysqli->select_db("chinesegame2");
...

answered Feb 2, 2014 at 20:33

symcbean

48.4k6 gold badges64 silver badges99 bronze badges

Collectives™ on Stack Overflow

Create database and tables with PHP

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related