My Form Problems

Posted: Mon Dec 12, 2005 8:49 pm

Thanks to Raven, I've got all my output pages working great. Now I need to get the input form working. I've created simple forms before, this one seems way more complex.

Below is what the form will look like. (Try to ignore the colors if you don't like them, but I'm writing the form for those who are black and white challenged. ) Smile

My first task is to pull the value of the next auto_increment in the icid column into the cell to the right of ID: This is so it can become a part of the Member#. I don't know if this is possible, because this id value doesn't actually exist before the form is submitted. Could I find the last value and add 1? If so, what would the query look like.

The code for the row looks like this:

Code:

<tr>


        <td width="40%" style="background-color: #CCFFFF"><b>ID</b></td>


        <td width="60%" colspan="2" style="background-color: #CCFFFF"><?php /* need the next auto-increment number from icid */ ?> </td>


      </tr>


      <tr>

registered · Posted: Mon Dec 12, 2005 9:03 pm

CodyG wrote:

My first task is to pull the value of the next auto_increment in the icid column into the cell to the right of ID: This is so it can become a part of the Member#. I don't know if this is possible, because this id value doesn't actually exist before the form is submitted. Could I find the last value and add 1? If so, what would the query look like.

I would not rely on just incrementing by one from the max ID already set. You could do it if you could guarantee that you would be the only one submitting this form at the same time. I would suggest that you re-assess your need for the ID number and why do you need to embed it in the Member#? If I knew a little bit more what you are trying to do with the sequence ID and the Member#, I might be able to suggest a substitute data model and form structure.

Are you able to leave the ID and Member# off of the form and auto-assign those OR, just go ahead and assign the ID and member# and then just present the final member# back to the user once it is saved in the DB?

Posted: Mon Dec 12, 2005 11:05 pm

Quote:

Are you able to leave the ID and Member# off of the form and auto-assign those OR, just go ahead and assign the ID and member# and then just present the final member# back to the user once it is saved in the DB?

Only one or two people will ever have access to this form and never at the same time.

Our members need unique member numbers that reflect the original join date. So, this is how we are going to assign those unique numbers. xx(last two digits of the year ie: 05); xx(month digits ie:12); abc (first three letters of last name); and xxx (ID number.) Therefore for the 101st record, a member # would look something like 0512GRE101

Assume "mr and ms greatmember" join on the same day... therefore I need to generate a unique number for them. However... if it could be created after the form is submitted, and displayed in the redirect... that would work too.

Thanks for your help and have a happy nuking day. Smile

Posted: Tue Dec 13, 2005 1:23 pm

After a little more research on your thoughts ...
The form could be submitted without user input and using a mysql_insert_id() the id could be displayed after the insert query.
So, how does the member #, entered in the form field, get appended with the id value, so the data stored in the member_number field is the correct value?

Now I'm completely confuddled.

Going back to my original idea... is it possible to display the next auto increment value of an id column which is set to auto increment?

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

I'm assuming that you have a column that is an auto-increment field. Why do you care what the member # is before you create it? Why not pull in the information you need and then save it, sans the unique id appended. Immediately reread the record to grab the REAL uniqueID, append it to the member id column and save it back. It's just 2 extra sql calls. There are other ways but this is simple and clean.

Posted: Tue Dec 13, 2005 5:02 pm

ok, so it's two forms... one as above without the id and member number. Then, after the form is submitted there is a redirect to another form which displays the new ID number and a form field to sumbit the new member number. Have I got it?

I'll play around with a simple form and when I get this sorted I'll be back with questions about saving the date variables.

Thanks Raven and Montego.

Posted: Tue Dec 13, 2005 5:48 pm

After a walk I had another thought about this. What about the following?

>Link to Add Member
> Do you really want to add a new member? Yes/No.
> If yes, populate new record row with id and redirect to form page with new id displayed using mysql_insert_id()
> Fill out form with member number and everything else.

Posted: Tue Dec 13, 2005 6:57 pm

CodyG, to be honest, I never try to embed meaning into key fields, such as a member ID. I don't see why you cannot use an auto-sequence field for your internal management of the member's ID (which would never really need to be displayed per se to the user) and then keep the join datetime and the names in other fields (which you already have in your form). It really goes against my "grain" to make a key field have "smarts" embedded in it as you run into issues like what you are experiencing.

Take the nuke_users table for instance. You don't log in with your user_id number do you? No. You log in with your more friendly username field. Each user record in the table has all the other information in it that you might want to see on a web page somewhere. However, this user_id field is used through Nuke and Forums for managing user information.

Are you sure you need to create this concatenated member number? I'd be willing to bet you that it is not really necessary, even without knowing 100% how you intend on using it in the rest of your app.

Posted: Tue Dec 13, 2005 9:23 pm

btw, I'm only a wannabe super-programmer who has been using nuke since ver 6.0 with port, so I totally appreciate any and all help, solutions and discussion. As I said above, I've written simple modules with forms, but this one has the most complexity.

Quote:

Are you sure you need to create this concatenated member number? I'd be willing to bet you that it is not really necessary, even without knowing 100% how you intend on using it in the rest of your app.

Perhaps if I explain a bit more about the use of this form and show you the db table... note the column member_number. It's in this column where I want to store this unique combo of date, alpha and icid.

Code:




CREATE TABLE `inner_circle` (


  `icid` int(5) NOT NULL auto_increment,


  `firstname` varchar(20) NOT NULL default '',


  `lastname` varchar(20) NOT NULL default '',


  `location` varchar(20) NOT NULL default '',


  `member_number` varchar(15) NOT NULL default '',


  `sysnick` varchar(25) NOT NULL default '',


  `email` varchar(50) default NULL,


  `orig_join` date NOT NULL default '0000-00-00',


  `future_expire` date NOT NULL default '0000-00-00',


  `notes` text,


  `welcome` int(1) NOT NULL default '0',


  `admin_nick` varchar(25) NOT NULL default '',


  PRIMARY KEY  (`icid`),


  ENGINE=MyISAM AUTO_INCREMENT=13 ;

The website where this script will find a home was nuked a few years ago, ver 7.3. It is but one resource of a non-profit organization which supports seniors in my community.

What I'm trying to do is bring the existent membership database into the 21st century. Currently, paid member records are recorded in a Word Document. It gets messy, it is unsortable re membership expiry dates, and is inaccessible to the board members unless the .doc file is shared. The only thing I want to keep in this .doc file are the realtime mailing labels.

I thought about using Access or some other standalone db program to keep track of paid members, but the same inaccessible issue arises. So, my solution is to use a sortable members table and allow view only accessto the secretary, chairperson, treasurer, volunteer co-ordinator and sysadmin. They can login to the website (using regular nuke functions) from where ever and (thanks to NSN Groups) will be able to view the paid members table. Only a few members, secretary, sysadmin and probably treasurer will actually be allowed to insert and/or update the table.

This will be like a nuke module, but it won't really have anything to do with any nuke function and will only be available to a few as I said above.

One workaround... the person entering the new data could view the entire table output before going to the add member form and they could remember what the last ID is and then append the next number to the member # created in the add member form field. But I'd rather have some number displayed and not rely on the memory of a previous page for user input.

As far as requiring a unique member number, it's a tradition ... this is for the real-life membership cards which are printed with a unique number. I can't imagine it being actually used for any function that would have to do with the website.

Perhaps you're right. Perhaps this organization could live without it's membership numbers. But, (and this is where my ego lives) I'd rather just find a solution now, and give them what they want, instead of telling them they need to change because I'm incapable of doing what they wanted and they would need to hire super-programmer to get it done.

Posted: Wed Dec 14, 2005 7:00 am

CodyG wrote:

Perhaps you're right. Perhaps this organization could live without it's membership numbers. But, (and this is where my ego lives) I'd rather just find a solution now, and give them what they want, instead of telling them they need to change because I'm incapable of doing what they wanted and they would need to hire super-programmer to get it done.

These are all fine requirements. What I am proposing is that you already have two of the key pieces of information being collected, at member entry time, that is used in your member number: name and original join date. Once the new member information is added to the database, you will have the third piece of information, the ID number (in the "icid" field).

Therefore, why do you need to store a concatenation of these values? On your user-friendly screens that you will provide to these few folks, you simply display the concatenation of this info as a "member_number" rather than trying to store it in the database.

Also, when your users key-in the member number on any maintenance / display screens you write, you will have the ID number starting in the eigth digit of the member number that they enter and then you use that ID to go retrieve the necessary information from this table using the "icid" field.

This would remove this issue entirely as you will be properly formatting the display when needed and they can still enter their familiar member numbers and you can decode it to retrieve whatever information that you need from the 'inner_circle' table.

Hope this helps explain a bit more what I was thinking. Thanks for being specific on the requirements. It helped alot to understand what you are trying to accomplish.

Regards,
montego

Posted: Thu Dec 15, 2005 11:12 am

I think I'm learning something. Smile

Quote:

Therefore, why do you need to store a concatenation of these values?

I see your point. I don't need to store it. The number only needs concatenation at the time of display.
Good solution. Smile

I'll be back when I get that sorted out.

Posted: Thu Dec 15, 2005 7:19 pm

I'm back, but after spending a day with the mysql manual and php manual, and trying everything I could think of, I have not been able to get the icid concatentated to the member number. So, just how do I get this to work?

I've tried everything and got everything from a blank white page to a column of 0's to a column of blank fields. Below is my latest attempt which gives a blank column field. $myrow["member_number"."icid"]

Code:







echo "<table border=1 align=center class=sortable id=innercircle>\n";





         echo "<tr align=\"center\"><td>ID</td><td>FirstName</td><td>LastName</td><td>Location</td><td>MemberNumber</td><td>SysNick</td><td>Email</td><td>Original Join</td><td>Future Expire</td><td>Notes</td><td>Welcome</td><td>AdminNick</td></tr>\n";





while ($myrow = mysql_fetch_array($result)) {





        printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",


$myrow["icid"], $myrow["firstname"], $myrow["lastname"], $myrow["location"], $myrow["member_number"."icid"], $myrow["sysnick"], $myrow["email"], $myrow["orig_join"], $myrow["future_expire"], $myrow["notes"], $myrow["welcome"], $myrow["admin_nick"]);





}





       echo "</table>\n";

Posted: Thu Dec 15, 2005 7:45 pm

I'm assuming your fetching logic is correct. This is but one way to do it.

Change
while ($myrow = mysql_fetch_array($result)) {

printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
$myrow["icid"], $myrow["firstname"], $myrow["lastname"], $myrow["location"], $myrow["member_number"."icid"], $myrow["sysnick"], $myrow["email"], $myrow["orig_join"], $myrow["future_expire"], $myrow["notes"], $myrow["welcome"], $myrow["admin_nick"]);

}

to

while ($myrow = mysql_fetch_array($result)) {
$memNumber = trim($myrow["member_number"]).trim($myrow["icid"]);

printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
$myrow["icid"], $myrow["firstname"], $myrow["lastname"], $myrow["location"], $memNumber, $myrow["sysnick"], $myrow["email"], $myrow["orig_join"], $myrow["future_expire"], $myrow["notes"], $myrow["welcome"], $myrow["admin_nick"]);

}

Posted: Thu Dec 15, 2005 7:57 pm

Brilliant! It works great! Thanks Raven.

Posted: Thu Dec 15, 2005 8:09 pm

CodyG wrote:

Brilliant! It works great! Thanks Raven.

This is why Raven is THE MAN!

worship

Posted: Thu Dec 15, 2005 9:39 pm

Raven is so the man with a plan when it comes to nuke. Smile

Now to my next issue.... php/mysql dates. I am only guessing at what should work for this submit.... so, any and all feedback is all good.

I've looked at the code in the event calendar and been through several tutorials about php/mysql dates and although I think I know what I don't know, I'm still quite befuddled.

What I need ...

I need the orig_join Year select to count from 1994.

Code:




$orig_startYr = date("Y", $today); // get the year from $today


echo "<select name='orig_dateYr'> \n";


for ($n=$orig_startYr; $n<=$orig_startYr+3; $n++) //<<<<<<< change this ?


{


echo " <option value = $n";


if ($orig_startYr == $n )


{


echo " selected";


}


echo "> $n ";


}


echo "</select> \n";

values for $orig_join and $future_expire, but the way my form is set up I've got SIX variables!
$orig_join = $orig_dateMO . $orig_dateDay . $orig_dateYr
$future_expire = $future_dateMO . $future_dateDay . $future_dateYr
I've yet to get a clue how this concatenation is accomplished and do I need to care about datatypes here? I would so appreciate some help, even if my date functions are all wrong and I should be using some other method.

Here is my INSERT. It's an amended bit of code I hope will come in useful for updates too... though I haven't sorted through that issue at all.

Code:







// build sql insert


// get the variables from the URL request string


   $icid = $_POST['icid'];


   $firstname = $_POST['firstname'];


   $lastname = $_POST['lastname'];


   $location = $_POST['location'];


   $member_number = $_POST['member_number'];


   $sysnick = $_POST['sysnick'];


   $email = $_POST['email'];


   $orig_join = $_POST['orig_join'];


   $future_expire = $_POST['future_expire'];


   $notes = $_POST['notes'];


   $welcome = $_POST['welcome'];


   $admin_nick = $_POST['admin_nick'];





// if $icid is not defined, we have a new entry, otherwise update the old entry


if( $icid )


{


   $query = "UPDATE `inner_circle` SET `firstname`='$firstname', `lastname`='$lastname', `location`='$location',`member_number`='$member_number',


         `sysnick`='$sysnick', `email`='$email', `orig_join`='$orig_join', `future_expire`='$future_expire', `notes`='$notes', `welcome`='$welcome', `admin_nick`='$admin_nick' WHERE `icid`='$icid'";


}


else


{


   $query = "INSERT INTO `inner_circle` ( `firstname`, `lastname`, `location`, `member_number`, `sysnick`, `email`, `orig_join`, `future_expire`, `notes`, `welcome`, `admin_nick` )


      VALUES ( '$firstname', '$lastname', '$location', '$member_number', '$sysnick', '$email', '$orig_join', '$future_expire', '$notes', '$welcome', '$admin_nick' )";


}

The following is the relevant form code. (See form in first post.)

Code:




<?php


$today= time();                 //stores today's date  


?>


      <tr>


        <td width="40%"><b>Original Join Date</b></td>


<?php





$todayMO = date("m", $today); // get the month from $today


echo " <td width=\"60%\" colspan=\"2\"><select size=\"1\" name=\"orig_dateMO\"> \n";


for ($n=1; $n<=12; $n++)


{


echo "<option value =$n\n";


if  ($todayMO == $n)


{


echo " selected";


}


echo "> $monthName[$n]\n";


}


echo "</select>";





/* build list for the day */





$todayDay=date("d", $today); // get the day from $today


// pulldown date selectors for orig_join


$orig_todayMO = date("m", $today); // get the month from $today


echo " <td width=\"60%\" colspan=\"2\"><select size=\"1\" name=\"orig_dateMO\"> \n";


for ($n=1; $n<=12; $n++)


{


echo "<option value =$n\n";


if  ($orig_todayMO == $n)


{


echo " selected";


}


echo "> $monthName[$n]\n";


}


echo "</select>";





/* build list for the day */





$orig_todayDay=date("d", $today); // get the day from $today


echo "<select name='orig_dateDay'> \n";


for ($n=1; $n<=31; $n++)


{


echo " <option value=$n";


if  ($orig_todayDay == $n)


{


echo " selected";


}


echo "> $n\n";


}


echo "</select>\n";





$orig_startYr = date("Y", $today); // get the year from $today


echo "<select name='orig_dateYr'> \n";


for ($n=$orig_startYr; $n<=$orig_startYr+3; $n++)


{


echo " <option value = $n";


if ($orig_startYr == $n )


{


echo " selected";


}


echo "> $n ";


}


echo "</select> \n";





?>


      </tr>


      <tr>


        <td width="40%" ><b>Future Expire Date</b></td>





<?php





// pulldown date selectors for future_expire





$future_todayMO = date("m", $today); // get the month from $today


echo " <td width=\"60%\" colspan=\"2\" ><select size=\"1\" name=\"future_dateMO\"> \n";


for ($n=1; $n<=12; $n++)


{


echo "<option value =$n\n";


if  ($future_todayMO == $n)


{


echo " selected";


}


echo "> $monthName[$n]\n";


}


echo "</select>";





/* build list for the day */





$future_todayDay=date("d", $today); // get the day from $today


echo "<select name='future_dateDay'> \n";


for ($n=1; $n<=31; $n++)


{


echo " <option value=$n";


if  ($future_todayDay == $n)


{


echo " selected";


}


echo "> $n\n";


}


echo "</select>\n";





$future_startYr = date("Y", $today); // get the year from $today


echo "<select name='future_dateYr'>\n";


for ($n=$future_startYr+1; $n<=$future_startYr+4; $n++)


{


echo " <option value = $n";


if ($future_startYr == $n )


{


echo " selected";


}


echo "> $n ";


}


echo "</select> \n";