how to insert this 1

[Wulfgen]

Hi all,

first off I dont know a darn thing about databases - that being said I hope someone can help.

I have inherited a set of files that requires access to a mysql db (and I dont know a darn thing about db, but, am willing to learn)

The db uses this script - below to write tables and records to the db (or supposedly does - I could be wrong) I made the DB OK and have it recognized by the admin script however its looking for records and tables that don't exist... yet...

Is there an easy way to enter all this into the DB? some form of install script?

--
-- Create schema phpstorelocator
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ phpstorelocator;
USE phpstorelocator;

DROP TABLE IF EXISTS `slstate`;
CREATE TABLE `slstate` (
  `stateID` int(10) unsigned NOT NULL auto_increment,
  `statename` varchar(100) default '',
  `recordlastmodified` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordlastmodifiedby` varchar(50) default '',
  `recordcreated` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordcreatedby` varchar(50) default '',
  PRIMARY KEY  (`stateID`)
);


DROP TABLE IF EXISTS `slstore`;
CREATE TABLE `slstore` (
  `storeID` int(10) unsigned NOT NULL auto_increment,
  `companystoreID` varchar(50) default '',
  `storename` varchar(100) default '',
  `address` varchar(100) default '',
  `city` varchar(50) default '',
  `suburb` varchar(50) default '',
  `stateID` int(10) unsigned default '0',
  `country` varchar(50) default '',
  `zippostcode` varchar(20) default '',
  `telephone1` varchar(20) default '',
  `telephone2` varchar(20) default '',
  `fax` varchar(20) default '',
  `email` varchar(100) default '',
  `website` varchar(100) default '',
  `description` varchar(255) default '',
  `openmonday` varchar(20) default '',
  `closemonday` varchar(20) default '',
  `opentuesday` varchar(20) default '',
  `closetuesday` varchar(20) default '',
  `openwednesday` varchar(20) default '',
  `closewednesday` varchar(20) default '',
  `openthursday` varchar(20) default '',
  `closethursday` varchar(20) default '',
  `openfriday` varchar(20) default '',
  `closefriday` varchar(20) default '',
  `opensaturday` varchar(20) default '',
  `closesaturday` varchar(20) default '',
  `opensunday` varchar(20) default '',
  `closesunday` varchar(20) default '',
  `opencloseinfo` varchar(255) default '',
  `storemanager` varchar(50) default '',
  `maplink` varchar(150) default '',
  `directions` varchar(255) default '',
  `recordstatus` varchar(20) default '',
  `recordlastmodified` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordlastmodifiedby` varchar(50) default '',
  `recordcreated` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordcreatedby` varchar(50) default '',
  PRIMARY KEY  (`storeID`)
);


DROP TABLE IF EXISTS `sluser`;
CREATE TABLE `sluser` (
  `userID` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(50) default '',
  `password` varchar(50) default '',
  `userfullname` varchar(50) default '',
  `accesslevel` varchar(20) default '',
  `recordstatus` varchar(20) default '',
  `recordlastmodified` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordlastmodifiedby` varchar(50) default '',
  `recordcreated` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordcreatedby` varchar(50) default '',
  PRIMARY KEY  (`userID`)
);

I have this in the dir, ut when I go to the page it doesnt appear to do anything - what am I doing wrong? Shouldn't there be more to the script than this?

 

[mbrooks]

If you have an account access to the database, on the command-line do

Create a new database

$ mysqladmin -u <user> -p create <database>

Import the database from file

$ mysql -u <user> -p <database> < <filename>

M. Brooks

http://mbrooks.info

 

[Wulfgen]

Err OK.... I would suggest that would work - all i did was go to the cpanel of the hosting area and made a mysql database gave it a name-pwd-id thats the total extent of my sql knowledge.... I feel like a dork!

 

[Wulfgen]

Earlier I found this on the web and was hoping that somehow a script could run like this (this works fine - for one table that is - no records) to enter all the above into the database - you see where I'm going here - its to make a doofusus (me) life easier.....

<?php
// Make a MySQL Connection
mysql_connect("localhost", "usrname", "pwd") or die(mysql_error());
mysql_select_db("phpstorelocator") or die(mysql_error());

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE slstate(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
 name VARCHAR(30), 
 age INT)")
 or die(mysql_error());  

echo "Table Created!";

?>

 

[mbrooks]

Here you go.

<?php
// Make a MySQL Connection
mysql_connect("localhost", "usrname", "pwd") or die(mysql_error());
mysql_select_db("phpstorelocator") or die(mysql_error());

// Create a MySQL tables in the selected database
mysql_query("CREATE TABLE `slstate` (
  `stateID` int(10) unsigned NOT NULL auto_increment,
  `statename` varchar(100) default '',
  `recordlastmodified` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordlastmodifiedby` varchar(50) default '',
  `recordcreated` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordcreatedby` varchar(50) default '',
  PRIMARY KEY  (`stateID`)
)") or die(mysql_error());

mysql_query("CREATE TABLE `slstore` (
  `storeID` int(10) unsigned NOT NULL auto_increment,
  `companystoreID` varchar(50) default '',
  `storename` varchar(100) default '',
  `address` varchar(100) default '',
  `city` varchar(50) default '',
  `suburb` varchar(50) default '',
  `stateID` int(10) unsigned default '0',
  `country` varchar(50) default '',
  `zippostcode` varchar(20) default '',
  `telephone1` varchar(20) default '',
  `telephone2` varchar(20) default '',
  `fax` varchar(20) default '',
  `email` varchar(100) default '',
  `website` varchar(100) default '',
  `description` varchar(255) default '',
  `openmonday` varchar(20) default '',
  `closemonday` varchar(20) default '',
  `opentuesday` varchar(20) default '',
  `closetuesday` varchar(20) default '',
  `openwednesday` varchar(20) default '',
  `closewednesday` varchar(20) default '',
  `openthursday` varchar(20) default '',
  `closethursday` varchar(20) default '',
  `openfriday` varchar(20) default '',
  `closefriday` varchar(20) default '',
  `opensaturday` varchar(20) default '',
  `closesaturday` varchar(20) default '',
  `opensunday` varchar(20) default '',
  `closesunday` varchar(20) default '',
  `opencloseinfo` varchar(255) default '',
  `storemanager` varchar(50) default '',
  `maplink` varchar(150) default '',
  `directions` varchar(255) default '',
  `recordstatus` varchar(20) default '',
  `recordlastmodified` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordlastmodifiedby` varchar(50) default '',
  `recordcreated` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordcreatedby` varchar(50) default '',
  PRIMARY KEY  (`storeID`)
)") or die(mysql_error());

mysql_query("CREATE TABLE `sluser` (
  `userID` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(50) default '',
  `password` varchar(50) default '',
  `userfullname` varchar(50) default '',
  `accesslevel` varchar(20) default '',
  `recordstatus` varchar(20) default '',
  `recordlastmodified` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordlastmodifiedby` varchar(50) default '',
  `recordcreated` timestamp NOT NULL default '0000-00-00 00:00:00',
  `recordcreatedby` varchar(50) default '',
  PRIMARY KEY  (`userID`)
)") or die(mysql_error());

echo "Tables Created!";

?>

M. Brooks

http://mbrooks.info

 

[Wulfgen]

Dang!! -- you made that so easy! now I can get to adding items to the script to actually populate the DB and see if its functioning OK


Thank you so much - I'm going to study the lines you wrote as well to see if i can glean some info into my befuddled brain cells.

Again thank you and a star for you too!

 

[Wulfgen]

Hello mbrooks,

looks like that did it - but I've just discovered that in order to get info into the dab there are a lot of fields to fill in -- how hard would it be to have a tab deliminated or txt file or something with the data as an array to insert into the fields?

That way an excel chart could be generated with the listings and imported into the DB (all of a sudden I'm taking the ultra crash learning course)

 

[guelphdad]

Look in the manual for LOAD DATA INFILE it is the fastest way for you to import data into your tables.

 

[Wulfgen]

Manual?

The very first two sentences of this thread gives you an idea that I'm just starting to paddle in databases.....

 

[johnwm]

... so the first thing you should do is to look in the manual!

I see you have asked 73 questions, but only found 12 of the answers given to be valuable. Check out faq222-2244 which will give guidance on asking better questions, and give some ideas of the basic research you can do before asking your question here.

To save you using google here is the link to guelphdad's excellent idea.

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Wulfgen]

Is this some kind of really nice way to flame me?

Asked 73 questions.... is that over this (entire) site in different areas?? and only found 12 of them helpful... does that also include answers like this one where the (help) is a note telling me to ask better questions...? (Is there ever a perfect question, or better yet a perfect answer?) Or someone saying yes you can do that, but not saying how, or someone simply saying "look in the manual" What kind of help is that. Thats like going to a mechanic with a broken car and hey says did you start it? - duh How much time did you spend gathering that info? and to what end? saying something nasty (in a nice way of course)

If I had a manual - to what?

Please, I may not be the sharpest tack in the box - if I was, why would I be here asking (imperfect questions according to you). This is a help forum, I really dont care if you have had a bad day, or just are tired of people asking dumb questions... if you dont want to assist - then dont -- but why disrespect the people asking questions?

If you get all up and mad about what I have said, remember you stated this tone, not me... i went to the link you posted and you have taken the assumption (literally) that I know what this is - if I did, as I said before, why would I be asking in the first place. Yea I know, people ask question here even if they are knowledgable, true. But I stated my shortcomings from the beginning. Expecting me to be fully conversant in two or so days on this is rather ridiculous...

 

[ingresman]

I don't think John was flaming you. How it works here is people usualy come after they have tried to help them selves fix the issue and come up against a brick wall. You've been posting to the PHP forum since 2005, Im sure you know the score.
Your questions are not imperfect they just show that you havn't done much research. I've just typed "loading data into mysql" into google and got 575,000 answers back, the first of which is load data command. So the self help to get started is there.
You ask the question If I had a manual - to what , well mysql !!. Go to the mysql.com site and have a look around.
You make an analogy with a car mechanic, well if you had painter and decorator as your role rather than technical user I'm sure the expectations would be different.
If I were you I'd get a book on mysql (something a bit better than the dummys range) which you feel comfortable with, look on amazon as a start.
Finaly as group we are always willing to help but you have to help yourself to begin with.

 

[johnwm]

Thank you for the clarification ingresman. There was no flame intended. The FAQ I referred to is a standard FAQ to help people ask better questions, and get better results. The 73 questions are listed in your profile, as is the number of posts marked as valuable. It's a fair bet, given the number of experts in these forums, that if you've only found one good answer for every six questions that you ask, you could do with some help on framing your questions more clearly, and with a bit more background research.

It's reasonable to suppose that any computer professional will have access to the manual for software that he is using, particularly when the manual is available online. My link (as I'm sure you noticed) is to the relevant section of the MySQL manual to solve your problem.

There is an excellent system in these forums to avoid flame wars. It is the Red Flag button below every individual post. This gives you the opportunity to advise site management if you feel that any post is inappropriate in any way. They can and will then take appropriate action, which is why these forums are the best run and most technically helpful to computer professionals.

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Wulfgen]

Yeah youre right John... a google search using that criteria get 895,000 replies

http://www.google.com/search?hl=en&q=loading+data+into+mysql&btnG=Google+Search&aq=f&oq=

and guess what? the same questions I'm asking, over and over - not answers - questions... so you actually have to search page by page - link by link (which I did before I got here as a last effort) would you like a copy of my browser history?? You failed to mention that aspect....just that there were a lot of replies in Google - the same can be said for Ask and Yahoo - which btw bring up virtually the same replies - natch.

Yikes I asked questions over a 5 year period (which you'all were kind enpugh to poinr out) but also failed to notice that the amounts of questions asked during that time have sequentially reduced in number (I guess that means I either learned something or figured it out myself - which in some cases I had to because I ran into some people with "tudes) Also of those questions I did and did not 1) get answers 2) figured it out 3) just used another method - where's your data on that?

Oh for heavens sake guelphdad - the area obviously you are the most adept in is mysql - I'll bet without a doubt when you first started in sql you were'nt so smart - possibly you too asked a question and got ripped over it - (I guess its payback and I'm it)

Like I said you all are being so, so nice - but underneath it all you are saying some not nice things - yes, yes, so what if I don't have a manual - am I expected to run out and buy lotsa books on mysql and access or whatever, because I have a question about entering data into a table? Something I've never had to do before? Oh thats right - I'm supposed to know all about it, so I can ask and informed question - kinda like catch22 to me.

Do you guys know all about media and audio overlays and timeline effects?? How about F/X on movie titles? maybe I can run circles around you there - but I dont think I'm gonna give you hell about it because you dont know.. (in a nice way of course). You have no idea of my history or abilities in any area (as I dont know yours)

I dont think its fair that you can write whatever you want and be opinionated for whatever reason you think you may have - I'm not going to "Red-Flag" anyone... what's the point! I'm just standing up for myself thats all. I suppose I'm off to elance, maybe paying for the answer will be less of a hassle.

 

[ingresman]

This is my last comment on this subject Wulfgen. The manuals are avaiable on line you don't have to buy anything. There are primers and articles avaiable for free. This I would wager is where the vast majority of us learnt our Mysql. On a google search you will get loads of hists you have to read them and make a judgement on the relevance.
As for meda f/x etc... If I was even remotely interested in this I would start on the web and perhaps buy a book or two. I wouldn't wade straight into a help forum. I don't use mysql or PHP professionaly (and never have) but have an interest in dbms and web scripting languages and I also like to help people who help them self.

 

[johnwm]

Wulfgen - my last post as well. I guess you didn't trouble to actually click on the link in my post - it takes you straight to the appropriate page of the manual.

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Wulfgen]

Dang!

what is it are you not getting here - I dont know anything about this at all (as I said from the beginning) so expecting me to simply understand this is nutz

Why send me somewhere where I quite obviously am out of my depth - it may be fine for you (who understands it) to simply say "go here read this and you will be all-knowing" without any base knowledge of the subject its all just greek to me - understand!!!!

So be smug and say I answered you - you didn't -you just made it more confusing for me - which makes me ask even more questions (which naturally would p*** you off, because I didnt ask the right way) As I said before - catch22