Unknown error creating MySQL table with PHP

Question

I have two PHP documents. One that connects to my MySQL server and database (it will also create the database if it doesn't exist). This document is titled "db_connect.php". My next PHP document is titled "create.php" and it is designed to connect to a specific table within the database and create that table if it doesn't exist. There's also a javascript document involved in this which makes it so the user can type things and enter them into the table without the page being refreshed. I don't think you'll need this document and so I won't include it, but I thought you guys might find it helpful to know that this is for a message board.

Here's my db_connect.php file:

<?php
    $db = "my_db";
    //establish a connection with the server
    $connection = mysqli_connect('localhost', 'username', 'password');
    if(!$connection){
        exit("<p>Could not establish a connection :" . mysqli_connect_error() . "</p>");
    }
    //connect to the database
    $dbSelect = mysqli_select_db($connection, $db);
    if(!$dbSelect){
        // Create database
        $sql="CREATE DATABASE " . $db;
        if (mysqli_query($connection, $sql)) {
        } else {
          echo "<p>Error creating database: " . mysqli_error($connection) . "</p>";
        }
    }
?>

Here's my create.php file:

<?php
//connect to the database
include('db_connect.php');
$table = 'NDI';
//update the table if the notes are posted
if(isset($_POST['notes'])){
    $notes=$_POST['notes'];
    $name=$_POST['name'];
    $file = $_POST['file'];
    $file2 = $_FILES['file'];
    echo "<p>Hello $file $file2</p>";
    /////////////////////////////////////////////
    //Check for file type
    /////////////////////////////////////////////
    if ((($_FILES["file"]["type"] == "image/gif")
        || ($_FILES["file"]["type"] == "image/png")
        || ($_FILES["file"]["type"] == "image/x-png")
        || ($_FILES["file"]["type"] == "image/jpeg")
        || ($_FILES["file"]["type"] == "image/jpg")
        || ($_FILES["file"]["type"] == "image/pjpeg")
        || ($_FILES["file"]["type"] == "application/x-shockwave-flash")
        )
        && ($_FILES["file"]["size"] < 999000)){
        /////////////////////////////////////////////
        //Check for errors
        /////////////////////////////////////////////
        if ($_FILES["file"]["error"] > 0){
            echo "Error: " . $_FILES["file"]["error"] . "<br />";
        }else{
        ///////////////////////////////////////////
        //Set the upload
        ///////////////////////////////////////////

            echo "Upload: " . $_FILES["file"]["name"] . "<br />";
            echo "Type: " . $_FILES["file"]["type"] . "<br />";
            echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
            echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";
            /////////////////////////////////////////////////////////
            //Check to see if file exists already
            /////////////////////////////////////////////////////////
            if (file_exists("../uploads/" . $_FILES["file"]["name"])){
                //echo $_FILES["file"]["name"] . " already exists. ";
                $_FILES["file"]["name"] =  rand(1, 1000).$_FILES["file"]["name"];
            }
            ////////////////////////////////////////////////////////////
            //If not, move to the upload folder
            ////////////////////////////////////////////////////////////
            $path = '../uploads/';
            $tmp_name = $_FILES["file"]["tmp_name"][$key];
            $fn = basename( $_FILES['file']['name']); 
            move_uploaded_file($_FILES['file']['tmp_name'], $path.$fn);
            //move_uploaded_file($_FILES["file"]["tmp_name"],
            //"../uploads/" . $_FILES["file"]["name"]);
            echo "Stored in: ../uploads/". $_FILES["file"]["name"];
            $myImg =    "../uploads/" . $_FILES['file']['name'];
                //echo "\n $myImg";
        }
        //echo "<a href=../uploader/>Back</a>";
    }else{
        echo "Invalid file";
        //echo $_FILES["file"]["type"];
    }
    if(!$myImg){
        $myImg="../uploads/blank.png";
    }
    if(!$name){
        $name="anonymous";
    }
    $sql= "INSERT INTO `$table` SET `name` = '$name', `notes`='$notes', `img`='$myImg'";
    if (mysqli_query($sql)) {
        echo '<p>Entry added</p>';
        echo '<p><a href="index.php">' . $title . ' Home</a> </p>';
    } else {
        echo '<p>Error adding page: ' . mysqli_error() . '</p>';
    }
}
    //display results
$choices = mysqli_query("select * from " . $table);
if(!$choices){
    // Create table
    $sqlc="CREATE TABLE $table(`id` INT(5) AUTO_INCREMENT, `img` VAR_CHAR(50), `name` VAR_CHAR(25), `notes` TEXT(500))";

    // Execute query
    if (mysqli_query($connection, $db, $sqlc)) {
    } else {
      echo "Error creating table: " . mysqli_error($connection, $db);
    }
}
while($row = mysqli_fetch_array($choices)){
    $img=$row['img'];
    $note=$row['notes'];
    $name=$row['name'];
    echo "<p class='note'><img src='$img'><span class='name'>$name: </span>$note</p>";
}
?>

The problem I'm running into is that the page echos the error: "Error creating table: " but it doesn't give me any of the error details that I told it to; so I really have no idea what to do. Any feedback you guys could give would be greatly appreciated.

This statement if (mysqli_query($connection, $db, $sqlc)) is basically telling SQL: connect to DB show the (word) my_db as as string (or try to pass as a parameter) then perform this query $sqlc="CREATE TABLE $table... doesn't seem to make sense. You may have meant to use if(mysqli_query($connection, $sqlc)) — Funk Forty Niner
– Funk Forty Niner, Commented May 6, 2014 at 3:59
@Fred-ii- I tried changing if (mysqli_query($connection, $db, $sqlc)) to if (mysqli_query($connection, $sqlc)) and I still receive the same error. — Logan Kling
– Logan Kling, Commented May 6, 2014 at 4:09
Most likely because the table already exists (or does it?). You first have $table = 'NDI'; (which I don't know if it exists or not), then you're querying with $choices = mysqli_query("select * from " . $table); then telling it to create the table $sqlc="CREATE TABLE $table — Funk Forty Niner
– Funk Forty Niner, Commented May 6, 2014 at 4:16
Ok, I think I know what the problem is. This VAR_CHAR(50) should be VARCHAR(50) you had an underscore. Plus VAR_CHAR(25) to VARCHAR(25) — Funk Forty Niner
– Funk Forty Niner, Commented May 6, 2014 at 4:23
Add this , PRIMARY KEY (id) after TEXT(500) and put ticks around id SO doesn't render ticks well in comments. — Funk Forty Niner
– Funk Forty Niner, Commented May 6, 2014 at 4:38

Funk Forty Niner · Accepted Answer · 2014-05-06 05:02:15Z

1

Change this line:

$sqlc="CREATE TABLE $table(`id` INT(5) AUTO_INCREMENT, `img` VAR_CHAR(50), `name` VAR_CHAR(25), `notes` TEXT(500))";

to

$sqlc="CREATE TABLE $table(`id` INT(5) AUTO_INCREMENT, `img` VARCHAR(50), `name` VARCHAR(25), `notes` TEXT(500), PRIMARY KEY (`id`))";

It's VARCHAR instead of VAR_CHAR plus you need to add a primary key to the auto_increment.

Quoted from http://www.tizag.com/mysqlTutorial/mysqltables.php

PRIMARY KEY is used as a unique identifier for the rows. Here we have made "id" the PRIMARY KEY for this table. This means that no two ids can be the same, or else we will run into trouble. This is why we made "id" an auto-incrementing counter in the previous line of code.

In other words, MySQL needs a column to uniquely identify a row.

answered May 6, 2014 at 5:02

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

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

Comments

Markus · Accepted Answer · 2014-05-06 04:03:39Z

1

I might be wrong about this, but try leaving out the $db variable when calling mysqli_query and mysqli_error

 if (mysqli_query($connection, $sqlc)) {

...

  echo "Error creating table: " . mysqli_error($connection);

Does that help?

answered May 6, 2014 at 4:03

Markus

111 bronze badge

2 Comments

Funk Forty Niner Over a year ago

As I said

Logan Kling Over a year ago

This just makes me receive the error: "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 'VAR_CHAR(50), name VAR_CHAR(25), notes TEXT(500))' at line 1".

pringi · Accepted Answer · 2017-01-17 14:34:43Z

0

Try to write the create SQL order including the database name like this:

create table databasename.yourTable(etc table syntax)

edited Jan 17, 2017 at 14:34

pringi

4,8306 gold badges42 silver badges48 bronze badges

answered Jan 17, 2017 at 12:59

user6026401

413 bronze badges

1 Comment

Eineki Over a year ago

Hi user, I appreciate your answer but at the same time I think you should be less concise (take it as a suggestion for your further answers). By the way the main problem on this question was that the mysqli_* function calls missed the connection parameter. Your solution is a bit out of scope. As far it will not produce harm would not solve the problem too (and it will be prone to downvote for this very reason).

Collectives™ on Stack Overflow

Unknown error creating MySQL table with PHP

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related