Simple PHP/MySQL Create Table Script

Question

So I have this script just to create a table in my database. I've copied it over from an old script I did that is working right now. How come this one is not working? Anyone?

The error I am getting is "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "'= varchar (20) NOT NULL, column_two = int NOT NULL auto_increment, column_thre' at line 2"

<?php

include("server_connect.php");

mysql_select_db("assignment5");

$create = "CREATE TABLE tbltable (
column_one = varchar (20) NOT NULL,
column_two = int NOT NULL auto_increment,
column_three = int NOT NULL,
column_four = varchar (15) NOT NULL,
column_five = year,
PRIMARY KEY = (column_one)
)";

$results = mysql_query($create) or die (mysql_error());

echo "The tables have been created";

?>

Oh wow... I'm an idiot. I must have copy pasted from the wrong script and edited this with the = signs. Doh... New to mysql. Thanks a whole bunch guys. — user2581841
– user2581841, Commented Jul 16, 2013 at 23:07
Please note that mysql_query is deprecated now (as of php 5.5) php.net/manual/en/function.mysql-query.php So I would not use it for a project which will have long life period. — Sergiy T.
– Sergiy T., Commented Jul 17, 2013 at 9:05
This question appears to be off-topic because it caused by a local condtion: a typo. — madth3
– madth3, Commented Jul 18, 2013 at 0:17

Amal · Accepted Answer · 2013-07-16 23:29:59Z

8

Remove all = as already suggested:

$create = "CREATE TABLE tbltable (
column_one varchar (20) NOT NULL,
column_two int NOT NULL auto_increment PRIMARY KEY,
column_three int NOT NULL,
column_four varchar (15) NOT NULL,
column_five year
)";

Each and every table should have a primary key and you must specify AUTO_INCREMENT column as PRIMARY KEY. In this case, the AUTO_INCREMENT column is column_two and I've set that as the PRIMARY KEY.

edited Jul 16, 2013 at 23:29

answered Jul 16, 2013 at 23:03

Amal

76.8k18 gold badges133 silver badges154 bronze badges

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

2 Comments

user1646111 Over a year ago

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

user1646111 Over a year ago

#1068 - Multiple primary key defined

Ravi Chauhan · Accepted Answer · 2015-07-07 13:22:12Z

MySQLi Procedural

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

if (mysqli_query($conn, $sql)) {
    echo "Table MyGuests created successfully";
} else {
    echo "Error creating table: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

(PDO)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // sql to create table
    $sql = "CREATE TABLE MyGuests (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
    )";

    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Table MyGuests created successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>

ekhumoro · Accepted Answer · 2017-12-07 03:34:06Z

-1

The solution is to assign all the privileges to the BD user, like this:

GRANT ALL PRIVILEGES ON *. * TO 'user_name' @ 'localhost';

edited Dec 7, 2017 at 3:34

ekhumoro

122k23 gold badges272 silver badges400 bronze badges

answered Dec 7, 2017 at 3:23

CRISTIAN STEVEN MONTERROSO PRE

1

1 Comment

ekhumoro Over a year ago

Please only use English language on this site.

Collectives™ on Stack Overflow

Simple PHP/MySQL Create Table Script

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related