How to UPDATE data in MySQL

[Breana]

Ok let me see:

[CODE=mysql]--
-- Table structure for table `items`
--

CREATE TABLE `items` (
`itemid` int(10) unsigned NOT NULL default '0',
`title` varchar(255) default NULL,
`directions` text,
`userid` int(10) unsigned default NULL,
`rating` int(10) unsigned default '0',
`categoryid` int(10) unsigned default NULL,
`date` varchar(20) NOT NULL default '',
`story` text,
`hits` int(10) unsigned default '0',
`status` char(1) default 'W',
`gameinfo` text,
`gamerating` text,
`publisher` text,
`genar` text,
PRIMARY KEY (`itemid`),
UNIQUE KEY `recipieid` (`itemid`),
KEY `recipieid_2` (`itemid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;[/CODE]
Ut oh, i see an error, it should be itemid not recipe?

[etiainen]

Look, you might wanna check out some MYSQL tutorial or a book (i recommend Sql for mysql developers - a comprehensive tutorial and reference by Rick F. van der Lans)

try reading trough the shema i posted before...

Foreign keys are fields in one table that refer to primary keys in another, thus creating a link between them.
So, that last shema i wrote should have looked like this:

[code=mysql]

1.
--
-- Table structure for table `comments`
--
CREATE TABLE `comments` (
`itemid` int(10) unsigned NOT NULL default '0',
`userid` int(10) unsigned default NULL,
`date` varchar(20) NOT NULL default '',
`name` varchar(30) NOT NULL default '',
`comment` text,
`status` char(1) default 'W',
`commentid` int(10) NOT NULL auto_increment,
PRIMARY KEY (`commentid`),
FOREIGN KEY `itemid` REFERENCES `items` (`itemid`),
FOREIGN KEY (`userid`) REFERENCES `users` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 3 ;

[/code]




And, also in that items table set field itemid like this (the line 6):

...[code=mysql]
itemid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, [/code]
...

-it's a primary key and it can't have default value or it wont be unique for all the records.

[pbmods]

MyISAM tables support foreign keys? I thought that wasn't implemented yet....

[pbmods]

Heya, Breana.

Ok let me see:

[CODE=mysql]--
-- Table structure for table `items`
--

CREATE TABLE `items` (
`itemid` int(10) unsigned NOT NULL default '0',
`title` varchar(255) default NULL,
`directions` text,
`userid` int(10) unsigned default NULL,
`rating` int(10) unsigned default '0',
`categoryid` int(10) unsigned default NULL,
`date` varchar(20) NOT NULL default '',
`story` text,
`hits` int(10) unsigned default '0',
`status` char(1) default 'W',
`gameinfo` text,
`gamerating` text,
`publisher` text,
`genar` text,
PRIMARY KEY (`itemid`),
UNIQUE KEY `recipieid` (`itemid`),
KEY `recipieid_2` (`itemid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;[/CODE]
Ut oh, i see an error, it should be itemid not recipe?

Only what's in the parenthesis is important. The 'recipieid` is just the name of the index. However, you have a lot of redundancy in your indexes; you don't need the UNIQUE KEY `recipieid` nor the KEY `recipieid_2`. They don't do anything directly bad, but they do make your script run more slowly and take up extra disk space.

[Breana]

Ok i changed it to:

[CODE=mysql]CREATE TABLE `items` (
`itemid` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0',
`title` VARCHAR( 255 ) DEFAULT NULL ,
`directions` TEXT,
`userid` INT( 10 ) UNSIGNED DEFAULT NULL ,
`rating` INT( 10 ) UNSIGNED DEFAULT '0',
`categoryid` INT( 10 ) UNSIGNED DEFAULT NULL ,
`DATE` VARCHAR( 20 ) NOT NULL DEFAULT '',
`story` TEXT,
`hits` INT( 10 ) UNSIGNED DEFAULT '0',
`status` CHAR( 1 ) DEFAULT 'W',
`gameinfo` TEXT,
`gamerating` TEXT,
`publisher` TEXT,
`genar` TEXT,
PRIMARY KEY ( `itemid` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1[/CODE]

So what do i need to do for the comment part, and it wont let me import the 900 games i added so i goto re do those later it sucks. but i want it to be right and speedy lol.

So the comments table would have to be like this?
[CODE=mysql]--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
`itemid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`userid` INT(10) UNSIGNED DEFAULT NULL,
`DATE` VARCHAR(20) NOT NULL DEFAULT '',
`name` VARCHAR(30) NOT NULL DEFAULT '',
`comment` TEXT,
`status` CHAR(1) DEFAULT 'W',
`commentid` INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`commentid`),
FOREIGN KEY `itemid` REFERENCES `items` (`itemid`),
FOREIGN KEY (`userid`) REFERENCES `users` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 3 ;[/CODE]

[etiainen]

MyISAM tables support foreign keys? I thought that wasn't implemented yet....

Hm, yeah, that seams to be true... at least as i can conclude by first few hit titles on google...

The thing is I'm using them on my dev server (v 5.0.41)
And the shemas seam to work, at least for the creation part. Interestingly I never checked if it really checks those constraints...

On the other hand Breana might change db engine to InnoDB,
but I don't know her/his design so that shema of mine was only an educated guess...

From what I can see, there are lots of places for improvements in her/his queries, and I think he/she should read some stuff about SQL before continuing... Even a short tutorial like the one on www.w3schools.c om might help.

Database is a pretty slippery ground in the system, and it should be handled with care...

Well, that's my closing for tonight, hope I was of some help...

ALL MY BASE ARE BELONG TO YOU!
And may the source be with you Breana!

[pbmods]

Heya, Breana.

Each table can only have one AUTO_INCREMENT column. That would be your primary key. Foreign keys do not AUTO_INCREMENT, as that is taken care of by the table that uses that key as its primary.

[Breana]

I did it i got it working the comments i mean, i removed the old code i forgot about $commentid = nextid(COMMENTS , "commentid" );
bacause i remember in the tutorial i used to make this he said remove it...
12 comments :)

i feel so stupid sorry guys, i really thank you for all your time u did with me i learned a lot lol.

And i did have to change the sql for comments to this:

[CODE=mysql]--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
`itemid` int(10) unsigned NOT NULL,
`userid` int(10) unsigned default NULL,
`DATE` varchar(20) NOT NULL default '',
`name` varchar(30) NOT NULL default '',
`comment` text,
`status` char(1) default 'W',
`commentid` int(10) NOT NULL auto_increment,
PRIMARY KEY (`commentid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 4 ;[/CODE]

[etiainen]

Ok i changed it to:

[CODE=mysql]CREATE TABLE `items` (
`itemid` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0',
`title` VARCHAR( 255 ) DEFAULT NULL ,
`directions` TEXT,
`userid` INT( 10 ) UNSIGNED DEFAULT NULL ,
`rating` INT( 10 ) UNSIGNED DEFAULT '0',
`categoryid` INT( 10 ) UNSIGNED DEFAULT NULL ,
`DATE` VARCHAR( 20 ) NOT NULL DEFAULT '',
`story` TEXT,
`hits` INT( 10 ) UNSIGNED DEFAULT '0',
`status` CHAR( 1 ) DEFAULT 'W',
`gameinfo` TEXT,
`gamerating` TEXT,
`publisher` TEXT,
`genar` TEXT,
PRIMARY KEY ( `itemid` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1[/CODE]

So what do i need to do for the comment part, and it wont let me import the 900 games i added so i goto re do those later it sucks. but i want it to be right and speedy lol.

So the comments table would have to be like this?
[CODE=mysql]--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
`itemid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`userid` INT(10) UNSIGNED DEFAULT NULL,
`DATE` VARCHAR(20) NOT NULL DEFAULT '',
`name` VARCHAR(30) NOT NULL DEFAULT '',
`comment` TEXT,
`status` CHAR(1) DEFAULT 'W',
`commentid` INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`commentid`),
FOREIGN KEY `itemid` REFERENCES `items` (`itemid`),
FOREIGN KEY (`userid`) REFERENCES `users` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 3 ;[/CODE]

Ok, here's just one more:

No,
this: `itemid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
goes into items table:
[CODE=mysql]CREATE TABLE `items` (
`itemid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR( 255 ) DEFAULT NULL ,
`directions` TEXT,
`userid` INT( 10 ) UNSIGNED DEFAULT NULL ,
`rating` INT( 10 ) UNSIGNED DEFAULT '0',
`categoryid` INT( 10 ) UNSIGNED DEFAULT NULL ,
`DATE` VARCHAR( 20 ) NOT NULL DEFAULT '',
`story` TEXT,
`hits` INT( 10 ) UNSIGNED DEFAULT '0',
`status` CHAR( 1 ) DEFAULT 'W',
`gameinfo` TEXT,
`gamerating` TEXT,
`publisher` TEXT,
`genar` TEXT,
PRIMARY KEY ( `itemid` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1[/CODE]

comments table create query shold look like i typed it a few posts ago...

At least that's my idea... Again, you should be better off with some books/tutorials.

AYB!
E.

[Breana]

I just went to mysql tutorials and got points running great it now updates my points to.
you were right, update did the trick, i commented out nextid from the code and walla worked..

Thanks to every one who helped me. I am still learning so i will be back but thanks agian.
I got to start addng my games again.....long night.

[etiainen]

I just went to mysql tutorials and got points running great it now updates my points to.
you were right, update did the trick, i commented out nextid from the code and walla worked..

Thanks to every one who helped me. I am still learning so i will be back but thanks agian.

You're welcome.

.....long night.

Tell me about it. Being a code monkey is one hell of a summer job.

[pbmods]

Heya, Breana.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)