How to check if a table exists?

Haris · 09-19-2007, 08:11 PM

How to check if a table exists in the database? I want to execute the function in my class which will create the table if only the table doesn't exists already.

EDIT:

As far as I know, I can accomplish it in this way:

PHP Code:


			
        /*
         * Summary:     Creats default database table for administration panel
         */
        
        class Admins extends Database{
        
        public function __construct(){
            $szSQL = "SELECT * FROM admins";
            $szResult = $this->execute($szSQL);
            $iNumRows = mysql_num_rows($szResult);
            if($iNumRows == 0){
            $szSQL = 'CREATE TABLE `classes`.`admins` (
                        `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
                        `user` VARCHAR( 225 ) NOT NULL ,
                        `pass` VARCHAR( 225 ) NOT NULL ,
                        `rank` INT( 1 ) NOT NULL ,
                        UNIQUE (`id`)
                        ) ENGINE = InnoDB ';
            @$this->execute($szSQL);
            }
        }

Mission failed or accomplished?

Wildhoney · 09-19-2007, 08:32 PM

Code:

CREATE TABLE IF NOT EXISTS `classes`.`admins`

Haris · 09-19-2007, 08:34 PM

Thanks, it saves 5 lines of code.

Haris · 09-19-2007, 08:42 PM

Quote:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\r\n `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,\r\n `user` VARCHAR( 225 ' at line 1

Now what's wrong with syntax? :(

Tanax · 09-19-2007, 08:43 PM

PHP Code:


			
CREATE TABLE `classes`.`admins`

What does that do? Creates 2 tables? :S
Sorry for noobie question..

Wildhoney · 09-19-2007, 08:45 PM

That works fine for me on localhost. Try doing just:

Code:

CREATE TABLE `classes` 	(
				`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
				`user` VARCHAR( 225 ) NOT NULL ,
				`pass` VARCHAR( 225 ) NOT NULL ,
				`rank` INT( 1 ) NOT NULL ,
				UNIQUE (`id`)
                        ) ENGINE = InnoDB

Haris · 09-19-2007, 08:49 PM

It was due to my queries automatically escaping. How can I avoid this error? :S

I know I can remove the escaping and do it manually but that's not the safest option?

PHP Code:


			
        public function execute($szSQL){
            $szSQL = mysql_real_escape_string($szSQL);
            $szResult = mysql_query($szSQL) or die(mysql_error());
            return $szResult;
        }

Wildhoney · 09-19-2007, 08:58 PM

You don't want to escape your $szSQL variable. You ONLY escape user input.

Salathe · 09-19-2007, 09:04 PM

To to clarify Wildhoney's post. You don't want to mysql_real_escape_string the entire SQL query all in one go. It is only intended to escape portions of user input (generally the values of columns).

PHP Code:


			
$szTitle = ...  (from $_POST, $_GET, etc?)
$szSafeSQL = sprintf('SELECT * FROM myTable WHERE title = "%s"', 
                     mysql_real_escape_query($szTitle));