Sqlite File Translation Database Fields Data Access Change

I had the totally right idea last time, I just tried to change too much at the same time and messed something up. http://translationstop.com/keta/search.php this time I took it slow; first change Services to Mill, tested, then changed the text showing to Mill Name (and Mill Type, I think that's clearer), tested, then changed the order and the reset code on the bottom. It's all goodly now!

Okay, but remember that, when you make any changes, you need to do more than just look at the page, see if it still looks pretty, and then put your stamp of approval on it. The functionality part is pretty intricate, and you can easily destroy some of it. So you need to test it on many levels, such as the infamous Next button, or punching in some queries into the search boxes and then press Next to see if the questions still remain there properly, and that the Next function still works. I put a lot of effort into getting that to work and it's an important function, even though you might not realise it yet. I have a good idea how databases can be used and how your own users, no matter how computer dense they might be, might eventually want to use your online dbase.

As for our predicament about Cedar becoming Îdar%, that looks very much to me like special characters. I will bet you One Million Dollars that %ce = Î somewhere in html. Not sure what to do about that, we'll figure it out, I have confidence.

I really don't know what it's all about (don't forget I mentioned the "bc" search in the Region search box) but eventually I'll try to tackle that. But I really don't know where to start. Perhaps I'll have to break down and approach some forums and ask them. I can't imagine why the html code would replace that.

I am sending all the mills in the database that have email addresses their listing, so there will probably be 1) a lot of updating, and 2) several orders over the next week or two. Did you say something about my zipping the database and sending it back to you to upload whenever I make changes? I guess my version is Access is older, or maybe you want to make sure I don't mess up our universe completely? hahah

Just zip and send the .mdb file. Mine is new version so it can open the older one no probs, where yours is older so I have to save mine as older for you.

Just make whatever changes you want and then send it. SHIFT+ENTER saves records. CRTL S is for saving the dbase itself, which you shouldn't worry about, unless yo actually make changes to the structure and the file. shift enter is for the data itself.

And no, I certainly did not say send it to me every time you make a piddly change. I said to the CONTRARY. It takes time to convert it for the web, so just do it only once in a while after a bunch of important changes or something. Eventually I'll make the online edit for to spare me this procedure, but that will be a lot of work. Still have tons of my own stuff I need to catch up on. Your dbase is fully functional, so I will put that on the shelf for now. k

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

Hi Yuri,

still not rolling in the dough but the last few months were fairly good and it has given me the opportunity to chip away at some debts. If it keeps up like this, which it may, I will hope to have paid back all my debts within about 7 months.

Concerning your communication script (CS), a friend of mine gave me an idea that I should offer it to other translation companies, which I think I will try. Charge a small fee per every word translated through the system but not actually sell the software itself. For this I'll need to set up special administration rights that can do pretty well everything the administrator can except erase projects and only have access to the projects they set up. I thought I'd try this myself, because I still do not have enough cash, but it may become an interesting revenue stream. Anyway, I was wondering if you had any quick pointers. Such as a quick tip which files I should be working on, how to get mysql working on my computer so that I can test it, and anything else you can think of.

Thanks a lot,

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

Hi Yuri,

thank you for the tip and will try it. I just thought I remember once asking for your help about a certain php file I did not send you but I thought you somehow downloaded it, so I was worried that someone could do the same with your scripts. I was thinking of being extra paranoid and putting as many functions as possible in files located out of the htdocs folders, so out of the web, which my new server allows. Is it better to have many small files as opposed to many functions within one big file? If there are a lot of users, do they all fight over the one big file, in which case the script runs faster if a lot of the functions are broken up into separate files?

I'm really enjoying developing your work further and am learning a lot from how you designed things. Once I set up the subadmin rights for customers I can give you access if you'd like to check out my progress. But boohoo, today I searched the internet and I see there are a few pretty developed systems out there doing the same thing mine does, I think. I will have to download them and try out their demo package. Otherwise, while looking for such software I was shocked to find out one of my pages is second on google for that word search.

What projects are you working on? I'm working on some charity projects too.

If file has php-extension, noone can see the code trying to open the script in browser. and to protect older, you cn create in every such folder the file "index.html" with blank page. So nobody will see files in folders.

> Hi Yuri,

> managed to make some good progress with your script and hopefully

> soon will be

> able to start offering it as a service. One thing I'd like to ask

> you though is if you

> know how I could protect it so that no one could copy them all. I

> could password

> protect some of the folders, but how would I hide other scripts

> which are not in

> the password protected folders?

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

I attached the page I downloaded for SQLite Administrator (http://sqliteadmin.orbmu2k.de/). It's a free program and perhaps still on my FTP in the web.programs if you did not erase it, or possibly in Mismanagement/Backup somewhere.. I use this when converting from Access to SQlite. Fairly easy: with Sqlite Administrator just open the sql database you want to update (Database > Open…). Then go to Data > Import Data. Now select "Clear table before import", otherwise it will append the data and you may end up with a billion duplications. Then press the Open File button.

Oops, but first, open your MS Access dbase. Click on Tables on the left hand side, then right click on the table you want to export and choose Export from the dropdown menu. At the bottom for Type choose Text Files and give the file name a .csv ending. Press Next then choose Delimited, Next choose Semicolon and I think "Include Field names on first row", keep text qualifier as " then Next, Finish (note where it is saved).

Now back in Sqlite Admin, after pressing Open File you choose ; (semicolon, as you selected in Access during export) as separator character, "Yes" for "Does first row contain field names", and for the Field Mapping section, assuming the field names are the same for both the Access and Sqlite dbase (if you are creating any new fields in Access, make sure to do exactly the same in Sqlite first), you can press the little button to the top right of that section and the fields should line up properly. Check that everything is okay and mapped properly. Then just press Import Data, and check to make sure that the data is correct (such as through the Result tab in Sqlite Administrator).

The dbase viewer which comes with the program I already sent you can also import, but I find that with larger data tables such as yours it sucks up all the cpu and can take forever. This only uses the harddrive and is fairly quick.

If it doesn't work, then use your logic and tweak the settings, cause I'm writing these instructions from memory.

Note also that you can edit the data and add fields etc. directly in one of these two sqlite dbase viewers, so you wouldn't even have to work in Access and then export. Originally I set up Access so you could see what things would generally look like online.

Soonish I hope to finish your dbase so you can edit/add stuff directly online. Or do you feel more comfortable doing changes offline in Access (or one of the viewers)?

Have a great blast! –

Okay, so takin a break from translating, which is so much more likely when I smoke some of the good stuff, and gonna do your shit tonight. Didn't get a chance to download the latest version so will leave the Form to you (sure you can figure it out) and hope I've got the latest version of the Search page. If not you can just Compare the two in Word and merge what needs to be merged. I'll leave the previous version on the net with _old on the end.

The software I sent you a billion and 1 years ago you can download from portablewebap.com.

I guess you erased my email I sent with that? I may have written the instructions in a Word file but I can't find it. To run a webpage within it you gotta copy the entire site into localhost folder (on my computer C:\Web\PHP_Portable\Program\www\localhost\), and then put the address like http://127.0.0.1:8001/keta/search.php into your browser. 127.0.0.1 is always the IP address for your own computer and 8001 must be the port number the program chose. The PHP scripts will become functional on your computer.

It comes with a sqlite dbase viewer. You don't install the software but just double click on the programs. Read the readme notes etc.

Either with that viewer or the one I mentioned to you previously you can open and edit the dbase, as I mentioned, so you wouldn't have to even change it in Access and then convert, like I mentioned. It's up to you how you go about it.

If later you change it online directly, you will also have to do it offline, unless you want to abandon making changes offline. The advantage of changing offline is that, for example in Access, you can do global search and replace changes etc. If you make change online you can also just download the dbase and then convert to Access. All pretty interchangeable.

Well off to upgrade! k

ok so I added the two fields to the database, I added them to the bottom of the Fields List, then plopped them in a good spot in Form View. Mill Status Last Updated I just filled a few of them, so this guy tomorrow can see the idea. Can you please add a search parameter to the online version, to include Mill Status ?? this will make my database useful to a huger amount of people. I also edited the Region field to make them consistent (Canada, BC; Canada, NL, etc etc) and I edited the Mill Type fields to be consistent (Mill, Shake & Shingle Mill, Pulp & Paper Mill, Remanufacturer, Wood Preserver, Panel Mill, and Wholesaler, Exporter and Broker). There was one other thing I had to change, in the Table View the first bunch of listings had said "BC Mill?" for the Region field, but they are actually in Alberta. So I changed all those, but everywhere else in your Table View when it switches either Region or Mill Type there is an extra line. So there is not between AB Mills and BC Mills. (dunno if it matters, but just in case for the magical doodley stuff you do). I added two more mills at the end, (I didn't insert them inside the BC Pulp & Paper Mills group as I would have liked because I think you said something before about messing up the order). Then, of course already before, I started updating the existing entries according to what the people have told me. Those are the only changes from what you have. I *think* the amount of time and work for you to upload it this time might be less because you won't have to go back in and make the Region and Mill Type fields consistent. Please Please Please do it within the next 24 hours, it's 1:00 Sunday and I have the meeting at 2:00 Monday. thanky thanky thanks ever so.

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

Exciting times at Keta's database corporation.

How to use this file:

- I would suggest you copy it somewhere else on your computer. Whenever I send you updates to these "webpages", you can Compare Documents in Word to see what changes I've added to this Word file.

- in case you do not know how to Compare Documents in Word, it should be under Tools, possibly Tools > Revisions. It is part of the Revisions tool in Word. You can change the options for colouring, set differently on each Word program/computer. I like to have mine so that crossed out/erased text is bright yellow with a line crossed through it, and new/added text is italic and bright green (my background is always dark green, cause I feel it's kinder on my eyes – perhaps like I have my old laptop set up at your place).

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

The package which you have just unzipped on your computer and in which you found this Instructions.doc file is a fantastic standalone little web php/sqlite application. Obviously for PC.

Throw it into any directory and it should run – even off a CD or memory stick etc. Just fantastic.

At the root of the parent PHP directory you will find a program portablewebap.exe, which launches the little portable server (should show up as a blue ball in your system tray at the bottom right of your screen – which you can shut down when done by right clicking and choose Exit), and then should launch a web browser.

I have only modified the dude's scripts to serve our needs, so obviously there is a lot still in there that we don't need. But it doesn't take up much room, so no pain for now.

On the left hand side in the browser you will find two important links:

- Admin – that will show on the right hand side "SQLite Database Browser". Click on that link to open up the SQLite database. This is not necessary but only for your perusal, if interested. Once the db window is open you can Open Database to view your data in Sqlite format (converted/exported from Access). I didn't want to screw around with the guy's folder structures, so to open your database, you have to go to:
: PHP\Program\www\localhost\examples\sqlite\
Go to this sqlite folder and you will see all the scripts I am using for your webpage.

- Examples – now click on Examples to view your "online" dbase, which on the right side will show the link "Example Program using SQLite".
Obviously I will be changing the navigation over time to get away from his goofy setup to yours, but one step at a time.
Now click on "Keta's dbase" on the left hand side and you should see your "online" database. You can bookmark this page for easy viewing whenever I send you an update (although by some point I'll probably change the entire navigation structure and look and feel).

Whether you are viewing the source of the data through Access or Sqlite, you will see (and probably know) that not all Email and Website records have something in it. But those which do will make the links work on the webpage (such as for example the mill Canadian Forest Products Ltd. (Canfor) - Grande Pr.. will link to its website if you click on the mill name.

Boucher Bros. Lumber Ltd. has a record but it's website seems to be dead.

A.B. Cushing Mills Ltd. has an email address in its record, so if you click on Contact under the Email heading it should activate your email program. ). In the future I'll try to add some fancy code which will maybe popup a windows saying "Sorry, this mill does not have a website", or some nilly willy like that.

This is obviously a first draft and now I see how the guy did it, so its fairly simple to modify from here on in.

At the very top of the page I created a header.html file (found in the sqlite folder mentioned above – coloured bright green), which you can change yourself. If you do make any changes to it send it to me so that I can update my original version. If you don't want to use an html header at all (I created it so that you can change it easily in your Dreamweaver or whatever) you can open the file header.php (in Notepad, but I believe Dreamweaver can open php files no probs) and put // before the line require_once("header.html"); at the top, so that it would read:

//require_once("header.html");

PHP understands anything on ONE line after // as a comment and simply ignores it.

In PHP the special language is always buried within:

<?php

…

where I believe regular html code goes outside of that.

Within the PHP section you can put html code but it has to be in a special way. For example, a line starting with

echo

will produce on the page something in html. So when you open the header.php file you will see how html is incorporated within the PHP section of the PHP file. Not necessary for you but just to give you an idea how it all interconnects.

The favlist.php file is what you are viewing through the browser (your data), the config.php file explains where the sqlite database is, and the rest of the files are his other stuff that I can probably eventually erase.

Note that the Update link on the page does NOT work. I can probably get it to work no problem, but you said you want the page for viewing data only, so I expect I'll eventually change this to display some other field.

So I just sent you this to show you how it could look online (although look and feel and everything else is certainly changeable) to give you an idea, to prove to myself that I can get it working, and so that we can start tweaking from there. Remember that these are all just different ways of looking at the underlying data. The core original data will remain in Access, because I know how to work quickly with that. This then gets converted into sqlite format and viewed through the php scripts for the web. It is simple commands which draw data from the underlying table and fields, so we can make whatever fields you want visible on the web, or only certain records etc. (or organize in ranges, as you previously mentioned), without touching the original data.

Okay, glad to see some more progress! Now to screw around with my own online database so that I can keep learning. Will need instructions from you to keep working on your pages (which fields you want shown, any restrictions, look and feel, sorting, etc. etc.).

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

okay, just got your email on my mobile. So all my work seemed just to entice one moron in becoming a new customer, and who thought there was too much information. Painful. Anyway, like I wrote in another email, I really want to get away from free extra work like this. I'll get you set up so you can do it yourself, finish the babyfication, and think you got a good deal out of it. Hope that seems fair. Otherwise, I'm sure that new users will occasionally be asking for this and that, which means I could be working on this forever.

One thing I noticed is that the postal code is now gone as well. Probably I mentioned this a long time ago and asked you what you want - like if you want to make changes in the Access etc. - and I guess you just let that email blow by without addressing it, and now you'll have to deal with merging the data somehow. I converted my dbase to 97 and zipped it, and will try to upload it, cause its too big to send by email with this dialup. If you send me a large file again, make sure to ask first, cause it may need to be zipped.

I guess you can sort it according to the companies, see if things line up. If they don't then you need to insert record where you added a company, for example. Once you line things up you can copy/paste the postal codes, assuming that is the only difference. I may have done a lot of changes since I sent you the 97 version a long time ago, so you may have to go through it and quick compare. And I hope I wont have to start going through old emails and resending them just because you do not take the time to organise these things.

I'm glad that I worked on it and hopefully will be able to sell my services through it, and that it helped to get me off the island, but I really cant just keep working on it forever. Got tons of projects of my own.

Good luck merging! Will send you the link to download my version once I get it up.

Concerning the global warming idea, he said once that the real down and dirty figures are not on the site and someone would have to figure out all the calculations or something. Will ask and confirm this. He just wants the idea out there to help save the world and doesn't really care about making money off it. He's already got 50 houses across Europe and I learned he used to put together mega projects but sold the company cause he got tired of a suit and having to be places etc. Seems like my kind of guy and I'm wondering if I might learn a lot of things while hanging out with him for a year or two. We'll see.

latero

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

first of all, the newer sqlite thing didn't work for me. It wouldn't import at all. Suspect this is because I didn't have any table or any fields. Toyed with it for a while but really don't know how to work it.

Yah, there were a few quirks I had to struggle around myself. Hard to write by email if I am not there with you. Just gotta use logic. I believe I wrote my instuctions very carefully. Now my mind is not in it so it would take a lot of time to sink back into it. Perhaps you could refer to something specific in my instructions that you do not understand and send that to me by email etc., otherwise the best is just to think like a hacker and figure it out, I'd say, since I feel I was as meticulous and clear with my instructions as I could.

went back to the earlier one (the portableweb thingy). So, now, I guess the info you had filled out was from a very early version of our database. (the folder is dated Feb '09). Everything is lovely, except I am missing some fields. No problemo, started adding fields but OOOPPSS, it adds them to the top! So the question is: how to I reorder the fields?? I was in table view and in other views (can't even remember what now, but it had the fields displayed all in a column with like a + sign at the top and a vertical line coming down). Anyways, I tried right-clicking, hoping for a menu to come up, tried dragging and dropping (that didn't work at all) and a couple of other things.

If you are in MSA you can select a column with your left mouse button, then click it again (at the top, once the whole column is selected black), and DRAG it left or right, releasing it where you want it. But the arrangement of the columns does not necessarily affect the online version.

The info from my .csv file did indeed import (YAY!) and everything is in the right place (right info in the right field) but of course it cuts off when it gets to Comments. So I want to add the rest but the new fields are coming in before MILL ID which of course we do not want. Aren't you proud of me?? haha, probably not. Anyway, just let me know how to add new fields at the END of the existing field list.

In MSA or what? Be careful that this then needs to be duplicated in the online version, so be careful about how you go about all this and always make sure you keep a copy of the originals in case you screw something up so that you can go back, or compare your changes so that you can analyse them and hopefully come up with a solution. This is the basic simple principle I follow and end up understanding everything. I think if you just take the time and mental energy it'll click in your head and everything will then be understandable and easy. Another thing I do is to always write comments into a Word file, once I finally figure out the little trick etc. Cause next time I want to go through the same procedure I often forget what the little trick was and end up going through the entire investigative procedure again.

Sometime in the future when I am more confident I will play with the new sqlite admin software but for now this one is working nicely with less set up time for me so that's OK. I did try to open the portableweb table in the new sqlite software but that didn't work.

Not sure what you are saying here. Keep in mind that the database is like a web page which you can view in different browsers, like firefox, chrome or safari for example. The browsers are like different TVs watching the same TV show. Get this into your head and I think you'll start to understand the entire thing better.

thanks buddy, am quite pleased at the power you have given me to do this myself. I totally can not afford to pay any more for someone else to do it, we are looking to 1Q or 2Q next year before the lumber industry recovers so its still going to be a while before a whole bunch of new customers come along. Am working toward having all this ready and zippy-dee-do-dah before that, which seems to be happening so . . . one step at a time baby.

Yup, and good to start experimenting with this now, before the actual workload comes in. The low times are good for this R&D stuff, and once the jobs start rolling in you want all your capacity free (and efficient and trained!) to gobble up as much cash as possible!!!! :0)