Code doesn't create new table, ends after displaying catagory.

curlious

This is my first php script. It must have untold errors because it will not create a new table when a new catagory is entered. I manually created a table and filled the 4 fields with data but the display function only displays the catagory after the form.
Here is the code

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html>

<head>
  <title></title>
  <meta name="GENERATOR" content="Quanta Plus" />
  <meta name="AUTHOR" content="" />
  <meta http-equiv="Content-Type" content="text/html; charset=" />
  <meta name="KEYWORDS" content="" />
  <link rel="stylesheet" type="text/css" href="css/mtcss.css" title="mtcss" />
  <STYLE type="text/css">
     @import url(css/mtcss.css);
  </STYLE>
</head>
<body>

<h1>Good Grief!</h1>

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<h2>Input Link Information</h2>
<table summary="LinkFormLayout" cellspacing="4" cellpadding="4">
  <tbody>
    <tr>
	    <td>Link Catagory:</td>
	    <td><input type="text" name="linkCatagory" size="24" maxlength="24" /></td>
    </tr>
    <tr>
	    <td>Link Text:</td>
	    <td><input type="text" name="linkText" size="32" maxlength="128"/></td>
    </tr>
    <tr>
	    <td>Link URL:</td>
	    <td><input type="text" name="url" size="72" maxlength="256" /></td>
    </tr>
    <tr>
	    <td>Link Comment:</td>
    </tr>
    <tr>
	    <td colspan="2">
	<textarea name="linkComment" cols="72" rows="4" ></textarea>
            </td>
    </tr>
    <tr>
	    <td colspan="2">
	     <input type="submit" name="submit" value="Submit!" />
	    </td>
    </tr>
  </tbody>
</table>
</form>

<?php
  $username="root";
  $password="********";
  $database="LinksDatabase";

  @mysql_pconnect('localhost',$username,$password) or die('Could not connect to MySQLServer');
  @mysql_select_db($database) or die('Unable to select database');

  if (isset($_POST['submit'])){
  	$query = "CREATE TABLE IF NOT EXSISTS " . $_POST['linkCatagory'] . " (
 	  id int(6) NOT NULL auto_increment,
 	  linkText varchar(64) NOT NULL,
  	  url varchar(256) NOT NULL,
  	  linkComment MEDIUMTEXT,
  	  PRIMARY KEY (id),
  	)";
        mysql_query($query);
  }

  mysql_close();
?>


<?
  if (isset($_POST['submit'])){
  	$linkCatagory=$_POST['linkCatagory'];
	$linkText=$_POST['linkText'];
  	$url=$_POST['url'];
  	$linkComment=$_POST['linkComment'];

@mysql_pconnect("localhost",$username,$password) or die("Could not connect to MySQLServer");
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO" . $linkCatagory . "VALUES ('','$linkTitle','$linkText','$url','$linkComment')";
mysql_query($query);
mysql_close();
   }
?>

<?php

  @mysql_pconnect("localhost",$username,$password) or die("Could not connect to MySQLServer");
  @mysql_select_db($database) or die( "Unable to select database");

  $catagoryTables = mysql_list_tables($database);

  if ($catagoryTables){
    while (list($catagory) = mysql_fetch_row($catagoryTables)){
      $query = "SELECT * FROM" .  $catagory;
      $result = mysql_query($query);
      $num = mysql_num_rows($result);

  echo "<dl><h3>$catagory</h3>"; 

  $i=0;

  echo $num;
  while ($i <= $num) {
//$linkID = mysql_result($result,$i,"id");		      
$linkText=mysql_result($result,$i,"linkText");
$url=mysql_result($result,$i,"url");
echo "<dt><a href=\"$url\">$linkText</a></dt>";

$linkComment=mysql_result($result,$i,"linkComment");
echo "<dd>$linkComment</dd>";
$i++;
  }
  echo '</dl>';
}	
  }
  mysql_close();
?>
<h1>Here I am at the End!</h1>
</body>
</html>

subwayman

First, lets help you find the problem. It's probably in the query so a trick in debugging queries is to echo out the query string prior to executing the query. This will let you know what is actually getting pushed through the mysql. I have lost count of the timees I have been convinced a piece of code creatres the right query only to find its poo.

One other thing. Why are you creating a new table for each category? This is very poor db design. Create a category table once and add categories to it as they are needed. Somewhere on this site is an artical on database normalisation - the process of deisgining a database to be efficient and by all means storing data only once. Have a read of it. I think you'll find it useful.

curlious

I took your advice. Then I fixed the queries.

$query = "CREATE TABLE IF NOT EXSISTS " . $_POST['linkCatagory'] . " (
 	  id int(6) NOT NULL auto_increment,
 	  linkText varchar(64) NOT NULL,
  	  url varchar(256) NOT NULL,
  	  linkComment MEDIUMTEXT,
  	  PRIMARY KEY (id)
  	)";
        echo $query.'<br>';

This outputs
CREATE TABLE IF NOT EXSISTS Game Sites ( id int(6) NOT NULL auto_increment, linkText varchar(64) NOT NULL, url varchar(256) NOT NULL, linkComment MEDIUMTEXT, PRIMARY KEY (id) )

The second query is now

$query = "INSERT INTO " . $linkCatagory . " VALUES ('','$linkText','$url','$linkComment')";
  	echo $query.'<br>';

This outputs:
INSERT INTO Game Sites VALUES ('','Planet Quake','http://www.planetquake.com','A hardcore site for Quake fraggers!')

And the finale one

$query = "SELECT * FROM " .  $catagory;
      echo $query.'<br>';

outputs:
SELECT * FROM Game Sites

Are the queries ok now. It still isn't displaying the data in Game Sites just the fact that there is a Catagory Game Sites

I realize this is probably horrible db design still being my first code I'd like to get it working. If you have the link to the db normilization I will happily read it after then refactor my code over and over again.

subwayman

Can you have a table/column name with spaces in? Seems a bit odd to me.

curlious

I don't know I ended up creating the table in phpMyAdmin and it allowed it.
When it lists the table it succesfuly lists Game Sites which is the one I created in phpMyAdmin
but it doesn't display any of the data in the table which is currently just one link with text and comment.

I attempted to compensate for the space in the table name by doing this

 $table = str_replace(" ","\ ",$catagory);
      $query = "SELECT * FROM " .  $table;
      echo $query.'<br>';

That didn't help.