SQL issues

I've spent a little time teaching myself the basics of PHP and SQL, I've hit a little snag and would appreciate some guidance from anyone who is more experienced in these areas.

I made a php file which would:

  1. Create a table in an existing database
  2. Write data to that table
  3. Read the data back

The next thing was to convert the working script into functions (it's much nicer that way.

Now, the 'create table' function doesn't seem to create a table for some reason, and I'd appreciate any pointers that passing people 'in the know' could give.

It generates the following output:

  1. Table 'gtd-List' does not exist...
  2. Creating 'gtd-List' Table Now....
  3. (This line is to check the SQL string) --- CREATE TABLE gtd-List (itemID int NOT NULL AUTO_INCREMENT, PRIMARY KEY(itemID), listname tinytext, owner varchar(255), description longtext )Attempted Creation
  4. Table 'gtd-List' does not exist...
  5. Entering default data for 'gtd-List'.
  6. Table 'murk1e58_gtd.gtd-List' doesn't exist

The php is in three files, gtd-config.php contains the variables for the sql

<?php

/* CHANGE THESE TO REFLECT YOUR SETTINGS */

$host = "";
$db_name = "databasename";
$user = "username";
$pass = "password";
/* End of user configurable variables */

/* DO NOT CHANGE ANYTHING BELOW THIS LINE */

?>

The file gtd-install.php, the thing which is run, is as follows. It is supposed to include the two servant files and then connect to the database, (I'm a bit hazy with mysql_select_db, why should this be needed after mysql_connect), check if a table exists, and if it doesn't it should be created with default data. Finally, that data is read out.

<?php

require ("gtd-config.php");
require ("gtd-general-functions.php");

// Connect to the database
$con = mysql_connect($host,$user,$pass) or die('Could not connect:' . mysql_error());

mysql_select_db($db_name, $con);

// This is needed to clean up after myself in testing
// DELETE
// Drop table from previous versions 
// mysql_query("DROP TABLE gtd-List"); 
// END DELETE

if (!table_exists('gtd-List',$host,$user,$pass,$db_name)) 
  { // If the tables do not exist, create them....
  create_table('gtd-List',$host,$user,$pass,$db_name);
  insert_data('gtd-List',$host,$user,$pass,$db_name);
  }

mysql_close($con);

$con = mysql_connect($host,$user,$pass);
mysql_select_db($db_name,$con);

/* DISPLAY TEST DATA */
mysql_select_db($db_name,$con);
$result = mysql_query("SELECT * FROM `gtd-List`") or die(mysql_error());

while($row = mysql_fetch_array($result))
  {
  echo $row['itemID'] . "<br />";
  echo $row['listname'] . " " .$row['owner'] . " " .$row['description'];
  }

mysql_close($con);
?>

The general functions are supposed to be flexible, as once I've basic functionality of creating tables, the idea is to create several using the same function calls.

Now there are two issues, firstly I seem to have to pass the database information down to the function and reconnect there, which is a bit ugly.

Secondly, there seems to be a bug in create_table() - this is the one that is really bugging me. Before putting things into the function it worked as expected.

Any thoughts on what I've done wrong would be good (bear in mind that I've only been seriously looking and php and sql for a few hours, with a few weeks WordPress experience).

<?php

function table_exists($table,$hst,$us,$ps,$db)
  {
  $sql = mysql_connect($hst,$us,$ps) or die(mysql_error());
  mysql_select_db($db,$sql) or die(mysql_error());
  if (mysql_query("SELECT 1 FROM `".$table."` LIMIT 0")) {
    echo "The table called '" . $table . "' exists...<br />";
    return true;
  }
  else {
    echo "Table '" . $table . "' does not exist...<br />";
    return false;
  }
  }

function create_table($table, $hst,$us,$ps,$db)
{
  $con = mysql_connect($hst,$us,$ps);
  mysql_select_db($db, $con);

  echo "Creating '". $table ."' Table Now....<br />";
  if ($table == 'gtd-List') {
    $sql = "CREATE TABLE " . $table . "
    (itemID int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(itemID),
    listname tinytext,
    owner varchar(255),
    description longtext 
    )";

    mysql_query($sql,$con);
    echo $sql;
    echo "Attempted Creation";
    table_exists($table,$hst,$us,$ps,$db);
  }
  else
  {
    echo "I don't know how to create '" . $table ."'. Sorry!";
    return false;
  }
  return true;
}

function insert_data($table, $hst,$us,$ps,$db)
{
  $con = mysql_connect($hst,$us,$ps);
  mysql_select_db($db, $con);

  echo "Entering default data for '" . $table ."'.<br />";
  if ($table == "gtd-List") 
   {
     mysql_query("INSERT INTO gtd-List
     (listname, owner, description)
     VALUES
     ('At Home', 'Mark', 'Work out how to do SQL')");
   }

   else
   {
     echo "I don't have default data for '" . $table ."'<br />";
     return false;
   }
 return true;
}

?>