Create Account
Sign In

Survey ServiceUserbar Generator (for forums)Live CounterDNS Lookup Service (DNS Zone)Htpasswd File GeneratorStylish Text GeneratorGoogle PageRank CheckerSignature Maker (for forums) onlineWhois Lookup of a Domain NameJavascript Code Compressor (Minifier)
You are here: Webestools > Forum > Web Sites > PHP/MYSQL > can't update array data mysql/php

can't update array data mysql/php

To create and answer topics, you must to be logged.
Pages: 1
Author Message
annhoeun
13/12/2014 at 17:09:49
annhoeun
Member
 Insert data into table is OK.
table name: item_request & purchase request
-----------------------------------------------------------------------------
<?php require_once('../includes/connection.php');?>
<?php require_once('../includes/get_username.php');?>
<?php include_once('../includes/header.php');?>
<?php include_once('../sidebar.php');?>

<?php
if(isset($_GET["attempt"]))
{
$attempt=$_GET["attempt"];
}
?>
<?php
ob_start();
date_default_timezone_set("Asia/Bangkok");
//echo date_default_timezone_get();
$sql = mysql_query("SELECT * FROM user");
$row = mysql_fetch_array($sql);
$pr_requester = $row['name'];
$pr_unit = $row['department'];
$pr_date = date('d/M/Y');
?>
<table id="contentbox">
<tr>
<td id="content">
<div class="name"><b>Welcome, <?php echo $username; ?></b> | <a href="../logout.php">Logout</a></div>
<div class="label">Purchase Request (PR)</div>
<hr />
<form name="formsearch" method="post" onsubmit="return validateForm()" action="search_pr.php">
<table>
<tr>
<td class="search">Search Request :</td>
<td><input type="text" size="40px" name="search" placeholder="Search here..." /></td>
<td><input type="submit" value="Search" style="cursor:pointer;"/></td>
</tr>
</table>
</form>
<?php
if(isset($attempt))
{
if($attempt == "success")
{
?>
<div class="success">Record successfully updated.</div>
<?php
}
elseif($attempt == "saved")
{
?>
<div class="success">Purchase request successfully saved.</div>
<?php
}
elseif($attempt == "exist")
{
?>
<div class="error">Unable to add purchase request already exist.</div>
<?php
}
elseif($attempt == "empty")
{
?>
<div class="error">All *star fields are required</div>
<?php
}

}
?>
<?php
if (isset($_POST['submit']))
{

$pr_purpose = mysql_real_escape_string(htmlspecialchars($_POST['pr_purpose']));
if ( //$pr_purpose =='' ||
$_POST['account_number'][0] == '' ||
$_POST['item_quantity'][0] =='' ||
$_POST['item_estunitprice'][0] =='' ||
$_POST['item_unit'][0] =='' ||
$_POST['item_activity'][0] =='' ||
$_POST['item_description'][0] =='')
{
header("Location:pr.php?attempt=empty");
}
else{
$sql = 'SELECT * FROM purchase_request, item_request WHERE pr_purpose = "'.$pr_purpose.'" && pr_date >= CURDATE() && item_description ="'.$_POST['item_description'][0].'" && item_quantity ="'.$_POST['item_quantity'][0].'"';

if ($result = mysql_query($sql))
{
if(mysql_num_rows($result))
{
header("Location: pr.php?attempt=exist");
}
else{


$sqlpr = mysql_query("INSERT INTO purchase_request (pr_date, pr_requester, pr_purpose) values(now(),'$pr_requester','$pr_purpose')",$conn) or die(mysql_error());
$last_id = mysql_insert_id();

foreach($_POST['item_description'] as $i => $item_description)
{ // Get values from post.
$item_quantity = mysql_real_escape_string($_POST['item_quantity'][$i]);
$item_description= mysql_real_escape_string(htmlspecialchars($_POST['item_description'][$i]));
$item_quantity = mysql_real_escape_string(htmlspecialchars($_POST['item_quantity'][$i]));
$item_unit = mysql_real_escape_string(htmlspecialchars($_POST['item_unit'][$i]));
$item_estunitprice = mysql_real_escape_string(htmlspecialchars($_POST['item_estunitprice'][$i]));
$item_accode = mysql_real_escape_string(htmlspecialchars($_POST['account_number'][$i]));
$item_activity = mysql_real_escape_string(htmlspecialchars($_POST['item_activity'][$i]));
$item_availablebudget = mysql_real_escape_string(htmlspecialchars($_POST['item_availablebudget'][$i]));


// Estimate available budget = 0 if data was not input

if ($item_availablebudget == ''){$item_availablebudget = '0';}
if ($item_unit == ''){$item_unit = '&nbsp;';}
$item_price = $item_quantity * $item_estunitprice;
// Add to database
$sqlir = mysql_query("INSERT INTO item_request
(item_prnumber,
item_description,
item_quantity,
item_unit,
item_estunitprice,
item_price,
item_department,
item_accode,
item_activity,
item_availablebudget)
values(
'$last_id',
'$item_description',
'$item_quantity',
'$item_unit',
'$item_estunitprice',
'$item_price',
'$pr_unit',
'$item_accode',
'$item_activity',
'$item_availablebudget'
)",$conn) or die(mysql_error());
header("Location: pr.php?attempt=saved");
}
}
}
}
}
?>
<fieldset>
<legend><div class="legend"><b>Please fill-up the space provided below</b></div></legend>
<div id="add_pr">
<form name="form1" method="post" onsubmit="return validateForm1()" action="">

<table >
<tr>
<td class="search">* fields are required</td>
</tr>
<tr >
<td class="search"> Request Dated:</td>
<td> <?php echo $pr_date; ?> </td>
<td class="search"> Request Unit:</td>
<td size=> <?php echo $pr_unit; ?> </td>
</tr>
<tr>
<td class="search"> Requester: </td>
<td> <?php echo $pr_requester; ?> </td>
<td class="search"> Purpose: </td>
<td>
<textarea name="pr_purpose" rows="2" cols="50" placeholder="*Enter request purpose here..."></textarea>
</td>
</tr>
</table>

<table class="item_table" id="alternatecolor">
<tr>
<th>No</th>
<th>Check box</th>
<th>Item Description</th>
<th>Quantity</th>
<th>Unit (book, pc, etc)</th>
<th>Estimate UnitPrice</th>
<th>Budget Code</th>
<th>Activity</th>
<th>Available Budget</th>
</tr>
<tr>
<TD> 1 </TD>
<TD><INPUT type="checkbox" size="1px"name="chk"/></TD>
<td><input type="text" size="23px" name='item_description[]' placeholder="*description" /></td>
<td><input type="text" size="2px" name='item_quantity[]'onkeyup="if (/\D/g.test(this.value)) this.value = this.value.replace(/\D/g,'')" placeholder="*quantity"/></td>
<td>
<?php

$sql = mysql_query("select * from item_unit");
echo '<select name="item_unit[]" >';
echo '<option value="">*None..</option>';
while($row = mysql_fetch_assoc($sql))
{
echo '<option value="'.$row['unit_name'].'">'. $row['unit_name'].'</option>';
}
echo'</select>';
?>
</td>
<td><input type="text" size="2px" name='item_estunitprice[]' placeholder="*Est price"/></td>
<td>
<?php

$sql = mysql_query("select * from accode");
echo '<select name="account_number[]" >';
echo '<option value="">*None..</option>';
while($row = mysql_fetch_assoc($sql))
{
echo '<option value="'. $row['account_number'].'">'. $row['account_number'].'</option>';
}
echo'</select>';
?>
</td>
<td><input type="text" size="3px" name='item_activity[]' placeholder="*Enter activity"/></td>
<td><input type="text" size="5px" name='item_availablebudget[]' placeholder="Budget"/></td>
</tr>















<tr>
<TD> 2 </TD>
<TD><INPUT type="checkbox" size="1px"name="chk"/></TD>
<td><input type="text" size="23px" name='item_description[]' placeholder="*description" /></td>
<td><input type="text" size="2px" name='item_quantity[]'onkeyup="if (/\D/g.test(this.value)) this.value = this.value.replace(/\D/g,'')" placeholder="*quantity"/></td>
<td>
<?php

$sql = mysql_query("select * from item_unit");
echo '<select name="item_unit[]" >';
echo '<option value="">*None..</option>';
while($row = mysql_fetch_assoc($sql))
{
echo '<option value="'.$row['unit_name'].'">'. $row['unit_name'].'</option>';
}
echo'</select>';
?>
</td>
<td><input type="text" size="2px" name='item_estunitprice[]' placeholder="*Est price"/></td>
<td>
<?php

$sql = mysql_query("select * from accode");
echo '<select name="account_number[]" >';
echo '<option value="">*None..</option>';
while($row = mysql_fetch_assoc($sql))
{
echo '<option value="'. $row['account_number'].'">'. $row['account_number'].'</option>';
}
echo'</select>';
?>
</td>
<td><input type="text" size="3px" name='item_activity[]' placeholder="*Enter activity"/></td>
<td><input type="text" size="5px" name='item_availablebudget[]' placeholder="Budget"/></td>
</tr>





















</table>
<INPUT type="button" value="Add Row" style="float: right"onclick="addRow()" />
<INPUT type="button" value="Delete Row" style="float: right"onclick="deleteRow()" /><br></br>

<table>
<tr>
<td class="add">
<input type="submit" name="submit" style="cursor:pointer;" value="Save">
<input type="reset" name="Btncancel" style="cursor:pointer;" value="Clear">
</td>
</tr>
</table>
</form>
</div>
</fieldset>
<hr />
<table class="inventory_table" id="alternatecolor">
<tr>
<th>Request Purpose</th>
<th>Item Description</th>
<th>Quantity</th>
<th>Unit</th>
<th>Estimate UnitPrice</th>
<th>Budget Code</th>
<th>Activity</th>
<th>Available Budget</th>
<th width="70">Actions</th>
</tr>
<?php
include('../includes/ps_pagination.php');
$sql = 'SELECT * FROM purchase_request, item_request WHERE purchase_request.pr_id=item_request.item_prnumber GROUP BY pr_id ORDER BY pr_id DESC';

//Create a PS_Pagination object
$pager = new PS_Pagination($conn, $sql, 15, 20);

//The paginate() function returns a mysql result set for the current page
$rs = $pager->paginate();

while($row = mysql_fetch_array($rs))
{
?>
<tr>
<td><?php echo $row["pr_purpose"];?></td>
<td><?php echo $row["item_description"];?></td>
<td><?php echo $row["item_quantity"];?></td>
<td><?php echo $row["item_unit"];?></td>
<td><?php echo $row["item_estunitprice"];?></td>
<td><?php echo $row["item_accode"];?></td>
<td><?php echo $row["item_activity"];?></td>
<td><?php echo '$',$row["item_availablebudget"];?></td>
<td>
<a href='edit_pr.php?pr_id=<?php echo $row["pr_id"];?>'>Edit</a> | <input type="button" onclick="window.open('print_pr.php?pr_id=<?php echo $row["pr_id"];?>','_blank','resizable=yes')" value='Print' />
</td>
</tr>
<?php
}
?>
<tr>
<td colspan="7"><?php
//Display the navigation
//echo $pager->renderFullNav();
echo '<div class="pager" >'.$pager->renderFullNav().'</div>';
?>
</td>
</tr>
</table>
</td>
</tr>
</table>
<?php require('../includes/footer.php');?>
----------------------------------------------------------------------
It's OK for insert data into table

But for update existing data it can't update data if it's more than two row in table item_request
Here the code below
------------------------------------------------------------------------

<?php require_once('../includes/connection.php');?>
<?php require_once('../includes/get_username.php');?>
<?php include('../includes/header.php');?>
<?php include('../sidebar.php'); ?>

<?php
ob_start();
date_default_timezone_set("Asia/Bangkok");
?>


<table id="contentbox">
<tr>
<td id="content">
<div class="name">Welcome, <b><?php echo $username; ?></b> | <a href="../logout.php">Logout</a></div>
<div class="label">Purchase Request Details</div>
<hr />
<form name="formsearch" method="post" onsubmit="return validateForm()" action="search_pr.php">
<table>
<tr>
<td class="search">Search for :</td>
<td><input type="text" name="search" size="40px" placeholder="Search here..." /></td>
<td><input type="submit" value="Search" style="cursor:pointer;"/></td>
</tr>
</table>
</form>
<?php

function valid($pr_id, $pr_date, $pr_requester, $pr_purpose, $error)
{
?>
<fieldset>
<legend><div class="legend"><b>Update purchase request details</b></div></legend>
<div id="edit_pr">
<form name="form1" method="post" action="">
<table>
<tr>
<td><input type="hidden" name="pr_id" value="<?php echo $pr_id; ?>"/></td>

<tr>
<td class="search">* fields are required</td>
</tr>
<tr>
<td class="search"> Request Dated:</td>
<td> <?php $formatted = date('d/M/Y', strtotime($pr_date)); echo $formatted;?> </td>
<td class="search"> Request Unit:</td>
<td size=> <?php echo $pr_requester; ?> </td>
</tr>
<tr>
<td class="search"> Requester: </td>
<td> <?php echo $pr_requester; ?> </td>
<td class="search"> Purpose: </td>
<td>
<textarea name="pr_purpose" rows="2" cols="50" placeholder="*Enter request purpose here..."><?php echo $pr_purpose; ?></textarea>
</td>
</table>




<table class="item_table" id="alternatecolor">
<tr>
<th>No</th>
<th>Check box</th>
<th>Item Description</th>
<th>Quantity</th>
<th>Unit (book, pc, etc)</th>
<th>Estimate UnitPrice</th>
<th>Budget Code</th>
<th>Activity</th>
<th>Available Budget</th>
</tr>


<?php
$sql = mysql_query("SELECT * FROM item_request WHERE item_prnumber=$_GET[pr_id]");


//$row = mysql_fetch_array($sql);


$sqlitemdescription=mysql_query("SELECT * FROM item_request WHERE item_prnumber=$_GET[pr_id]");
$ii=mysql_num_rows($sqlitemdescription);
echo $ii;



while($row = mysql_fetch_array($sql)){

//foreach($row['item_description'] as $i => $item_description){

// Get values from database.
?>

<tr>
<TD> 1 </TD>
<TD><INPUT type="checkbox" size="1px"name="chk"/></TD>
<td>
<input type="text" size="23px" name='item_description[]' placeholder="*description" value="<?php echo $row["item_description"][$ii];?>" />
</td>
<td>
<input type="text" size="2px" name='item_quantity[]'onkeyup="if (/\D/g.test(this.value)) this.value = this.value.replace(/\D/g,'')" placeholder="*quantity" value="<?php echo $row["item_quantity"];?>" />
</td>

<td>
<?php
$sql = mysql_query("select * from item_unit");
echo '<select name="item_unit[]" >';
echo '<option value="'.$row[item_unit].'">'.$row[item_unit].'</option>';
while($row1 = mysql_fetch_assoc($sql))
{
echo '<option value="'.$row1['unit_name'].'">'. $row1['unit_name'].'</option>';
}
echo'</select>';
?>
</td>

<td><input type="text" size="2px" name='item_estunitprice[]' placeholder="*Est price" value='<?php echo $row["item_estunitprice"];?>' />
</td>
<td>
<?php

$sql = mysql_query("select * from accode");
echo '<select name="account_number[]" >';
echo '<option value="'.$row[item_accode].'">'. $row['item_accode'].'</option>';
while($row2 = mysql_fetch_assoc($sql))
{
echo '<option value="'. $row2['account_number'].'">'. $row2['account_number'].'</option>';
}
echo'</select>';
?>
</td>
<td>
<input type="text" size="3px" name='item_activity[]' placeholder="*Enter activity" value='<?php echo $row["item_activity"];?>' />
</td>

<td>
<input type="text" size="5px" name='item_availablebudget[]' placeholder="Budget" value='<?php echo $row["item_availablebudget"];?>' /></td>
</tr>

























</table>

<?php
// }
}
?>
<INPUT type="button" value="Add Row" style="float: right"onclick="addRow()" />
<INPUT type="button" value="Delete Row" style="float: right"onclick="deleteRow()" /><br></br>





















<table>
</tr>
<td class="add">
<input type="submit" name="submit" style="cursor:pointer;" value="Update">
<a href="pr.php"><input type="Button" name="Btncancel" style="cursor:pointer;" value="Cancel"></a></td>
</tr>

</table>
</form>




</div>
</fieldset>
<?php

}

if (isset($_POST['submit']))
{
if (is_numeric($_POST['pr_id']))
{
$pr_id = $_GET['pr_id'];
//$pr_date = mysql_real_escape_string(htmlspecialchars($_POST['pr_date']));
//$pr_requester = mysql_real_escape_string(htmlspecialchars($_POST['pr_requester']));
$pr_purpose = mysql_real_escape_string(htmlspecialchars($_POST['pr_purpose']));

mysql_query("UPDATE purchase_request SET

pr_purpose='$pr_purpose'
WHERE pr_id='$pr_id'") or die(mysql_error());

header("Location: pr.php?attempt=success");

}
else
{
echo 'Error!';
}
}
else
{
if (isset($_GET['pr_id']) && is_numeric($_GET['pr_id']) && $_GET['pr_id'] > 0)
{
$pr_id = $_GET['pr_id'];
$result = mysql_query("SELECT * FROM purchase_request WHERE pr_id=$pr_id")
or die(mysql_error());
$row = mysql_fetch_array($result);

if($row)
{
$pr_date = $row['pr_date'];
$pr_requester = $row['pr_requester'];
$pr_unit = $row['pr_requester'];
$pr_purpose = $row['pr_purpose'];


valid($pr_id, $pr_date, $pr_requester, $pr_purpose,'');
}
else
{
echo "No results!";
}
}
else
{
echo 'Error!';
}
}
?>






























<hr />
<table class="inventory_table" id="alternatecolor">
<tr>
<th>Request Purpose</th>
<th>Item Description</th>
<th>Quantity</th>
<th>Unit</th>
<th>Estimate UnitPrice</th>
<th>Budget Code</th>
<th>Activity</th>
<th>Available Budget</th>
<th width="70">Actions</th>
</tr>
<?php
include('../includes/ps_pagination.php');
$sql = 'SELECT * FROM purchase_request, item_request WHERE purchase_request.pr_id=item_request.item_prnumber GROUP BY pr_id ORDER BY pr_id DESC';

//Create a PS_Pagination object
$pager = new PS_Pagination($conn, $sql, 15, 20);

//The paginate() function returns a mysql result set for the current page
$rs = $pager->paginate();

while($row = mysql_fetch_array($rs))
{
?>
<tr>
<td><?php echo $row["pr_purpose"];?></td>
<td><?php echo $row["item_description"];?></td>
<td><?php echo $row["item_quantity"];?></td>
<td><?php echo $row["item_unit"];?></td>
<td><?php echo $row["item_estunitprice"];?></td>
<td><?php echo $row["item_accode"];?></td>
<td><?php echo $row["item_activity"];?></td>
<td><?php echo '$',$row["item_availablebudget"];?></td>
<td>
<a href='edit_pr.php?pr_id=<?php echo $row["pr_id"];?>'>Edit</a> | <input type="button" onclick="window.open('print_pr.php?pr_id=<?php echo $row["pr_id"];?>','_blank','resizable=yes')" value='Print' />
</td>
</tr>
<?php
}
?>
<tr>
<td colspan="7"><?php
//Display the navigation
//echo $pager->renderFullNav();
echo '<div class="pager" >'.$pager->renderFullNav().'</div>';
?>
</td>
</tr>
</table>
</td>
</tr>
</table>
<?php require('../includes/footer.php');?>
---------------------------------------------------------
Pages: 1