Greetings cajler-ga,
I have done this sort of multi-insert time entry system for a client
before, so I was able to conjure the logic up with help from my memory
(and from the PHP/MySQL documentation!). With some error checking and
formatting clean-up, it should plug into your application easily.
Your 3 requirements in the question are noted in comments within the
script below. I tend to pop in and out of PHP code a bit, but
hopefully
the indentation will remain when I post this and you can follow along.
This was tested on an Apache web server running PHP 4 and MySQL.
If you have any questions at all, please don't hesitate to ask for
clarification.
I do hope this helps!
-- mother-ga
Additional Resources:
PHP Documentation
http://www.php.net
<?php
/* Connect and selecting database - modify for your own server */
mysql_connect ("YOURHOST", "USERNAME", "PASSWORD");
mysql_select_db ("kw");
/* If time entries submitted */
if ($function=="insert") {
/* Printing HTML : Start entry added table for verification */
?>
<B>Entries added to kw_time: </B><BR>
<table>
<tr>
<td>Employee No</td>
<td>Date</td>
<td>Hours</td>
</tr>
<?php
/*
3. On submit the HTML form should insert into a seperate table
(kw_time) the following values: emp_no, date(from the text
box) and
Hours (also from the text box) for all of the rows, not just
one at a
time.
*/
/* Loop through the entries followed by counter "i" */
for ($i=0; $i<$num_rows; $i++) {
/* Assign dynamic variable to arrays, and then assign each
key */
$emp_info = "emp_".$i;
list($emp_no, $date, $hours) = $$emp_info;
/* Print and run insert queries for each entry array */
$i_query = "INSERT INTO kw_time (emp_no, date, hours)
VALUES ($emp_no, \"$date\", $hours)";
$i_result = mysql_query ($i_query);
/* Printing HTML : Show added entries */
?>
<tr>
<td><?php echo $emp_no ?></td>
<td><?php echo $date ?></td>
<td><?php echo $hours ?></td>
</tr>
<?php } ?>
</table>
<?php
}
/*
1. Reads the values from one table in a mySQL database
(DB: kw, tablename: kw_emps, fields: emp_no, con, last_name,
first_name, loc)
/*
/* Performing SQL query */
$query = "SELECT * FROM kw_emps";
$result = mysql_query ($query);
/* Print and populate employee date and hours table */
/* Printing HTML : Start table and form */
?>
<form action="<?php echo $SCRIPT_NAME ?>?function=insert"
method="POST">
<table>
<tr>
<td>Employee No</td>
<td>Con</td>
<td>Last Name</td>
<td>First Name</td>
<td>Loc</td>
<td>Date</td>
<td>Hours</td>
</tr>
<?php
/*
2. Format the results into an HTML table/form that adds two
additional
columns using editable text boxes. Date & Hours
(Keep date in MySQL format: YYYY-MM-DD)
*/
for ($count = 0; $row = mysql_fetch_array($result); $count++) {
?>
<tr>
<input type="hidden" name="emp_<?php echo $count ?>[]"
value="<?php echo $row[0] ?>">
<td><?php echo $row[0] ?></td>
<td><?php echo $row[1] ?></td>
<td><?php echo $row[2] ?></td>
<td><?php echo $row[3] ?></td>
<td><?php echo $row[4] ?></td>
<td><input type="text" name="emp_<?php echo $count ?>[]"
size=11 value="<?php echo date('Y-m-d') ?>" maxlength=10></td>
<td><input type="text" name="emp_<?php echo $count ?>[]"
size=4 value="0" maxlength=4></td>
</tr>
<?php } ?>
<input type="hidden" name="num_rows" value="<?php echo $count ?>">
</table>
<input type="submit" value=" Add Entries ">
</form> |
Request for Answer Clarification by
cajler-ga
on
31 Mar 2003 10:15 PST
mother-ga,
Thanks for the response, but I think I am missing something.
Using your example I was able to query the database for the employees
and the revelant information into a form that added the two additional
fields for date & hours.
I see that you say:
if ($function=="insert") --> do the insert sql statement
In the form tag you set the action to "<?php echo $SCRIPT_NAME
?>?function=insert" method="post"
I am assuming I could replace <?php echo $SCRIPT_NAME ?> with the name
of my page (time.php)?
If I make that change and add the following:
$function = $HTTP_GET_VARS['function'];
right after the mysql_select_db line and run the page I get the
select, but once I fill out the hours and click the submit button I
just get the same page with a new table at the top with headers only,
and when I open mysql I see that no entries have been added.
I think I am missing something simple. Please let me know if you can
shed any light on this for me.
Thanks again
|
Clarification of Answer by
mother-ga
on
31 Mar 2003 13:24 PST
Hello, here are my responses to your questions (labeled with a "-->"):
--> I am assuming I could replace <?php echo $SCRIPT_NAME ?> with the
name of my page (time.php)?
Yes, as long as the code inside and including " if
($function=="insert") { " is present.
--> If I make that change and add the following: $function =
$HTTP_GET_VARS['function'];
The value of $function in this added line is "insert." This should not
have any effect on the other code on the page.
--> but once I fill out the hours and click the submit button I just
get the same page with a new table at the top with headers only, and
when I open mysql I see that no entries have been added.
This would only happen if the entire " if ($function=="insert") " is
being skipped for some reason. You mentioned that you are changing
<?php echo $SCRIPT_NAME ?> to "time.php." Are you keeping all of the
code together, or splitting it up into two files that have different
names? If you are starting with one file to display the table with
fields for date and hours, and then you want a different file to do
the insert, you must copy the code inside and including the " if
($function=="insert") " loop in that other file.
Let me know if you are indeed splitting up the files, and I will post
what each file should contain to run properly. Otherwise, paste your
script into another clarification and I will look at it and let you
know why that conditional is not being run.
Thanks!
-- mother-ga
|
Request for Answer Clarification by
cajler-ga
on
01 Apr 2003 06:27 PST
mother-ga,
Well, I took another stab at it and I am still not getting values
inserted. I did switch to two pages this morning in an attempt to
understand things better.
I have two pages: time.php(for entering) & result.php(for reviewing)
Just to clarify, I have a mySQL(named: kennywood) DB with 2 tables:
kw_emps with the following fields:
con
loc
emp_no
first_name
last_name
mi
dept
id
kw_timecard with the following fields:
emp_no
date
hours
**********************************************
time.php is the following:
<?php
/* Connect and selecting database - modify for your own server */
mysql_connect ("localhost", "root", "cajler");
mysql_select_db ("kennywood");
/*1. Reads the values from one table in a mySQL database
(DB: kw, tablename: kw_emps, fields: emp_no, con, last_name,
first_name, loc) /*
/* Performing SQL query */
$dept = $HTTP_GET_VARS['dept'];
$query = "SELECT * FROM kw_emps where dept=$dept";
$result = mysql_query ($query);
/* Print and populate employee date and hours table */
/* Printing HTML : Start table and form */
?>
<form action="result.php?function=insert" method="POST">
<table border="1">
<tr>
<td>Company</td>
<td>Location</td>
<td>Emp Number</td>
<td>First Name</td>
<td>Last Name</td>
<td>MI</td>
<td>Department</td>
<td>ID</td>
<td>Date</td>
<td>Hours</td>
</tr>
<?php
/*2. Format the results into an HTML table/form that adds two
additional columns using editable text boxes. Date & Hours
(Keep date in MySQL format: YYYY-MM-DD)*/
for ($count = 0; $row = mysql_fetch_array($result); $count++) {
?>
<tr>
<input type="hidden" name="emp_<?php echo $count ?>[]" value="<?php
echo $row[7] ?>">
<td><?php echo $row[0] ?></td>
<td><?php echo $row[1] ?></td>
<td><?php echo $row[2] ?></td>
<td><?php echo $row[3] ?></td>
<td><?php echo $row[4] ?></td>
<td><?php echo $row[5] ?></td>
<td><?php echo $row[6] ?></td>
<td><?php echo $row[7] ?></td>
<td><input type="text" name="emp_<?php echo $count ?>[]" size=11
value="<?php echo date('Y-m-d') ?>" maxlength=10></td>
<td><input type="text" name="emp_<?php echo $count ?>[]" size=4
value="0" maxlength=4></td>
</tr>
<?php } ?>
<input type="hidden" name="num_rows" value="<?php echo $count ?>">
</table>
<input type="submit" value=" Add Entries ">
</form>
**********************************************
result.php is the following:
<?php
/* Connect and selecting database - modify for your own server */
mysql_connect ("localhost", "root", "cajler");
mysql_select_db ("kennywood");
/* If time entries submitted */
$function = $HTTP_GET_VARS['function'];
if ($function=="insert") {
/* Printing HTML : Start entry added table for verification */
?>
<b>Entries added to kw_time:</b><br>
<table>
<tr>
<td>Employee No</td>
<td>Date</td>
<td>Hours</td>
</tr>
<?php
/* 3. On submit the HTML form should insert into a seperate
table
(kw_time) the following values: emp_no, date(from the textbox)
and
Hours (also from the text box) for all of the rows, not just
one at a time. */
/* Loop through the entries followed by counter "i" */
for ($i=0; $i<$num_rows; $i++) {
/* Assign dynamic variable to arrays, and then assign each key */
$emp_info = "emp_".$i;
list($emp_no, $date, $hours) = $$emp_info;
/* Print and run insert queries for each entry array */
$i_query = "INSERT INTO kw_timecard (emp_no, date, hours) VALUES
($emp_no, \"$date\", $hours)";
$i_result = mysql_query ($i_query);
/* Printing HTML : Show added entries */
?>
<tr>
<td><?php echo $emp_no ?></td>
<td><?php echo $date ?></td>
<td><?php echo $hours ?></td>
</tr>
<?php } ?>
</table>
<?php
}
?>
**********************************************
Like I said, the select works - it's result.php that does not seem to
be working properly. If you can help I'd greatly appricate it.
Thanks again
|
Clarification of Answer by
mother-ga
on
01 Apr 2003 15:15 PST
Hi, and thanks for your response. At first glance, I noticed that in
"time.php" you are inserting into the first hidden field the value for
"ID" ($row[7]) instead of the value for "emp_no" ($row[2]).
Change the following:
<input type="hidden" name="emp_<?php echo $count ?>[]" value="<?php
echo $row[7] ?>">
to
<input type="hidden" name="emp_<?php echo $count ?>[]" value="<?php
echo $row[2] ?>">
That should not, however, be impeding the insert. I will test it some
more tonight and look into the documentation to see why your insert is
not working. By the way, be careful about posting the root password to
your database here... maybe you should change it or at least not allow
remote access! :)
I will update more tonight. Thanks for your patience!
-- mother-ga
|
Clarification of Answer by
mother-ga
on
01 Apr 2003 20:58 PST
Hello again,
I did some testing and indeed the section that I pointed out to you
above is what is preventing your insert from working. If that ID that
was being passed into the hidden form field had no value for any of
the employees, the mysql_query($result) line would throw a syntax
error instead of doing the insert. Even if the ID field has a value,
your "time.php" file is expecting the value for emp_no anyway, so it
should be fixed.
Try replacing the code and see if it works. During your own testing,
try inserting " echo mysql_error(); " right after the "$result =
mysql_query(); " on result.php, and this will tell you if your insert
worked or if it threw an error instead. Let me know how that works for
you.
Thanks,
mother-ga
|
Request for Answer Clarification by
cajler-ga
on
02 Apr 2003 11:24 PST
mother-ga,
Thanks for all of the assistance.
I made the changes you suggested and when I add the " echo
mysql_error(); " I get the following error:
You have an error in your SQL syntax near ' "", )' at line 1
So I did some basic error checking by adding " echo $i_query; " right
above the mysql_error line and now I get this:
INSERT INTO kw_timecard (emp_no, date, hours) VALUES (, "", )You have
an error in your SQL syntax near ' "", )' at line 1
It looks to me like there are no values being passed to the query.
Is there something specific in my php.ini file I need to set for this
to function properly? I have set the two pages up and made the
changes you suggested and I am still not getting data inputed.
Thanks again for all the help - it is greatly appreciated.
|
Clarification of Answer by
mother-ga
on
02 Apr 2003 12:30 PST
What version of PHP are you running? I am not sure this matters, but
it won't hurt to check. I will look into this today and get back to
you when I have an idea of what could be going on. It is quite hard to
debug like this, without access to your server! Unfortunately that is
how we have to do it... we will get it figured out.
-- mother-ga
|
Request for Answer Clarification by
cajler-ga
on
02 Apr 2003 14:20 PST
mother-ga,
I am running: PHP 4.2.1
On Windows XP Pro
with Apache 1.3.2.6
With mySQL 3.23.51
Sorry I can't make this more available for you - it is for in Intranet
application. If it would help I can post files to my web server or
email you files. Let me know what I can do.
Thanks again
|
Clarification of Answer by
mother-ga
on
03 Apr 2003 07:31 PST
Hello again --
I think that your version or configuration of PHP is not allowing the
sort of dynamic variable naming that my server is set up to do. As a
workaround, as I don't think we should mess with your config file, try
this replacement in "result.php" and let me know if this works for
you.
Replace this line:
list($emp_no, $date, $hours) = $$emp_info;
with
list($emp_no, $date, $hours) = $HTTP_POST_VARS[$emp_info];
Thanks,
-- mother-ga
|
Clarification of Answer by
mother-ga
on
03 Apr 2003 07:56 PST
We also need to correctly call the other post and get arguments being
passed from "time.php":
Replace this line:
if ($function=="insert") {
with
if ($HTTP_GET_VARS['function']=="insert") {
And replace this line:
for ($i=0; $i<$num_rows; $i++) {
with
for ($i=0; $i<$HTTP_POST_VARS['num_rows']; $i++) {
I believe this solves the problem. By default, a security feature in
PHP will not allow referencing POST and GET arguments from a form
directly. The HTTP_POST_VARS method works in versions over 4.0. There
is a shorter version for versions 4.2 and up, which you could also
use: $_GET['var'] for arguments passed with the GET method (and in
URL's), and $_POST['var'].
Let me know if this works for you.
Thanks,
-- mother-ga
|
,
1 Comment
)