Forums

What is the correct syntax for the WHERE statement in the DB Multi Record Loader? And what is being referenced? Am I using $_POST arry as an input or a text field on the form? Should I be using {curly brackets}? Can I use multiple WHERE clauses? If so, do I use AND between each clause or just list each one on a separate line?

Sorry for all the questions. I've been hunting for these answers but haven't been able to find them anywhere. Thank you for your input.

Justin

Hi Justin,

As far as I can see from a dig around the code you can use PHP in the WHERE box but not the curly brackets syntax.

Bob

Thanks for the reply, Bob. I'm apologize but I'm still a little cloudy on the correct syntax for the WHERE box. I saw from another post that someone used syntax similar to:

`first_name` LIKE '%{fname}%';

Is this correct syntax? (Assuming that I have a text box field named 'fname' on my form). When I try this, I get no results.

I also tried:

`first_name` LIKE '%a%';

just to see if I would get something, but again I get no results. If I leave the WHERE box blank, I get all the results.

I also tried using PHP:

<?php
if ( isset($form->data['lname'])) {
	if ( isset($form->data['fname'])) {
		echo " `first_name` LIKE '%{$form->data['fname']}%' AND `last_name` LIKE '%{$form->data['lname']}%'"; 
	}
	else {
		echo " `last_name` LIKE '%{$form->data['lname']}%' ";
	}
} 
else if ( isset($form->data['fname'])) {
    echo " `first_name` LIKE '%{$form->data['fname']}%' ";
    }
}
?>

Again, this produced no results.

Hi Justin,

YOu can add any PHP you like in the box but the result has to be a valid MySQL WHERE query. There are quite a lot of different ways that a WHERE query can be phrased so it's hard to answer the general question except to refer you to the MySQL manual (which is not the clearest in the world).

The query `first_name` LIKE '%{$fname}%'; (with added $) will find all records where the first_name column includes the value of the 'fname' input. So an entry of rob would find 'robert', roberta', 'arobain', 'hectorob', etc.

Bob

Ok, that's good to know. It just needs to be valid SQL syntax.

Is it required that I use the ` character when referring to fields in the table?

Also, my data is stored in a table called pd_directory (which is the table that my multi record loader in pointed to), so I want to make sure I'm referencing the correct things in my where statement. For example: if I have a text box on my form called "fname" and I have a field in the pd_directory table called first_name, would this be the correct WHERE clause...

`pd_directory`.`first_name` LIKE '{%$fname%}'

or is it unnecessary to reference the table explicitly? I just want to make sure I'm actually comparing the correct things and it's a bit confusing how the text boxes on the form are being referenced in this scenario.

I may be going about this in the wrong way, or maybe I'm on the verge of a breakthrough🙂 Or maybe it's time to start a different thread!

Thanks again for your patience and help,
Justin

Ok, after trying everything I actually got this to work in the where statement:

first_name LIKE '%<?php echo $_POST['fname'];?>%';

It's ugly and cumbersome, but it works.

I was unable to get anything like this to work:

`first_name` LIKE '%{$fname}%';

It's frustrating because I'd like to use cleaner code and it seems that the curly braces offer that, however I can't seem to get them to work :?

I tried implementing {$fname} in various ways and echo it to the screen to make sure I was getting the correct values, but had no luck there either.

Thanks again!
Justin

Hi Justin,

You need to define $fname before it will work. Try this as an example:

<?php
$fname = $form->data['fname'];
echo " `first_name` LIKE '%{$fname}%' ";
?>

Bob