CODEWITHSUNDEEP
phpmysqlsqldatabasecreate-table
puaction
puaction

Reputation: 211

mySql is not creating new table on the fly

Why do I need to reload / refresh / run this script TWICE to get the new table under the new database that have just been created ?

IOW... This script is working perfectly but not on first run.

Only after refresh it creates the new table.

<?php

$dbhost = '127.0.0.1';
$dbusername = 'root';
$dbuserpass = '';
$dbname = 'newDatabase';

$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);
if (!mysql_select_db($dbname, $link_id)){mysql_query('CREATE DATABASE '.$dbname, $link_id);}

$sql = "CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8";

mysql_query($sql);

?>

Upvotes: 0

Views: 915

Answers (3)

spencer7593
spencer7593

Reputation: 108400

When the mysql_select_db errors because the database doesn't exist, the CREATE DATABASE statement is executed.

Following that, there's no second call to mysql_select_db to change to the newly created database.

The CREATE TABLE statement is likely encountering a "no database selected" type error.

Those calls to mysql_query can raise SQL errors; it's a good idea to check for successful completion, rather than just assuming that everything went to plan.

And, for the love of all that is good and beautiful in this world, don't use the mysql_ interface for new development. Use mysqli or PDO instead.

Upvotes: 2

Luke Cousins
Luke Cousins

Reputation: 2156

Probably, if the database didn't exist then it wouldn't select it so the CREATE TABLE would fail.

Try changing the $sql query to:

$sql = "CREATE TABLE IF NOT EXISTS `" . $dbname . "`.`users` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8";

EDIT: As others have suggested, another possible solution would be to call mysql_select_db (or your your favourite newer alternative to the mysql_* functions) but this involves more data transfer to the database so using this method will be slightly more efficient. Although that efficiency will be negligible in this scenario probably!

Upvotes: 3

GrahamTheDev
GrahamTheDev

Reputation: 24825

<?php


$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);
if (!mysql_select_db($dbname, $link_id)){
mysql_query('CREATE DATABASE '.$dbname, $link_id);
mysql_select_db($dbname, $link_id); //add this
}

You basically create the database (when it doesnt exist) but dont then connect to it te first time around - adding that line should fix it.

Upvotes: 1

Related Questions