unable to create table mysql php

Hi experts, I have this simple php script below. It is not creating the table in to mysql.
Can you please check it out and possibly find where the error in the code is?


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="gr" xml:lang="gr">

<head>
<title>title</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="StyleSheet" type="text/css" href="code/main.css" />
</head>
<body>
      
<div style="position:absolute; left:0px;  top:0px; right:0px;width:100%; height:100%; padding:0px; margin:0px; text-align:left;">

start
<?php
include "code/dbinfo.inc.php";

mysql_connect(localhost,$username,$password);
mysql_select_db($database) or die( "Unable to select database");

mysql_query("CREATE TABLE pediko_tbl (   // it does not create the table here
id int(6) NOT NULL auto_increment,
link_th varchar(100) NOT NULL,
link varchar(100) NOT NULL,
UNIQUE KEY (link_th))")or die( "Unable to create table");
mysql_query("SET NAMES 'utf8'");


$i=0;
if ($handle = opendir('path')) {
    while (false !== ($file = readdir($handle))) {
        if ($file != "." && $file != "..") {

            mysql_query("INSERT INTO pediko_tbl VALUES('','$file' , '$file')");
            echo "$file";
       $i++;
        }
    }
    closedir($handle);
}

mysql_close();

echo "all $i inserted";
?>



</div>
</body>
</html>
ikon32Asked:
Who is Participating?
 
Bruce SmithSoftware Engineer IICommented:
You must declare the id as the primary key:
   
CREATE TABLE thegamepoker.pediko_tbl (id int(6) NOT NULL auto_increment,link_th varchar(100) NOT NULL,link varchar(100) NOT NULL,Primary KEY (id), UNIQUE KEY (link_th));

Open in new window

0
 
psimationCommented:
General tip for debugging SQL issues in PHP -

1. Echo your SQL query in your code after variable substitution.
2. Use PHPMyAdmin or MySQL Workbench to the run that exact query that is being echoe'd

In your code above, try to assign the mysql_query to a variable, ie, $result = mysql_query(..........) mysql_query() will not run "on it's own" - you have to assign to a variable.
0
 
Shinesh PremrajanEngineering ManagerCommented:

mysql_query("CREATE TABLE IF NOT EXISTS pediko_tbl (

Update the SQL query to this.

The better option is to first create the table in the database and then run the script for insert query.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Bruce SmithSoftware Engineer IICommented:
Actually, my apologies, it doesn't HAVE to be the Primary key, it can be declared as UNIQUE instead also. Either way.

1. Primary


CREATE TABLE thegamepoker.pediko_tbl (id int(6) NOT NULL auto_increment,link_th varchar(100) NOT NULL,link varchar(100) NOT NULL, PRIMARY KEY (id), UNIQUE KEY (link_th));

Open in new window

2. Unique


CREATE TABLE thegamepoker.pediko_tbl (id int(6) NOT NULL auto_increment,link_th varchar(100) NOT NULL,link varchar(100) NOT NULL, UNIQUE KEY (id), UNIQUE KEY (link_th));

Open in new window

0
 
Bruce SmithSoftware Engineer IICommented:
PS. ignore the "thegamepoker." in front of the table name. I was testing it in my environment.  :)
0
 
Beverley PortlockCommented:
Change your DIE clause from

die( "Unable to create table");

to

die( "Unable to create table " . mysql_error() );

then at least you'll know why it failed.
0
 
ikon32Author Commented:
Big thanks to all, your comments were excellent. I had to accept all as correct answers cause the solution I guess  was a combination... found that out after I tried all your solutions. thanks so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.