Customer DB Creation Part 1

Credit - 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
This is not all that well organized and a lot of it is ... well, as I think of it ... or ... as/if I even think about it. If you prefer a more organized approach then I suggest that you purchase one of Julie's books.

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.
Taking notes can be very benificial...

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:

  • An IDentifier. All DB's require some type of main identifier or key to access the records. This will be a relational DB and the identifier will be useful in maintaining our relationships. What relationships? Well, we are gona have more than one table and we need to make sure we access the correct info in each table. Therefore we need some kind of relationship.
    Fields: One; an auto incrementing integer.
  • Customer name. This will be two fields. One for first name and one for last. Now then, we may really want 4 fields. Why? Well, some people like to use their full name which will include a middle initial and possibly a Jr, Sr or II etc. I myself am a Jr and I usually include my middle initial. So, that settles it. We will have 4 fields, with the middle initial and the name suffix both optional.
    Fields: Four; fname, minitial, lname, suffix
  • Address. This will contain the usual information. Street and number, City, State and Zip code. Might want a comment field for any special directions???
    Fields: Five; Street/number, City, State, Zip, Comment field
  • Telephone. Here we will need to handle 10 digits and possibly two dashes. And an extra four for a possible extension. And then there is the question of how many phone numbers for this person do we want. There is the possibility of 4: Home, Work, Cell and Fax. I would say a 15 digit field for each would cover all four plus a 5 digit field for extensions. Oooops.. need room for the dashes 17 and maybe an x for extension? So the field is possibly 18 characters in length.
    Fields: Four; Area Code, Exchange, Bank Number, Extension
  • Email. Ah yes in this modern age we all, or at least most of us, have an email address. Now then, some of these can be quite long. And some of us have more than one. What's a good length? How's about 50? That should fit all the personal emails that I know of. We'll also include their connection type here.
    Fields: Two; email address, Internet connection type. (slow, DSL, Cable ...etc)
  • Dates. One date would be nice, the date of first contact. Another that might be useful would be the last contact date. Now we must be careful on these dates. A proper format is needed in case we want to sort on a date field. I do believe that the format of month, day, year is the best. There will be a two digit month, a two digit day and we'll have a 4 digit year... just because. In the database we will have the leading 0's on the months and days. They can be eliminated in the displays if so desired.
    Fields: One; date ( Think this should be two. First then last contact. Well, as I dig into it more, the contact dates are in the "Service Call Records". After all, that is what this is all really for.)
  • The 'Puter. Now here there can be lot of info. The Manufacturer, the model number, Processor, amount of Memory, Operating System, CD, DVD, HD info and Video. All except the Manufacturer and model number can be optional.
    Fields: Eleven; Mftr, Model, Processor, Memory, OS, CD, DVD, HD, Video, Serial Number, System number, Comments
  • Printer. Manufacturer, model, comments
  • Scanner. Manufacturer, model, comments
  • Reason. Of course we need the reason for the call.
  • Solution. And it would be nice to know what was done.

    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:

Table Name Field Names
master_name id, fname, minitial, lname, suffix, date_entered, date_updated, data_complete
address id, master_id, date_entered, date_updated, street, city, state, zipcode
directions id, master_id, date_entered, date_updated, note
telephone id, master_id, date_entered, date_updated, tel_area, tel_exchange, tel_bank, tel_extension
email id, master_id, date_entered, date_updated, email_address, conn_type
computer id, master_id, date_entered, date_updated, manufacturer, model, processor, mem_type, mem_amnt, OS_name, CD_type, DVD_type, HD_size, Video_type, Serial Number, System number, notes
printer id, master_id, date_entered, date_updated, manufacturer, model, notes
scanner id, master_id, date_entered, date_updated, manufacturer, model, notes
call_reason id, master_id, date_entered, date_updated, pending, call_date, notes
solution id, master_id, free_pay, service_date, date_updated, notes

    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)
      mysqld_safe --user=mysql &
([11 Apr 20XI]   I once again am setting things up -and- the MySql DB would not start. Had to create a mysql dir in /var/log.
   Then we still had problems.   mysql_install_db evidently did not get executed. Had to run it, from the command line in root.
   Problems still: File './mysql-bin.index' not found
)

/etc/init.d/mysql start    -   /etc/init.d/mysql stop

Fire-up the Next Day:  ok.   Once again in all my bungling ... I got it to start working and I really don't know what I did!! Something with the stuff above!!....... ARRRGGG!!!
===========================================================================================
If you have not already created a DB you should enter MySQL and do so. The following commands will create a DB and grant permissions to me: (gota be 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:
master_name table id will start at 1.


   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.
  Whooops... just checked on the Web and I should have been able to enter a field or column name with spaces. Probably had to be surrounded by quotes... hmmmmmm. Nope. Using straight mysql I could not enter a field name with spaces in it. (Just hate it when this happens.)

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:

  $conn = mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());

Now this just gets us connected. We still have to pick our database.

  mysql_select_db($db_name, $conn) or die(mysql_error());

    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:
First,MI,Last,Suffix

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:

  • Add a Customer tables:
    • master_name
    • address
    • telephone
    • email
  • Add Cust PC Info tables:
    • computer
    • perif_equip
  • Add Cust Call Info tables:
    • call_reason
  • Add Cust Soution Info tables:
    • solution

TRCustomer Admin System

Administration
View Records

    As 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).

    action="javascript:launchwin('EnterNewCust.php' , 'addcust' , 'width=500,height=560')"

I, personally, don't like big windows all the time. So, I use a JavaScript window.open function to display the values.

Name & Address Information
First Name:
M.I.:
Last Name:
Suffix:
Address Line 1:
Special Notes:
City:
State:
Zip:
Primary Phone:
Area:
()
Xchng:
-
Bank:
Extension
Secondary Phone:
Area:
()
Xchng:
-
Bank:
Extension
E-mail Address:
Conn Type:
Data Complete: No Yes
If you don't like the lines, then just change border from 1 to 0 in the table.

    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.
   Client - HTML / JavaScript.    Server - PHP / MySQL.
This separates the variables emensely. So, we need to work a little harder to get what we want. "Normally", in a form the 'action' is referring to another HTML file so you just put its name in the action field. As you can see above, I am not doing this normally. For the forms, you also normally use POST or GET to pass and recieve data between them and PHP. In this case we have JavaScript in between. My trials and tribs led to the following in order to pass a form field to a PHP script thru JavaScript:

<form name="modcust" id="modcust" method="POST" action="" onsubmit="launchphpwin()">
   <input type="Text" name="client" size="20" maxlength="20">
   <input value="Modify a Customer" type="submit">
</form>

    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:

    $client = $_GET[Clientval];

The string value entered is passed through to PHP. This way I can specify a starting point in my customer search/selection. I can either enter a whole name or just part of a name. If, for example, I just wanted the Taylor's, I would enter 'Taylor' in the name field. If there was more than one I would get all of them listed and I could make my selection. However, if I wanted all the names beginning with 'Ta' listed I would just enter 'Ta%'. Then I would see all the names that began with 'Ta'. (the quotes are not entered)

//build and issue query
$sql ="SELECT id, fname, lname FROM $table_name WHERE lname LIKE \"$client\" ORDER BY lname";

    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:

    <form method=\"POST \"action=\"show_modcustomer.php\">

Notice the space AFTER the POST and none after the   \"   before action. This along with some other incorrect spacing was causing the 'id' to NOT be sent to the show_modcustomer.php script. Sometimes the error is not immediately obvious. So... just remember, even if you copy supposedly working code, there can be a mistake... or two or more. Ok. Back to the modifications.


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:

  • Select what is to be done - Initial Menu
  • Enter a new customer
  • Save the data to the DB
  • Pick a customer to modify
  • Display the selection
Problem is: the selection to modify comes up in the selection Window ... which is to small. Sooooo... we need to make the JavaScript call, to open a Window, more parameter friendly.

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.
Obtained From Web Developers Journal

<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:
  alter table telephone drop tel_extention; - will drop the element in error
  alter table telephone add tel_extension char(3); - will add the correct element

    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:

  • Select what is to be done - Initial Menu - own win
  • Enter a new customer - via a form - own win
  • Save the data to the DB - separate proc
  • Pick a customer to modify - from a few or many
  • Display the selection - and make changes
  • Save the changes to the DB - separate proc

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???

    

  • TRDB_menu.php : Select what is to be done - Initial Menu - own win
  • EnterNewCust.php : Enter a new customer - via a form - own win
  • AddCust2DB.php : Save the data to the DB - separate proc
  • PickModCust.php : Pick a customer to modify - from a few or many
  • ModifyCust.php : Display the selection - and make changes
  • UpdCust2DB.php : Save the changes to the DB - separate proc
  • DelCustFromDB.php : Remove a Customer from the DB - separate proc

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.

    


Valid HTML 4.01! Click here to validate current page. Best viewed with ANY browser! Valid CSS! Click here to validate current CSS.


Copyright © 2004-2004 All rights reserved.