Jump to content

SQL Structure


XC-Runner

Recommended Posts

I'm new to mySQL, so bear with me.

 

I need to create a database like this:

 

Database (Skills)

--Table (Woodcutting)

 

Then in that table, I need these values:

 

Regular = 25

Oak = 37.5

Willow = 67.5

Teak = 85

Maple = 100

Mahogany = 125

Yew = 175

Magic = 250

 

But I'm unsure as how to structure it. I was guessing this:

 

tree (VARCHAR(15))

regular

...

magic

 

And

 

exp (VARCHAR(8))

25

...

250

 

But then I got lost at when I insert the data, doesn't this get overwritten each time?

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

[span style=\"color:#0000BB\"]<?

$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"INSERT INTO woodcutting(tree, exp) VALUES(\'Regular\', \'25\')\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#0000BB\"]?>[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

Doesn't that insert Regular -> 25, but then rewrite over it the next tree and exp?

 

Thanks,

 

XC-Runner

Link to comment
Share on other sites

No it doesn't. INSERT should never overwrite (UPDATE) or replace (REPLACE) any records in your table. Even so, it's in your best interest to create a unique id field. It will probably come in handy later on when dealing with intertable relations, and it's good bookkeeping practice anyways.

Link to comment
Share on other sites

No it doesn't. INSERT should never overwrite (UPDATE) or replace (REPLACE) any records in your table. Even so, it's in your best interest to create a unique id field. It will probably come in handy later on when dealing with intertable relations, and it's good bookkeeping practice anyways.

305007[/snapback]

 

Ok, thanks. Even if I won't need an id field, it's still good to have? Wouldn't that just be extra stuff I don't need?

 

XC-Runner

Link to comment
Share on other sites

Ok. So I wrote this program quickly to throw the stuff into the database.

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

[span style=\"color:#0000BB\"]<?php

 

$connection [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_connect[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\'localhost\'[/span][span style=\"color:#007700\"], [/span][span style=\"color:#DD0000\"]\'username\'[/span][span style=\"color:#007700\"], [/span][span style=\"color:#DD0000\"]\'password\'[/span][span style=\"color:#007700\"]);

if(![/span][span style=\"color:#0000BB\"]$connection[/span][span style=\"color:#007700\"]) {

  echo [/span][span style=\"color:#DD0000\"]\'Can\\'[/span][span style=\"color:#0000BB\"]t Connect[/span][span style=\"color:#007700\"]: [/span][span style=\"color:#DD0000\"]\' . mysql_error();

}

 

$db = mysql_select_db(\'[/span][span style=\"color:#0000BB\"]united_skills[/span][span style=\"color:#DD0000\"]\');

if(!$db) {

  echo \'[/span][span style=\"color:#0000BB\"]Can[/span][span style=\"color:#007700\"]&[/span][span style=\"color:#FF8000\"]#092;\'t Choose DB: \' . mysql_error();

[/span][span style=\"color:#007700\"]}

 

[/span][span style=\"color:#0000BB\"]$trees [/span][span style=\"color:#007700\"]= array (

[/span][span style=\"color:#DD0000\"]\"Regular\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]25[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Oak\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]37.5[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Willow\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]67.5[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Teak\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]85[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Maple\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]100[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Mahogany\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]125[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Yew\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]175[/span][span style=\"color:#007700\"],

[/span][span style=\"color:#DD0000\"]\"Magic\" [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]250

  [/span][span style=\"color:#007700\"]);

 

foreach([/span][span style=\"color:#0000BB\"]$trees [/span][span style=\"color:#007700\"]as [/span][span style=\"color:#0000BB\"]$tree [/span][span style=\"color:#007700\"]=> [/span][span style=\"color:#0000BB\"]$exp[/span][span style=\"color:#007700\"]) {

  [/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"INSERT INTO woodcutting ( tree, exp ) VALUES ( $tree, $exp )\"[/span][span style=\"color:#007700\"];

  [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"])

    or die([/span][span style=\"color:#DD0000\"]\'Can\\'[/span][span style=\"color:#0000BB\"]t Add Data[/span][span style=\"color:#007700\"]: \'[/span][span style=\"color:#0000BB\"] . mysql_error());

}

 

?>        [/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

And it returns:

 

Can't Add Data: Unknown column 'Regular' in 'field list'

 

Which means the table isn't set up correctly...?

 

woodcutting

Field---Type--------------Null------Default

tree---varchar(20)-------Yes-------NULL

exp---varchar(5)---------Yes-------NULL

 

?

 

XC-Runner

Link to comment
Share on other sites

It should be:

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

\"INSERT INTO woodcutting( tree, exp ) VALUES( \'$tree\', \'$exp\')\";

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

Also, why is your exp field a VARCHAR type? Shouldn't you just use INT?

 

Thirdly, I'll bet it takes a lot more time to work out this script than just manually enter the data either with phpMyAdmin or the MySQL command prompt. :P

Link to comment
Share on other sites

It should be:

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

\\\"INSERT INTO woodcutting( tree, exp ) VALUES( \'$tree\', \'$exp\')\\\";[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

Also, why is your exp field a VARCHAR type? Shouldn't you just use INT?

 

Thirdly, I'll bet it takes a lot more time to work out this script than just manually enter the data either with phpMyAdmin or the MySQL command prompt. :P

305039[/snapback]

 

Thanks Arenium. And yes, it probably does take more time to right out the script, but I do like the extra practice I get writing out in PHP code. Plus I'm learning MySQL now, so it's probably best I learn how to code connectons, etc. with PHP.

 

XC-Runner

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.