• Please review our updated Terms and Rules here

Anyone good with SQL?

D

Druid6900

Veteran Member
Joined
May 7, 2006
Messages
3,809
Location
Hamilton, Ontario, Canada
My SQL is a bit rusty (ok, REALLY rusty and disintegrating).

This table is not being created in MySQL and it's the only one of 13 that isn't.

There is probably something glaringly wrong, but, I don't know what it is.

Any help is appreciated


// ###################### creating options table #######################
$DB_site->query("CREATE TABLE ".$dbprefix."options (
title VARCHAR(100),
hometitle VARCHAR(100),
shopurl VARCHAR(100),
homeurl VARCHAR(100),
securepath VARCHAR(100),
companyname VARCHAR(100),
address TEXT,
city VARCHAR(30),
state VARCHAR(20),
zip VARCHAR(20),
country VARCHAR(30),
phone VARCHAR(20),
faxnumber VARCHAR(20),
contactemail VARCHAR(100),
taxrate VARCHAR(10),
shipups VARCHAR(5),
grnd VARCHAR(5),
nextdayair VARCHAR(5),
seconddayair VARCHAR(5),
threeday VARCHAR(5),
canada VARCHAR(5),
worldwidex VARCHAR(5),
worldwidexplus VARCHAR(5),
fixedshipping VARCHAR(5),
method VARCHAR(15),
rate VARCHAR(20),
productpath VARCHAR(50),
catimage VARCHAR(50),
catopen VARCHAR(50),
viewcartimage VARCHAR(50),
viewaccountimage VARCHAR(50),
checkoutimage VARCHAR(50),
helpimage VARCHAR(50),
cartimage VARCHAR(50),
tablehead VARCHAR(50),
tableheadtext VARCHAR(50),
tableborder VARCHAR(50),
tablebg VARCHAR(50),
shipchart VARCHAR(50),
ship1p1 VARCHAR(50),
ship1us VARCHAR(50),
ship1ca VARCHAR(50),
ship2 VARCHAR(50),
ship2p1 VARCHAR(50),
ship2p2 VARCHAR(50),
ship2us VARCHAR(50),
ship2ca VARCHAR(50),
ship3 VARCHAR(50),
ship3p1 VARCHAR(50),
ship3p2 VARCHAR(50),
ship3us VARCHAR(50),
ship3ca VARCHAR(50),
ship4p1 VARCHAR(50),
ship4us VARCHAR(50),
ship4ca VARCHAR(50),
visa VARCHAR(50),
mastercard VARCHAR(50),
discover VARCHAR(50),
amex VARCHAR(50),
check VARCHAR(50),
fax VARCHAR(50),
moneyorder VARCHAR(50),
cc VARCHAR(50),
payable VARCHAR(100),
paypal VARCHAR(50),
paypalemail VARCHAR(100),
shopimage VARCHAR(100),
centerborder VARCHAR(50),
centerheader VARCHAR(50),
centercolor VARCHAR(50),
centerfont VARCHAR(50),
centerbg VARCHAR(50),
useheader VARCHAR(50),
usefooter VARCHAR(50),
myheader VARCHAR(50),
myfooter VARCHAR(50),
thumbheight VARCHAR(50),
thumbwidth VARCHAR(50),
picheight VARCHAR(50),
picwidth VARCHAR(50),
showstock VARCHAR(50),
showitem VARCHAR(50),
showintro VARCHAR(50),
orderby VARCHAR(50),
outofstock VARCHAR(50),
cs VARCHAR(5),
po VARCHAR(5),
license VARCHAR(50),
handling VARCHAR(20),
imagel VARCHAR(1),
showbestsellers varchar(5),
showspecials varchar(5),
language varchar(20),
showcattotals varchar(5),
shipcalc varchar(5),
shipusps varchar(5),
itemsperpage int(5),
usesecurefooter varchar(5),
mysecurefooter varchar(50),
usesecureheader varchar(5),
mysecureheader varchar(50),
mustsignup varchar(5),
uspsserver varchar(200),
uspsuser varchar(100),
uspspass varchar(100),
catsdisplay VARCHAR(10),
allwidth VARCHAR(10),
centerwidth VARCHAR(10),
tablewidth VARCHAR(10)
)");
 
Man, my SQL is very rusty so I don't know if I'll be much help. One thought...Right at the beginning you have...

$DB_site->query("CREATE TABLE ".$dbprefix."options (

There is a space at the end of TABLE and the inverted commas. Could this be important? Should it be there?

Tez
 
Your SQL seems to have the correct syntax... Just a couple of thoughts:
1. $dbprefix - Does it have a correct value? I guess that it has, you probably use this when creating all tables...

2. Watch out for the length of the rows... I don't know what the default page size for a tablespace in MySQL is, but if it is 4kb then you can't create that table. The length of the row would be greater than 4kb.

// Z
 
One quick thought...

One quick thought...

Try replacing the TEXT definition for the 'address' field with a VARCHAR(255). That should be more than enough to hold pretty much any address.

Other than that, your CREATE TABLE command looks fine, and since you say it is the only table out of 13 that isn't being created, your DB objects are probably fine.

If you are still having problems, PM me, and I'll dig through some of my old code and see what I can come up with.

(Disclaimer: I used to do a boat-load of Linux/Apache/MySQL/Perl development, but that was many years ago)
 
Zeela said:
Do you get any error message from MySQL? Any hints would be appreciated. ;)

// Z
Click to expand...

Yes, the program gives me a wonderfully informative error message....

echo "<p>There seems to have been a slight problem with the database.\n";
echo "Please try again by pressing the refresh button in your browser.</p>";
echo "An E-Mail has been dispatched to our <a href=\"mailto:$contactemail\">Technical Staff</a>, who you can also contact if the problem persists.</p>";
 
Something I noticed is that all the other sections that produce tables have something like this;

$DB_site->query("CREATE TABLE ".$dbprefix."category (
categoryid INT UNSIGNED NOT NULL AUTO_INCREMENT,

As the first line of the section, but, as you'll note, the one I'm having problems with doesn't have an xxxid INT UNSIGNED NOT NULL AUTO_INCREMENT LINE.

I don't know if this is strange or not.
 
:D Now that's an informative error message...

Can you try to create the table manually from some admin tool? Then you should get a better error message.

// Z
 
Uh, how about something simple, like :

you have some columns with "int" "varchar" instead of "INT" "VARCHAR"

yes, yes, the CaSe of the LeTtErS (and I also hate people that Do tHaT)
 
chuckcmagee said:
Uh, how about something simple, like :

you have some columns with "int" "varchar" instead of "INT" "VARCHAR"

yes, yes, the CaSe of the LeTtErS (and I also hate people that Do tHaT)
Click to expand...

No, these tables have LC attributes and they created correctly;

// ###################### creating discounts table #######################
$DB_site->query("CREATE TABLE ".$dbprefix."discounts (
id int(10) unsigned NOT NULL auto_increment,
productid varchar(50),
discount varchar(10),
type char(1),
frombuy int(11),
tobuy int(11),
sold varchar(10),
displayit char(1),
message varchar(250),
PRIMARY KEY (id)
)");

// ###################### creating templates table #######################
$DB_site->query("CREATE TABLE ".$dbprefix."templates (
id int(10) unsigned NOT NULL auto_increment,
title varchar(50),
template text,
description varchar(250),
PRIMARY KEY (id)
)");
 
Hmm, ok, I see that "option" is a reserved word. Maybe that's it.

Maybe try "opts" instead of "options", just another guess.

I did do Oracle database management for years, but, as you say, one gets rusty fast.
 
chuckcmagee said:
Hmm, ok, I see that "option" is a reserved word. Maybe that's it.

Maybe try "opts" instead of "options", just another guess.

I did do Oracle database management for years, but, as you say, one gets rusty fast.
Click to expand...

Doesn't seem to matter what I change options to, the same thing happens.

I tried putting the code in through a SQL query and, after changing ".$dbprefix." to the actual prefix, the error I got (again really helpful) was "ERROR: Unknown Punctuation String @ 9"

I tried using a completely different database and got exactly the same results, the first 12 tables create correctly and that one doesn't (it doesn't even partially create it, so, I'm thinking it has to be something right at the front).

Thanks for the suggestions, so far.
 
I tried your query on an MySQL 3.X server, and got the following error:

check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(50),
fax VARCHAR(50),
moneyorder VARCHAR(50),
cc VARCHAR(50),
payabl' at line 61
Click to expand...

The reserved keyword we all missed is check! :D
 
Well, I got tired of screwing around with it, so, broke it up into a 'create table' section and re-wrote the code as an "insert into table" structure and it worked.

Then I decided I didn't like that package and switched to another LOL

You never know what you've got 'til it works :)
 
I'm not quite sure I understand how splitting a query into two allows you to use reserved keywords as field names, but if you found a solution already... :-D
 
You must log in or register to reply here.
Back
Top