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

[ROUBOS]

Hi,
I want to create a simple database (3 or 4 tables), and I want it to be online with user login so I thought the use of Elxis. First can it be done? And how should I go about achieving this.
Here is what I need to do.

I need to created an order tracking system. OK, so a Client's table to store client info. A product table. An order table.  And I need to have forms for the user to input a client, and then to place an order. And print some reports.
This is what happens.
The database user, will enter a new client into the database. Then in the order form he'll pick the client's name. He will pick from a drop-down list the product series, which filters and displays the product type. Then enter the amount. The program will calculate the amount of pallets required (depending on product series and type, different amount per pallet), and display that amount. The order can have a processed or delivered flag. Delivery dates etc etc. Print reports on today's orders etc. And just browse orders and change details as required.
So it is simple as a thought. Trying to set it up in Access 2010 now to see how it will work, but putting it online is a different story. Need to be able to have a user login to access this, so I thought that I could use an Elxis site.

Any thoughts, ideas, recommendations on how to tackle this will be appreciate. Is there an Elxis extension that could make my life easier?

Thanks in advance

[datahell]

CMSs in general, and in particularly Elxis CMS, have a big advantage: they provide you a full working platform pre-built so you dont have to re-invent the wheel. By using Elxis you can focus only on your application without caring on how to connect to the database, provide access, etc. My proposal is to use the Elxis database and create a small component for the application interface. A component can be very simple and if it is only for your own use you dont have to build fancy things. Off course you need some php knowledge but it is much less that the knowledge you would need if you build that application outside Elxis as a standalone one.

To get start fast follow these steps:
1. Go to Elxis database via phpmyadmin and create your tables. elx_mytable, ....
2. Go to table elx_components and add a new entry for your component (see existing components as samples)
3. Go to table elx_acl and add 2 entries for your component (see existing components as samples)
4. Go to folder components and create a sub folder with the name of your component (eg. com_track)
5. Create these files:
components/com_track/track.php
components/com_track/track.xml
These are the absolutely requires files a component may have.

Your component will be accesible like that: http://www.example.com/track/

6. Your component will have some tasks, like the form display and submission, view/print report, etc. See how other component are built as samples and ask here if you need help.

[ROUBOS]

thanks for the reply.

I've never coded in php. Found some easy to follow tutorials on creating html forms with php to enter data into the database and to view records.
How hard is it to create calculated fields (calculate depending on drop box selection and populate a text box with the result.). And how hard to populate one dropbox, depending on the selection of another dropbox.?

[datahell]

You can not develop any web application without knowing a programming language like PHP.
Chained select boxes can be created using javascript. You can use AJAX or not, this depends on the number of combinations between the 2 select boxes.

I will write you a quick sample code how to do it using simple javascript (The AJAX method is far more complex and I can't write such example here).
Select box "country" list countries and select box "cities" list cities.
When you change the country (onchange event) the loadcities javascript function is executed and loads the corresponding cities for the selected country.

The select boxes (Italy is selected by default)
<select name="country" id="country" onchange="loadcities();">
<option value="it" selected="selected">Italy</option>
<option value="gr">Greece</option>
</select>
<select name="cities" id="cities">
<option value="Milano" selected="selected">Milano</option>
<option value="Rome">Rome</option>
<option value="Torino">Torino</option>
</select>

The javascript (I use the DOM method for cross-browser compatibility)
function loadcities() {
      var cObj = document.getElementById('country');
      var pObj = document.getElementById('cities');
      for (var i= pObj.length; i > 0; i--) { pObj.remove(i-1); }
      if (cObj.options[cObj.selectedIndex].value == 'gr') {
            var option=document.createElement("option");
            option.text="Athens";
            option.value="Athens";
            pObj.add(option, null);
            option.text="Thessaloniki";
            option.value="Thessaloniki";
            pObj.add(option, null);
            option.text="Patra";
            option.value="Patra";
            pObj.add(option, null);
     } else {
            var option=document.createElement("option");
            option.text="Milano";
            option.value="Milano";
            pObj.add(option, null);
            option.text="Rome";
            option.value="Rome";
            pObj.add(option, null);
            option.text="Torino";
            option.value="Torino";
            pObj.add(option, null);
    }
    pObj.selectedIndex = 0;
}

Note
The cities can be put in an array and iterate through the array. You can use different arrays for each language, and in general improve the above script a lot.

[ROUBOS]

Thanks for your replies. Need your help with something here now. I managed to get things started using MySQL, PHP. (XAMPP locally, hpMyAdmin)
I created a database with tables, and html forms to populate the tables using php mysql.

The problem I'm having is in the relationships between tables.
I have 2 tables that look like this:

tblBlockSeries
~~~~~~~~~~~~~~~~~~~~~~
SeriesID (key, auto increment)
BlockSeries (varchar)

and

tblBlockType
~~~~~~~~~~~~~~~~~~~~~
TypeID (key, auto increment)
SeriesID (the idea was to use this to join the two tables)
BlockType (VARCHAR)
BlocksPerPallet (INT)

So I populated the table tblBlockSeries.
Trying to populate the table tblBLockType I have created a html form. Managed to populate a dropdown box with rows form the BlockSeries of the table tblBlockSeries. I want to select the BlockSeries from the dropdown box, and type in the fields for BlockType and BlocksPerPallet.

I cannot get the relationship right, so that when I choose a BlockSeries, the SeriesID to populate the Series ID of the tblBLockType table. Since One BlockSeries can have many BlockTypes. (one to many relationship.)

I hope I'm making sense.

Here is my code for the form to populate the table tblBlockType:

Code: [Select]

<html>
<body>

<form action="insert-blocktype.php" method="post">
<table>
<tr>
<td>Block Series:</td>
<td>
<?php
$link = mysql_connect("localhost", "db_user", "password");
mysql_select_db("my_db", $link);

$query="SELECT SeriesID,BlockSeries FROM tblblockseries";

$result = mysql_query ($query);
echo "<select name=blockseries=&#39;&#39;>Block Series</option>";

while($nt=mysql_fetch_array($result)){
echo "<option value=$nt[SeriesID]>$nt[BlockSeries]</option>";
}
echo "</select>";
?>
</td>
</tr>
<tr>
<td>Block Type:</td>
<td><input type="text" name="blocktype"></td>
</tr>
<tr>
<td>Blocks Per Pallet:</td>
<td><input type="text" name="blocksperpallet"></td>
</tr>
<tr>
<td><input type="submit"></td>
</tr>
</form>

</body>
</html>
and here is the code for the insertion of the data into the table:

Code: [Select]

<?php
$link = mysql_connect(&#39;localhost&#39;, &#39;db_user&#39;, &#39;password&#39;);
if (!$link) {
    die(&#39;Could not connect: &#39; . mysql_error());
}
mysql_select_db("my_db", $link); 

$insert="INSERT INTO tblblocktype (BlockType, BlocksPerPallet)
VALUES
(&#39;$_POST[blocktype]&#39;,&#39;$_POST[blocksperpallet]&#39;)";


if(mysql_query($insert,$link)){
echo "Record added";
}else{
die(&#39;Error: &#39; . mysql_error());
}
mysql_close($link);
header(&#39;Location: &#39; . $_SERVER[&#39;HTTP_REFERER&#39;]);
?>
The SeriesID field in the tblBlockType table, should be populated based on the BlockSeries selection.

Isn't that how it should be working? Is my understanding of relationships wrong? Is that how keys and foreign keys work?
The SeriesID field in both tables should have the same value in order for the tables to be joined?

I'm using phpMyAdmin as a database tool.

I appreciate your help.

[ROUBOS]

I see all these attributes in phpMyAdmin like Index. An not sure how to set them up properly

There has to be something I'm not doing right in setting up a foreign key, and all those cascade etc options

[datahell]

Your naming is a little bit confusing.
The first thing to do is to focus on your target, what you want to do? Create 2 chained select boxes, right? This is your target.
I personally think of the database as a data store. I don't put much login on it, I prefer using php for that. Off course you need a common column in the 2 tables to make the join but this join can be done outside the database. How? Let's see.

Execute 2 queries and get the results as arrays.
The first array ($series) will have all the entries from table "tblBlockSeries" and the second ($types) from "tblBlockType".
$rows is an array we will create containing our final result.

Code: [Select]

$rows = array();
if ($series) {
//iterate through $series
foreach ($series as $ser) {
$id = $ser['SeriesID'];
$rows[$id] = array(
'BlockSeries' => $ser['BlockSeries'],
//types will contain the entries from the second db table
'types' => array()
);
}
//iterate through $types
if ($types) {
foreach ($types as $type) {
$id = $type['SeriesID'];
if (!isset($rows[$id])) { continue; } //wrong entry, dont use it
$rows[$id]['types'][] = array(
'BlockType' => $type['BlockType'],
'BlocksPerPallet' => $type['BlocksPerPallet']
);
}
    }
}
$rows is a multilevel array containing your final result. The 'types' array contains the "BlockTypes" for each "Block series".

Recommendation
Use lower case names for your db columns. This way you reduce a lot typing errors. PHP is case sensitive.

[ROUBOS]

kind of lost now.
I want to populate the second table.
The first table is populated with the series, and the second table I want to populate with types. One Series has many types.
Check out my tables screenshot.
Now the form I've created, populates a combo-box with the series from the series table, and I type in the other fields.

With a correct relationship setup, shouldn't the second table be populated with the id from the first table?
 

[datahell]

The solution to your problem is what I already wrote you. It is simple, an array containing the combined results that can be easily handled via php to generate the required html and javascript afterwards.

To answer your question: To join the 2 tables execute the query below.
SELECT a.*, b.BlockSeries FROM tblBlockType a INNER JOIN tblBlockSeries b ON b.SeriesID = a.SeriesID;

[ROUBOS]

OK,
I dropped those tables and started again. Re-created them and now the relationship seems to be fine. When I input data from within phpMyAdmin it works (looks like it) so I don't mind about this.
I guess I should ignore relationships and just use queries with php to get the data out and in the tables.

I've created an Order_Table and I need to create an order-form. Now this form requires the user to select BlockSeries with a dropdown box, which should populate another dropdown for BlockType depending on the BLockSeries selected.  (Isn't that what you pointed out? I'm trying to read your code  )

Then populate the order-table.
Next create a report showing all orders from orders table, with options to edit or delete orders.

I'll get there eventually.

[ROUBOS]

The code you've given me creates two arrays with the data of both tables.
How do I populate a dropdown-box with data from the first array,
and then populate another dropdown-box depending on the first option.

Populate the second dropdown box block-type where series-id is the same as the series-id of the first dropdown box selection?

[datahell]

The code I wrote you generates 1 array ($rows), not 2. Use it to generate HTML and javascript as on my previous post.

Example how to make html from $rows:

<select name="sieries" id="sieries" onchange="loadTypes();">
<?php
$selected = 1; //the default seriesID
foreach ($rows as $id => $row) {
    $sel = ($id == $selected) ? ' selected="selected"' : '';
    echo '<option value="'.$id.'"'. $sel.'>'.$row['BlockSeries'].'</option>';
?>
</select>
<select name="types" id="types">
<?php
if ($rows[$selected]['types']) {
   foreach ($rows[$selected]['types'] as $type) {
      echo '<option value="'.$type['BlockType'].'">'.$type['BlockType'].' '.$type['BlocksPerPallet'].'</option>';
  }
}
?>
</select>

[ROUBOS]

Thanks for your help.
You're right, needed some javascript with php to code this.

I managed to do it all ok. Now it all works.

The problem I'm having is that when I submit an empty form, it creates an empty entry in the database.
How can I simply test and make sure all fields have value in them?

also looked at this: http://www.blueicestudios.com/chained-select-boxes-using-php-mysql-ajax/

[datahell]

Once more the golden rule:
NEVER, EVER, USE DATA DERIVED FROM THE USER WITHOUT VALIDATING THEM FIRST.

You should filter and validate the data before doing anything else with them. Only if they are valid to insert them into the database.
I strongly advise you to follow be recommendation regarding filtering as you don't use PDO and you are not aware of security issues.

In a simple form, filtering/validating is like this (filter_input documentation, validate and sanitize filters).

$mystring = trim(filter_input(INPUT_POST, 'mystring', FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW));
$myint = isset($_POST['myint']) ? (int)$_POST['myint'] : -1;

if (($mystring != '') && ($myint > -1)) {
    //insert data into the database (use $mystring and $myint, not the original values from $_POST !)
}

Depending on the data you wait to be submitted you validate the form fields respectively. For instance in the above example $myint is an integer with values 0, 1, 2, .... If you want to allow only values between 2 and 5 you will change the check to this:
if (($mystring != '') && ($myint > 1) && ($myint < 6)) {

You must be careful with text and strings.
If you want only the latin ascii chars you can strip out the HIGH ASCII chars too (dont use it on unicode strings):
$mystring = trim(filter_input(INPUT_POST, 'mystring', FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH));

trim function is not unicode aware. You need a function like Elxis 4.x eUTF::trim to work with utf-8 encoded strings.
If you want $string to have at least 3 chars your validation should change to this:
if (($mystring != '') && (strlen($mystring) > 2) ....
strlen is also not utf-8 friendly. See Elxis 4.x eUTF::strlen

If you accept quotes in strings you must use addslashes, if in your server magic slashes are disabled, to escape them. Else you will be hacked! If you dont accept single/double quotes you should remove them with preg_replace first. backslashes, and characters like the following are also very dangerous: #, *, `, %

Some fields have only specific values. E.g. values from a select box can not be outside a range. So, there are safer ways to validate these.

Example:
$valid_colours = array('red', 'black', 'blue', 'purple');
if (in_array($colour, $valid_colours)) {
     //$ colour contains an acceptable value
} else {
    //$color contains a bad value
}

Final note
Be careful. Web applications built by amateurs, and especially outside a good framework such as Elxis, are most likely to be hacked... You must be very careful how you handle the user submitted data. This is why I initially adviced you to built an Elxis component and not a standalone application. Elxis includes already all the tools to handle the data easy and safe. Now you have to build these tools by yourself without having good php knowledge...

[ROUBOS]

thanks for your all your help.
I will look into the security issues and the data validation. I'm a noob when it comes to php so yes it is a problem.
I also created my tables with utf8_general_ci Collation. Shouldn't have since it is not multilingual. I never knew that it could cause such problems. (Being a HTML/CSS, Photoshop user).

What is MySQL's default collation? UTF8 is recommended right? Or go for a latin one? (Only english is used)

I read that latin1_swedish is the default so changed to that