Jump to content

How to fix this command?


rain13
 Share

Recommended Posts

Does anyone know how to fix this table create command? I am trying to create table that would contain information for gallery image.SQL command:

'CREATE TABLE mytable (		Hits INT AUTO_INCREMENT,		RatingSum INT,		RatingCount INT,		Year INT,		Month INT,		MetaData STRING,		Description STRING,	);

Error code

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''CREATE TABLE mytable ( Hits INT AUTO_INCREMENT, RatingSum INT, RatingC' at line 1
Link to comment
Share on other sites

You shouldn't have that aprostophe at the start.
It came when I copied it out of my php.Here's my code.
<?php$user="***";$password="***";$database="np10124_galerii";$tblName = "mytable";mysql_connect("localhost",$user,$password);@mysql_select_db($database) or die( "Unable to select database");$query="SELECT * FROM ".$tblName;$result=mysql_query($query);if (!$result){	echo "No table exists";	$query	= 'CREATE TABLE '.$tblName.' (		Hits INT AUTO_INCREMENT,		RatingSum INT,		RatingCount INT,		Year INT,		Month INT,		MetaData STRING,		Description STRING,	);';	$result=mysql_query($query);}else{	echo "This.exists";}?>

With out aprostophe.

CREATE TABLE mytable (	Hits INT AUTO_INCREMENT,	RatingSum INT,	RatingCount INT,	Year INT,	Month INT,	MetaData STRING,	Description STRING,)

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STRING, Description STRING, )' at line 7

Edited by SoItBegins
Link to comment
Share on other sites

you should use char or varchar instead of STRING if you are going to store strings. and you must mention the length of the fieldhttp://dev.mysql.com/doc/refman/5.0/en/string-types.html

Edited by birbal
Link to comment
Share on other sites

for varchar it said.

import.php: Missing parameter: import_type (FAQ 2.8)import.php: Missing parameter: format (FAQ 2.8)

Does this also need lenght?How can I create it with dynamic lenght? Description can be at any size, It can be just few words but description can also be almost article which means if I give it 30 as lenght parameter then it may not always work. Also if I want to update description later, how I can enter longer text than I created if i created text varchar with fixed lenght? That's why need way to deal with dynamic size.

Edited by SoItBegins
Link to comment
Share on other sites

difference bitween char and varchar is in char type if you declare the legth of it 5 then it will take space as 5 though you use 3 chacracter long stringwhere as in varchar it will take place for 3 charcacters.but in both case you have to specify the maximum lenght. you have to consider yourself how much space can take a field maximum. if you need more space than char and vacrhar data type can store you have to use text,longtext etc. there is more info on above link.you can always increase the size of it later if you need any. but in later if you going to change the data type of any column it must match the criteria/limitation of new data type with the existed data.

Link to comment
Share on other sites

Whats wrong with this?

CREATE TABLE mytable (	Hits INT AUTO_INCREMENT,	RatingSum INT,	RatingCount INT,	Year INT,	Month INT,	MetaData VARCHAR(30),	Description VARCHAR(30));

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Edit: forsomereason it didn't like AUTO_INCREMENT.

Edited by SoItBegins
Link to comment
Share on other sites

auto increment field cant hold duplicate key. make that field primary key or unique constraint.

CREATE TABLE mytable (	Hits INT AUTO_INCREMENT PRIMARY KEY,	RatingSum INT,	RatingCount INT,	Year INT,	Month INT,	MetaData VARCHAR(30),	Description VARCHAR(30))

Edited by birbal
Link to comment
Share on other sites

Thank you very much.Now 1 more problem I have with updating.I used this to insert something into db

INSERT INTO mytableVALUES (0, 0, 0, 2011, 2, "meta", "description")
I inserted 2 rows , 1st row with description. In second row I had description2.Now why this command replaces Description in both rows instead of changing it in row where I have Description=description ? I mean why does it also replace Description in row where I have Description=description2 ?
UPDATE mytableSET Description = "New description"WHERE Description = description

If I now want to update Description to something longer than 30 chars then how should I call my update command?

Link to comment
Share on other sites

WHERE Description = "description" string should be quoted here.

Edited by birbal
Link to comment
Share on other sites

If I now want to update Description to something longer than 30 chars then how should I call my update command?
You don't; you change the table definition to allow more than 30 characters in the `description` field (e.g. by using ALTER TABLE).
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...