PHP and MYSQL database connection and table creation only once

Question

I'm developing a small application for my academic. i have a registration form and a log_in form. Here is what i want, i have a database configuration file which contains "connection to server, creating database and creating table queries" included in it. I've included this database file at the top of my registration form so that when the page is loaded the "query should execute and create database and table ONLY ONCE". But the problem is the query is successfully executing for the first time but when the registration page is loaded again, it prompt's an error "DATABASE ALREADY EXISTS". Please me. I've attached the db.php code..

<?php
$dbhost  = 'localhost';
$dbuser  = 'root';
$dbpass  = '';
$connect = mysql_connect($dbhost, $dbuser, $dbpass);
if (!$connect) {
    die('SERVER CONNECTION FAILED...\n: ' . mysql_error());
}
;
$sql    = 'CREATE DATABASE USERS';
$retval = mysql_query($sql, $connect);
if (!$retval) {
    die('DATABASE CREATION FAILED\n: ' . mysql_error());
}
;
$sql = "CREATE TABLE USERS( " . "Memberid int(10) NOT NULL AUTO_INCREMENT,
        " . "Name varchar(100) NOT NULL,
        " . "Username varchar(20) NOT NULL,
        " . "Password varchar(10) NOT NULL,
        " . "Email varchar(20) NOT NULL,
        " . "Activation varchar(40) DEFAULT NULL,
        " . "Status int(1) NOT NULL DEFAULT '1',
        " . "PRIMARY KEY (`Memberid`)); ";
mysql_select_db('USERS');
$retval = mysql_query($sql, $connect);
if (!$retval) {
    die('COULD NOT CREATE TABLE\n: ' . mysql_error());
}
;
mysql_close($connect);
?>
<html>
    <body>
    //registration form code
    </body>
</html>

score 4 · Accepted Answer · answered Mar 13 '14 at 05:01

4

query to create database only once if it doesn't exists

            CREATE DATABASE IF NOT EXISTS DBName;

query to create table only once if it doesn't exists

            CREATE TABLE IF NOT EXISTS tablename;

answered Mar 13 '14 at 05:01

Ananth

1,520
3
15
28

score 1 · Answer 2 · answered Mar 13 '14 at 04:59

1

You are able to create database/table once only.

Change

CREATE DATABASE

To:

CREATE DATABASE IF NOT EXISTS

Same changes should be applied for the table creation statement.

answered Mar 13 '14 at 04:59

Crutched

51
2

score 1 · Answer 3 · answered Mar 13 '14 at 05:00

1

Try this change

CREATE DATABASE IF NOT EXISTS USERS

And for table

CREATE TABLE IF NOT EXISTS tablename;

answered Mar 13 '14 at 05:00

BKM

6,949
7
30
45

PHP and MYSQL database connection and table creation only once

3 Answers3

Linked