FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Can't create mySQL table





DoctorBeaver
I've created a database from Direct Admin, given it a username & password, installed my application and changed the file permissions to 777. The install routine is connecting to the database (at least, no error is reported in that section of the code).

However, when it tries to create the table it gives the error message from the "or die" bit. This is the whole code section:-

Code:
    // Connect to the database.

    mysql_pconnect($dbServer,$dbUser,$dbPass) or die("RAR");
    mysql_selectdb($dbName) or die("ERROR: could not connect to database. <br>Please go back and check the information you entered.");

    // Create new table.

    $sql = "CREATE TABLE `redcms_users` (";
    $sql .= "`user_id` INT( 11 ) NOT NULL AUTO_INCREMENT ,";
    $sql .= "`user_uname` TEXT NOT NULL ,";
    $sql .= "`user_password` TEXT NOT NULL ,";
    $sql .= "`user_level` INT( 11 ) DEFAULT '0' NOT NULL ,";
    $sql .= "`user_name` TEXT NOT NULL ,";
    $sql .= "`user_email` TEXT NOT NULL ,";
    $sql .= "`user_location` TEXT NOT NULL ,";
    $sql .= "`user_gender` TEXT NOT NULL ,";
    $sql .= "`user_dob` DATE DEFAULT '0000-00-00' NOT NULL ,";
    $sql .= "`user_site` TEXT NOT NULL ,";
    $sql .= "`user_msn` TEXT NOT NULL ,";
    $sql .= "`user_aim` TEXT NOT NULL ,";
    $sql .= "`user_yahoo` TEXT NOT NULL ,";
    $sql .= "`user_icq` TEXT NOT NULL ,";
    $sql .= "`user_joined_date` DATE DEFAULT '0000-00-00' NOT NULL ,";
    $sql .= "`user_joined_time` TIME DEFAULT '00:00:00' NOT NULL ,";
    $sql .= "`user_active` TEXT NOT NULL ,";
    $sql .= "`user_key` INT( 11 ) DEFAULT '0' NOT NULL ,";
    $sql .= "PRIMARY KEY ( `user_id` )";
    $sql .= ") TYPE = MYISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;";

    mysql_query($sql) or die("ERROR: Failed to execute query. <br><br> " . $sql);


The SQL query that it displays looks sound to me. Here's the error message:-

Code:
ERROR: Failed to execute query.

CREATE TABLE `redcms_users` (`user_id` INT( 11 ) NOT NULL AUTO_INCREMENT ,`user_uname` TEXT NOT NULL ,`user_password` TEXT NOT NULL ,`user_level` INT( 11 ) DEFAULT '0' NOT NULL ,`user_name` TEXT NOT NULL ,`user_email` TEXT NOT NULL ,`user_location` TEXT NOT NULL ,`user_gender` TEXT NOT NULL ,`user_dob` DATE DEFAULT '0000-00-00' NOT NULL ,`user_site` TEXT NOT NULL ,`user_msn` TEXT NOT NULL ,`user_aim` TEXT NOT NULL ,`user_yahoo` TEXT NOT NULL ,`user_icq` TEXT NOT NULL ,`user_joined_date` DATE DEFAULT '0000-00-00' NOT NULL ,`user_joined_time` TIME DEFAULT '00:00:00' NOT NULL ,`user_active` TEXT NOT NULL ,`user_key` INT( 11 ) DEFAULT '0' NOT NULL ,PRIMARY KEY ( `user_id` )) TYPE = MYISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;


I've already installed this appplication on my own PC using JSAS and I had no problem with it, so the coding is sound. I went through exactly the same routine that I did on Frihost (without having to change the permissions).

Any ideas? Could it be a permissions problem? That's all I can think of.
AftershockVibe
Quick checklist:

Does you user have permission to use the CREATE TABLE command?
You're using funny single quote marks in places - ` instead of ' (there is a difference!), may be an odd problem if they're not paired up correctly.

Also, why are you using persistent connections? Seems unecessary for what you're doing here. Not a criticism, just wondering...
DoctorBeaver
It's just a proprietary package I downloaded. I haven't altered the code at all. FOr my own purposes, I used phpAdmin to add extra fields afterwards. That's what I was going to do again on Frihost.

As I said initially, it installed with no problem inside my JSAS. There was no problem about whether it had permission to create a table, it just did it. How do I check whether it has permission on Frihost?

I changed those funny quote marks to ordinary ' but it made no difference.

UPDATE

I created the table by hand and cut that section of code out of install.php, however I got this:-

Code:
Warning: fopen(config.php): failed to open stream: Permission denied in /home/doctorbe/domains/docslodge.frih.net/public_html/install.php on line 44

Warning: fwrite(): supplied argument is not a valid stream resource in /home/doctorbe/domains/docslodge.frih.net/public_html/install.php on line 50


The 2nd warning was repeated about 12 times (I assume once for each field).

There was also a similar error on the close statement at the end of the messages above.
AftershockVibe
The fopen() errors are because you do not have chmod permissions to open that particular file. Make sure the file has permissions and not just the directory.

This is totally unrelated to your MySQL problems though. If you're using DirectAdmin then have a look under My Account > MySQL Management.

Select your database and hit modify priveleges for your user of choice.

EDIT:It has occurred to me that you may not actually have a database and are instead relying on the SQL to do this (or not at all). Just create one if you don't and everything should work fine.
DoctorBeaver
I've definitely got a database becuase I created it & added the table myself. This last problem was just trying to load the preliminary data.
Stubru Freak
I don't think Frihost supports DEFAULT CHARSET=charset but I don't think it's the problem. But you could try.
Rhysige
I would be looking at the default charset as a problem (I have had to change it before).
Dont change ` to ' it just wont help the situation. ` is typically used in field and table names most people leave them off but its good proactise not to.
' is used for values.. for example
SELECT `somefield` FROM `sometable` WHERE `someotherfield`='avalue'
This is the same as
SELECT somefield FROM sometable WHERE someotherfield='avalue'
but the first one is better practice.

What else does install do that you could perhaps do manually? insert into the database?
DoctorBeaver
Rhysige - this particular program only insterts 1 record so it's not a problem to add it by hand. However, I've got at least 6 other applications to load that use databases and some of those create many tables with initial values. They would be a real pain to do manually.
Rhysige
Is it happening on all of them?
DoctorBeaver
I haven't tried any others as I'm away for a few days & not on my own PC. I'll check it out further when I get back home.
Related topics
E-Cards with PHP & MySQL
HTTP AUTH with PHP and mySQL
mysql table keyword search
A very good PHP MySQL Tutorial
mySQL create table problem (parse error)..
Export and Import Mysql Table
how to selecting this from a mysql table ?
Developing a Login System with PHP and MySQL
create swishmax Login with phpbb db
a new mysql table...
Weird mysql problems, missing table that is there!
Subtrat values to a mysql table script
Can't create new table.
MySQL table relations
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.