Computer Tips Data Database Search

uh, yeah. no idea basically. just sent an email to Dave asking about the mySQL thing and also how to do passwords.

Hopefully we wont go the mysql route, the more I think about. Need to learn more before I develop strategy, as I wrote.

this reminds me of a friend of mine a long time ago who was doing the web design for something asking another friend, who was hosting the server, "I'm getting an error message, apparently there are squirrels in my socks drawer"

I don't understand that, but it sounds funny. But one thing I've learned from my programming learning now clarifies for me what all those hackers have been joking about. When I download pirated programs its often funny to read the info.txt or whatever as prepared by these hackers. They often go to great pains to draw something nice using various characters in a text only file. And then talk about their philosophy a bit, and "fck the rich" and all that. Usually quite funny reading. But often they'll say something like "Conquer or die!" Always "or die", and never really got that, but now I see that in php and possibly other script languages, the "or die" is a command which you should always use, like

function (eat soup) or die (print "you're fat, go on a diet")

where the "or die" is called up if the first function fails, for some reason.

Well, that's your php lesson for today. Can't tell you too many of my secrets!

hahaha, oh man. the hilarity. -Keta PS: getting quotes for the database job will have to wait until Monday, the shit has literally hit the fan in forestry this week, I have two things to write today and have no idea what I'm gonna say. It's almost 11:00 in the morning and I am vaguely freaking out.

probably just boring tech talk to you, but I just cant help but get a giggle out of something like this. In any case, it's going to be a long learning curve anyway, and think you don't quite grasp the time it would have taken you to get something like this operating.  SQLite does not need to be "installed" before it is used. There is no "setup" procedure. There is no server process that needs to be started, stopped, or configured. There is no need for an administrator to create a new database instance or assign access permissions to users. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after a system crash or power failure. There is nothing to troubleshoot. SQLite just works.

-------------------------------------------------

okay, here's my latest strategy. By the way, I just ran into a zadrhelicek. Know what that word is? Great Czech word. But I know how to resolve that and it will be a project for tomorrow.

Anyway, so the plan is to develop it first offline in MSA (Access), so we can play around with it and get ideas etc. You can have your powerful offline version and send people packaged data that they can't get from the online version, since you're so scared about someone scoffing it. Once the offline one is set up, which should be fairly quick, I can start working on the online thing. That will require the learning curve, but I already figured so far that I can just export the data from the tables of the offline version into .csv files, which Sqlite can import and construct it's own database engine, which the php scripts can then work with. That'll certainly be exciting.

If you want to know what the little zadrhelicek is (you know of seznam.cz's dictionary? I use it for my translation work all the time), because my harddrive got fried in London, the dude fixing it installed XP pro on it, but he didn't install IIS (Window's Internet Information Service). I need that to install PHP and all that stuff on my computer, in effect creating a functional web engine on my computer. Another zadrhelicek is that all my CD drives are broken, but I figure I can just go to the internet cafe, bring my copy of XP pro, copy it to my computer, and I assume that his installed XP pro will allow me to install IIS from my install version of XP. Then it will be exciting to get my little virtual web thing going and start to screw around.

So, when will you be able to send me your data files? You know, you could even just send me the basic structure, like the first 100 records or something of every table, so that I can begin to construct the database. Once you're done I would only have to import the updated data into the already existing database. I do not need to wait until you finalise the data before commencing. Over time I'm sure you'll be updating and importing data all the time. I just need the basic fields and concept.

----------------------------------------------

dude you are on FIRE! at this rate we will have this done in two weeks. yeeee haaaa!

heh heh, yah, I'm on fire, cause I've been wanting to move towards something more intelligent than translate about shit or endlessly pound trees into the ground for the past who knows how long. I've even found project management a tedious bore. So I'm glad to exercise my mind, learn something new, and hopefully move towards this direction, and other more intelligent directions in the future.

But I think you're being a bit hopeful about two weeks, or how long its going to take me. It took me about two weeks to get a grasp of msa and get my database accounting thing going, and msa is like Word - a file menu, insert file, insert picture, resize inserted object with mouse... PHP is brutal programming code with no mouse at all. You have to write a file with commands, and if you forget a single character, or the logic of your program reasoning is faulty, or a path name is wrong etc., you'll simply get a "syntax error", and then you have to rip everything open and analyse it to try and figure out where your error is. This is a whole new challenge. And this doesn't even include trying to get the scripts to work with a database engine like mysql. That is a separate object with its own finicky elements, and getting php to talk streamlessly with mysql can be like mediating between two arguing lovers who don't want to talk to each other. The more I think about it the more I've decided I will abandon mysql, cause its so much more finicky and complicated, and unnecessary for your little database, and go with sqlite, so you don't need your webdude to set anything up. Last night I was screwing around with sqlite and successfully wrote my first sql commands. sql is a language of its own, so you might write something like:

select source,target from lang
where target like 'uz%';

That was my last command I was playing with, and basically means: take info from the columns Source and Target, found in the table Lang, from records where the column Target contain "uz" plus any string of characters after uz.

This command spit out all my translator records from English to Uzbek, and Turkish to Uzbek, etc. This "command" spit out results. PHP would be issuing such commands TO the database. The above command would be embedded in the PHP script as it is "talking to" the database. I find it all fascinating, but just wanted to give you an idea all that I have to learn. I find two weeks rather optimistic, but if I had no other work, you had already sent me your data file, and worked on this full time, hey, maybe. Right I'm quite in the dark on a lot of things, but I'm shooting for the Feb delivery. We'll see.

Just for fun, I'll copy here the first 39 out of 127 lines of my absolutely simple email contact form found at

http://001yourtranslationservice.com/contact.php

-----------------------------------------------

<?
$headerpage="kenax_contact_us_frames.html";
$botpage="kenax_contact_us_bottom.html";
$emailhome="[email protected]";
include("ip.php");
$ips=file("ip.php");
$isspammer=0;
foreach($ips as $ipone)
           {
           if($REMOTE_ADDR==trim($ipone))
                       $isspammer=1;
           }
if(!empty($_POST))
           {
           if(empty($_POST[email]))
                       {
                       foreach($_POST as $key=>$val)
                                  {
                                  ${$key}=$val;
                                  }
                       foreach($_SERVER as $key=>$val)
                                  {
                                  ${$key}=$val;
                                  }
                       $messagebody=
                       $message."\n
                       -----------------------\n
                       FROM:".$another.", ".$name."\n
                       -----------------------\n
                       IP:".$REMOTE_ADDR;
                       if($isspammer!=1)
                                  {
                                  if(!empty($emailhome)&&!empty($subject)&&
                                             !empty($messagebody))
                                  if(mail($emailhome, $subject, $messagebody,
                                  "From: $name <contact@$SERVER_NAME>\r\n"
                                  ."Reply-To: ".$another."\r\n"))
                                  echo "Your message was successfully sent!<br>";

---------------------------------------------

This is kindergarden php and the most basic level. It doesn't even attach files, for example, but it does have an anti spam feature in the script above.

It refers to two .html files, so if you go to the site, what you see are two files, and the 127 lines of php only refer to the middle section where you punch in your email data. The four little boxes. That's it dude. 127 lines for that, and leave any character out of the above or have the logic wrong, you get a "server error" and you just stare at it trying to figure out what to do.

Yah, certainly would like to upgrade my computer. Was possibly considering a mac, if you can believe it. Apparently the latest ones run on unix, and can launch Windows or mac, which I find quite intelligent. I'd use the mac part for graphics and editing flicks, and the pc for work. My laptop is good, and the six batteries I bought for it was certainly a wise decision, and its light and small, but its pretty crappy at graphics stuff. Certainly would love to buy some more toys around here. Especially for a backup, cause without this laptop I'm simply DEAD.

Well, off to finish the translation. Got 10 pages, but fortunately my translation memory already translated half of it, so it should go fairly quickly.

Lookin forward to your file! I have a feeling I'll see how I could have done all your work in four hours as opposed to two weeks, but that's just part of the trick of working with these databases and you get to have some experience of your own. Been down that road a gjillion times. latergator

 got a reply from Dave that there is indeed mySQL on the server, he said . . . "We will set-up the MySql database for you. Will send the link and login info when done." whatever that means. I was thinking about that too, sending you along a truncated version of the data, you probably have some super secret way to file it all into the database that wouldn't take several long evenings of boring inserting tabs to accomodate fields not filled out. I probably won't be able to do that today though coz I'm under a deadline (got all my writing done by 8:30 last night, so that's something). Will send tomorrow morning. super suck about the computer problems . .. if this all goes down how I think it will soon you will have so much work you will not only be on Thassos but you will be able to upgrade/regrade/install or even buy new whatever you need to have a well-running machine! I gotta get Disk Warrior for my mac, been noticing little glitchy things which I know if I just let it go will eventually involve paying someone to initialize and reinstall which I dont' want because I dont' have the liscense to like half the software I am using *cough cough*

-----------------------------------------------

I like the idea of us working it out offline first, when you're ready, then putting up a test and sending select people on different levels of crappy computers to make sure they can use it.

I believe php is cross platform (mac, linux, windows etc.), and mostly cross browser as well, so if it works on your computer, it's definitely going to work on others and no need to test. Last night I modified the guy's script and got my own thing working in seconds. Bloody easy. I'm sure there are a kzillion sites where programmers uploaded their own codes and snippets, which I can just use and modify myself, so I can now see how I'm gonna start doing very interesting things for my own website. It's all open source. I like what sqlite wrote about its copyright:

The SQLite source code contains no license since it is not governed by copyright. Instead of a license, the SQLite source code offers a blessing:

May you do good and not evil
May you find forgiveness for yourself and forgive others
May you share freely, never taking more than you give.

Read recently that google's revenues, pretty well all advertising, was 26 billion a year, slowly catching up to Microsoft's 42 billion. But ms's revenues are mostly from Windows and Office, for which it is increasingly possible to get free replacements. Look at google docs for example. Google is even entering the mobile phone market with its open source. The future is in open source and its so cool, and think ms's revenues will start to corrode away. It itself admits it fell asleep on the internet search thing and missed that train. Looking forward to making some real interesting projects, and for charity, and make money in different ways now. With a domain costing only 30 bucks for 3 years and I have pretty well unlimited space on my server, I should be able to make enough revenue from my websites alone, especially if I only need 10 bucks a day to survive. Will be interested to see what fruitions out of this new sphere I've entered.

Apparently the latest ones run on unix, and can launch Windows or mac, which I find quite intelligent. I'd use the mac part for graphics and editing flicks, and the pc for work. yeah the tech guy for the biologist's website (old style computer geek), who is rabidly anti-Microsoft and refuses to use any apps or add-ons that are microsoft based (quite a challenge when you're putting up an image and media heavy website which is basically a blog that anyone anywhere with a password is supposed to be able to update, but that's another story for another day),

I don't think blogs have anything to do with ms. The only ms thing would be active-x, which is a browser based script. I prefer Firefox and rarely have to resort to using ie. I don't trust ie's security. By the way, I've stumbled on some super duper pooper security tricks for windows security if you'd like. Basically locks it down and prevents any virus from getting in. The viruses these days are getting lethal and no software is good enough anymore. You need to "lock down" your windows.

he uses linux only, builds his own computers, . . . you know, that type of geek. Anyways he came here to make sure I knew what the hell I am doing when I first started doing that website and praised my mac "it's 90% linux" and even found some open source stuff that I didn't know I had down loaded (when I downloaded free Open Office because I freaking refuse to pay Microsoft for Excel and Word) which allowed him to talk directly to his server in NY.

Do you have msa installed on your pc? I tried opening a file in openoffice and it didn't work. I have office 7 you can download from my site (same password as for movies) if you like. Or do you have office for mac?

I think Mac is really useful for high end finicky stuff that Windows causes a pain in the ass, unless you're willing to escape to dos (which I saw you do on the little laptop you left here).

Every windows has dos, as windows is built on dos. Before windows, there was only dos. Mac came along and apparently its hardware is like the mouse and graphics thing, which is why it is stable for that. Along comes billy and, as a silent investor, buys the rights to dos for 50,000 bucks from some geekball in a back office, imitates mac, opens up the platform for third party software developers, and the rest is history. What you probably saw on my dinky comp was not dos but shell-lite or something like that, which replaces Window's shell (the start menu, desktop icons, right clicking with your mouse, and pretty well what all the programs run on). It's a little trick I learned how to squeeze more out of a weak computer with low ram. Window's shell sucks up a lot more ram and tends to crash. Switching to this shell I really got a lot more out of my old laptop, which had only 163 ram, pentium 1.

Having the right tool totally makes your life easier. There's supposed to be some new Mac shit coming out but nobody really knows what is happening with developments at the company now that Steve Jobs is quite sick. Stocks are dicey.

I'd probably be happy buying an used one at a fraction of the cost. But we'll see once and when I do have the cash. I don't like window shopping or drooling when I don't have the cash. I'm sure Mac will stay around for a while still.

Lookin forward to your file! I have a feeling I'll see how I could have done all your work in four hours as opposed to two weeks, but that's just part of the trick of working with these databases and you get to have some experience of your own. Been down that road a gjillion times. latergator

totally have also been there mega-times, for different things! I don't get frustrated though when I come across the 'easy' way, take it as a learning experience. Once I go through that process the knowledge is firmly hammered in my brain! Its kind of a good way to learn, sort of. File coming later today, we have a rare blue sky clear day so want to enjoy the outside for now. supposed to rainrainrain for the next like two weeks.

starting to warm up here, finally. Been raining every day for the past week or something. Probably because I'm in the interior, in Nic's. But I need the fast internet from the Turkish side, otherwise I'd be on the beach, as expected. But every day is longer and getting nicer. Think I'll aim to get off this island end of February, and have a nice and slow meandering drive up along the Greek coast. Hopefully the weather will be okay once I get to Thassos. REally looking forward to that drive.

Now to check my mail and hope I see your file!

-------------------------------

As I said I think to best provide a range for the categories showing volume or sizes, since that is less cumbersome than putting the precise number. So for example . . .. instead of Production: 265 Mfbm/8 hr shift, 135 MMfbm/yr

yah, I think we should definitely talk about this some more, cause I can only imagine the amount of time you are wasting.

For example, lets take the following:

100 Mile Wood Products
Box 1646, 100 Mile House, BC V0K 2E0
Phone: 250-395-4544
Fax: 250-395-4111
President: Jack Kreschuk

I would approach this with the following strategy. I would search and replace using formatting, so that the name of the company at the top would become the first field in each record. You want things organised in Excel (which will get easily converted into any other database format) such that all the data for each mill is on a single line/record in a table.

Then I would search and replace to convert the bold, smaller font text to become field header. From the pdf file you sent me it already looks organised enough, if I use this search and replace of formatting tactic. There is no reason to get paranoid about search and replacing this way, because none of the data is changing. You are more likely to accidentally screw something up through your endless manual piddling.

So the above data would be converted to:

I accomplished this by simply converting the above text to table in Word, using the : character as the division thing. I then copied that into Excel, copied it in Excel, then Paste Special back in Excel, selecting the Transpose button, which converted it from Word's structured format:

The "Mill" and "Address" I added manually.

Once in the Transposed format in Excel, it would only be a matter of lining up the fields properly so that all the Address fields etc. are in the same column. I'd probably figure out some way to do that. So I really do think I could have done this all in a few hours. Who knows, maybe half an hour.

Now something very important, why on EARTH are you converting the data into ranges? The more you piddle without consulting with me the more you might be screwing things up, lest totally wasting your time. The ranges can easily be dealt with later in the search query. I believe exact information is very valuable, so why on earth convert to ranges, manually??? You are altering the data and its very bad.

For example, using a search query, it is very easy to organise data into ranges. It would spit out a results table, which you could broadcast on the net, throwing all mills which fit into a particular range into one field, and other ranges in another field. This is all easily achieved using search queries, based on the underlying table. Do not fiddle with the original data manually. A ridiculous big no no in the database world and you shouldn't even be touching it. Slap your wrist now! Just convert the damn raw data into something workable, or let me do it myself. Honestly. I want to get off this island end of February, not wait until then to watch you destroy the data. I just need the raw data in the Transposed format I mentioned, and I'll take care of the rest. What you make available for viewing on the net is an ENTIRELY different matter. You can only release 1% of your data, in any format and arrangement you like. No one will be able to hack in to get the original table files (security is another thing I will be learning).

Stop wasting your time!!!!!!

-----------------------------------

okay, coolness. I'll definitely make it editable for you, and you can easily copy paste data from Excel into the individual tables. My strategy is first to do it offline, make it as robust as possible, then you could undestand it better, and then tell me what parts of it you want online. The online part can look totally different than the offline.

Off to work!!

The manual conversion to ranges is preposterously ridiculous, and I can only imagine what the heck you are doing.  Ok OK Ok, the manual conversion thing to ranges I am not doing, I didn't do, I won't do. I was just asking is all. the updating of info is vital because it has to be done anyway. so, to my mind its better to start with as recent a data as possible than to have you build the database THEN I have to go in and edit everything. This is the actual information contained in the listings I am talking about. Most of the updating is done, except the Panel Mills, Remans and Wholesalers. You can work on the stuff I have already updated in the meantime. Unless you are saying, perhaps I didn't understand you, that you don't want to start building the database until you have the entire lists (not sure why that would be). The database has to be editable anyways, I have to be able to add new mills or delete ones that close, just as I have to be able to change the mill name or number or employees, or contact names and such. So I think you can get working on what I send you today, 85% of the whole thing, then plop in the Panel Mills, Remans and Wholesalers in a week. No? Its not about a security issue that I think someone will steal it, I wanted to be clear with you yesterday about what I am sending, how I send it and what you do with it. I'm not trying to step on your toes, I think I always said last week that I would send you this stuff over this weekend, I don't think I'm behind in my schedule that I said. So it's coming today, but - again - there's a bit more that I still have to contact the mills and confirm the info, so another smallish chunk will come next weekend. Its February 1st today so we're totally on schedule!

-----------------------------------------------

okay, here's what I've done so far. Can you zip or unzip? rtf files can get pretty bloated.

You will see what I've done up to by doing a search for the <<< character string. I saved up to that point in a separate file and will work on converting that, and you can work on the rest. The boring tedious part that you said you'd do.

This is what needs to be done:

- I would suggest go through it in two sweeps (unless I think of additional necessary sweeps), because whenever I did stuff like this I found it faster to focus on only one chore at a time, rather than on several. But if you happen to see a chore 2 job while doing a chore 1 job, no harm in doing the 2 job, since you're there. But I'd suggest keep focusing on the 1 job, lest you forget it and overlook a job. But if you do overlook I guess I can catch it at the end.

- job 1 - go through all the job types (President, General Manager etc.) and copy the exact names into the square brackets as I have at the top of the file. I will search and replace it later so that it falls within the Contact Persons field, so it has to be EXACT. Separate each of them by a comma. You can split up your Word file into two pains, keeping the top part of the file visible while you scroll through the rest of the file (or just copy that section to a new file).

- later I will search and replace the address section so that it is preceeded by "Address:"

- job 2 - in Word you can go to Tools > Options > View tab > Formatting marks > select "Paragraph marks". I always use this feature. You will then see a backward P wherever ENTER was pressed on the keyboard. You need to manually remove whever the field data is "broken".

For example:

Species: Aspen 50%, White Spruce 40%,
Jack Pine 10%
Products: dimension, rough lumber, pallet
stock

The above is on four lines, when it should be on two. If you set up your Word as I said, you will see a backward P after "40%," and "pallet".

These all need to be deleted so that the record is in one line. To help you with this, you can create a Macro whereby you select something, and then go Search and Replace, replacing ^p with a space, and then double space with a single space. When it asks you if you want to search the rest of the document (outside the selection), you always say no (or press Escape on the keyboard).

Tell me if there should be more fields, or if some fields should be combined, such as the Contact Persons or Phone (which will incorporate Sales Phone) and Fax (to incorporate Sales Fax).

So basically job 3, which you can do at the same time as job 1, is to analyse if any fields need to be incorporated into another one, and put the field to be incorporated in [square brackets] as I have done.

So you see, still a lot of work to do, but at least I have an idea what needs to be done, and I'll start working on the section I've taken for myself, and build the database from that. The rest can be imported later, once it's in the correct format. I'll send you the db once I have it done, hopefully today. Or at least what I've accomplished so far and you can start to give ideas how to develop it further.

latero!

----------------------------------------------

I'll be sending you instructions as ideas come to me.

Just ran into the following:

Shipping: truck
Employees: 50
Grading Agency: AFPA Mill No. 032
Millar Western Forest Products Ltd.
Whitecourt Wood Products Division
5004 52 Street, Whitecourt, AB T7S 1N2
Phone: 780-778-2221

whereby I had to add the company name above each division, in which case this company will have several separate records. Later it will have to be on the same line, such as:

Millar Western Forest Products Ltd. - Whitecourt Wood Products Division

Best if you could do that, but make sure that the formatting will be the same as applied to mill names, because I will search and replace that formatting such that it follows by an ENTER and then "Address:", to create the field name for the next line.

In Word you can copy (ctrl shift C) and paste (ctrl shift V) formatting - or you can assign these keyboard shortcut keys through Tools > Customise. Or you can create a Style and always choose that style with your mouse or something.

Therefore, once correctly converted, the above should look like:

Shipping: truck
Employees: 50
Grading Agency: AFPA Mill No. 032
Millar Western Forest Products Ltd. - Whitecourt Wood Products Division
5004 52 Street, Whitecourt, AB T7S 1N2
Phone: 780-778-2221

Oops, just noticed that Mill No. is bold. Is that supposed to be a new field? All fields must start on a new line.

I'm guessing I'll have to ask you to convert each of these into a separate table, using the : as the delimiter, and then to transpose each of these into Excel, as I previously explained. I'll let you know once I'm done with my part. Still a lot of prep work to do, unfortunately, but at least I'm getting it set up.

--------------------------------------------

but I think the February deadline will be kept so that's what matters.

cool and glad you're happy. Although I'm a little disappointed because I thought you might tell me great tails of narrowly escaping death by drowning in your own salivation.

For the prepwork, I would suggest the following: focus on the most important data (BC P&P or whatever), pick some section you can handle, and get it finalised in chunks. The bigger the chunks the better for you because that would save you certain global search and replace procedures, but as you are learning, there is still lots of manual steps to go through. Once you get a section done, send it to me and I'll process it. In the meantime I'll be developing the stuff, which is a separate process from the raw data preparation, so worst case scenario, you might only have half the data on the web by some date, but it will be functional, and ready for more imports as soon as you get some more prep work done.

If you will be busy and making tons of cash, and are in a rush for this, another alternative is I look for some cheapo Ukrainian dude and charge you between 3 and 5 bucks an hour for such grunt work. But it would have to be enough work to make the farmout worthwhile, cause I've been too poor to farm out work for a long time and therefore would have to hunt down a cheapo.