sql syntax and variables from filter

Natalia
Offline
Fresh Boarder

Mehr

3 Jahre 10 Monate her - 3 Jahre 10 Monate her #1070 von Natalia

Natalia antwortete auf sql syntax and variables from filter

Hi, I can not understand why no longer displayed schedule.

{flashchart height="400" width="100%" title="Сравнение питания пользователя с рекомендациями Пирамиды питания" dbname= "nspotyom_jmln1" dbhost="localhost" dbuser= "nspotyom_jmln1" dbpassword = "......." x_label_rotate="40" y_step="20" data="database" sql="SELECT name_food, dose_pyramid, dose_user FROM uh46v_estim_piramid WHERE 'dnd'=#userid# " bar_animation="grow-up" y_min="0" dimension="unit" x_label_rotate="40" y_step="1" legend="Пирамида питания, Пользователь" legend_fontsize="20"} {/flashchart}

Thank you

Anhänge:

Letzte Änderung: 3 Jahre 10 Monate her von Natalia.

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

mukopaje singogo
Autor
Besucher

3 Jahre 11 Monate her #1066 von mukopaje singogo

mukopaje singogo antwortete auf sql syntax and variables from filter

Hi
Thanks I managed with the variables thanks for you help. Actually with directPhp content plugin I can inject some PHP in article. But for some reason can't pick them in flashchart.

Is there a way I could only load charts that have sql true according to my filter. Or maybe I should ask this in the joomla blog.

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

jochen
Offline
Administrator

Mehr

3 Jahre 11 Monate her - 3 Jahre 11 Monate her #1065 von jochen

jochen antwortete auf sql syntax and variables from filter

hi,

as far as I know, you cannot have php-code in Joomla's articles. But you may use flashchart's variable substitution. E.g. for '___ProvinceID' use '#___ProvinceID#'

sql=" sql="SELECT XXX FROM YYY where
ProvinceID='#___ProvinceID#' and DistrictID='#___DistrictID#'......."

Letzte Änderung: 3 Jahre 11 Monate her von jochen.

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

mukopaje singogo
Autor
Besucher

3 Jahre 11 Monate her - 3 Jahre 11 Monate her #1064 von mukopaje singogo

mukopaje singogo antwortete auf sql syntax and variables from filter

hi, thanks for your prompt replies, i am new to both joomla and the flashchart and i went straight into the migration. I think i am missing something here. Probably its how to get variables from joomla in my case the url appended variables and use in my sql query of flashchart. I have also tried a proper query like in your reply before but it doesnt work. I dont understand these variables [___ProvinceID] which i suppose should be coming from the url which looks like this www.domain/index.php?option=com_content&...=3&___DistrictID=301

so since i do not understand this '[___ProvinceID], I get my own variable from the url like this:

<?php
$provinceId = $_GET['___ProvinceID'];
?>
//when i echo $provinceId in my article i get correct values as in url but  the dont seem to work in my flashchart query. here is what i tried.

<?php
if(isset($_GET['___StartDate'])) {
		$startDate = $_GET['___StartDate'];
		
	}
	
	if(isset($_GET['___EndDate'])) {
		$endDate = $_GET['___EndDate'];
	}
	
	if(isset($_GET['___ProvinceID'])) {
		$provinceId = $_GET['___ProvinceID'];
	}
	
	if(isset($_GET['___DistrictID'])) {
		$districtId = $_GET['___DistrictID'];
	}
?>

{flashchart data="database" sql="SELECT PeriodShortName, SUM(number_of_plhiv_support_group_male), SUM(number_of_plhiv_support_group_female) FROM xxxx WHERE ProvinceID = '$provinceId' AND DistrictID = '$districtId' AND PeriodID >= '$startDate' AND PeriodID <= '$endDate'" title="" y_legend="" x_legend="" legend="Male,Female" type="bar_stack" show_barvalues="2" tag_style="padding-bottom:5px; font-size:12px; font-weight:bold; font-family:Sans-Serif; color:#000000;font-rotate: 360;" }chartPLHIVEnrolled{/flashchart}

with the above i get the chart but with no data, it cant filter and when i append: GROUP BY PeriodShortName ORDER BY PeriodID, at the end of the query i get *** flashChart - invalid/missing parameter:
- no data found via sql="

I really dont know how else you could help me perhaps point me to solution close enough.

Letzte Änderung: 3 Jahre 11 Monate her von mukopaje singogo.

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

jochen
Offline
Administrator

Mehr

3 Jahre 11 Monate her #1063 von jochen

jochen antwortete auf sql syntax and variables from filter

hi,
the way you use the double quotes in your flashchart tag, only sql="SELECT XXX FROM YYY" will be processed.
Flashchart has no tag for "sqlwhere" or "sqlgroup" . You must respecify your sql-tag. (e.g.

sql="  sql="SELECT XXX FROM YYY where 
ProvinceID='[___ProvinceID]' and DistrictID='[___DistrictID]' and PeriodID>=[___StartDate]
 and PeriodID<=[___EndDate' 
GROUP BY PeriodShortName ORDER BY PeriodID"

)

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

mukopaje singogo
Autor
Besucher

3 Jahre 11 Monate her #1062 von mukopaje singogo

mukopaje singogo antwortete auf sql syntax and variables from filter

{flashchart data="database" sql="SELECT XXX FROM YYY" sqlwhere="ProvinceID='[___ProvinceID]',DistrictID='[___DistrictID]',PeriodID>=[___StartDate],PeriodID<=[___EndDate]" sqlgroup="GROUP BY PeriodShortName ORDER BY PeriodID" title=" " legend=" " type=" " show_barvalues=" "}XXXXXXX{/flashchart}

thats part of the query and how its used, i am able to get results with that but the filtering doesn't work. Its like only the select is working and the WHERE, GROUP BY and ORDER dont work

Bitte Anmelden oder Registrieren um der Konversation beizutreten.