WHERE box

Hi,

In my form I use a DB Multi record loader. In the advanced tab, in the WHERE statement, I have this code, which works ok.

<?php
$user = &JFactory::getUser();
echo " cf_user_id = ".$user->id 
?>

Now I've an other code for my search box, which works ok too:

`projectnaam` LIKE '%<?php echo $form->data['search']; ?>%' OR `projectnummer` LIKE '%<?php echo $form->data['search']; ?>%' ORDER BY `cf_modified` DESC 

So far so good.
------------------------------------
My problem is to combine these two codes in the same WHERE statement box in the DB Multi Record Loader. With the combined code, my form doesn't work anymore.

`projectnaam` LIKE '%<?php echo $form->data['search']; ?>%' OR `projectnummer` LIKE '%<?php echo $form->data['search']; ?>%' ORDER BY `cf_modified` DESC 

<?php
$user = &JFactory::getUser();
echo " cf_user_id = ".$user->id 
?>

What am I doing wrong?

Hi proso,

Mostly you aren't thinking about the result you want. If you add a Debugger action you can see the query that is generated - you'll find that the second part of the WHERE clause now comes *after* the ORDER BY and breaks the MySQL.

<?php
$user = &JFactory::getUser();
echo " `cf_user_id` = {$user->id} AND ( `projectnaam` LIKE '%{$form->data['search']}' OR `projectnummer` LIKE '%{$form->data['search']}%' ) ORDER BY `cf_modified` DESC ";
?>

You'll need to check that I've got your logic correct.

Bob

I understand it now. And it works😀
Thanks

Hi all,

Last post I said the code works, but I found out there is still an issue which I can't figure out.

When I use my search box, with for example the name: 'my search box works', it finds the record, but when I only use a part of it, for example: 'works', no records are found!!

This code works for both examples

`projectnaam` LIKE '%<?php echo $form->data['search']; ?>%' OR `projectnummer` LIKE '%<?php echo $form->data['search']; ?>%' ORDER BY `cf_modified` DESC 

But the issue happens in this code:

<?php
$user = &JFactory::getUser();
echo " `cf_user_id` = {$user->id} AND ( `projectnaam` LIKE '%{$form->data['search']}' OR `projectnummer` LIKE '%{$form->data['search']}%' ) ORDER BY `cf_modified` DESC ";
?>

Does someone knows how to solve that?

You are missing a % in the projectnaam section
( 'projectnaam'` LIKE '%{$form->data['search']}' should be
( 'projectnaam'` LIKE '%{$form->data['search']}%'

I'm not sure if it works like this, if not you can try:
( 'projectnaam'` LIKE "%'{$form->data['search']}'%"

Rob

Hi RobP,

Thank you very much. Your first suggestion works!!!