Creating a New Table | Spring Into PHP 5

What if you want to create a whole new table in a database? For example, say that business is so good that you want to start selling vegetables, too. How about creating a vegetables table on-the-fly? No problem. To create a new table, use the CREATE TABLE SQL statement, as here, where we're configuring the name and number fields in the new vegetables table:

 $query = "CREATE TABLE vegetables (name VARCHAR(20), number     VARCHAR(20))"; $result = mysql_query($query)     or die("Query failed: " . mysql_error());

Now you can insert data into the new vegetables table with INSERT:

 $query = "INSERT INTO vegetables (name, number) VALUES('corn',     '2083')"; $result = mysql_query($query)     or die("Query failed: " . mysql_error());

The full code appears in phpcreate.php, Example 8-5.

Example 8-5. Creating a new table, phpdatacreate.php

 <HTML>     <HEAD>         <TITLE>Creating a new table</TITLE>     </HEAD>     <BODY>         <CENTER>             <H1>Creating a new table</H1>             <?php                  $connection = mysql_connect("localhost","root","")                      or die ("Couldn't connect to server");                  $db = mysql_select_db("produce",$connection)                      or die ("Couldn't select database");                  $query = "CREATE TABLE vegetables (name VARCHAR(20),                      number VARCHAR(20))";                  $result = mysql_query($query)                       or die("Query failed: " . mysql_error());                  $query = "INSERT INTO vegetables (name, number) VALUES(                      'corn', '2083')";                  $result = mysql_query($query)                       or die("Query failed: " . mysql_error());                  $query = "INSERT INTO vegetables (name, number)                      VALUES('spinach', '1993')";                  $result = mysql_query($query)                       or die("Query failed: " . mysql_error());                  $query = "INSERT INTO vegetables (name, number)                      VALUES('beets', '437')";                  $result = mysql_query($query)                       or die("Query failed: " . mysql_error());                  $query = "SELECT * FROM vegetables";                  $result = mysql_query($query)                       or die("Query failed: " . mysql_error());                  echo "<TABLE BORDER='1'>";                  echo "<TR>";                  echo "<TH>Name</TH><TH>Number</TH>";                  echo "</TR>";                  while ($row = mysql_fetch_array($result))                  {                      echo "<TR>";                      echo "<TD>", $row['name'], "</TD><TD>",                                   $row['number'], "</TD>";                      echo "</TR>";                  }                  echo "</TABLE>";                  mysql_close($connection);              ?>          </CENTER>     </BODY> </HTML>

You can see the new table in Figure 8-5. Very cool.

Figure 8-5. Creating a new table.