PDA

View Full Version : URGENT: Does anyone here know how to use MySQL?



Shade
04-28-2008, 04:32 PM
I'm trying to create a simple, modifiable inventory table for my final project, but I just can't figure out how to properly set up a database. I have watched countless instructional videos and it's just not clicking, and I'm quickly running out of time to get this done... :(

Twes
04-28-2008, 04:38 PM
Can't help you buddy. Wish I could.

Los Angeles
04-28-2008, 06:15 PM
Databases make me feel like I have a learning disability. They completely baffle me. :sorry:

Shade
04-28-2008, 06:43 PM
Databases make me feel like I have a learning disability. They completely baffle me. :sorry:

Ditto. I'm going over the instructional vids for the billionth time and am slowly starting to understand some basic stuff, but everything's due before Thursday. I don't know if I can figure it out in time. Looks like it's time to pull some all-nighters...

brichard
04-28-2008, 10:54 PM
Ditto. I'm going over the instructional vids for the billionth time and am slowly starting to understand some basic stuff, but everything's due before Thursday. I don't know if I can figure it out in time. Looks like it's time to pull some all-nighters...

When I worked with Kiva Networking as a sales guy, several years ago, the techies there loved MySQL and they all thought Bill Gates was the anti-christ. Find some Linux or Unix lovers and you will probably be 7 degrees of separation from a My SQL afficianado.

Good Luck!

Shade
04-29-2008, 11:19 AM
No one? :sad:

Doug
04-29-2008, 11:43 AM
Download the MySQL GUI Tools.

http://dev.mysql.com/downloads/gui-tools/5.0.html

MySQL Administrator has a GUI you can use to create user IDs, schema (the DB), tables in the DB, columns in the tables, etc.

Doug
04-29-2008, 12:11 PM
You probably already have some sample "create table" SQL. Here's another one:



drop table if exists PACER_GAME;

CREATE TABLE PACER_GAME
(
PACER_GAME_ID MEDIUMINT NOT NULL,
SEASON_ID MEDIUMINT NOT NULL,
GAME_DATE DATETIME NOT NULL,
IS_HOME_GAME MEDIUMINT NOT NULL,
OPPONENT VARCHAR(16) NOT NULL,
THREAD_POSTED DATETIME,
GUESS_THREAD_ID MEDIUMINT,
THREAD_CLOSED DATETIME,
WINNER_POSTED DATETIME,
PACER_SCORE MEDIUMINT,
OPPONENT_SCORE MEDIUMINT,
PRIMARY KEY(PACER_GAME_ID),
FOREIGN KEY (SEASON_ID) REFERENCES SEASON (SEASON_ID)
);

Shade
04-29-2008, 03:04 PM
Thanks Doug! I'm gonna check that out. :)

I'm not allowed to use a GUI for my class, but maybe it'll help me understand the code a little better.

Btw, does anyone know how to use the source command to load a .sql file into MySQL? I keep getting a syntax error whenever I try:


mysql> source randomfile.sql

Twes
04-29-2008, 04:01 PM
I think Shade is one of these guys who will be late to his own funeral.

:D

Shade
04-29-2008, 05:47 PM
Hey Doug, is that the code for the RoboDoug table?

Could you (or anyone) give me any tips on how to build a basic inventory table? Basically, I'm looking to construct a table for my gaming cards, so I need variables for the card name, set, value, and quantity.

Right now, my basic table looks like this:



DROP TABLE IF EXISTS mtgStore;
DROP TABLE IF EXISTS card;
DROP TABLE IF EXISTS name;
DROP TABLE IF EXISTS set;
DROP TABLE IF EXISTS value;
DROP TABLE IF EXISTS quantity;

CREATE TABLE mtgStore (
cardID int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) default NULL,
set varchar(50) default NULL,
value varchar(10) default NULL,
quantity varchar(5) default NULL,
PRIMARY KEY (cardID)
);

INSERT INTO mtgStore VALUES (
null, 'Fulminator Mage', 'Shadowmoor', '11.36', '2'
);

INSERT INTO mtgStore VALUES (
null, 'Vexing Shusher', 'Shadowmoor', '12.10', '0'
);

INSERT INTO mtgStore VALUES (
null, 'Bitterblossom', 'Morningtide', '13.12', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Chameleon Colossus', 'Morningtide', '10.92', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Mutavault', 'Morningtide', '19.51', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Garruk Wildspeaker', 'Lorwyn', '15.39', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Thoughtseize', 'Lorwyn', '14.65', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Pithing Needle', '10th Edition', '11.09', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Tarmogoyf', 'Future Sight', '37.75', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Damnation', 'Planar Chaos', '15.69', '1'
);
Am I at least on the right path?

Doug
04-29-2008, 06:40 PM
Hey Doug, is that the code for the RoboDoug table?

Could you (or anyone) give me any tips on how to build a basic inventory table? Basically, I'm looking to construct a table for my gaming cards, so I need variables for the card name, set, value, and quantity.

Right now, my basic table looks like this:



DROP TABLE IF EXISTS mtgStore;
DROP TABLE IF EXISTS card;
DROP TABLE IF EXISTS name;
DROP TABLE IF EXISTS set;
DROP TABLE IF EXISTS value;
DROP TABLE IF EXISTS quantity;

CREATE TABLE mtgStore (
cardID int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) default NULL,
set varchar(50) default NULL,
value varchar(10) default NULL,
quantity varchar(5) default NULL,
PRIMARY KEY (cardID)
);

INSERT INTO mtgStore VALUES (
null, 'Fulminator Mage', 'Shadowmoor', '11.36', '2'
);

INSERT INTO mtgStore VALUES (
null, 'Vexing Shusher', 'Shadowmoor', '12.10', '0'
);

INSERT INTO mtgStore VALUES (
null, 'Bitterblossom', 'Morningtide', '13.12', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Chameleon Colossus', 'Morningtide', '10.92', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Mutavault', 'Morningtide', '19.51', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Garruk Wildspeaker', 'Lorwyn', '15.39', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Thoughtseize', 'Lorwyn', '14.65', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Pithing Needle', '10th Edition', '11.09', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Tarmogoyf', 'Future Sight', '37.75', '1'
);

INSERT INTO mtgStore VALUES (
null, 'Damnation', 'Planar Chaos', '15.69', '1'
);
Am I at least on the right path?

Yes, that is one of the RoboDoug tables. There's others.

That's the one that holds the Pacers game schedule, and keeps track of what RoboDoug has done for the game - posted the guess thread, closed it, posted the winner. And the score of the game. The guesses are kept in another table, as is the season.

In this case, I'm not using auto-increment keys, so my program calculates the PACER_GAME_ID itself.

But basically, for that table, I'm doing my inserts about like you:



insert into PACER_GAME (PACER_GAME_ID, SEASON_ID, GAME_DATE, IS_HOME_GAME, OPPONENT)
values (7009, 1071, '2007-10-31 19:00', '1', 'Washington');
insert into PACER_GAME (PACER_GAME_ID, SEASON_ID, GAME_DATE, IS_HOME_GAME, OPPONENT)
values (7010, 1071, '2007-11-2 19:00', '1', 'Miami');
insert into PACER_GAME (PACER_GAME_ID, SEASON_ID, GAME_DATE, IS_HOME_GAME, OPPONENT)
values (7011, 1071, '2007-11-3 20:00', '0', 'Memphis');
insert into PACER_GAME (PACER_GAME_ID, SEASON_ID, GAME_DATE, IS_HOME_GAME, OPPONENT)
values (7012, 1071, '2007-11-7 19:00', '1', 'L.A. Clippers');
insert into PACER_GAME (PACER_GAME_ID, SEASON_ID, GAME_DATE, IS_HOME_GAME, OPPONENT)
values (7013, 1071, '2007-11-9 19:00', '0', 'Charlotte');
insert into PACER_GAME (PACER_GAME_ID, SEASON_ID, GAME_DATE, IS_HOME_GAME, OPPONENT)
values (7014, 1071, '2007-11-10 19:00', '1', 'Denver');And so on... (I actually generate the inserts by using a Perl program to parse the .CSV format schedule that is available on pacers.com...)

I use the column names in my insert statement, but that's optional as long as the values match the order of the columns in the table you are inserting into.

Are card, name, set, value, and quantity other tables, or the columns in the mtgStore table?



DROP TABLE IF EXISTS card;
DROP TABLE IF EXISTS name;
DROP TABLE IF EXISTS set;
DROP TABLE IF EXISTS value;
DROP TABLE IF EXISTS quantity;
If they are just the columns, then there is no reason to have those statements.

I tend not to allow NULL in a column unless it can truly be NULL. For example, the first columns in my table have NOT NULL because it would make no sense to have a game without an opponent. However, the others are nullable because I can have a game that doesn't have a score yet.

Hope that helps. I'll be gone for the rest of the night, but I'll check back tomorrow to see if you have any questions.

Oh, and "quantity" should probably be a numeric type of some sort...

I orginally wrote RoboDoug using MySQL 3.x - if I were writing that DDL (the create table stmt) now, I would probably use the standard SQL data types instead the MySQL extensions. See: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Los Angeles
04-30-2008, 12:16 AM
You guys might as well be speaking Klingon. :dunce:

Shade
04-30-2008, 01:12 AM
Are card, name, set, value, and quantity other tables, or the columns in the mtgStore table?

This is one of the things I'm actually unsure about. I think they'll need to be other tables, but I'm not sure how to link the tables to get them to output the results I'm looking for.

Basically, on the HTML/PHP side, I want to create a page that lists the variables I listed above, probably in a table. I want the user to be able to select a particular card from the inventory, push a button, and go to a second page that displays the results via table output. Basically, similar to a very basic online store interface.



Oh, and "quantity" should probably be a numeric type of some sort...I thought it was. :shrug:

Btw, I've been trying to use the "source" command to insert my .sql file with these commands into MySQL so that I don't have to go through the tedious nature of doing everything line-by-line in MySQL, but I keep getting a syntax error when I try. Maybe my .sql file isn't properly formatted...?

Trader Joe
04-30-2008, 01:29 AM
This is jibberish.

Doug
04-30-2008, 08:53 AM
This is one of the things I'm actually unsure about. I think they'll need to be other tables, but I'm not sure how to link the tables to get them to output the results I'm looking for.



Think about it like this, a database table can model a real world object, in this case a "card".

A "card" as certain attributes that we want to associated with it - values that make it what it is. Things like a "name", a "set", a "value", etc. Those become columns in the table.

Those attributes also have certain properties, like what type of data can it hold (numeric, character, etc)? How big can it be? (length) Is it acceptable if that attribute does not have any value at all? (NULL / NOT NULL).

All of that figures in to how you define your tables.




Basically, on the HTML/PHP side, I want to create a page that lists the variables I listed above, probably in a table. I want the user to be able to select a particular card from the inventory, push a button, and go to a second page that displays the results via table output. Basically, similar to a very basic online store interface.






So, does the table you put up early contain every bit of information about the cards? In other words, does your program basically

select cardID, name from mgtStore order by name asc;
*** display list to user on a web page ***
*** let the user select a card name by clicking on it ***
select * from mgtStore where cardID = whattheyclickedon
*** display another web page with the detail information

or do you need to select the detail information from another table?
Are the cards grouped in any way (decks?) or related to other objects in your DB? For example, multiple "cards" might belong to a "deck" which has a single "owner".

I thought it was. :shrug:

varchar is a text data type.




Btw, I've been trying to use the "source" command to insert my .sql file with these commands into MySQL so that I don't have to go through the tedious nature of doing everything line-by-line in MySQL, but I keep getting a syntax error when I try. Maybe my .sql file isn't properly formatted...?

To troubleshoot, break it down to just the "create table" statement and see what happens.

Twes
04-30-2008, 09:52 AM
How many credit hours does Doug get for this?

:D

Shade
04-30-2008, 04:23 PM
Okay, I've modified my code a bit, but I still can't get the source command to work, which is seriously hamstringing me in terms of time.


DROP TABLE IF EXISTS store;

CREATE TABLE store (
cardID int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) default NULL,
set varchar(50) default NULL,
value decimal(3,2) default NULL,
quantity int(11) default NULL,
PRIMARY KEY (cardID)
);

INSERT INTO store VALUES (
null, 'Fulminator Mage', 'Shadowmoor', '11.36', '2'
);

INSERT INTO store VALUES (
null, 'Vexing Shusher', 'Shadowmoor', '12.10', '0'
);

INSERT INTO store VALUES (
null, 'Bitterblossom', 'Morningtide', '13.12', '1'
);

INSERT INTO store VALUES (
null, 'Chameleon Colossus', 'Morningtide', '10.92', '1'
);

INSERT INTO store VALUES (
null, 'Mutavault', 'Morningtide', '19.51', '1'
);

INSERT INTO store VALUES (
null, 'Garruk Wildspeaker', 'Lorwyn', '15.39', '1'
);

INSERT INTO store VALUES (
null, 'Thoughtseize', 'Lorwyn', '14.65', '1'
);

INSERT INTO store VALUES (
null, 'Pithing Needle', '10th Edition', '11.09', '1'
);

INSERT INTO store VALUES (
null, 'Tarmogoyf', 'Future Sight', '37.75', '1'
);

INSERT INTO store VALUES (
null, 'Damnation', 'Planar Chaos', '15.69', '1'
);


This is all slowly starting to make sense to me. I think. :dunce:

Doug
04-30-2008, 06:27 PM
Okay, I've modified my code a bit, but I still can't get the source command to work, which is seriously hamstringing me in terms of time.

What error do you get?

Does it spit anything out?

I put your "create table" statement in a file and was able to use the source command.

You do have a syntax error, however.

You can also just redirect your source file into the mysql command, like this:

C:\>mysql -u RoboDoug -p fbm < foo.sql
Enter password: ********

RoboDoug is the user it is connecting to MySQL with and fbm is the database. -p says prompt me for the password.

It then spits out the error text telling me there's an error in the SQL. If you do something similar, you should get some error text, too.

The "start mysql and source the file" approach worked for me, too.

C:\>mysql -u RoboDoug -p fbm
Enter password: ********
mysql> source foo.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that...

Shade
04-30-2008, 09:04 PM
I get this error:


ERROR:
Failed to open file 'store.sql', error: 2
This is the command that we were told to put in to access our database:


mysql -h localhost.localdomain -u myusername -pMy instructor suggesting downloading phpMyAdmin, which I did, but I can't even figure out how to open the damn thing.

The instructional videos are simply inadequate for beginners to learn this stuff. A lot of "little stuff" that is necessary to know is neglected. I learned some of this stuff simply through online research.

I'm going to keep working on it, but I'm pretty screwed at this point. It's due in less than 8 hours. And now the server for the vids is down, so I'm REALLY screwed.

Doug
04-30-2008, 10:09 PM
I get this error:


ERROR:
Failed to open file 'store.sql', error: 2
This is the command that we were told to put in to access our database:


mysql -h localhost.localdomain -u myusername -p

Is store.sql in the same directory you are executing the mysql command in?

What O/S are you using?

FYI: One error in your SQL is that "set" is a SQL keyword. You'll have to name that column something else.

Doug
04-30-2008, 10:10 PM
And if you can't "source" the file in, you can probably cut-and-paste it in.

Anthem
04-30-2008, 11:41 PM
URGENT: Does anyone here know how to use MySQL?
:thatswhatshesaid:

Wait... wrong thread?

Shade
05-01-2008, 12:37 PM
Is store.sql in the same directory you are executing the mysql command in?

What O/S are you using?

FYI: One error in your SQL is that "set" is a SQL keyword. You'll have to name that column something else.

I'm not sure. I have the store.sql file in the same directory that I have all of my other stuff that's on the server. Like I said before, the videos are really not clear on small stuff like that.

I'm using XP.

I never would have known that about "set." Again, something that was neglected in the video instructions.

I'll be surprised if I passed that class. I just really, really hate doing well in a class until the final, and then having the final take such a significant chunk out of the final grade (40%, in this case). If I'm going to fail, I'd like to know early on so I don't waste my time.

Anyway, thanks a lot for your help, Doug. I really appreciate it.

Doug
05-01-2008, 01:54 PM
I'm not sure. I have the store.sql file in the same directory that I have all of my other stuff that's on the server. Like I said before, the videos are really not clear on small stuff like that.

I'm using XP.

I never would have known that about "set." Again, something that was neglected in the video instructions.

I'll be surprised if I passed that class. I just really, really hate doing well in a class until the final, and then having the final take such a significant chunk out of the final grade (40%, in this case). If I'm going to fail, I'd like to know early on so I don't waste my time.

While it might be a bit late, try opening a command window and "cd" ing to the directory where your store.sql file is. Then run the mysql command from there. Alternatively, start mysql and put the full path on the front of your store.sql file. source c:\MyStuff\store.sql



Anyway, thanks a lot for your help, Doug. I really appreciate it.

You're welcome. (I would have said "no problem" but Scott H might kill me.)

Databases are a bit tricky the first couple of times you use them. After a while, though, it gets easier.

Shade
05-01-2008, 08:47 PM
I'm going to try again later to figure this all out, but right now I need to take a break from it for a little bit.