Create custom Pages with data from the Database

Permalink
Hello all,

I am trying to create a way to show items from a database table on different pages. I've tried it with blocks, where I added 3 filtering options through a select option, but it didn't really work out as I wanted as the AJAX never returned the actual data from the database.

The Idea is to have 3 filtering options, whereby these options are rows from three different database tables - i.e. category, range and collection. When I select these three values and hit on Submit I would like to get a list of the items from the main table (lets say articles), where the articles are filtered out by the 3 filtering options mentioned above ( category, range and collection).

Can anyone suggest me a stable and robust way of creating what I've planed, as I searched a lot and all I found was just partially what I was looking for. Maybe someone already did something similar and could give me some hints, or someone has a good idea of how he would do it and would share it.

Thank you all for you time!

greetings

brance
View Replies:
JohntheFish replied on at Permalink Reply
JohntheFish
How is the data being added to the database and which tables?
brance replied on at Permalink Reply
brance
Hi JohntheFish,

well the Tables are being added through an SQL Dump, so simply imported in the concrete5 database. There are 4 tables that are being used:
- articles (holds the id, name of the article, the key values for the other three tables)
- collections (hold the collection_id and the collection name)
- productranges (holds the product range name)
- categories (holds the category name of the product)

Now when I select all the key values from the collections, productranges and categories table I would like that the page shows a list of the related articles from the articles table. So lets say I've selected category "rings", ranges "accessoires", collection "Limited Edition", then the page should display all the articles that would return a query where category = rings, range = accessoires and collection = limited edition.

I hope you can understand what I've meant by describing this.

Thank you very much,
greetings
DreamMedia replied on at Permalink Reply
DreamMedia
Hi,

sounds for me that you already doing the right thing by creating a block which offers the filtering options and then retrieves the data with an ajax call to the backend.

I think your problem is that eather your SQL query is wrong and therefore returns the wrong data, you handle the result of the query in a wrong way or your ajax call itself is setup up in an incorrect way.

I would start with just sending back the ajax data from the backend which you send to the backend to see if the ajax call works in the first place.
Then create a result object in the backend "by hand" without any DB calls. Structure it the way it should look like with a successful DB call. Send it back to the frontend and see if that works.
If this all passed, eather you SQL query is the problem or you creating your result object in a wrong way.

Hope that helps ...
brance replied on at Permalink Reply
brance
Hi DreamMedia,

well as I said I tried to do it over AJAX, but the response was always empty, even when I checked in the browser inspector under Network, the request header had the correct values, but the response header of the ajax file was always empty.

<div id="dom-target" style="display: none;">
   <?php
      $tools_url = Loader::helper('concrete/urls')->getToolsURL('karussell_query'); //this file has the AJAX function
   ?>
   </div>
<script type="text/javascript">
   function getValues() {
      var myData = "<?php echo $tools_url; ?>";
           var kollektion = jQuery("#kollektion_select").val(); //these are the select values for the filters
           var productrange = jQuery("#product_ranges").val();
           var categories = jQuery("#categories_select").val();
           $.ajax({
               type: "POST",
               url: myData,
               dataType: 'json',


And here is the karussell_query.php file, that holds the actual AJAX:

<?php defined('C5_EXECUTE') or die("Access Denied.");
$db = Loader::db();
$collection = $_POST['pkoll'];
$productrange = $_POST['prange'];
$productcategory = $_POST['pcat'];
$results = $db->GetAll("select * from articles where WEB_RANGE='".$productrange."'"); //just for testing purposes I chose to only filter by productrange
return $results;
exit;
?>


I'm no expert with AJAX, but I've already made some little projects with AJAX where everything worked fine, but then I just needed to write data to a database, now I have to read it from the database. Maybe I am doing something wrong in the code?

thanks
JohntheFish replied on at Permalink Reply
JohntheFish
@DreamMedia has recommended the same diagnostic process as I would have suggested. Test the ajax with simple return. Test the sql query without ajax. Then bring them together.

You may find these howtos provide some ideas
http://www.concrete5.org/documentation/how-tos/developers/concrete5...
http://www.concrete5.org/documentation/how-tos/developers/some-tric...
http://www.concrete5.org/documentation/how-tos/developers/ajax-less...

Looking at your code snippets,
If you are replacing an html table, the jQuery load() method cuts a lot of the possible glitches from ajax calls by simply loading an html fragment.

If you are loading json data, the c5 ajax helper shortens the work involved (not at the same time as load() as that expects html)

In your database call, you are best using a ? placeholder and a parameters array rather than building text into the query, especially when data comes from a browser and leaves you open to sql injection.

Once you have it working, you should also add a validation token as per
http://www.concrete5.org/documentation/how-tos/developers/use-token...

If your table is not too big (up to a few hundred items total), another approach would be to load it all and then use a jQuery table sort/filter widget.

Yet another approach is to use my Blocks By Ajax for the ajax side of it. You could then write a simple form to trigger an ajax refresh with data, and a simple block that just shows data according to the post request (wrapped in a blocks by ajax template). (and most of that could also be built without writing php by adding Magic Data)
JohntheFish replied on at Permalink Reply
JohntheFish
Looking again,

Your ajax request is expecting an html fragment.

Your tool is returning a raw php variable.

What the tool should be doing is echoing html. An easy test is to dump the variable as per the fragments in the php diagnostic howto.
brance replied on at Permalink Reply
brance
Hi JohntheFish,

thanks for the explanation. I've managed to get my AJAX working with a simple return. Well not the question is "Is it possible to pull DB rows from the AJAX call and use them to be displayed on some page where that block is being added." So that when I select my filter options and click on i.e. "Update list" or "Query" I get a list of the related items in the Add Block window, and if everything is fine I click on the blue "Add" to add the block to the page.

To answer one of your thoughts, there can't be an mysql injection as the Block is being added by administrators and there is no search/filter option available at the frontend, just for the backend user.

thanks in advance,
greetings
JohntheFish replied on at Permalink Reply
JohntheFish
Calling a tool, you should be able to run similar ajax calls in the block add/edit.

The main obstacle is that during add a block instance does not yet exist, so there is no block ID.

Such limitations are demonstrated in the AJAX Lessons howto/addon.