Detecting/creating MySQL database

Question

Why this code does not recognize the existing database?

$result = mysql_query("SHOW DATABASES LIKE $database", $conn);
echo $result."       aaaaaaaaaaaaaaaaaaa<br>";
if ($result) 
{
    echo ("Datebase <font color='#FF0000'><b> $database </b></font> already exist.<br /><br />");
}
else
{
    mysql_query("create database IF NOT EXISTS $database CHARACTER SET utf8 COLLATE utf8_general_ci");
    echo ("Datebase <font color='#FF0000'><b> $database </b></font> is succesfully created.<br /><br />");
}

$database is my database name that I want to create or check if it exsists. — Narek
– Narek, Commented Feb 13, 2011 at 13:28
@Haim Evgi. Not true in this case you'll want an exact match, not a wildcard search. — GolezTrol
– GolezTrol, Commented Feb 13, 2011 at 13:31

Thew · Accepted Answer · 2011-02-13 13:38:48Z

1

This code make my somekinda sick...

Here is a good code:

<?php

$query = "SHOW DATABASES LIKE ". $database;
$mysqlquery = mysql_query($query);
if($mysqlquery){
    echo '<b>Database already exist.<br>';
}
else
{
    $createquery = "CREATE DATABASE IF NOT EXISTS ". $database ." CHARACTER SET utf8 COLLATE utf8_general_ci";
    $mysqlcreatequery = mysql_query($createquery);
    if($mysqlcreatequery){
        echo 'Database '. $database .' created. ';
    }
    else
    {
        echo 'Database is not created!';
    }
}
?>

answered Feb 13, 2011 at 13:38

Thew

16k20 gold badges61 silver badges102 bronze badges

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

Comments

Dalmas · Accepted Answer · 2011-02-13 13:23:05Z

1

You forgot the quotes (always necessary when using strings in SQL) :

$result = mysql_query("SHOW DATABASES LIKE '$database'", $conn);

answered Feb 13, 2011 at 13:23

Dalmas

26.6k9 gold badges71 silver badges82 bronze badges

3 Comments

Narek Over a year ago

But why "create database IF NOT EXISTS $database CHARACTER SET utf8 COLLATE utf8_general_ci" without quotation works, but SHOW command does not?

Dalmas Over a year ago

Well in your example it's not really a string. MySQL waits for an identifier here, with a specific format (no spaces) and don't need quotes to know where your database name ends. You can have spaces in your database name, but in this case you need to use quotes. It's different when using LIKE since MySQL expect a string value type, which can potentially contains spaces.

Dalmas Over a year ago

Anyway just use quotes and it will work (or it would mean there is another error somewhere else)

Dr.Molle · Accepted Answer · 2011-02-13 13:22:34Z

0

As the pattern is a string, you'll have to quote it:

"SHOW DATABASES LIKE '$database'"

answered Feb 13, 2011 at 13:22

Dr.Molle

117k16 gold badges200 silver badges206 bronze badges

Comments

GolezTrol · Accepted Answer · 2011-02-13 13:30:40Z

0

The database is a string as stated by Dr.Molle. Also, watch out for wildcards. An _ is a single character wildcard, so when you look for the database order_2010 it will also match orders2010, which will lead to the assumption that order_2010 is indeed an existing database.

Small chance, but I thought I should mention it. :)

answered Feb 13, 2011 at 13:30

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

2 Comments

Narek Over a year ago

Ok so what is the better way that checks if $database exists?

GolezTrol Over a year ago

Escape the wildcards by replacing _ with \_ and % with \%.

Collectives™ on Stack Overflow

Detecting/creating MySQL database

4 Answers 4

Comments

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related