Solved

unable to create table mysql php

Posted on 2011-02-24
7
452 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

733 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