Home News Contact Us Forum About Us Demos Products F.A.Q.
Shopping Cart
You currently have 0 items in your cart.


Recent Events
  • 25/11/2021 Black Friday Promotion

    BIG SALE, 35% discount for all our extensions. Use BF21 coupon code. Hurry up the discount is valid till 1 December.

  • 30/04/2021 Important information

    We transferred to another hosting so if you are experiencing any problem let us know. We will fix it as soon as possible.


2Checkout.com, Inc. is an authorized retailer of goods and services provided by ARI Soft. 2CheckOut




Follow us on twitter



Welcome, Guest
Please Login or Register.    Lost Password?

How to create a dynamic query string
(1 viewing) (1) Guest
Go to bottomPage: 12
TOPIC: How to create a dynamic query string
#34483
How to create a dynamic query string 9 Years, 8 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>&nbsp;</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?
The administrator has disabled public write access.
 
#34485
Re:How to create a dynamic query string 9 Years, 8 Months ago Karma: 737
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
The administrator has disabled public write access.
 
#34486
Re:How to create a dynamic query string 9 Years, 8 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
The administrator has disabled public write access.
 
#34487
Re:How to create a dynamic query string 9 Years, 8 Months ago Karma: 737
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
The administrator has disabled public write access.
 
#34489
Re:How to create a dynamic query string 9 Years, 8 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
The administrator has disabled public write access.
 
#36272
Re:How to create a dynamic query string 9 Years, 6 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.
The administrator has disabled public write access.
 
Go to topPage: 12