creating a new mySQL database

Discussion in 'PHP' started by palsypal, Apr 30, 2006.

  1. #1
    hello hello

    i want to create a database in mysql after checking if the name allready exists.
    So i made a little script, but it does not work.

    <?php
    
    $dbhost = 'localhost';
    $dbusername = 'root';
    $dbpassword     = '';
    $dbname = 'test1';
    
    $connect = mysql_pconnect($dbhost, $dbusername, $dbpassword);
    if (!$connect) die('Could not connect: ' . mysql_error());
    
    $dbnameexists = FALSE;
    $result = mysql_list_dbs($connect);
    
    while ($row2=mysql_fetch_array($result, MYSQL_NUM)) 
    {
      	echo"$row2[0]<br>";
    	if ($row2[0] ==  $dbname)
    	{
    		$dbnameexists = TRUE;
    	} 
    }
    
    if (!$dbnameexists)
    {
    	echo"$dbname doesnt exist";  
    	$query = "CREATE DATABASE $dbname"; 
    	$result = mysql_query($query) or die (mysql_error());
    }
    else
    {
      echo"$dbname already exists...";
    }
    
    mysql_close($connect);
    ?>	
    
    PHP:
    error: it seems to create the database before it checks if the name exists. Then it returns an error that it allready exists.
    brrrr very confusing
    anyone could help?
    thanks
    palsypal
     
    palsypal, Apr 30, 2006 IP
  2. onlyican.com

    onlyican.com Peon

    Messages:
    206
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #2
    CREATE TABLE IF NOT EXISTS tablename(
    Details
    );

    For example, a basic users table would be
    
    $query = "CREATE TABLE IF NOT EXISTS users (
      id_num int(50) unsigned NOT NULL auto_increment,
      name varchar(250) DEFAULT '0' ,
      email varchar(250) DEFAULT '0' ,
      username varchar(250) DEFAULT '0' ,
      pass varchar(250) DEFAULT '0' ,
      active enum('y','n') DEFAULT 'n' ,
      level enum('a','u') DEFAULT 'u' ,
      PRIMARY KEY (id_num),
      UNIQUE KEY id_num (id_num)
    )";
    
    PHP:
     
    onlyican.com, Apr 30, 2006 IP
  3. onlyican.com

    onlyican.com Peon

    Messages:
    206
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Sorry, misread the question, db not table

    try on your line 23

    if ($dbnameexists == false)
     
    onlyican.com, Apr 30, 2006 IP
  4. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #4
    You could just use mysql_create_db() - if it returns true, you created the database, if false, it already exists.
     
    exam, Apr 30, 2006 IP
  5. palsypal

    palsypal Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Hay,
    what a fast reply,
    indeed mysql_create_db() is the most easy way, but it gives a fatal error when i try it:
    Fatal error: Call to undefined function mysql_create_db()
    not nice :(


    I tried the next code:
    
    <?php
    
    $dbhost = 'localhost';
    $dbusername = 'root';
    $dbpassword     = '';
    $dbname = 'test31';
    
    $connect = mysql_pconnect($dbhost, $dbusername, $dbpassword);
    if (!$connect) die('Could not connect: ' . mysql_error());
    $query= "CREATE DATABASE $dbname";
    $result = mysql_query($query);
    
    if (!$result)echo"$dbname allready exists, choose another name... <br>";
    
    mysql_close($connect);
    ?>
    
    PHP:
    The query is executed, but the $result is always false, also other queries like "DROP DATABASE $dbname" always return false.
    Is there an explanation for this weird behaviour, or should I just live with it?

    thank you,
    Palsypal
     
    palsypal, May 1, 2006 IP
  6. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Make sure the user you're connecting to MySQL with bas permission to show/create databases
     
    exam, May 1, 2006 IP