ARI Soft - How to create a dynamic query string

Home

News

Forum

About Us

Demos

Products

F.A.Q.

Featured Demos

ARI Smart Content demo

ARI Quiz demo

WordPress Quiz plugin

WordPress Lightbox plugin

e-mail: info@ari-soft.com

Shopping Cart

You currently have 0 items in your cart.

Show Cart

Recent Events

23/11/2024 Black Friday 2024

BIG SALE, 30% discount for all our extensions. Use BF24 coupon code. Hurry up the discount is valid till 3 December.

more...
31/12/2023 New Year SALE

We are glad to announce New Year SALE. 25% discount for all our extensions. Use NY24 coupon code. Hurry up the discount is valid till 7 January.

more...

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

Welcome, Guest
Please Login or Register. Lost Password?

ARI Soft Forum

Joomla components

ARI Data Tables

How to create a dynamic query string

(1 viewing) (1) Guest

Page: 12

TOPIC: How to create a dynamic query string

Forum Tools

#34483

How to create a dynamic query string 12 Years 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?

luispest81

The administrator has disabled public write access.

#34485

Re:How to create a dynamic query string 12 Years ago Karma: 760

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

admin

The administrator has disabled public write access.

#34486

Re:How to create a dynamic query string 12 Years 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

luispest81

The administrator has disabled public write access.

#34487

Re:How to create a dynamic query string 12 Years ago	Karma: 760
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

admin

The administrator has disabled public write access.

#34489

Re:How to create a dynamic query string 12 Years 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

luispest81

The administrator has disabled public write access.

#36272

Re:How to create a dynamic query string 11 Years, 10 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.

tmediacom

Last Edit: 2013/01/31 01:09 By tmediacom.

The administrator has disabled public write access.

Page: 12

Useful Links