User interface logic for telephone book input problem

[sersha]

Hi all PHP experts!

 

I would like to ask for advice regarding a following problem. There is a table with a telephone usage data (number, date, starttime etc) which should be processed in a different ways, also compared with a telephone book containing all known callers. Each caller can have a few numbers (work, home, cell etc) and each number can be shared by a few callers. That implies a link table, being many-to-many relation.

 

My problem is to make a proper logic for inputting new callers into that telephone book. I thought along the following lines

 

Repeat
GetName(IN name OUT namid)
        Repeat
            GetNumber(IN number OUT numid)
            SaveTables(name, number, namid, numid)
        Until NextNameButton pressed
Until false

 

Some preliminaries as follows:

relevant tables:

desc callers;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| calid   | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| fname   | varchar(15)      | YES  |     | NULL    |                | 
| mname   | varchar(20)      | YES  |     | NULL    |                | 
| lname   | varchar(20)      | YES  |     | NULL    |                | 
| alias   | varchar(15)      | YES  |     | NULL    |                | 
| comment | varchar(20)      | YES  |     | NULL    |                | 
+---------+------------------+------+-----+---------+----------------+

mysql> desc numbers;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| numid | int(10) unsigned     | NO   | PRI | NULL    | auto_increment | 
| catid | smallint(5) unsigned | YES  |     | NULL    |                | 
| ownid | smallint(5) unsigned | YES  |     | NULL    |                | 
| num   | char(15)             | YES  |     | NULL    |                | 
+-------+----------------------+------+-----+---------+----------------+

mysql> desc calnums;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| calid | int(10) unsigned | YES  |     | NULL    |       | 
| numid | int(10) unsigned | YES  |     | NULL    |       | 
+-------+------------------+------+-----+---------+-------+

 

Stored procedures to load the tables :

 

DELIMITER $$
DROP PROCEDURE IF EXISTS `telephony`.`insertname`$$
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `insertname`(IN fnam varchar(15),mnam varchar(20),lnam varchar(20),alia varchar(15),commt varchar(20),OUT cid int unsigned)
BEGIN
INSERT INTO callers (fname,mname,lname,alias,comment) VALUES (fnam,mnam,lnam,alia,commt);
SET cid=(SELECT (COUNT(calid)) FROM callers); 
END$$
DELIMITER ;


DELIMITER $$
DROP PROCEDURE IF EXISTS `telephony`.`insertnumber`$$
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `insertnumber`(callerid int unsigned,cati smallint unsigned,owni smallint unsigned, nr char(15))
BEGIN
DECLARE checknum CHAR(15);
SET checknum=(SELECT numid FROM numbers WHERE num=nr);
/* if number is NOT in the database*/
IF checknum IS NULL THEN

INSERT INTO numbers (catid,ownid,num) VALUES (cati,owni,nr);
INSERT INTO calnums (calid,numid) VALUES (callerid,(SELECT MAX(numid) FROM numbers));

ELSE

/*if number is in the database, use obtained previously numid*/
INSERT INTO calnums (calid,numid) VALUES (callerid,checknum);
END IF;    
END$$
DELIMITER ;

 

Include files which are making forms:

NameDisplay.html

<fieldset><legend style="background-color:yellow;">Personal info</legend>
<b>First name</b>    
<input type=text name="fname" value="<?print $_POST['fname'];?>" maxlength=15><br />
<b>Middle name</b>
<input type=text name="mname" value="<?print $_POST['mname'];?>" maxlength=20><br />
<b>Last name</b>     
<input type=text name="lname" value="<?print $_POST['lname'];?>" maxlength=20><br />
<b>Nickname</b>      
<input type=text name="nname" value="<?print $_POST['nname'];?>" maxlength=15><br />
<b>Comment</b>       
<input type=text name="comment" value="<?print $_POST['comment'];?>" maxlength=20><br />
</fieldset><br />

 

NumberEntry.html

 

<fieldset><legend style="background-color:yellow;">Number details</legend>
<b>Telephone</b>     
<input type=text name="number" maxlength=15><br /><br />
<fieldset><legend >This number is a</legend>
<input type="radio" name="phonetype" value="1" id="home" accesskey="1" checked> <label for="home"/>Home</label>
<input type="radio" name="phonetype" value="2" id="mobile" accesskey="2"> <label for="mobile"/>Mobile</label>
<input type="radio" name="phonetype" value="3" id="job" accesskey="3"> <label for="job"/>Job</label>
<input type="radio" name="phonetype" value="4" id="fax" accesskey="4"> <label for="fax"/>Fax</label>
</fieldset><br />
<fieldset><legend >And is used by</legend>
<input type="radio" name="owner" value="1" id="family" accesskey="1" checked/> <label for="family">Family</label>
<input type="radio" name="owner" value="2" id="ni" accesskey="2"> <label for="ni"/>Ni</label>
<input type="radio" name="owner" value="3" id="na" accesskey="3"> <label for="na"/>Na</label>
<input type="radio" name="owner" value="4" id="bhi" accesskey="4"> <label for="bhi"/>Bhi</label>
</fieldset>

 

And the main code:

 

<html><body><?include "selectdb.php"?>
<form action="<?php echo $_SERVER['PHP_SELF']?>" method="POST">

<?if (!($_POST['submit'])) {?>             <!-- If button Submit is NOT pressed - show first form-->
<?include "NameEntry.html"?>
<br />
<input type=submit name="submit" value="Submit">
<input type=reset name="reset" value="Clear">
</form>
<?}
else
{?> <!-- If button Submit IS pressed - store name, get out calid, display the number form-->
<?
$fname=$_POST['fname'];$mname=$_POST['mname'];$lname=$_POST['lname'];$nname=$_POST['nname'];
$comment=$_POST['comment'];

$query="call insertname('$fname','$mname','$lname','$nname','$comment',@cid)";
// query uses stored procedure, where @cid is a session variable in MySql, 
mysql_query($query) or die('Error, procedure insertname() failed '.mysql_error());

//pull out the value of a session variable
$result=mysql_query("select @cid");$callerid= mysql_result($result,0);
echo "$query"; echo "<br />\$callerid $callerid<br />"; //for debugging
?>

<form action="<?php echo $_SERVER['PHP_SELF']?>" method="POST">
<?include "NameDisplay.html"?>
<?include "NumberEntry.html"?>
<input type=submit name="submit" value="Next number">
<input type=submit name="next" value="New name">
</form>
<!-- -->
<?$phonetype=$_POST['phonetype'];$owner=$_POST['owner'];$number=$_POST['number']; 

$query="call insertnumber('$callerid','$phonetype','$owner','$number')";
mysql_query($query) or die('Error, procedure insertnumber() failed '.mysql_error());

echo "<br />$query";
?><br />

<?}?>
</html></body>

 

So, I obviously am doing something wrong... perhaps even contrary to my initial intentions.

Maybe I should use a different logic and / or different controls / buttons?

I am concerned here only with the interface logic, stored procedures do their work well.

 

Your advice or suggestions would be extremely appreciated! (I am sort of sitting in a mental rut for quite a while

Thank you very much in advance....