One Table is not being created other is being created

Question

I am working on creating table in to my database with mysql and here is my code:

mysqli_query($link, "CREATE TABLE IF NOT EXISTS `review` (
            `clients_id` int(15) NOT NULL AUTO_INCREMENT,
            `client_id` varchar(150) NOT NULL DEFAULT '',
            `rating` tinyint(2) NOT NULL DEFAULT '0',
            `proj_date` date NOT NULL DEFAULT '0000-00-00',
            `proj_desc` text NOT NULL DEFAULT '',
            `photoname` text NOT NULL,
            `companyname` text NOT NULL,
            `feedback` text NOT NULL,
            `status` tinyint(1) NOT NULL DEFAULT '0',
            `emailid` varchar(100) NOT NULL DEFAULT '',
            `customratings` varchar(100) NOT NULL DEFAULT '',
            `photo_option` varchar(100) NOT NULL DEFAULT '',
            `title` varchar(100) NOT NULL DEFAULT '',
            `citation` varchar(100) NOT NULL DEFAULT '',
            `date_option` varchar(100) NOT NULL DEFAULT '',
            `rating_option` varchar(100) NOT NULL DEFAULT '',
            PRIMARY KEY (`clients_id`),
            FULLTEXT KEY `feedback` (`feedback`)
            ) ENGINE=MyISAM  AUTO_INCREMENT=1") or mysqli_error($link);

But this is not reflecting into my database ? Why were I may be going wrong ?

but I tried creating other table with the following code

mysqli_query($link, "CREATE TABLE IF NOT EXISTS `setting` (
            `id` int(11) NOT NULL auto_increment,
            `script_url` text NOT NULL,
            `date` varchar(4) NOT NULL,
            `rateing` varchar(4) NOT NULL,
            `photo` varchar(4) NOT NULL,
            `dateformat` varchar(4) NOT NULL,
            `page_limit` int(4) NOT NULL,
            `proj_desc` varchar(4) NOT NULL,
            `companyname` varchar(4) NOT NULL,
            `text_color` varchar(255) NOT NULL,
            `citation_color` varchar(255) NOT NULL,
            `bg_color` varchar(255) NOT NULL,
            `border_color` varchar(255) NOT NULL,
            `ratingsformat` varchar(250) NOT NULL,
            `rating` varchar(250) NOT NULL,
            `customratings` varchar(250) NOT NULL,
            `speed` varchar(250) NOT NULL,
            `pagination` varchar(250) NOT NULL,
            `version` varchar(250) NOT NULL,
            `global_option` varchar(250) NOT NULL,
            PRIMARY KEY  (`id`)
            ) ENGINE=MyISAM AUTO_INCREMENT=0;
        ") or mysqli_error($link);

it is being created correctly and both the tables are in the same file

Possible duplicate of [How to 'insert if not exists' in MySQL?](https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — Prashant Pokhriyal, Nov 22 '17 at 06:13
The mysql syntax is now ok (after edit) - so if there is a problem, check PHP side, or the connection. — Breaking not so bad, Nov 22 '17 at 07:11
@ringø I am creating these tables in the same single php file while one table is being created and other table is not being created. What could be the problem? — Nawaz Ghori, Nov 22 '17 at 07:15
If I have remove DEFAULT ' ' in review ,the table is being created. Why is it so? — Nawaz Ghori, Nov 22 '17 at 11:51

score 1 · Answer 1 · answered Nov 22 '17 at 06:36

1

No you cannot create table using an insert statement.

There are four types of SQL statements:

DML (DATA MANIPULATION LANGUAGE)
DDL (DATA DEFINITION LANGUAGE)
DCL (DATA CONTROL LANGUAGE)
TCL (TRANSACTION CONTROL LANGUAGE)

DML is your SELECT, INSERT, UPDATE and DELETE statements... DDL is you your CREATE, ALTER and DROP statements.

See more info about types of SQL statements

In order to insert data in your table, first you need to create it.

How to create sql table from php

answered Nov 22 '17 at 06:36

Hooman Bahreini

11,018
7
41
74

Edited my question now have a look at it – Nawaz Ghori Nov 22 '17 at 07:08
@Nawaz, this is a new/different question now, I think it would be best to ask a separate question for this. By changing the question, all the answers become irrelevant. Also if you are getting any error message, could you please include them. – Hooman Bahreini Nov 22 '17 at 07:39

score 0 · Answer 2 · answered Nov 22 '17 at 06:20

No. "INSERT INTO" used to insert the data to existing table only.You should create the table with respective column before try to insert the values.

Or you can check the table exist or not before going insert."If exists" u can insert the value else just create and insert the values.

score 0 · Accepted Answer · answered Nov 22 '17 at 11:56

This worked for me but I don't know how?

I just removed DEFAULT '' present while creating review table and table just got created Here is edited code

mysqli_query($link, "CREATE TABLE IF NOT EXISTS `review` (
            `clients_id` int(15) NOT NULL AUTO_INCREMENT,
            `client_id` varchar(150) NOT NULL,
            `rating` tinyint(2) NOT NULL,
            `proj_date` date NOT NULL,
            `proj_desc` text NOT NULL,
            `photoname` text NOT NULL,
            `companyname` text NOT NULL,
            `feedback` text NOT NULL,
            `status` tinyint(1) NOT NULL,
            `emailid` varchar(100) NOT NULL,
            `customratings` varchar(100) NOT NULL,
            `photo_option` varchar(100) NOT NULL,
            `title` varchar(100) NOT NULL,
            `citation` varchar(100) NOT NULL,
            `date_option` varchar(100) NOT NULL,
            `rating_option` varchar(100) NOT NULL,
            PRIMARY KEY (`clients_id`),
            FULLTEXT KEY `feedback` (`feedback`)
            ) ENGINE=MyISAM  AUTO_INCREMENT=1") or mysqli_error($link);

This leads me to 1364 mysql error while inserting data – Nawaz Ghori Nov 29 '17 at 06:31 — Nawaz Ghori, Nov 29 '17 at 06:31

One Table is not being created other is being created

3 Answers3