 
Customer DB Creation Part 1Credit - Up Front Most of the credit for all of this and everything goes to the Lord. He has given me everything and whenever I need help in this He will usually provide it. I thought that I was finished until I started entering the customers that I have already served. At that time I found that I needed to add a section for parts, labor and total cost. That may sound silly to some, but... this was just going to be a list of customers and it evolved. So at the tail end of section II you will find the addition of data and code for the parts, labor and costs. And I thank the Lord for pointing that out to me on the first entry and not the 20th or greater. Now then, I am not necessarily the brightest bulb on the street but,
on the other hand, I'm not the dullest either. I have based a lot of this on two books written
by Julie C. Meloni: "Teach Yourself PHP, MySQL and Apache" and "PHP Fast & Easy Web Development".
Her site can be found at: Thickbook.com Side Note: JFYI For any and all...
As I have said before in other pages, I write these up as much, if not more, for myself as anyone. Taking notes
in this case has been a big, big help. I "updated" my Linux System -and- this included updates of
MySQL and PHP. My database quit working... well, sorta. I couldn't list out the customers. I could look at them
individually but could not list them all. Puzzeled, I tried some half hearted attempts at correcting the problem.
Due to lack of business (and at the time, interest)
and many, many interruptions I never got around to getting it fixed. Well now I want to and there are more
problems with it. It is cutting off the names at 6 chars and the phone numbers, all parts, at one number!!
Well, in trying to re-learn all this I have found ... through my notes ... that somehow, someway the fields in
the DataBase got shortened!!! Now then, I didn't save a display of the "describe" of all tables but
enough that I could see that the values were supposed to be what I put in. So I need to edit the DB and get all
the tables at the proper sizes. The database discussed here can be found in the Products/DownLoads section on this WebSite. Planning/Designing Ok now, the first thing we want is a list of the items that we want to keep track of. I mean after all, that is what this database is for. So, what is it that we will put in this DB? We'll make a list:
Well, that's all the data that I can think of at this time. There is of course the matter of making all this data related. Mainly cause I don't want to put it all in one table. There will be multiple records. In addition, it was mentioned that there could be more than one kind of an item, like two phone numbers or two email addresses. What if two of our customers live together? Room-mates or something. Now we can have duplicate phone numbers and addresses. Think we should put an ID in each table plus a copy of the main or master ID. Logically speaking, the main record should be the individuals name. So, we will use the key from it as the master_id. Technically, the name and master ID are the only required items in the whole mess. Granted, it doesn't mean a whole lot without the other data but it is still technically not required. To make this flexible, we could just enter the customer's names and then fill in the other areas later. Thinking ahead, we could have a flag in the name record that would get set or cleared when the rest of the info was entered. This flag could cause the name to appear in red when all the clients were listed. Or something like that anyway. Table layouts Special Note: It does make a difference on the order of your table entries and the order of your saves and reads. At least here in MySql. I have had the the tables in one order and the reads in another and had problems. Thought that it didn't make any difference. Must have been when I was using Oracle. Hmmm... At any rate .. be careful and exercise caution. In my 'alterations' I ran into trouble when I didn't have them in the same order. Following somewhat along the line of Julie, we will have the following:
Well, I sorta copied and then added items to this database in comparison to Julie's. As I was laying it out, I wasn't gona put all those dates in. And then thinking (I do that once in a while) that I wasn't going to require all the info at once, I should have some means of knowing when I entered it. Then, further thinking, I also better know when I changed it. Julie is one smart lady. Thanks Julie if you hear about or read this. The Table Creations If your MySQL is not already running, you can start it with: (as root) mysql> mysql> CREATE DATABASE TRcustomers; Query OK, 1 row affected (0.00 sec) mysql> show databases; +-------------+ | Database | +-------------+ | TRcustomers | | mysql | | test | +-------------+ 3 rows in set (0.00 sec) mysql> GRANT ALL ON TRcustomers.* to ctaylor@localhost; Query OK, 0 rows affected (0.00 sec) mysql> use TRcustomers Database changed mysql> The last command above says use the database
that we just created. The table creations will be as follows:
create table master_name (
-> id int not null primary key auto_increment,
-> fname varchar (20),
-> minitial char (2),
-> lname varchar (20),
-> suffix varchar (4),
-> date_entered datetime,
-> date_updated datetime,
-> data_complete enum ('Yes', 'No')
-> );
Each of the following table id's will start at one.
create table address ( -> id int not null primary key auto_increment, -> master_id int not null, -> date_entered datetime, -> date_updated datetime, -> street varchar (30), -> city varchar (20), -> state char (2), -> zipcode varchar (10) -> ); create table telephone ( -> id int not null primary key auto_increment, -> master_id int not null, -> date_entered datetime, -> date_updated datetime, -> tel_area char (3), -> tel_exchange char (3), -> tel_bank char (4), -> tel_extension varchar (5) -> ); create table email ( -> id int not null primary key auto_increment, -> master_id int not null, -> date_entered datetime, -> date_updated datetime, -> email_address varchar (50), -> conn_type varchar (20) -> ); create table computer ( -> id int not null primary key auto_increment, -> master_id int not null, -> date_entered datetime, -> date_updated datetime, -> manufacturer varchar (25), -> model varchar (20), -> processor varchar (10), -> mem_type varchar (10), -> mem_amnt varchar (10), -> OS_name varchar (15), -> CD_type varchar (10), -> DVD_type varchar (10), -> HD_size varchar (10), -> Video_type varchar (20), -> Serial_Number varchar (25), -> System_Number varchar (20), -> notes text -> ); The following 2 tables have been dropped in the name of efficiency. And that other thing ... normalization. They were replaced with a perif_equip table that will cover the other 'stuff'. create table printer ( -> id int not null primary key auto_increment, -> master_id int not null, -> date_entered datetime, -> date_updated datetime, -> manufacturer varchar (25), -> model varchar (20), -> notes text -> ); create table scanner ( -> id int not null primary key auto_increment, -> master_id int not null, -> date_entered datetime, -> date_updated datetime, -> manufacturer varchar (25), -> model varchar (20), -> notes text -> );
create table call_reason (
-> id int not null primary key auto_increment,
-> master_id int not null,
-> date_entered datetime,
-> date_updated datetime,
-> pending enum ('Yes', 'No'),
-> call_date datetime,
-> note text
-> );
create table solution (
-> id int not null primary key auto_increment,
-> master_id int not null,
-> date_entered datetime,
-> date_updated datetime,
-> free_pay enum ('Free', 'Pay'),
-> service_date datetime,
-> note text
-> );
Sorta forgot the directions comments. Course I'm still not sure if I want it. And maybe we'll
put the text in the address table itself. We'll think about it.
create table directions ( -> id int not null primary key auto_increment, -> master_id int not null, -> date_entered datetime, -> date_updated datetime, -> note text -> );Well, I thought about it. Gona add the comment field to the address table. Let's see now, what tables do we have??? mysql> show tables; +-----------------------+ | Tables_in_TRcustomers | +-----------------------+ | address | | call_reason | | computer | | email | | master_name | | perif_equip | | solution | | telephone | +-----------------------+ 8 rows in set (0.00 sec)Allrighty, they are listed in alphabetical order. Let's look at the address table. mysql> describe address; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | master_id | int(11) | | | 0 | | | date_entered | datetime | YES | | NULL | | | date_updated | datetime | YES | | NULL | | | street | varchar(30) | YES | | NULL | | | city | varchar(20) | YES | | NULL | | | state | char(2) | YES | | NULL | | | zipcode | varchar(10) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec)Ok, let's add the notes column/field to this table. mysql> alter table address add notes text; Let's now display the results: mysql> describe address; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | master_id | int(11) | | | 0 | | | date_entered | datetime | YES | | NULL | | | date_updated | datetime | YES | | NULL | | | street | varchar(30) | YES | | NULL | | | city | varchar(20) | YES | | NULL | | | state | char(2) | YES | | NULL | | | zipcode | varchar(10) | YES | | NULL | | | notes | text | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec)Much more efficient to put the directions comment field as part of the address table. Now then, there is a GUI MySQL Manager by EMS that I tried out whilst creating this database. The table creation in the EMS manager is a little to cumbersome. At least in the Linux version. They claim to have a 'Designer' section but that is only in the Windows version. There is to much pointing and clicking from data menus. It was much simpler to just enter the info using mysql. If you are new to this, it may be a help. All of the possible data types that can be entered are presented in the menus. Try it. You get a fully functional version for 30 days. EMS MySQL Manager I ran into a situation problem on the data entry for the 'computer' table.
When entering it via mysql, I entered 'Serial Number' which should have been 'Serial_Number'. I had
a space in the next value also, 'System Number'. When I enter the ');' at the end, mysql complained
about the values and trashed all my entries. I had to enter all the columns again. I tried this
error in MySQL Manager and it let me enter the values with the spaces!! Hmmmmmmm... I previously
had trouble with the 'enum' entries in MySQL Manager now it is allowing invalid field names. Normalization The are some rules that deal with organization in the way your tables are related and for future growth. Two of the tables above are almost duplicates of each other. The printer and scanner. We should be able to use one table for both. This should be a properly constructed DB and not just something thrown together. Even your 'test' ones should take some time... cause they usually become working types. The following table will replace the printer and scanner tables. It is also a more generic one that will handle any other things they have connected to their PC(s). For the most part anyway. create table perif_equip ( -> id int not null primary key auto_increment, -> master_id int not null, -> date_entered datetime, -> date_updated datetime, -> equip_type varchar (25), -> manufacturer varchar (25), -> model varchar (20), -> notes text -> );Alllrighty now. We are finished so we can exit out of mysql. mysql> exit Bye Connecting to the DB from PHP
Once we have MySQL running then we need to open a connection to it. This is done with the command: Notice the above steps involved some PHP variables. These are the items starting with a '$' which, in this case, are the PHP parameters used to connect and select our DB. You will want to set these variables at the beginning of the script or in an external include file. We don't want to use literals cause that makes later changes difficult. INSERT new rows (new data) into the tables First you must obtain the data to input. There are many ways... however, we are going to use PHP forms to gather our data. <?php<form method=\"post\" action=\"$_SERVER[PHP_SELF]\"> First,MI,Last,Suffix <input type="text" name="fname" size="10" maxlength="20"> <input type="text" name="minitial" size="2" maxlength="2"> <input type="text" name="lname" size="15" maxlength="20"> <input type="text" name="suffix" size="2" maxlength="4"> ?> Which will translate into the following on your screen: And after entering the data, we will place it in the DB with the following code:
//add names to master_name table
$add_master = "insert into master_name values ('', '$_POST[fname]',
'$_POST[minitial]', '$_POST[lname]', '$_POST[suffix]', now(),
now(), '$_POST[data_complete]')";
mysql_query($add_master) or die(mysql_error());
Hmmmmm... got this far and got another one of those interruptions. Now all I have to do is remember
what I was doing. Darn. A Little (Big?) SideTrack I mentioned a GUI up above that I was unhappy with. Since then I got to thinking, which I do once in a while, about a GUI database manager that I had used for Oracle. In fact two of them. One was/is called TOAD (Toolkit for Oracle Application Development??) and the other is called TOra (Toolkit for Oracle). TOAD is for Windows and TOra is now for both, Linux and Windows. I seem to remember creating tables using one or both of these programs and it was easier, so to speak, than using the scripts. You just filled in the columns with the proper data and saved it. However, right off I don't see that capability in TOra. So, let's investigate further. First, make sure your 'mysql' is up and running. We have entered table information above via mysql using SQL scripting. But I would like to use some GUI. Ok. Back at it. phpMyAdmin is the way to go I think. Whilst looking at the data in my MySQL DB, TOra called it an Indexed file(Type MyISAM). Which is really what it is. ISAM stands for Indexed Sequential Access Method. However, it appears at this point that I can do more with my MySQL DB via phpMyAdmin than I can with TOra. Which only stands to reason.
Now then, before continuing any farther I believe that what we need to do is plan/design our screens. BEFORE we write the code. The code should fit the screen and not the screen the code. The screen is our operator interface and we want them to enjoy the chores as much as possible. One thing that helps is a subtle soothing screen. Also with proper flow and layout. Now then, the first item will be a menu screen where we can post the choices. Which will be:
TRCustomer Admin SystemAdministrationAs I stated at the beginning, most of this is based on work by Julie C. Meloni. I have made changes to fit my own needs but the original ideas came from her books. If you desire further information you might purchase one or more of her books. Now then, in order to get to this form, at least in my DB, you use the following in
the action field of the form (above). Ok... now we get to the sticky part. I am trying to use ideas from both of Julie's books. The first creates a contact list and the second creates an online address book. So, here is where I/we find out if I/we have learned anything. I liked ideas from both of her books. Problem is... the data entry screen above calls a PHP script that is expecting to write all the info into one table. Unfortunately, mine/ours is multiple tables. Therefore, the add cutomer to database has to change. The main item in the DB is the Customer Name and Address that we have just created above. The other items are more or less superfulous. Without the customer they mean nothing. Now then, as mentioned previously, two or more customers can have the same information. In fact, they can even have the same name. Just look in a phone book and see how many people have the same name. What makes them unique? A number. In the phone book it is their phone number. Here in our DB it is going to be a Master ID number that will automatically increment as we enter our customers. Now just where do we enter this magic number??? The magic number is automatically created by using the auto_increment field attribute in a table at creation. It will start at the value of '1' and increment after each addition. After, not before. If, for some reason, you delete all of the entries in a table, the number will not reset. You must use the following to start at '1' again:
alter table master_name auto_increment=1;
In the example above I used the master_name table. The action will only reset the specified table. If you must
reset them all then you will have to change the table name and re-execute the command... for each.QUERY rows (existing data) in the tables Now then, this can be for two different reasons. One - to just look at the data, and Two - to modify the data. Either way you must make a selection or just list all the entries. Well, listing all of them is not to cool... unless you like sitting in front of you 'puter reading names and stuff... or you are printing out a report. (paperless society??) I went through a lot of trials and tribulations on this. Not having much luck,
I prayed about it. I asked for a better understanding of it all. The Lord granted this request. The stuff
that I was looking at all of a suden became clearer. (Thanks to the Lord God Almighty.) I wanted to be more
selective in the selections. Now, understand this. We are working with 'Client Side' data/code and
with 'Server Side' data/code. The JavaScript function to go with this is:
function launchphpwin()
{
ClientVar = "Clientval=" + document.modcust.client.value;
newwindelay = window.open('pick_modcustomer.php?' + ClientVar ,
'pickmod' , 'width=500,height=560');
if(javascript_version > 1.0)
{
setTimeout('newwindelay.focus();',250);
}
}
Inside the PHP code you will use the $_GET to obtain the results: Now then, pay attention to the details. In this latest resolution of mine, the form and the JavaScript are in the same file. And it is a PHP file. Hmmmmm ... how to explain. Well, I didn't want to just flat out copy Julie's code for one thing. It is good code and it is good for what it was intended ... learning. However, I want to do something a little more than an address book. It was a good place to start since I need all the customer's names and addresses anyway. But, in adapting it to my wants and needs I dumped some of her ways. She used a number of $display_block's. (of which I am still using some) Note: To Myself and anyone that cares. I was looking and researching as to why use these display_blocks. My conclusion is that, it is just another way to enter HTML inside PHP. Anything inside the <?php and ?> tags will get processed by the PHP parser. The output of this is sent to your browser as HTML. When you 'view the source', via the view menu on your browser, all you will see is the resultant HTML. You will never see the PHP code. You can mix HTML and PHP code in a number of ways. These display_blocks are just a way of avoiding the multiple tag blocks. They also allow you to conditionally execute one block of HTML code over another. AND to get access to the variables!! The other flag, is the file extension: .php which says parse this file. Performance can come into play here. There are some who say you can, and they do, setup your page(s) such that the PHP parser will parse all of your .php AND your .html files. Then you can just write your HTML files with a lot of <?php and ?> tags enclosing pieces of PHP throughout your code without worrying about the processing. This will cause your server to parse 'everyone of your HTML files', whether they contain PHP or not. That would be/is a very big waste of resourses. It would make yours ... and others on that server ... slower. Why? The server would be doing more work than neccessary. While it would be needlessly parsing your straight HTML files, everything else is in waiting. Yes, computers do multiple things, but not all at once. It only looks that way. They do multiple tasks in time slices. So, in this case your page would be using two time slices where in reality only one was required. So it will take twice as long to handle your page(s)... so to speak. And it would take longer to get to someone else's pages. Ohhh darn. Went through all the trouble to make the name selective and now when it goes over to the show_modcutomer.php it is missing the 'id'. Thought that I understood this but I guess not. So now we need to investigate this problem. Alllrighty now. I asked the Lord to open my eyes so that I could find the problem.
He did.
The original copy, for some reason, had spaces inserted BEFORE the \" on a number
of the items. Incorrect spacing is a killer!! Example: Another Note: I have taken to lower casing all the HTML tags. For some reason, I think possibly for reading clearification, a number if not all the HTML tags were upper case. I changed them to lower case. This, in itself, can cause problems. So, exercise caution on case changes. I have noticed the greatest 'difficulty' with the POST, GET and SESSION vars. This is possibly cause of multiple files and their interaction. And right now I am not going to change all of them. If I have said this before ... about the tag case ... it is worth repeating. Just incase anyone, besides me, is readin this, I added another table. It is called secphone and
is a copy of the telephone table with all the names changed from tel_xxx to sec_xxx. Reason?? I didn't
want to break out into a separate procedure for entering telephone numbers. The variables need different names. I'll leave
it at that and let you think about it. (Or maybe I should some more.. Ok now. The selection process has been 'debugged', and now we can get to displaying and changing the data. The first item is to read the DB and display what is there, or not, and then make the changes/additions as necessary. (time out... cat is hungry) We can't just go read the DB and have all that we want. Because we have multiple tables we must do multiple reads. Makes sense. All that we passed to the show mod routine was/is the 'id' ... the 'master_id' to be exact. With this we will get all the tables that contain it. That is their relationship. The Java Window - Sizing Alllllrighty now. We can:
Highlighting Forms Ooooopss ... more sharing: I found this little jewel whilst searching for other items. Usually the way. I'm looking for one thing
and find something else that is interesting.
<SCRIPT>
clr=new Array('yellow','white','silver');
function highlight(state) {
element=event.srcElement;
if (element.tagName=='INPUT') {
etype=element.type;
if ((etype=='submit' || etype=='reset') && state==1) state=2;
element.style.backgroundColor=clr[state];
element.focus();
}
}
</SCRIPT>
<FORM NAME="mainform" onmouseover="highlight(0);"
onmouseout="highlight(1);">
Ok - Back at it... Now then, we are going from an HTML form to JavaScript to PHP. The parameters are normally passed from the forms to PHP via Global Vars POST or GET. But they are not passed that way to JavaScript. They are passed via the document vars. Ok, this is turning into a JavaScript explanation. For further info on this jump to ta ta la laaaa.... My JavaScript Page. Bummmerr... A spelling error in an element of the telephone table. The table has 'extention' and it
is supposed to be 'extension'. Hmmmmm... I'll have to modify the table. Drop the item in error and add a new one
that is (hopefully) spelled correctly. The SQL commands to accomplish this are: Allrighty now. With the spelling error fixed, I was able to Pick a customer to modify and bring up their record via the new way of launching. Welllll, not really new but ... ummmm ... better. Anyway, the customer record was obtained and viewed and then after the addition of an onreset event, I was able to close the window with no changes to the DB. Now to get the changes/modifications update script going. Bummmerr, Bummmerr... I want to call the JavaScript windowing routine again, only this time there is more than one arg that needs to be passed. Not good. Ok now, let's re-think this. For this particular operation do I need to open a new window. I don't think so. We are going from modification to updating the DB. Info to be displayed will be the same. So, we can use the same window here. Instead of using the JavaScript call, just call the PHP routine. Yahooo... Whooopee. Ok now, we can:
Deleting a Customer Someday we may need to remove a customer from our DB. There are many reasons so it will suffice to say we need to remove one. This will essentially be the same as the modify only in this case the mod will be complete removal. Allllrighty... the ModifyCust.php has been changed to allow Updates or Deletes of customers in our DB. Haven't put the 'required' "Are you sure?" in there yet but that will be next. Since the choices are on the same form we want to make sure that they hit the correct botton. Are you sure? Ok, we are gona make a call to a Javascript function to ask the question... maybe. Gona have to re-think some of this. My main reason for using JavaScript in this application was/is to open smaller windows. However, it appears that I can not easily accomplish this. The GET/POST information from the forms is lost in the trip from Forms to JavaScript to PHP. So, it appears that I may need to open a window of the correct size for the whole transaction at the beginning. It may be bigger than needed at first but we will need to reuse it cause of the variables. Darn. Done???
HooooBoy.... Not done!! Updating... One of the pieces of information that we are looking for when we do an update is ... ta-tatata-tahhh ... the master_id. Now then, when entering the INITIAL, in other words the first, entry for a customer, IF we did not enter a phone number then there will NOT be a record with a master_id for this customer. Soooo, in addition to checking for the phone number we also need to check for the master_id. Shhheeeeze... Ok. Added a kludge for the master_id to the kludge for the date_entered and the date_updated. So I have something to check and if the master_id for an item is not there, then we do an insert instead of an update. The continuation will be in another WebPage. This will be for the Customer's PC info and for the calls and solutions. Oh yes, we need
a selection that will list all the customers ... to help us remember.
|
)
|
|
|