mysql table if not exists

[garrensilverwing]

hey guys i am trying to write a code with php/mysql that will create a table inside my comments database...however when i run the code the first time i visit the page it creates the table but if i go back to the page it says table already exists when i want it to display the comments left and the post comment input box
here is my code:

function pagecomments($thread)
    {
                $username = "frostbit_admin";
                $password = "***"; 
                $server = "localhost";
                $commentsdatabase="frostbit_comments";
                $mysqlconnection = mysql_connect($server, $username, $password); 
                if (!$mysqlconnection) 
                    { 
                        die('There was a problem connecting to the mysql server. Error returned: '. mysql_error()); 
                    } 
                $commentsdatabaseconnection = mysql_select_db($commentsdatabase); 
                if (!$commentsdatabaseconnection)
                    { 
                        die('There was a problem using that mysql database. Error returned: '. mysql_error());
                    }
                $sql="CREATE TABLE IF NOT EXISTS `$thread`(`id` int(16) NOT NULL AUTO_INCREMENT,`posted` datetime NOT NULL,`user` varchar(16) NOT NULL,`comment` text NOT NULL,`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`modifiedby` varchar(16) NOT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13;";
                mysql_query($sql) or die(mysql_error());
            }
function displaycomments($thread)
    {
    if(isset($_SESSION['user']))
        {
        $sql="SELECT * FROM `$thread` ORDER BY `posted` DESC;";
        $query=mysql_query($sql) or die(mysql_error());
        $commentcount=count($query);
        while($row = mysql_fetch_array($query))
            {
                $username=$row['user'];
                $text=$row['comment'];
                echo "<p class=\"desctxt\"><i>Posted by: <strong>$username</strong> at ".$row['posted']."</i></p><br>";
                echo "<div class=\"commentdesign\">$text</div>";
                echo "<p class=\"desctxt\" style=\"margin-bottom: .5em;\"><i>Last Modified: ".$row['modified']."</i></p>";
                commentoptions($username,$text);
            }
        }
    else
        {
            echo "<i>You must be logged in to view comments.</i>";
        }
    }
function commentoptions($username,$text)
    {
?>
    <div id="commentwrapper" style="position:relative; height:3em; width:25em; margin-right:auto; margin-left:auto; text-align:left;">
        <div style="position: relative; z-index: 1; width: 25em; height: 1em;" id="options">
        <div style="position: relative; z-index: 1; float: left;" id="viewer">
<?php
            echo "<img alt=\"reply\" src=\"http://".$GLOBALS['domain']."/images/commenticons/comment_add.png\" />";
            echo "<img alt=\"quote\" src=\"http://".$GLOBALS['domain']."/images/commenticons/comment_quote.png\" />";
            echo "<img alt=\"report\" src=\"http://".$GLOBALS['domain']."/images/commenticons/comment_warning.png\" />";
        echo "</div>";
    if($username == $_SESSION['user'])
        {    
        echo "<div style=\"position: relative; z-index: 1; float: left;\" id=\"poster\">";
            echo "<img alt=\"edit\" src=\"http://".$GLOBALS['domain']."/images/commenticons/comment_edit.png\" />";
            echo "<img alt=\"notify\" src=\"http://".$GLOBALS['domain']."/images/commenticons/comment_email.png\" />";
        echo "</div>";
        }
    if($_SESSION['admin'] == 1)        
        {
        echo "<div style=\"position: relative;  z-index: 1; float: right;\" id=\"admin\">";
            echo "<img alt=\"delete\" src=\"http://".$GLOBALS['domain']."/images/commenticons/comment_remove.png\" />";
            echo "<img alt=\"warn\" src=\"http://".$GLOBALS['domain']."/images/commenticons/comment_error.png\" />";
            echo "<img alt=\"lock\" src=\"http://".$GLOBALS['domain']."/images/commenticons/lock.png\" />";
            echo "<img alt=\"sticky\" src=\"http://".$GLOBALS['domain']."/images/commenticons/bulb_on.png\" />";
            echo "<img alt=\"edit\" src=\"http://".$GLOBALS['domain']."/images/commenticons/comment_edit.png\" />";
        echo "</div>";
        }
?>
    </div>
</div>

where
$globals['domain'] = my website url (since i test on my computer @ localhost it was the easiest way i could figure out how to rapdily change links)
$thread = the name of the page it will be displayed on also the name of the table
$username = the username of whoever is currently posting a comment
$text = the text of the comment

and hopefully the rest is self explanatory, thanks again in advance for the help

 

[misson]

What's the exact error you're getting? Do you have a URI for a live test page?

$globals['domain'] = my website url (since i test on my computer @ localhost it was the easiest way i could figure out how to rapdily change links)

$_SERVER['SERVER_NAME'] holds the server name, but you don't need even that because you can leave off the URI scheme and hostname:

<div style="position: relative; z-index: 1; float: left;" id="viewer">
    <img alt="reply" src="/images/commenticons/comment_add.png" />
    <img alt="quote" src="/images/commenticons/comment_quote.png" />
    <img alt="report" src="/images/commenticons/comment_warning.png" />
</div>

Also, if you're not using variable interpolation, use single quotes. Better would have been to use a multiline string:

            echo "<img alt=\"reply\" src=\"http://$_SERVER[SERVER_NAME]/images/commenticons/comment_add.png\" />
    <img alt=\"quote\" src=\"http://$_SERVER[SERVER_NAME]/images/commenticons/comment_quote.png\" />
    <img alt=\"report\" src=\"http://$_SERVER[SERVER_NAME]/images/commenticons/comment_warning.png\" />
</div>";

or switch to PHP only to print the server name:

<div style="position: relative; z-index: 1; float: left;" id="viewer">
    <img alt="reply" src="http://<?php echo $_SERVER[SERVER_NAME]; ?>/images/commenticons/comment_add.png" />
    <img alt="quote" src="http://<?php echo $_SERVER[SERVER_NAME]; ?>/images/commenticons/comment_quote.png" />
    <img alt="report" src="http://<?php echo $_SERVER[SERVER_NAME]; ?>/images/commenticons/comment_warning.png" />
</div>

Note: the link in your sig doesn't work. It links to "http://www.brianwallchess.x10hosting.com/", which is a PHP "Domain available!" page.

 

[garrensilverwing]

hey mission, im getting a mysql error saying that the table already exists when i go to the page
thanks for the $_server['server_name'] tip i didnt know that

i tried using the URI scheme way originally but when i called the function up on different pages all the links were broken because it isnt the same directory

ah using php only to print the server name is a better idea, i'm so inefficient huh lol

ok i will fix the link in my sig

 

[misson]

What's the exact error? Better find the file and line the error occurs at as well.

 

[garrensilverwing]

all i get where the comments are supposed to be it says
"Table 'fishingpolefirstblood' already exists" (this is from brianwallchess.net/news/news/fishing_pole_first_blood.php) its not a php error or even a mysql error that is all it says

 

[misson]

all i get where the comments are supposed to be it says
"Table 'fishingpolefirstblood' already exists" (this is from brianwallchess.net/news/news/fishing_pole_first_blood.php) its not a php error or even a mysql error that is all it says

If the error message is generated by your script, check the test that the query succeeds; it could be that the query succeeds but success isn't tested properly. If the error message turns out to be from MySQL (via mysql_error), then the table creation query doesn't have an "IF NOT EXISTS" clause, despite the sample code you posted.

While debugging, you should print the query and the line number where the error is printed.

 

[garrensilverwing]

well that is the thing, it usually gives me all that information but this time all it says is the table already exists, i went in and played around with it and now it doesnt say anything at all lol...

 

[misson]

Sounds like the live script wasn't the current version. Good thing it's working now.

 

[garrensilverwing]

no its not working, it isnt displaying any of the comments and everything after the comments isnt displaying, including the footer which was always there before :'(

 

[misson]

That's probably because the script uses "or die()" for error reporting, which it shouldn't.