Solved

unable to create table mysql php

Posted on 2011-02-24
7
450 Views
Last Modified: 2012-05-11
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
Comment
Question by:ikon32
7 Comments
 
LVL 17

Assisted Solution

by:psimation
psimation earned 125 total points
ID: 34976804
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
 
LVL 17

Assisted Solution

by:Shinesh Premrajan
Shinesh Premrajan earned 125 total points
ID: 34976862

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
 
LVL 11

Accepted Solution

by:
Bruce Smith earned 125 total points
ID: 34976891
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 11

Expert Comment

by:Bruce Smith
ID: 34976913
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
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 34976924
PS. ignore the "thegamepoker." in front of the table name. I was testing it in my environment.  :)
0
 
LVL 34

Assisted Solution

by:Beverley Portlock
Beverley Portlock earned 125 total points
ID: 34978421
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
 

Author Closing Comment

by:ikon32
ID: 34978508
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question