WHERE statement expression not working

Hello,

I am using Joomla 1.7.1 and Chronoforms V4.

I am having an issue with the WHERE statement ignoring two of my expressions within the DB Multi Record Loader.

Here is what I have so far within the DB Multi Record Loader function:

BASIC tab:

Table and Model ID are configured

ADVANCED tab:

Load Data: YES
Enable Associations: NO
Associated Models: Blank
Group Model Data: NO

WHERE statement: city = '<?php echo $form->data['city']; ?>' AND
industry = '<?php echo $form->data['industry']; ?>' AND
position LIKE '%<?php echo $form->data['position']; ?>%'

I would like all three expressions to be used when filtering data but the first two expressions (city and industry) are being ignored so no data comes back (I have the debugger installed). When I change the AND's to OR's it correctly calls the third expression (position) only. When I remove the third expression (position) no data comes back. So I feel comfortable saying that the "position" expression is working properly.

I have sliced and diced every combination I can think of but I am not able to determine why the first two expressions aren't working. Even when I use the LIKE function for all three expressions identically, the (city and industry) expressions are ignored.

There is only one difference I can think of between the three and is that the "city" and "industry" fields in the table I am calling were populated from a pre-filled multiple choices form that the user selected (using CV4 for all forms) (which is why I am using "=" vs. "LIKE") whereas the "position" field data was manually typed in by the user within that same original form.

I hope that helps....thanks

Hi swmckay ,

Please add a Debugger action to the On Load event and copy and paste the output here. It loosk at though the two pre-filled inputs aren't returning anything. Do you have them set to disabled?

Bob

I'm not sure what you mean by the "disabled" question. Where would I check to verify that?

I have a DB Record Loader function in the On Load event. I placed the debugger after the record loader function and it came back with this but this info is from a different table altogether. So the applicant looks at their record (below), hits search and comes back with the matching job listings (in theory).

Data Array:
Array
(
[chronoform] => jobsearchparameters
[option] => com_chronoforms
[Itemid] =>
[cf_id] => 2
[cf_uid] => 7918452e4616405b7d5af100014b7263
[cf_created] => 2012-01-09 13:09:38
[cf_modified] => 2012-02-25 21:20:05
[cf_ipaddress] => ::1
[cf_user_id] => 155
[position] => ceo
[industry] => Admin - Clerical
[city] => Anaheim,CA
[experience] => 10+ Years
[management] => 5+ Years
[employment] => Part Time
[education] => Bachelors
[save] => Save/Next
)

Debug Data
db_record_loader
SELECT * FROM `j16_chronoforms_data_jobsearchparameters` AS `J16ChronoformsDataJobsearchparameters` WHERE cf_user_id='155'

Hi swmackay,

Hmm can you get a dump of the query with the WHERE clause in it. I don't see anything wrong here though I notice that both city and industry values have punctuation in them. You may need to use $db->quote() to escape the values correctly.

Ignore the 'disabled' thought for the moment.

Bob

I tried the "db quote" approach but it was yielding the same results. To simplify matters I've removed all traces of punctuation from the 'city' and 'industry' fields.

Here is the data from the debugger results. Hopefully that answers your question about a 'dump of the query with the WHERE statement'.

Here are the results with the WHERE statement first with "AND" and then with "OR":

WHERE: (This one doesn't bring back or display any results)

city = '<?php echo $form->data['city']; ?>' AND
industry = '<?php echo $form->data['industry']; ?>' AND
position LIKE '%<?php echo $form->data['position']; ?>%'

DEBUGGER RESULTS:

Data Array:
Array
(
[position] => ceo
[industry] => Admin Clerical
[city] => AnaheimCA
[experience] => 10+ Years
[management] => 5+ Years
[employment] => Part Time
[education] => Bachelors
[Search_Jobs] => Search Jobs
[14246c7523f9fe11daee4191d20f07b1] => 1
[chronoform] => jobsearchparameters
[event] => submit
[option] => com_chronoforms
[Itemid] =>
[jobresults] => Array
(
)

)


WHERE: (This one correctly queries/displays the data based on the 'position' expression only.)

city = '<?php echo $form->data['city']; ?>' AND
industry = '<?php echo $form->data['industry']; ?>' OR
position LIKE '%<?php echo $form->data['position']; ?>%'


DEBUGGER RESULTS:

Data Array:
Array
(
[position] => ceo
[industry] => Admin Clerical
[city] => AnaheimCA
[experience] => 10+ Years
[management] => 5+ Years
[employment] => Part Time
[education] => Bachelors
[Search_Jobs] => Search Jobs
[14246c7523f9fe11daee4191d20f07b1] => 1
[chronoform] => jobsearchparameters
[event] => submit
[option] => com_chronoforms
[Itemid] =>
[jobresults] => Array
(
[0] => Array
(
[cf_id] => 11
[cf_uid] => a403875815c767d40d236d25626ba745
[cf_created] => 2012-02-05 05:26:27
[cf_modified] => 2012-02-11 11:58:55
[cf_ipaddress] => ::1
[cf_user_id] => 187
[position] => theceo
[industry] => Banking
[city] => AnaheimCA
[experience] => 10+ Years
[management] => 3-4 Years
[employment] => Part Time
[education] => Bachelors
[overnighttravel] =>
[jobsummary] => khkrhjwkjhrkwjhrwfs sdf sdf sa
[save] => Save
)

[1] => Array
(
[cf_id] => 9
[cf_uid] => 2e702ead205b5eab03b6907523c5c5eb
[cf_created] => 2011-12-27 16:06:57
[cf_modified] => 2012-02-26 18:42:34
[cf_ipaddress] => ::1
[cf_user_id] => 163
[position] => ceo
[industry] => Admin Clerical
[city] => TucsonAZ
[experience] => 5+ Years
[management] => 10+ Years
[employment] => Seasonal
[education] => Associates
[overnighttravel] =>
[jobsummary] => sfgjhdjdhgmfghtdhhtrsdh
[save] => Save
)

)

)


I am stumped. Thanks again for your help!