[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

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>
0
ikon32
Asked:
ikon32
4 Solutions
 
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 PremrajanTechnical 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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now