SQL Structure

[XC-Runner]

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()

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

[Arenium]

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.

[ryanlwh]

INSERT is to insert a new record

use UPDATE or REPLACE.

[XC-Runner]

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

[XC-Runner]

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

[Arenium]

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.

[XC-Runner]

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.

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