Your advice and opinions on a simple database application online. [SOLVED]

[datahell]

The charset is for the stored data, the collation is only for ordering and other string released functions.

Tables charset = utf-8
Collation: utf8_general_ci

I still advise you to develop an Elxis component instead of a standalone script...

[webgift]

Roubos, if you're familiar with Object oriented PHP programming, please take a look to the Elxis Framework. For Elxis CMS 4.x take a look to this article. There are so many methods that you can use ...

[ROUBOS]

Hi again,
I need advice on this code I've written.

I insert into an array the table content, and then I populate a html table using the array.
Now I want to do some calculations with the values of some <td> elements. The problem is that I'm trying to run the calculation (javascript) onfocus. It only works if I do it in order, and does not work when I randomly click on the element. Does not work when I click on it again once I've made changes.
Here is my PHP code:

Code: [Select]

<div style="float:left;"> <!-- DIV creating table and populating with 100 Series -->
<?php
$sql = "SELECT * FROM blocks_table WHERE series=&#39;100 Series&#39;";
$result = mysql_query($sql)or die(mysql_error());
$rowcounter=0;

echo "<table class=&#39;styled-table&#39;>";
echo "<tr><th>100 Series</th><th>Per Pallet</th><th>Quantity</th><th>Pallets</th><th>Extras</th></tr>";
while($row = mysql_fetch_array($result)){
$type100 = $row[&#39;type&#39;];
$perpallet100 = $row[&#39;per_pallet&#39;];
$rowcounter++;
// Now for each looped row
  echo "<tr><td>".$type100."</td>
<td><input type=&#39;text&#39; name=&#39;perpallet&#39; id=&#39;perpallet$rowcounter&#39; style=&#39;width:50px;&#39; value=&#39;".$perpallet100."&#39;/></td>
<td><input type=&#39;text&#39; name=&#39;quantity&#39; id=&#39;quantity$rowcounter&#39; style=&#39;width:50px;&#39;/></td>
<td><input type=&#39;text&#39; name=&#39;pallets&#39; id=&#39;pallets$rowcounter&#39; style=&#39;width:50px;&#39; onfocus=&#39;this.value=getTotal();&#39;/></td>
<td><input type=&#39;text&#39; name=&#39;extras&#39; style=&#39;width:50px;&#39;/></td>
</tr>";
} // End our while loop
echo "</table>";
?>

and here is the javascript:

Code: [Select]

<!-- calculation -->
<script type="text/javascript">
var kavli = 0;

function getTotal()
{
var total = 0;
kavli++;
var blockamount = 0;
var perpallet = 0;

blockamount = document.getElementById('quantity' + kavli);
perpallet = document.getElementById('perpallet' + kavli);

total = Math.floor(blockamount.value / perpallet.value);

return total;
}
</script>
<!-- end calculation -->

So a table row, shows a block-type, which has a per-pallet amount (how many blocks fit on a pallet), then I enter the Quantity of the blocks I want, and it should calculate the amount of pallets needed.

Simple really, but I cannot get it to work.

I think it does not work cause it's going through loops.

Any thoughts? An advice on how I should tackle this?

thanks for all your help.
I attached a screenshot of the table

EDIT: I used variables auto-increment to be able to give the html input types unique ids.
Works only if I enter an amount on the first field, then click on the next field. All in order.

When I randomly try to do it I get "0".

[datahell]

Never iterate though results like that. First collect the results, count them and then build the html.
Also your HTML sucks... Use double quotes for element attributes and add the "value" attribute in "text" elements (you have it only on the first element). Also you have multiple elements with the same name (use different IDs but "name" is the same). For compatibility with the standards these elements must be inside a FORM tag.


<td><input type="text" name="perpallet1" id="perpallet1" value="xxx" /></td>
<td><input type="text" name="quantity1" id="quantity1" value="xxx" /></td>
<td><input type="text" name="pallets1" id="pallets1" value="xxx" onfocus="getTotal(1);" /></td>
<td><input type="text" name="extras1" id="extras1" value="xxx" /></td>

<td><input type="text" name="perpallet2" id="perpallet2" value="xxx" /></td>
<td><input type="text" name="quantity2" id="quantity2" value="xxx" /></td>
<td><input type="text" name="pallets2" id="pallets2" value="xxx" onfocus="getTotal(2);" /></td>
<td><input type="text" name="extras2" id="extras2" value="xxx" /></td>

function getTotal(idx) {
   var ppidx = 'perpallet'+idx;
   var qidx = 'quantity'+idx;
   var xidx = 'pallets'+idx;
   if (!document.getElementById(pidx)) { return false; }
   if (!document.getElementById(qidx)) { return false; }
   if (!document.getElementById(xidx)) { return false; }
   var p = document.getElementById(pidx).value;
   if ((p == '') || !isNaN(p)) { return false; }
   var q = document.getElementById(qidx).value;
   q = parseInt(q, 10);
   var total = Math.ceil(q / p);
   document.getElementById(xidx).value = total;
}

[ROUBOS]

Hi,
thanks for the help and your time going through this.

The double quotes for element attributes where not used because it all sits inside a php echo and its the only way I could get it to work.
The name part of the elements and the value is a constant delete/rewrite because of my luck of php knowledge, trying to get it to work I keep deleting re-writing things. And they are all inside form tags. Sorry I did not copy and paste my entire code.


here is my entire page as it was when I posted it before:

Code: [Select]

<?php
// connect to the database
include(&#39;db.php&#39;)
?>
<html>
<head>
<!-- calculation -->
<script type="text/javascript">
var kavli = 0;

function getTotal()
{
var total = 0;
kavli++;
var blockamount = 0;
var perpallet = 0;

blockamount = document.getElementById('quantity' + kavli);
perpallet = document.getElementById('perpallet' + kavli);

total = Math.floor(blockamount.value / perpallet.value);

return total;
}
</script>
<!-- end calculation -->
<title>HB - Block Order Tracking System</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<img class="page-bg" src="page-bg.jpg">
<img class="header-img"src="header-bg.png">
<div id="wrap">
<center>
<form action="insert-order.php" method="post">
<table class="styled-table">
<tr>
<th>Order Date</th> <th>Company Name</th><th>Delivery Date</th>
</tr>
<tr>
<td><input type="date" name="orderdate"></td>
<td>
<?php
$query = mysql_query("SELECT company_name FROM client_table ORDER BY company_name");
echo "<select name=users>";
echo "<option value=blank selected></option>"; //for blank default option
while ($r = mysql_fetch_array($query))
{
$client = $r["company_name"];
echo "<option value=$client>$client</option>";
}
echo "</select>";
?>
</td>
<td><input type="date" name="deliverydate"></td>
</tr>
</table>
<br />
<div style="float:left;"> <!-- DIV creating table and populating with 100 Series -->
<?php
$sql = "SELECT * FROM blocks_table WHERE series=&#39;100 Series&#39;";
$result = mysql_query($sql)or die(mysql_error());
$rowcounter=0;

echo "<table class=&#39;styled-table&#39;>";
echo "<tr><th>100 Series</th><th>Per Pallet</th><th>Quantity</th><th>Pallets</th><th>Extras</th></tr>";
while($row = mysql_fetch_array($result)){
$type100 = $row[&#39;type&#39;];
$perpallet100 = $row[&#39;per_pallet&#39;];
$rowcounter++;
// Now for each looped row
  echo "<tr><td>".$type100."</td>
<td><input type=&#39;text&#39; name=&#39;perpallet&#39; id=&#39;perpallet$rowcounter&#39; style=&#39;width:50px;&#39; value=&#39;".$perpallet100."&#39;/></td>
<td><input type=&#39;text&#39; name=&#39;quantity&#39; id=&#39;quantity$rowcounter&#39; style=&#39;width:50px;&#39;/></td>
<td><input type=&#39;text&#39; name=&#39;pallets&#39; id=&#39;pallets$rowcounter&#39; style=&#39;width:50px;&#39; onfocus=&#39;this.value=getTotal();&#39;/></td>
<td><input type=&#39;text&#39; name=&#39;extras&#39; style=&#39;width:50px;&#39;/></td>
</tr>";
} // End our while loop
echo "</table>";
?>
</div>
</form>
</center>
</div>
</body>
</html>

Unfortunately I don't know php and trying to do this by google-ing and reading here and there.
 

[datahell]

Check this.

Code: [Select]

<?php
include(&#39;db.php&#39;)
?>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>HB - Block Order Tracking System</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
<script type="text/javascript">
function getTotal(idx) {
var ppidx = 'perpallet'+idx;
var qidx = 'quantity'+idx;
var xidx = 'pallets'+idx;
if (!document.getElementById(pidx)) { return false; }
if (!document.getElementById(qidx)) { return false; }
if (!document.getElementById(xidx)) { return false; }
var p = document.getElementById(pidx).value;
if ((p == '') || isNaN(p)) { return false; }
var q = document.getElementById(qidx).value;
q = parseInt(q, 10);
var total = Math.ceil(q / p);
document.getElementById(xidx).value = total;
}
</script>
</head>
<body>
<img class="page-bg" src="page-bg.jpg" alt="bg" />
<img class="header-img" src="header-bg.png" alt="header" />
<div id="wrap">
<form name="fmorder" action="insert-order.php" method="post">
<table class="styled-table">
<tr>
<th>Order Date</th><th>Company Name</th><th>Delivery Date</th>
</tr>
<tr>
<td><input type="text" name="orderdate" value="" /></td>
<td>
<?php
$query = mysql_query("SELECT company_name FROM client_table ORDER BY company_name");
echo &#39;<select name="users">&#39;;
echo &#39;<option value="" selected="selected"></option>&#39;;
while ($r = mysql_fetch_array($query)) {
echo &#39;<option value="&#39;.$r[&#39;company_name&#39;].&#39;">&#39;.$r[&#39;company_name&#39;]."</option>";
}
echo "</select>";
?>
</td>
<td><input type="text" name="deliverydate" value="" /></td>
</tr>
</table>
<br />
<div style="float:left;">
<?php
$sql = "SELECT * FROM blocks_table WHERE series=&#39;100 Series&#39;";
$result = mysql_query($sql)or die(mysql_error());
$rowcounter = 0;
echo &#39;<table class="styled-table">&#39;;
echo &#39;<tr><th>100 Series</th><th>Per Pallet</th><th>Quantity</th><th>Pallets</th><th>Extras</th></tr>&#39;;
while($row = mysql_fetch_array($result)){
$rowcounter++;
echo &#39;<tr><td>&#39;.$row[&#39;type&#39;].&#39;</td>
<td><input type="text" name="perpallet&#39;.$rowcounter.&#39;" id="perpallet&#39;.$rowcounter.&#39;" style="width:50px;" value="&#39;.$row[&#39;per_pallet&#39;].&#39;" /></td>
<td><input type="text" name="quantity&#39;.$rowcounter.&#39;" id="quantity&#39;.$rowcounter.&#39;" style="width:50px;" value="" /></td>
<td><input type="text" name="pallets&#39;.$rowcounter.&#39;" id="pallets&#39;.$rowcounter.&#39;" style="width:50px;" value="" onfocus="getTotal(&#39;.$rowcounter.&#39;);" /></td>
<td><input type="text" name="extras" value="" style="width:50px;" /></td>
</tr>&#39;;
}
echo &#39;</table>&#39;;
?>
</div>
</form>
</div>
</body>
</html>


[ROUBOS]

Hi, thanks for your reply and taking your time to look at this.
I've been away so I only got the chance to look at it again now.

It's not working at the moment. The code you've given me looks awesome. Been going through it to understand the logic and the way it flows.

The loop goes through and draws the table fine. But of-course it does not populate the "Pallets" field cause there is nothing in the "Quantity" field. And the onfocus does not work to display the total, since we're out of the loop once the page loads and cannot access the getTotal() function.
So I should try to draw the table with the loop, and then listen for the onfocus and call the getTotal function.

That's my understanding of it.

Thanks again


EDIT: NO, looking through the code it all seems fine. The loop is not the problem. It does draw the table giving unique names and ids. So the javascript function is called, not getting a result.
looks like the onfocus might not be working. reading online regarding this now, and people have been have strange results with "document.getElementById" too

[ROUBOS]

It works when I comment out: if ((p == '') || !isNaN(p)) { return false; }

Thanks Datahell for all your help. I really appreciate it.

It all works fine. I commented this line of code, and all works as it should be. I was trying to locate the problem using alert messages.

Thanks again

PS: should this line of code be written like this?:  if ((p == '') || isNaN(p)) { return false; } without the "!"

[datahell]

Yes, sorry, !isNAN is 2 negatives in a row which makes a logical positive. The correct is isNaN without the NOT symbol (!)

[ROUBOS]

Elxis is great and what makes it even better is the great support here in this forum.

Thanks once again Datahell.
Appreciate your help.

Ευχαριστώ πολύ.

[ROUBOS]

Hi once again,
I've populated a mysql table with form data before, but it was a single row at the time from a simple form.

The form we build here (from this post), will have to populate two different tables. (I've attached an image of the mysql tables).
The first table is straight forward to insert the data from the form, but the second table gets populate with many rows of data. (I've attached what the form looks like)

So I guess I need to loop through the html tables and insert rows into the mysql table. Or do I read until end of form?

Do I populate the mysql table with the data from the form with even the blank (zero) fields? In case we want to display the stored orders and edit them, we'll have to re-create this form with data from the table.

thanks... just thought I post here again while I'm trying to sort this out.

My code so far, the data that needs to go to the first table is easy to insert. Now for the rest :/ :

Code: [Select]

<?php
 // connect to the database
 include(&#39;db.php&#39;);

$insert="INSERT INTO order_table (company_name, delivery_address, order_date, delivery_date, status, hb_checked, loaded_by, pallets, extra_blocks, extra_pallets, total_pallets)
VALUES
(&#39;$_POST[companyname]&#39;,&#39;$_POST[deliveryaddress]&#39;,&#39;$_POST[orderdate]&#39;,&#39;$_POST[deliverydate]&#39;,&#39;$_POST[status]&#39;,&#39;$_POST[checked]&#39;,&#39;$_POST[loadedby]&#39;,&#39;$_POST[sumpallets]&#39;,&#39;$_POST[sumextras]&#39;,&#39;$_POST[makeup]&#39;,&#39;$_POST[grandtotal]&#39;)";
?>

<html>
<head>
<title>HB - Block Order Tracking System</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<img class="header-img"src="header-bg.png">
<div id="wrap">
<?php
if(mysql_query($insert)){
echo "Record Added";
}else{
die(&#39;Error: &#39; . mysql_error());
}
?>
<div id="centerDiv">
<ul class="centerUL">
<li><a href="index.html" class="button">Back</a></li>
</ul>
</div>
</div>
</div>
</body>
</html>
I will have to load all data from the html tables into an array? and use this array to populate the mysql table?

[ROUBOS]

GOT IT 

Code: [Select]



<?php
include(&#39;db.php&#39;); //connect

echo &#39;<pre style="text-align: left;">&#39; . print_r($_POST, true) . &#39;</pre>&#39;;

mysql_query("INSERT INTO order_table (company_name, delivery_address, order_date, delivery_date, status, hb_checked, loaded_by, pallets, extra_blocks, extra_pallets, total_pallets)
VALUES
(&#39;$_POST[companyname]&#39;,&#39;$_POST[deliveryaddress]&#39;,&#39;$_POST[orderdate]&#39;,&#39;$_POST[deliverydate]&#39;,&#39;$_POST[status]&#39;,&#39;$_POST[checked]&#39;,&#39;$_POST[loadedby]&#39;,&#39;$_POST[sumpallets]&#39;,&#39;$_POST[sumextras]&#39;,&#39;$_POST[makeup]&#39;,&#39;$_POST[grandtotal]&#39;)");

$currentid = mysql_insert_id();

foreach($_POST[&#39;100type&#39;] AS $key=>$value) {
$sql = "INSERT INTO order_details_table
(order_id, block_type, block_quantity, block_pallets, block_extras)
VALUES (&#39;$currentid&#39;, &#39;{$_POST[&#39;100type&#39;][$key]}&#39;,&#39;{$_POST[&#39;100quantity&#39;][$key]}&#39;,&#39;{$_POST[&#39;100pallets&#39;][$key]}&#39;,&#39;{$_POST[&#39;100extras&#39;][$key]}&#39;)";

$result = mysql_query($sql) or die(&#39;Could not insert data&#39;);
}

?>

now need to work on security in all my code