Forums

First: What a great combination of components are ChronoForms and ChronoConnectivity. With a little knowledge of tables, Forms and SQL I have been able to fill a database table and to retrieve data from the database.

My question:
I have the two following select clauses which work fine, apart from each other:

<?php
    if(JRequest::getVar('city')){
    ?>
    WHERE text_2 = "<?php echo JRequest::getVar('city'); ?>"
    <?php

and

	<?php
	if(JRequest::getVar('soort')){
	?>
	WHERE select_5 =
	"<?php echo JRequest::getVar('soort') ; ?>"
	<?php

I would like to combine those two selections to one. In SQL this would be something like

	Select * from database where city = "xxx" 
	AND 
	soort = "yyy"

How can this be reached using ChronoConnectivity.

Hi bobmijwaard,

I'm not quite sure what the question is but I'd try something like this

<?php
$where = array();
if ( JRequest::getVar('city') ) { 
    $where[] = "text_2 = ".JRequest::getVar('city');
}
if ( JRequest::getVar('soort') ) { 
    $where[] = "select_5 = ".JRequest::getVar('soort');
}
if ( !empty($where) ) {
    echo "WHERE ".implode(' AND ', $where);
}
?>

Bob

Later: added missing semi-colons.
Later still: and a bracket

With your code I get the following error:

PHP Parse error: syntax error, unexpected '}' in E:\xxx\components\com_chronoconnectivity\chronoconnectivity.php(95) : eval()'d code on line 5

The syntax however does look right

Hi bobmijwaard,

Sorry. missed a couple of semi-colons at the ends of lines. I'l go back and update my original post.

Bob

Thanks a lot Bob, works great !!

You still missed another "}" so here is the complete code for future readers:

    <?php
    $where = array();
    if ( JRequest::getVar('city') ) {
        $where[] = "text_2 = ".JRequest::getVar('city');
    }
    if ( JRequest::getVar('soort') ) {
        $where[] = "select_5 = ".JRequest::getVar('soort');
    }
    if ( !empty($where) ) {
        echo "WHERE ".implode(' AND ', $where);
		}
    ?>

        <?php
        $where = array();
        if ( JRequest::getVar('city') ) {
            $where[] = "text_2 = ".JRequest::getVar('city');
        }
        if ( JRequest::getVar('soort') ) {
            $where[] = "select_5 = ".JRequest::getVar('soort');
        }
        if ( !empty($where) ) {
            echo "WHERE ".implode(' AND ', $where);
          }
        ?>

Thought the above code was working perfect, but there seems to be a missing thing. The corresponding records from text_2 and select_5 are selected but displayed independently.
I need to show only the records containing the given "city" and "soort"


Example
city soort
X Italian
X bar
Y Italian
Z chinese

When doing a select on city = x and soort = bar only one record must be displayed.
With the current code two records are displayed

In sql the command will be select * from xx where text_2 = 'city' and select_5 = 'soort'

I have enough knowledge of SQL but still a lot of lacks in PHP

Any help will be appriciated.

Hi bobmijwaard,

I would expect this code to do exactly as you describe. The output should be

WHERE text_2 = some_city AND select_5 = some_soort

It could be that adding quotes would help . . .

Please add these lines temporarily to give some debugging code

<?php
$where = array();
if ( JRequest::getVar('city') ) {
    $where[] = "text_2 = ".JRequest::getVar('city');
}
if ( JRequest::getVar('soort') ) {
    $where[] = "select_5 = ".JRequest::getVar('soort');
}
if ( !empty($where) ) {
    echo "WHERE ".implode(' AND ', $where);
    global mainframe;
    $mainframe->enqueuemessage("WHERE ".implode(' AND ', $where));
}
?>

this should show the sql in a system message on the next page.

Otherwise, is it possible that there are more than one record meeting the WHERE conditions?

Bob

When adding your code I got the following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
Parse error: syntax error, unexpected T_STRING, expecting T_VARIA' at line 1 SQL=SELECT count(*) FROM jos_chronoforms_test
Parse error: syntax error, unexpected T_STRING, expecting T_VARIABLE or '$' in C:\xampp\htdocs\mijwaard_test\components\com_chronoconnectivity\chronoconnectivity.php(95) : eval()'d code on line 11

And yes it is possible that the output of the sql statement can have more then just one record

Hi bobmijwaard,

Apologies, it should be global $mainframe;

Bob

Hello Bob.

The output is:

WHERE select_5 = 'Chinees

Hi bobmijwaard.

I don't understand that - it only makes sense if text_3 is empty. Let's add some quotes and some more diagnostics here.

<?php
global $mainframe;
$city = JRequest::getVar('city');
$mainframe->enqueuemessage("city: ".print_r($city, true));
$soort= JRequest::getVar('soort');
$mainframe->enqueuemessage("soort: ".print_r($soort, true));
$where = array();
if ( JRequest::getVar('city') ) {
    $where[] = "`text_2` = '".JRequest::getVar('city')."'";
}
if ( JRequest::getVar('soort') ) {
    $where[] = "`select_5` = '".JRequest::getVar('soort')."'";
}
if ( !empty($where) ) {
    echo "WHERE ".implode(' AND ', $where);
    global $mainframe;
    $mainframe->enqueuemessage("WHERE ".implode(' AND ', $where));
}
?>

I hope that's OK, the nested quotes start to get tricky . . . and it may be that ChronoConnectivity can't parse them :-(

Bob

I am sure that text_2 is not empty. I have a small testset where text_2 is always filled.

The results from the code are

        * city:
        * soort: 'Overige'
        * WHERE `select_5` = ''Overige''

but also an error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Overige''' at line 1 SQL=SELECT count(*) FROM jos_chronoforms_test WHERE `select_5` = ''Overige''

and a warning

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\mijwaard_test\components\com_chronoconnectivity\chronoconnectivity.html.php on line 172

I appriciate your help very much. !!

Hi bobmijwaard,

We're picking up the value of 'city' not 'text_2' - is that correct.

And I clearly have an extra set of quotes that can be removed - that's what is causing the errors I think.

<?php
global $mainframe;
$text_2 = JRequest::getVar('text_2');
$mainframe->enqueuemessage("text_2 : ".print_r($text_2 , true));
$soort= JRequest::getVar('soort');
$city = JRequest::getVar('city');
$mainframe->enqueuemessage("city: ".print_r($city, true));
$soort= JRequest::getVar('soort');
$mainframe->enqueuemessage("soort: ".print_r($soort, true));
$where = array();
if ( JRequest::getVar('city') ) {
    $where[] = "`text_2` = ".JRequest::getVar('city');
}
if ( JRequest::getVar('soort') ) {
    $where[] = "`select_5` = ".JRequest::getVar('soort');
}
if ( !empty($where) ) {
    echo "WHERE ".implode(' AND ', $where);
    global $mainframe;
    $mainframe->enqueuemessage("WHERE ".implode(' AND ', $where));
}
?>

Bob

PS Who is about to sign off for the evening.

Hello Bob

Output code is now

        * text_2 :
        * city:
        * soort: 'Overige'
        * WHERE `select_5` = 'Overige'

We're picking up the value of 'city' not 'text_2' - is that correct.

The fields in the database are text_2 and select_5.

The database is filled by Chronoforms.

Hi bobmijwaard,

where do you try Bob's code ? if you do this in the "WHERE" box then please empty this box and paste the code in the head box and let me know the results!

Cheers
Max

I have removed the code from the where box and placed in it the header box, but still with the same result:

        * text_2 :
        * city:
        * soort: 'Overige'
        * WHERE `select_5` = 'Overige'

Regards

Hi bobmijwaard,

Plese try Max's suggestion - I still don't understand why we aren't seeing values for 'city' or for 'text_2' ?-)

Bob

Sorry Bob, the post above is does contains Max 's suggestion. Placed the code in the header box instead of the where box.

Hi bobmijwaard,

Yes, I think you posted as I was writing. I'm a bit baffled, I don't understand why there are no values for these variables.

Unfortunately ChronoConnectivity doesn't have a convenient backup - if it's OK with you and the site is online I could take a look in the morning. Email or PM me a SuperAdmin logon if you think that might help,

Bob

lets do one more simple test because this is really strange yes, put this code in the head, along with the other fields code of course (where do you have the text_2 field ???):

<?php echo "text_2=".JRequest::getVar('text_2'); ?>

please let me know the output of this!

Cheers
Max

Hi Max,

That's the first line in the last Output

* text_2 :

very odd.

Bob

Hi Max,

I looked at bobmijwaard's site this morning and found the problem, there were some stray quotes that effectively changed the field name from city to 'city'. Once they were removed this works OK.

Bob

Hi Bob,

Well done, great news!🙂

Thanks!
Max