Table Name Availability check with PHP

Question

i am trying to create a table with php script. and before that i am trying to check for available name with PHP. but it is not working.

even if i enter a table which exits it is showing Not Exists: Incorrect table name

$sql1 = "SELECT * FROM `$mainnamechk`;";

if (mysql_query($sql1))
{
echo "<img width='35' height='25' src='img/good.png' title='Database $mainnamechk Exists'>";
  }
else
  {
   echo "Not Exists:" . mysql_error();
  echo"<br>";

  }

@TimWithers yes i can use that. but i want a input box and when my people want to create DB.. they will check using this.. cause they don't know php and all.. so i am creating this... but it is not working.. — BlackCoder, Sep 26 '13 at 17:16
$sql1 = create table if not exist `table`() return true; if ($sql1){ echo ""; } — Kisaragi, Sep 26 '13 at 17:16
@BlackCoder: Your users don't have to know PHP. The code would run a `SHOW TABLES` query and examine the output. If the table is in the output, it exists. If it isn't in the output, it doesn't exist. (Out of curiosity, why do you want this application to be able to create tables anyway? In *most* cases that's not the right design.) — David, Sep 26 '13 at 17:18
It sounds like you're creating tables dynamically, which is a terrible and backwards approach to nearly any problem. — Sammitch, Sep 26 '13 at 17:19
@David actually every day... hundreds of tables will be added in this project.. if i use show tables.. they there will be thousands of tablas :O — BlackCoder, Sep 26 '13 at 17:21
@BlackCoder: Which further indicates that this is probably not the right design. Though it doesn't affect the solution to this specific question. `SHOW TABLES` still, well, shows you the tables. — David, Sep 26 '13 at 17:21

score 1 · Answer 1 · answered Sep 26 '13 at 17:15

1

if(mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$table."'"))==1) 
    echo "Table exists";
else echo "Table does not exist";

answered Sep 26 '13 at 17:15

jaipster

11,967
2
21
24

it is showing `Table does not exist` for all :( even if i enter an existing table name – BlackCoder Sep 26 '13 at 17:24
use %, like '%%' – jaipster Sep 28 '13 at 07:00
already tried in all possible ways :( – BlackCoder Sep 28 '13 at 07:24

score 1 · Answer 2 · 2013-09-26T17:49:25.067

1

$sql1=MYSQL_QUERY('SELECT count(name) FROM "'.$mainnamechk.'" LIMIT 1');

if(mysql_num_rows($sql1)==0)
{
 echo 'Not exists';
}
else
{
echo 'Exists';
}

edited Sep 26 '13 at 17:49

answered Sep 26 '13 at 17:19

i got an error " Warning: mysql_num_rows() expects parameter 1 to be resource, string given in xxxxxxxx.php on line 17" and the line 17 is `if(mysql_num_rows($sql1)==0)` – BlackCoder Sep 26 '13 at 17:22
I have fixed already, edited the note. – Sep 26 '13 at 17:26
It should work now. Try again – Sep 26 '13 at 17:30
still error - Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given inxxx.php on line 17 – BlackCoder Sep 26 '13 at 17:33
use this one: FROM " '.$mainnamechk.' " (just put " the front and the end) If error will appear again, in that case the problem is about $mainnamechk. – Sep 26 '13 at 17:37
iF will not work give an echo to your sql. And look how will PHP return you $mainnamechk. – Sep 26 '13 at 17:40

Rogue · Accepted Answer · 2013-09-26T18:03:40.497

0

Try checking the information_schema

SELECT count(*) FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = '" . $database . "') AND (TABLE_NAME = '" . $tablename . "');

This will return a 1 if it exists, or a 0 if it does not.

$database = "databasename";
$table = "tablename";
$con = new mysqli(/* info */);
$result = mysqli_query($con, "SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = '" . $database . "') AND (TABLE_NAME = '" . $table . "'");
// $result == 1 or 0

edited Sep 26 '13 at 18:03

answered Sep 26 '13 at 17:15

Rogue

11,105
5
45
71

tried but not working :( – BlackCoder Sep 26 '13 at 17:27
What does not work about it? Are you getting an error? – Rogue Sep 26 '13 at 17:27
Parse error: syntax error, unexpected T_STRING in xxx.php on line 15 AND line 15 is `$database = maintable; SELECT count(*) FROM information_schema.TABLES` the code is used is `SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = '" . $database . "') AND (TABLE_NAME = '" . $mainnamechk . "');` – BlackCoder Sep 26 '13 at 17:35
The name of your database is maintainable? Did you enclose that in quotes? Keep in mind you should be setting it to a result, I will update my answer – Rogue Sep 26 '13 at 17:43
yes the name is maintable and i used like this $database = "maintable"; – BlackCoder Sep 26 '13 at 17:47
Try the code as-is above, substituting the values for `$database` and `$table`. What happens? – Rogue Sep 26 '13 at 17:49
ok.. see this screen shot - http://snap.ashampoo.com/5ZOq5LJ6 and error is `Warning: mysqli_query() expects at least 2 parameters, 1 given in xxx.php on line 17` and line 17 is nothing :( – BlackCoder Sep 26 '13 at 18:03
Aha! Silly silly mistake on my part, pass the `$con` variable first. I've shown this in my answer. After that it should all work. – Rogue Sep 26 '13 at 18:04
actially i am connecting to database using include_once("config.php"); then shoud i need to use `$con = new mysqli(/* info */);` this again ? – BlackCoder Sep 26 '13 at 18:20
You will use whatever your mysqli connection variable is inside the config.php, provided it isn't closed. – Rogue Sep 26 '13 at 18:24

score 0 · Answer 4 · answered Sep 26 '13 at 17:16

0

Use INFORMATION_SCHEMA

SELECT * FROM information_schema.tables
    WHERE table_schema = 'database name' 
    AND table_name = 'table_name'
LIMIT 1;

answered Sep 26 '13 at 17:16

Aristona

8,611
9
54
80

Table Name Availability check with PHP

4 Answers4