Creating SQL queries through PHP reading in XML file

ziycon

I have the below code to read in the XML file and to print the query to screen to see what its generating. I have the XML file below that. I'm having problems checking for the for attributes like primary or autonumber, im unable to use if statements and I can't check if there is more then one field to create so that it adds in a colon. Any help much appreciated.

This is what the below ouputs:

Quote:

CREATE TABLE test_table_1 ( id INT(3) )
CREATE TABLE test_table_2 ( id IN(10) test VARCHAR(100) test_2 VARCHAR(15) )

Expand|Select|Wrap|Line Numbers

 <?php

$file = "queries.xml";

$xml = simplexml_load_file($file) or die ("Unable to load XML file!");
 
foreach ($xml->tables->table as $table) {

    $query = 'CREATE TABLE '.$table['name'].' ( ';
 
    foreach ($table->column as $column) {

        $query .= $column['name'].' '.$column['type'].'('.$column['size'].') ';

    }

    $query .= ' )';
 
    print $query.'<br/>';

}

?>

Expand|Select|Wrap|Line Numbers

 <?xml version="1.0" encoding="UTF-8"?>

<structure>

    <tables>

        <table name="test_table_1">

            <column name="id" type="INT" size="3" primary="true" autonumber="true" />

        </table>

        <table name="test_table_2">

            <column name="id" type="IN" size="10" primary="true" autonumber="true" />

            <column name="test" type="VARCHAR" size="100" />

            <column name="test_2" type="VARCHAR" size="15" />

        </table>

    </tables>

</structure>

ziycon

I've gotten a step further with the below code displaying the below queries, my only problem now is how to add the commas to seperate the different field data?

Expand|Select|Wrap|Line Numbers

 <?php

$file = "queries.xml";

$xml = simplexml_load_file($file) or die ("Unable to load XML file!");
 
foreach ($xml->tables->table as $table) {

    $query = 'CREATE TABLE '.$table['name'].' ( ';
 
    foreach ($table->column as $column) {

        $query .= $column['name'].' '.$column['type'].'('.$column['size'].') ';

        if($column['autonumber']=='true')

            $query .= 'NOT NULL AUTO_INCREMENT ';

        if($column['primary']=='true')

            $query .= 'PRIMARY KEY ';

    }

    $query .= ' )';
 
    print $query.'<br/>';

}

?>

Quote:

CREATE TABLE test_table_1 ( id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY )
CREATE TABLE test_table_2 ( id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY test VARCHAR(100) test_2 VARCHAR(15) )

TheServant

In your column foreach loop simply add a comma to the end of every loop, and then once you loop is finished you can remove the last comma added by using substr.

ziycon

Quote:

Originally Posted by TheServant

In your column foreach loop simply add a comma to the end of every loop, and then once you loop is finished you can remove the last comma added by using substr.

Got it sorted using a str_replace, thanks.

TheServant

No worries. .

Creating SQL queries through PHP reading in XML file

re: Creating SQL queries through PHP reading in XML file

re: Creating SQL queries through PHP reading in XML file

re: Creating SQL queries through PHP reading in XML file

re: Creating SQL queries through PHP reading in XML file

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about