[SOLVED] Update table

Bifter · October 28, 2008

Hiya all,

Struggling a bit here; would really appreciate a little help - recon you guessed that tho....

<?php
mysql_select_db("ipendpoi_Purchase", $conn);
$result = mysql_query("SELECT * FROM po ORDER BY number DESC");
echo "<table width='100%' border='1'>
<tr>
<th>Date</th>
<th>PO Number</th>
<th>Requester</th>
<th>Amount</th>
<th>Vendor</th>
<th>Department</th>
<th>End User</th>
<th>Invoiced</th>
</tr>";
$rowclass = 0;
while($row = mysql_fetch_array($result))
  {
  echo "<tr class = row" . $rowclass . ">";
  echo "<td>" . "<div align=\"center\">" . $row['date'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "<a href=\"upload_directory/Purchase-Order-" . $row['number'] . ".pdf\">" . $row['number'] . "</a>" . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['requester'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "£"  . $row['amount'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['vendor'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['department'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['End_User'] . "</div>" . "</td>";
  echo "</tr>";
  $rowclass = 1 - $rowclass;
  }
echo "</table>";mysql_close($conn);
?>

This basically displays a list of purchase orders, there are a couple of thing I need to add - with a little help from you guys:

1: Incorporate a tick box that will indicate whether that PO has a corresponding invoice, the SQL table has an invoiced Column.

2: Display the total of the Amount column.

Thanks in advance,

B.

MadTechie · October 28, 2008

How would you know whether that PO has a corresponding invoice ?

i assume a field called Invoiced is not null or something ?

do you have a quantity field, to workout the total amount ?

Bifter · October 28, 2008

thanks for replying, the user would tick the box to indicate this and the table column invoiced is updated with a yes...

Yes it would be the sum of the column Amount....

MadTechie · October 28, 2008

okay but how to i work out the total amount?

ie total amount = account X qty

as for the check box i assumed you have a unique ID as if your posting this back to update your need to know what records have been checked, bur for display it doesn't matter to much

<?php
mysql_select_db("ipendpoi_Purchase", $conn);
$result = mysql_query("SELECT * FROM po ORDER BY number DESC");
echo "<table width='100%' border='1'>
<tr>
<th>Date</th>
<th>PO Number</th>
<th>Requester</th>
<th>Amount</th>
<th>Vendor</th>
<th>Department</th>
<th>End User</th>
<th>Invoiced</th>
</tr>";
$rowclass = 0;
while($row = mysql_fetch_array($result))
  {
  echo "<tr class = row" . $rowclass . ">";
  echo "<td>" . "<div align=\"center\">" . $row['date'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "<a href=\"upload_directory/Purchase-Order-" . $row['number'] . ".pdf\">" . $row['number'] . "</a>" . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['requester'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "£"  . $row['amount'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['vendor'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['department'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['End_User'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">";
  /*if the value is "yes" then tick it*/
  $check = ($row['Invoiced'] == "yes")?"checked":"";
  /*I assume you have a unique ID called id*/
  echo "<input type=\"checkbox[".$row['id']."]\" name=\"Invoiced\" $check>";
  echo "</div>" . "</td>";
  echo "</tr>";
  $rowclass = 1 - $rowclass;
  }
echo "</table>";mysql_close($conn);
die;
?>

Bifter · October 28, 2008

I dont think I made myself very clear on this one - sorry, but I was looking for something along these lines:

<?
<style>
.rowgreen {
    background-color: #00FF00;
}
.rowred {
    background-color: #FF0000;
}
mysql_select_db("ipendpoi_Purchase", $conn);
$result = mysql_query("SELECT * FROM po ORDER BY number DESC");
echo "<table width='100%' border='1'>
<tr>
<th>Date</th>
<th>PO Number</th>
<th>Requester</th>
<th>Amount</th>
<th>Vendor</th>
<th>Department</th>
<th>End User</th>
<th>Invoiced</th>
</tr>";
function rowclass ( ) {
if ($row['invoiced'] == "Yes")
{return "green";}
else {return "red";}
}
while($row = mysql_fetch_array($result))
  {  
  echo "<tr class = row". rowclass () . ">";
  echo "<td>" . "<div align=\"center\">" . $row['date'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "<a href=\"upload_directory/Purchase-Order-" . $row['number'] . ".pdf\">" . $row['number'] . "</a>" . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['requester'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "£"  . $row['amount'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['vendor'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['department'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['End_User'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['invoiced'] . "<input type=\"checkbox\" name=\"checkbox\" id=\"checkbox\">";
  echo "<input type=\"submit\" name=\"Update\" id=\"Update\" value=\"Submit\">" . "</div>" . "</td>";
  echo "</tr>";
  }
echo "</table>";mysql_close($conn);
?>

So when the box is checked and update clicked the line

Bifter · October 28, 2008

The post submitted before I had finished:

<?php
<style>
.rowgreen {
    background-color: #00FF00;
}
.rowred {
    background-color: #FF0000;
}
mysql_select_db("ipendpoi_Purchase", $conn);
$result = mysql_query("SELECT * FROM po ORDER BY number DESC");
echo "<table width='100%' border='1'>
<tr>
<th>Date</th>
<th>PO Number</th>
<th>Requester</th>
<th>Amount</th>
<th>Vendor</th>
<th>Department</th>
<th>End User</th>
<th>Invoiced</th>
</tr>";
function rowclass ( ) {
if ($row['invoiced'] == "Yes")
{return "green";}
else {return "red";}
}
while($row = mysql_fetch_array($result))
  {  
  echo "<tr class = row". rowclass () . ">";
  echo "<td>" . "<div align=\"center\">" . $row['date'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "<a href=\"upload_directory/Purchase-Order-" . $row['number'] . ".pdf\">" . $row['number'] . "</a>" . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['requester'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "£"  . $row['amount'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['vendor'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['department'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['End_User'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['invoiced'] . "<input type=\"checkbox\" name=\"checkbox\" id=\"checkbox\">";
  echo "<input type=\"submit\" name=\"Update\" id=\"Update\" value=\"Submit\">" . "</div>" . "</td>";
  echo "</tr>";
  }
echo "</table>";mysql_close($conn);
?>

MadTechie · October 28, 2008

okay, but you will still need to change

<input type=\"checkbox\" name=\"checkbox\" id=\"checkbox\">

to

<input type=\"checkbox\" name=\"checkbox[]\" value=\"".$row['ID']."\" id=\"checkbox\">

then you can then do this

print_r($_POST['checkbox']);

to get the IDs of the records you ticked

Bifter · October 28, 2008

Thanks, would this work if I replaced 'ID' with 'number' as this is the PO number?

Also where would

print_r($_POST['checkbox']);

go?

MadTechie · October 28, 2008

Thanks, would this work if I replaced 'ID' with 'number' as this is the PO number?

Yeah that should be OK.. (i assume thats unique)

Also where would
print_r($_POST['checkbox']);
go?

thats just to output the select boxes so add it to the page your posting to

Bifter · October 28, 2008

sorry getting really confussed now....can I be cheeky and ask you to udate my code with how i would get the update button on each row to once click will add Yes to the invoiced column in the table???

MadTechie · October 28, 2008

BACKUP BACKUP your database

this should work but still backup first

i hope it makes sense

<style>
.rowgreen {
    background-color: #00FF00;
}
.rowred {
    background-color: #FF0000;
}
<?php
function rowclass ( )
{
if ($row['invoiced'] == "Yes")
{
	return "green";
}else{
	return "red";
}
}

mysql_select_db("ipendpoi_Purchase", $conn);
//1 or more ticked
if(count($_POST['invoiced']) > 0)
{
//Get the values and put them into a comma delimited string
$invoiced = implode(",",$_POST['invoiced']);
//update database
$result = mysql_query("UPDATE po SET invoiced ='Yes' WHERE number IN ($invoiced)");
echo "Updated";
}

$result = mysql_query("SELECT * FROM po ORDER BY number DESC");

echo "<table width='100%' border='1'>
<tr>
<th>Date</th>
<th>PO Number</th>
<th>Requester</th>
<th>Amount</th>
<th>Vendor</th>
<th>Department</th>
<th>End User</th>
<th>Invoiced</th>
</tr>";

echo "<form name=\"form1\" method=\"post\">";
while($row = mysql_fetch_array($result))
  {  
  echo "<tr class = row". rowclass () . ">";
  echo "<td>" . "<div align=\"center\">" . $row['date'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "<a href=\"upload_directory/Purchase-Order-" . $row['number'] . ".pdf\">" . $row['number'] . "</a>" . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['requester'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "£"  . $row['amount'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['vendor'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['department'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['End_User'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['invoiced'] . "<input type=\"checkbox\" name=\"invoiced[]\" value=\"".$row['number']."\" id=\"checkbox\">";
  echo "</tr>";
  }
echo "</table>";mysql_close($conn);
echo "<input type=\"submit\" name=\"Update\" id=\"Update\" value=\"Submit\">";
echo "</form>";
?>

EDIT: oops messed up the query (now fixed)

Bifter · October 28, 2008

Thank for that, doesn't update the SQL table tho ???

-- echo's "updated" with no errors --

Bifter · October 28, 2008

can you have 2 x $result ?

MadTechie · October 28, 2008

change

$result = mysql_query("UPDATE po SET invoiced ='Yes' WHERE number IN ($invoiced)");

to

$result = mysql_query("UPDATE po SET invoiced ='Yes' WHERE number IN ($invoiced)") or die(mysql_error());

lets see what error we get

dropfaith · October 28, 2008

try to echo the actual query see what its getting for info

echo $result;

echo this too it might not be getting the where statement

echo $invoiced;

Bifter · October 28, 2008

No error!!! check on PHPMyAdmin -not updating??!!

Bifter · October 28, 2008

echo $result;

= UpdatedResource id #6

echo $invoiced;

= 103-88,103-87,103-85 (these are the PO numbers)

MadTechie · October 28, 2008

ahh ok

change

$invoiced = implode(",",$_POST['invoiced']);

to

$invoiced = "'".implode("','",$_POST['invoiced'])."'";

i expected PO's like 1,2,3,40,59,69,77,84

Bifter · October 28, 2008

Very nice work, your gonna hate me for this tho; the colour isnt changing, permantly red?!?

MadTechie · October 28, 2008

okay change

i dumped that function as its not needed

<style>
.rowgreen {
    background-color: #00FF00;
}
.rowred {
    background-color: #FF0000;
}
<?php
mysql_select_db("ipendpoi_Purchase", $conn);
//1 or more ticked
if(count($_POST['invoiced']) > 0)
{
   //Get the values and put them into a comma delimited string
   $invoiced = "'".implode("','",$_POST['invoiced'])."'";
   //update database
   $result = mysql_query("UPDATE po SET invoiced ='Yes' WHERE number IN ($invoiced)");
   echo "Updated";
}

$result = mysql_query("SELECT * FROM po ORDER BY number DESC");

echo "<table width='100%' border='1'>
<tr>
<th>Date</th>
<th>PO Number</th>
<th>Requester</th>
<th>Amount</th>
<th>Vendor</th>
<th>Department</th>
<th>End User</th>
<th>Invoiced</th>
</tr>";

echo "<form name=\"form1\" method=\"post\">";
while($row = mysql_fetch_array($result))
  {
  $col = ($row['invoiced'] == "Yes")?"green":"red";
  echo "<tr class = \"row{$col}\" >";
  echo "<td>" . "<div align=\"center\">" . $row['date'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "<a href=\"upload_directory/Purchase-Order-" . $row['number'] . ".pdf\">" . $row['number'] . "</a>" . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['requester'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . "£"  . $row['amount'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['vendor'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['department'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['End_User'] . "</div>" . "</td>";
  echo "<td>" . "<div align=\"center\">" . $row['invoiced'] . "<input type=\"checkbox\" name=\"invoiced[]\" value=\"".$row['number']."\" id=\"checkbox\">";
  echo "</tr>";
  }
echo "</table>";
mysql_close($conn);
echo "<input type=\"submit\" name=\"Update\" id=\"Update\" value=\"Submit\">";
echo "</form>";


?>

Bifter · October 28, 2008

you are crazy good!!! thanks sooo much for your help....if I could buy you a drink I would.

Ta.

MadTechie · October 28, 2008

always happy to help

Sign In

[SOLVED] Update table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information