Table create from PHP fails

Question

    <?php  

    $db_host = "localhost"; 
    $db_username = "root"; 
    $db_pass = "";
    $db_name = "onlinestores"; 

      // Run the actual connection here  
    $connect_dude=mysqli_connect("$db_host","$db_username","$db_pass","$db_name");


    if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
     }
    ?>

    //creating table
    <?php
    include "connect_to_db.php";
    $sql_command="CREATE TABLE admin(
        id NOT NULL auto_increment,
        username varchar(16) NOT NULL,
        password varchar(16) NOT NULL,
        last_login date NOT NULL,
        PRIMARY KEY(id),
        UNIQUE KEY username(username)
        )";
    if(mysqli_query($connect_dude,"$sql_command")){
    echo "ookie dookie";
    }else {
    echo "it didn't work";
    }
    ?>

The code doesn't create any table in the database and shows me echo statement "it didn't work".

*sidenote:* why do you need double quotes in `mysqli_connect()` & `mysqli_query()` ? — Raptor, Jan 28 '14 at 05:41
Does the account you used to login have `CREATE` permission ? — Raptor, Jan 28 '14 at 05:42

score 2 · Accepted Answer · edited Jun 16 '14 at 02:10

2

Your SQL has syntax errors.

id column misses int
UNIQUE KEY username(username) should be UNIQUE KEY(username)

Try this:

$sql_command = "CREATE TABLE admin 
(id int NOT NULL auto_increment,
username varchar(16) NOT NULL,
password varchar(16) NOT NULL,
last_login date NOT NULL,
PRIMARY KEY(id),
UNIQUE KEY(username))";

To catch errors in mysql calls, use mysqli_error()

else {
    echo "it didn't work because: " . mysqli_error($connect_dude);
}

edited Jun 16 '14 at 02:10

CRABOLO

8,605
39
41
68

answered Jan 28 '14 at 05:50

grebneke

4,414
17
24

Thank you so much,boss.I appreciate a lot. – user2943607 Jan 28 '14 at 06:21

score 1 · Answer 2 · answered Jan 28 '14 at 06:05

Try like this, make sure you have proper names for variables before pushing code on production,

connect_to_db.php file:

<?php
  $db_host = "localhost"; 
  $db_username = "root"; 
  $db_pass = "";
  $db_name = "onlinestores"; 

  // Run the actual connection here  
  $mysqli=mysqli_connect($db_host, $db_username, $db_pass, $db_name);

  if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
  }
?>

create_table.php file:

<?php
  include "connect_to_db.php";

  $sql_command="CREATE TABLE admin(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(16) NOT NULL UNIQUE KEY,
    pass VARCHAR(16) NOT NULL,
    last_login DATE NOT NULL
  )";

  if($mysqli->query($sql_command) === TRUE)
    echo "ookie dookie";
  else
    echo "it didn't work";
?>

score 0 · Answer 3 · edited Jun 16 '14 at 02:10

replace your code:

$db_host = "localhost"; 
    $db_username = "root"; 
    $db_pass = "";
    $db_name = "onlinestores"; 

      // Run the actual connection here  
    $connect_dude=mysqli_connect("$db_host","$db_username","$db_pass","$db_name");


    if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
     }
    ?>

    //creating table
    <?php
    include "connect_to_db.php";
    $sql_command="CREATE TABLE admin(
        id int NOT NULL auto_increment,
        username varchar(16) NOT NULL,
        password varchar(16) NOT NULL,
        last_login date NOT NULL,
        PRIMARY KEY(id),
        UNIQUE KEY username(username)
        )";
    if(mysqli_query($connect_dude,"$sql_command")){
    echo "ookie dookie";
    }else {
    echo "it didn't work";
    }
    ?>

This does not affect the result. Variables in double quotes will be parsed & substituted. — Raptor, Jan 28 '14 at 05:44

Table create from PHP fails

3 Answers3

Linked