|
How to create a dynamic query string 11 Years, 4 Months ago
|
Karma: 0
|
Hi, I've purchased Ari Data Tables and everything works just fine as it normally should. But I need to do something that looks like this module isn't able to do.
I've created a customized php code that creates a form with four select lists.
If the user doesn't touch the forms on the page will get shown the entire table from the database. When the user selects something in the form, only the data that he has chosen gets viewed in the table, and this has to be done dynamically.
To make you understand what I've done I'll show you the code of my php script and what I do insert as a query in ARI Data Tables:
| Code: |
<?php
if (isset($_REQUEST['submit']) && ($_REQUEST['submit']=="Invia")){
if (isset($_REQUEST['casa_di_produzione']) && ($_REQUEST['casa_di_produzione']!="")){
$_REQUEST['query']="WHERE casa_product LIKE '%".$_REQUEST['casa_di_produzione']."%' ";
} else {
$_REQUEST['query']="";
}
if (isset($_REQUEST['region']) && ($_REQUEST['region']!="")){
if ($_REQUEST['query']==""){
$_REQUEST['query']="WHERE region_product LIKE '%".$_REQUEST['region']."%' ";
}else{
$_REQUEST['query'].="AND region_product LIKE '%".$_REQUEST['region']."%' ";
}
}
if (isset($_REQUEST['type']) && ($_REQUEST['type']!="")){
if ($_REQUEST['query']==""){
$_REQUEST['query']="WHERE type_product LIKE '%".$_REQUEST['type']."%' ";
}else{
$_REQUEST['query'].="AND type_product LIKE '%".$_REQUEST['type']."%' ";
}
}
if (isset($_REQUEST['year']) && ($_REQUEST['year']!="")){
if ($_REQUEST['query']==""){
$_REQUEST['query']="WHERE year_product='".$_REQUEST['year']."' ";
}else{
$_REQUEST['query'].="AND year_product='".$_REQUEST['year']."' ";
}
}
if (isset($_REQUEST['denomination']) && ($_REQUEST['denomination']!="")){
if ($_REQUEST['query']==""){
$_REQUEST['query']="WHERE denominazione_product='".$_REQUEST['denomination']."' ";
}else{
$_REQUEST['query'].="AND denominazione_product='".$_REQUEST['denomination']."' ";
}
}
}else{
$_REQUEST['query']="";
}
?>
<table border="0" cellspacing="0" cellpadding="2">
<form action="array.php" method="POST">
<tr>
<td> <fieldset>
<legend>Case di produzione</legend>
<select name="casa_di_produzione">
<?php
$connessione=mysql_connect("localhost","user1","");
mysql_select_db("vini",$connessione);
$query="SELECT * FROM casa_di_produzione";
$result=mysql_query($query);
$count=mysql_num_rows($result);
echo '<option value="" selected="selected"><strong>--Select The Producer--</strong></option>';
$i=0;
while ($i<$count) {
$name_casa=mysql_result($result,$i,"name_casa");
echo '<option value="'.$name_casa.'">'.$name_casa.'</option>';
$i++;
}
?>
</select>
</fieldset></td>
<td> <fieldset>
<legend>Region</legend>
<select name="region">
<?php
$query="SELECT * FROM region";
$result=mysql_query($query);
$count=mysql_num_rows($result);
$i=0;
echo '<option value="" selected="selected"><bold>-- Select The Region --</bold></option>';
while ($i<$count) {
$region_name=mysql_result($result,$i,"region");
echo '<option value="'.$region_name.'">'.$region_name.'</option>';
$i++;
}
?>
</select>
</fieldset>
</td>
<td>
<fieldset>
<legend>Type of Wine</legend>
<select name="type">
<?php
$query="SELECT * FROM type_of_wine";
$result=mysql_query($query);
$count=mysql_num_rows($result);
$i=0;
echo '<option value="" selected="selected">-- Select Type --</option>';
while ($i<$count) {
$type=mysql_result($result,$i,"type");
echo'<option value="'.$type.'">'.$type.'</option>';
$i++;
}
?>
</select>
</fieldset>
</td>
<td>
<fieldset>
<legend>Year</legend>
<select name="year">
<?php
$query="SELECT * FROM year";
$result=mysql_query($query);
$count=mysql_num_rows($result);
$i=0;
echo '<option value="" selected="selected">-- Select Year --</option>';
while ($i<$count) {
$year=mysql_result($result,$i,"year");
echo'<option value="'.$year.'">'.$year.'</option>';
$i++;
}
?>
</select>
</fieldset>
</td>
<td>
<fieldset>
<legend>Denomination</legend>
<select name="denomination">
<?php
$query="SELECT * FROM denominazione";
$result=mysql_query($query);
$count=mysql_num_rows($result);
$i=0;
echo '<option value="" selected="selected">-- Select denom. --</option>';
while ($i<$count) {
$denomination=mysql_result($result,$i,"denominazione");
echo'<option value="'.$denomination.'">'.$denomination.'</option>';
$i++;
}
mysql_close();
?>
</select>
</fieldset>
</td>
</tr>
<tr>
<td><input name="submit" type="submit" value="Invia"></td>
<td> </td>
</tr>
</form>
</table>
|
This code loads up in a module inserted in another module where I load ARI Data Tables Module. In the SQL query field in ARI Data Tables I just insert this query:
| Code: |
SELECT name_product, casa_product, region_product, type_product, alcohol_product, denominazione_product, capacity, price_product FROM wine {$REQUEST:query}
|
This query generates an error because the {$REQUEST:query} variable its included in quotes so the query doesn't get recognized.
Can you give a hand to solve this problem? |
|
|
|
|
|
|
Re:How to create a dynamic query string 11 Years, 4 Months ago
|
Karma: 747
|
Hello,
If you don't want to quote request variables, open <joomla_directory>\modules\mod_aridatatables\includes\kernel\DataTables\Models\class.DataTablesDataSqlModel.php file, find "function prepareQuery($query)" method and remove the following code:
| Code: |
AriArrayHelper::walkRecursive($request, array(&$db, 'Quote'));
|
Regards,
ARI Soft |
|
|
|
|
|
|
Re:How to create a dynamic query string 11 Years, 4 Months ago
|
Karma: 0
|
Hello again, thanks for the Replay, I have done what you told me in the last post, now the SQL query debug system doesn't give me errors anymore, but doesn't apply the query. Let's say, if I insert in the ARI Data Tables module this query string:
| Code: |
SELECT name_product, casa_product, region_product, type_product, alcohol_product, denominazione_product, capacity, price_product FROM wine WHERE casa_product LIKE '%Abbazia%'
|
It does what it suppose to do.
But if I insert this other query string:
| Code: |
SELECT name_product, casa_product, region_product, type_product, alcohol_product, denominazione_product, capacity, price_product FROM wine {$REQUEST:query}
|
The debug system writes exactly what I espect depending on the selected option in the form:
| Code: |
(mysql): SELECT name_product, casa_product, region_product, type_product, alcohol_product, denominazione_product, capacity, price_product FROM wine WHERE region_product LIKE '%Lazio%' AND type_product LIKE '%White%' LIMIT 0,1
Error (0):
|
But the table that gets printed on video is with all entries, so it's like no filter gets considered in the WHERE statement.
Do you have any clues about it.
Thank you in advance |
|
|
|
|
|
|
Re:How to create a dynamic query string 11 Years, 4 Months ago
|
Karma: 747
|
Could you send screenshot(s) of module settings page and SQL dump of "wine" table by email so we can investigate the problem or provide a link to a page where we can see the problem and a temporary access to your J! backend?
Regards,
ARI Soft |
|
|
|
|
|
|
Re:How to create a dynamic query string 11 Years, 4 Months ago
|
Karma: 0
|
|
I'll just post this to inform who has the same problem, that the problem was given by the fact that my custom php code didn't get read when I head server side pagination option enabled in ari data tables module.
Thanks a lot to ARI Soft assistance service,
very quick and professional
|
|
|
|
|
|
|
Re:How to create a dynamic query string 11 Years, 2 Months ago
|
Karma: 0
|
Hi -
I'm trying to do something similar in as much as I have created a custom form with three select lists CITY , CUISINE, PRICERANGE.
The select lists retrieve data from a table with let's say 300 rows.
What I Would like to do is as follows:
1, If the user doesn't select anything from the dropdowns and just clicks submit button the user will be shown the entire 300 rows from the table.
2. If the user selects from the CITY list only, he will get all rows from that match his CITY selection no matter the CUISINE or PRICERANGE.
3. The same goes for CUISINE and PRICERANGE in as much Italian as CUISINE he will get all rows that meet the Italian CUISINE regardless of what CITY or PRICERANGE.
4. If user were to select Italian from CUISINE and Under $15 from PRICERANGE rows that met this selection criteria regardless of what City.
When the user selects something in the form, only the data that he has chosen gets viewed in the table.
You can go to palmsprings-mobile.com/index.php/portfolio/video-portfolio to get a better idea of my goals.
This is the query I am currently using but generates error 500:
SELECT
CompanyName, City, WEB
FROM sheet4
WHERE City = {$REQUEST:City}
AND
cuisine = {$REQUEST:cuisine}
AND
price = {$REQUEST:price}
I hope I have explained adequately what I am trying to accomplish.
Thanking you in advance for any support you may provide towards the resolution of this issue and I look forward to your response. |
|
|
|
Last Edit: 2013/01/31 01:09 By tmediacom.
|
|
|
