[SOLVED] Update table

[Bifter]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

 

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

[Bifter]

can you have 2 x $result ?

[MadTechie]

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]

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]

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

[Bifter]

echo $result;

= UpdatedResource id #6

echo $invoiced;

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

[MadTechie]

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]

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

[MadTechie]

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]

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

 

Ta.

[MadTechie]

always happy to help