2. PHPRunner General questions
  3. Display records using where clause with null value
This topic is locked

Display records using where clause with null value

 		12/21/2005 6:15:12 PM
PHPRunner General questions
D
 dmarre author

Is it possible to make ._list.php display the records if certain tables are null. What i am trying to do is display on the .._list.php page only the records that have null values on two fields.
For example, if REF1 or REF2 are null then get records.

The query has to check both values and not just one. If either one are null do not display the record.

But, i also want to be able to search for records and display records regardless if they are null or not.
Ideas?
Admin 12/22/2005

Hi,
please try using Empty search type on Advanced search page.
D
 		dmarre author 12/22/2005

Yes, that works great.

Is it possible to set the query for the first page (.._list.php) to show records with empty values in the two fields i require instead of having to do an advance search?
If there is a way, I will need to also be able to find the records with no empty values in advance search.
T
 		Tommy B 12/22/2005

Yes, that works great.

Is it possible to set the query for the first page (.._list.php) to show records with empty values in the two fields i require instead of having to do an advance search?
If there is a way, I will need to also be able to find the records with no empty values in advance search.


You may try making the following change to the list page:

// make sql "select" string
if(!$gstrSQL)

$strSQL="SELECT * FROM ".AddTableWrappers($strTableName) "WHERE `field1` IS NULL AND `field2` IS NULL";
else

$strSQL = $gstrSQL;


Obviously, I don't know what your variables are, but this worked for me as far as displaying results where field1 & field2 both had values of NULL (empty).
Let me know if this works for you. In the meantime, support may offer a more surefire solution.
D
 		dmarre author 12/22/2005

Here is the error i get when i add that code.
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in test_list.php on line 156
And i did change your values field1 and field2 to the values for my database which are REF1 and REF2.

I see where you are going with it so i keep playing with it. Let me know if you or anyone else gets it.
Admin 12/23/2005

Hi,
please try the following:

Open Edit SQL querytab in PHPRunner, check off No records on first page box.

Then build your project and open ..._list.php file with a text editor.

Locate this snippet:

if(!$action)

$strSQL = AddWhere($strSQL,"1=0");

and replace it with:

if(!$action)

$strSQL = AddWhere($strSQL,"Field1is null and Field2is null");

where Field1and Field2are your actual field names.
D
 		dmarre author 12/23/2005

No good. It did not show any errors but it did not sort records based on the two fields having null values.
Admin 12/26/2005

Hi,
I wasn't able to reproduce this issue.

Please zip and send to support@xlinesoft.com the full set of generated PHP files along with your database creation script. I'll find what's wrong running your pages on my test box.
D
 		dmarre author 12/27/2005

The files are the same files I sent in for the Date search issue i was having. The file name is ups.zip . If you have deleted the files let me know and i will send you the files again. Thanks.