I want insert autodata in a table ..one data will be come from another table i called here p_no(personal number) which is come from another table(emp_info) n its a foreign key for another table attendance. I want to insert 1200 data automatically just click one button .1200 data means 1200 p_no with other info.
1.emp_info(p_no,name,designation..etc) P_no is primary key 2.attandance (aid(auto increament),attendance,tiffin,shift,ot date,p_no) p_no is foreign key. now I want to insert 1200 data automatically by clicking one button accessing p_no info from emp_info table.
I'm using dreamweaver, php, mysql.
<?php require_once('Connections/bndock.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
mysql_select_db($database_bndock, $bndock);
$query_Recordset1 = "SELECT p_no FROM emp_info";
$Recordset1 = mysql_query($query_Recordset1, $bndock) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
$val=10;
for($row_Recordset1=0;$row_Recordset1<=$val;$row_Recordset1++){
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO attndance (aid, doa, attnc, stnd, ot, shift, p_no) VALUES (%s, now(), %s, %s, %s, %s,%s)",
GetSQLValueString($_POST['aid'], "int"),
GetSQLValueString($_POST['attnc'], "text"),//attendance//doa=date
GetSQLValueString($_POST['stnd'], "text"),//tiffin
GetSQLValueString($_POST['ot'], "text"),
GetSQLValueString($_POST['shift'], "text"),
GetSQLValueString($_POST['p_no'], "int"));
mysql_select_db($database_bndock, $bndock);
$Result1 = mysql_query($insertSQL, $bndock) or die(mysql_error());
$insertGoTo = "attnd_show.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
}
?>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<table width="296" height="190" align="center" bordercolor="#ECE9D8" bgcolor="#ECE9D8">
<tr valign="baseline">
<td colspan="2" align="right" nowrap="nowrap"> </td>
</tr>
<tr valign="baseline">
<td width="77" align="right" nowrap="nowrap"><strong>Attendance:</strong></td>
<td width="193"><select name="attnc">
<option value="P" selected="selected">P</option>
<option value="A">A</option>
<option value="L">L</option>
<option value="Sick">Sick</option>
<option value="Suspend">Suspend</option>
<option value="TY">TY</option>
</select> </td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"><strong>Stand Easy :</strong></td>
<td><select name="stnd">
<option value="Yes">Y</option>
<option value="No">N</option>
</select> </td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"><strong>Over time:</strong></td>
<td><select name="ot">
<option value="Yes">Y</option>
<option value="No">N</option>
</select> </td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"><strong>Shift:</strong></td>
<td><select name="shift">
<option value="A">A</option>
<option value="B">B</option>
<option value="C">C</option>
<option selected="selected">-</option>
</select> </td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"><strong>P.No:</strong></td>
<td><select name="p_no">
<?php
do {
?>
<option value="<?php echo $row_Recordset1['p_no']?>" <?php if (!(strcmp($row_Recordset1['p_no'], $row_Recordset1['p_no']))) {echo "SELECTED";} ?>><?php echo $row_Recordset1['p_no']?></option>
<?php
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
?>
</select> </td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"> </td>
<td><input name="submit" type="submit" value="Insert record" /></td>
</tr>
</table>
<input type="hidden" name="aid" value="" />
<input type="hidden" name="MM_insert" value="form1" />
</form></td>
I think you want to code an attedance system marking the missing/sick days of employees.
I am not saying you should not insert 1200 values into the attendance table (sounds to me like you want an entry for every day around 4 year timespan)
but it would be a better idea to do something like:
a) pretend the employee is at work and working if no entry exists in "awaytime" (opposite of attendance) and display only missing/sick days in some form of callendar (php can generate something like this easily)
b) pretend the employee is evil and sick as long as he did not stamp in and generated an attendance entry by himself
For your inital question, if you stick to your 1200 entries... just do it with a for loop:
// mysql-query getting all employees (use PDO, i am old fashioned and still use mysql_ functions)
while( mysql_fetch_array($sql-query-getting-employees) )
{
$insert = "INSERT INTO ATTENDANCE (col1, col2...) VALUES ";
for( $i = 0; $i <= 2000; $++ )
{
if($i != 0) { $insert += ", "; } //append comma for more than 1 value
$insert += "( val1, val2 )";
}
}
and then insert values via mysql_query($insert)