Text -> SQL parser

paulOr · 03-05-2008, 12:48 PM

Im looking for a way to enter information into a text box like this:

Code:

table: users {
   user:VARCHAR;255;
   passwd:VARCHAR;255;
   url:VARCHAR;35;
}

and it will spit out IN TEXT ONLY, NO DATABASE INTERACTION AT ALL. the following:

Code:

CREATE TABLE `users` (
`user` VARCHAR( 255 ) NOT NULL ,
`pass` VARCHAR( 255 ) NOT NULL ,
`url` VARCHAR( 32 ) NOT NULL ,
) ENGINE = MYISAM

anyone know of a class that does this or knows of a simple way to do it ?

SOCK · 03-05-2008, 02:18 PM

The only thing that comes to mind is the PEAR SQL_Parser package, and it looks like the documentation is so sparse I can't tell if it comes close to doing what you're looking for.

abiko · 03-05-2008, 03:07 PM

Here is a snippet that would use your purpose, but in different format..
So your table data would look like this

Code:

table:users|user:VARCHAR;255;NOT NULL,passwd:VARCHAR;128;NOT NULL

:/

PHP Code:


			
<?php



$t = "table:users|user:VARCHAR;255;NOT NULL,passwd:VARCHAR;128;NOT NULL";



$d    =    explode( '|', $t );



$_table    =    explode( ':', $d['0']);

$tableName    =    $_table['1'];



$sql    =    "CREATE TABLE ".$tableName." ( \n";



$rows     =    explode( ',', $d['1']);



$c = count( $rows)-1;

$count = 0;

foreach( $rows as $_n => $row ) {

        

        $sql .= "\t";

        $_ro =    explode( ':', $row);

        $fieldName    =    $_ro['0'];

        $fieldOpt        =    $_ro['1'];        

        $fields    =    explode( ';', $fieldOpt);

        $sql .= "".$fieldName." ".$fields['0']." (".$fields['1']." ) ".$fields['2'];        

        if ( $count < $c ) {

            $sql .= ",";

        }    

        $sql .= "\n";

        $count++;

}

$sql .= ")ENGINE = MYISAM;";



echo $sql;



?>

Outputs:

Code:

CREATE TABLE users ( 
	user VARCHAR (255 ) NOT NULL,
	passwd VARCHAR (128 ) NOT NULL
)ENGINE = MYISAM;

Gareth · 03-05-2008, 03:25 PM

As abiko says; one has to realise that php doesn't worry about whitespace. Therefore, if you use something (other than a ,) to seperate the different parts of the string you can then use explode and echo them out.

DeMo · 03-05-2008, 04:42 PM

You could use XML too.. makes it a lot easier to parse.

Code:

<table name="users">
  <column name="user" type="VARCHAR" size="255" />
  <column name="passwd" type="VARCHAR" size="255" />
  <column name="url" type="VARCHAR" size="35" />
</table>

abiko · 03-05-2008, 04:44 PM

DeMo you are right, XML is a better solution :)
Simpler parsing, especially if you are using PHP5 (simpleXML) so no hassle there :)