multi-line mysql query from php

[trilbyfish]

Is there a way to run a multi-line mysql query (such as creating a database with about 20 fields and entering some data) from a php script?

[rhodesa]

I don't think it let's you do that. But just put them in separate mysql_query commands:

 

mysql_query("CREATE TABLE ...");
mysql_query("INSERT INTO ...");
mysql_query("INSERT INTO ...");
mysql_query("INSERT INTO ...");

[SharkBait]

 

Not sure about mulli-line query, but you can have the script execute multiple single queries.

 

IE:

$str = "CREATE TABLE myTable( 
          id INT,
          data VARCHAR(100)
        ) ";
$qry = mysql_query($str);

$str = "INSERT INTO myTable (id, data) VALUES ( 1, "This is fun")";
$qry = mysql_query($str);

 

granted that's a quick n dirty version.

[trilbyfish]

 

Not sure about mulli-line query, but you can have the script execute multiple single queries.

 

IE:

$str = "CREATE TABLE myTable( 
          id INT,
          data VARCHAR(100)
        ) ";
$qry = mysql_query($str);

$str = "INSERT INTO myTable (id, data) VALUES ( 1, "This is fun")";
$qry = mysql_query($str);

 

granted that's a quick n dirty version.

 

Ok thanks, would that let me do 2 or coloumns? WOuld i have to have a different variable each time?

[SharkBait]

That is for 2 columns, you can modify the create table to add as many columns as you need.

 

So the columns of course would be `id` and `data`

 

This might help: http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html

[wildteen88]

You can not run multiple mysql queries in one mysql_query call. What you're better of doing is defining your queries in an array, call this $sql_queries then use a loop to loop through the array for running the queries, eg.

 

// query for creating table. In this example a simple database for storing names/ages
$sql_queries[] = 'CREATE TABLE `names` (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
age INT(2) NOT NULL
)';
// list queries for populating the table
$sql_queries[] = 'INSERT INTO `names` (`name`, `age`) VALUES (\'Sam\', 47)';
$sql_queries[] = 'INSERT INTO `names` (`name`, `age`) VALUES (\'Mike\', 13)';
$sql_queries[] = 'INSERT INTO `names` (`name`, `age`) VALUES (\'Sally\', 29)';
$sql_queries[] = 'INSERT INTO `names` (`name`, `age`) VALUES (\'Jo\', 21)';

// now process the queries
foreach($sql_queries as $query)
{
    echo 'Running Query: <b><pre>'.htmlentities($query, ENT_QUOTES).'</pre></b>';

    mysql_query($query) or die('... FAIL!<br />Error: ' . mysql_error());

    echo '... SUCCESS!<br />';
}