PHP Query Help

I have a PHP Query, in CodeIgnitor.  I'm not sure how to write it. The following works 100%;
<?php
if(isset($_GET['wave_max'])) {
			$wave_max = $_GET['wave_max'];
		} else {
			$wave_max = "";
		}
		if(isset($_GET['wave_min'])) {
			$wave_min = $_GET['wave_min'];
		} else {
			$wave_min = "";
		}
	$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where_in('t1.field_id_3731', $ids)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		

Open in new window


But I need to change it just slightly.
I need to also add to it, if $wave_max or $wave_min have a value for field_id_3781.  So the Query would now read something like:
$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where_in('t1.field_id_3731', $ids)
OR
->where_in('t1.field_id_3781', A VALUE IS in $wave_min or in $wave_max)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();

Open in new window

LVL 7
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marco GasiFreelancerCommented:
You can try this:
$where = "t1.field_id_3731 IN $ids AND $wave_min IS NOT NULL OR $wave_min IS NOT NULL";
if(isset($_GET['wave_max'])) {
	$wave_max = $_GET['wave_max'];
} else {
	$wave_max = "";
}
if(isset($_GET['wave_min'])) {
	$wave_min = $_GET['wave_min'];
} else {
	$wave_min = "";
}
$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
				->from('exp_channel_data t1')
				->where_in($where)
				->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
				->get();

Open in new window

0
rgranlundAuthor Commented:
OK, I see where that is going.  Once implemented I get an error:
Fatal error: Call to a member function join() on a non-object in  
My code:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
		$cw ='';
		$pulsed = '';
		$pulsednp = '';
		$quasi = '';
		$ultrafastfp = '';
		$ultrafastf = '';
		$wave_max = '';
		$wave_min = '';
		$wavelength = '';
		$ids = '';	
$action=$_GET["action"];
	if($action=="laser_finder") {
		
	$where = "t1.field_id_3731 IN $ids AND $wave_min IS NOT NULL OR $wave_min IS NOT NULL";
		if(isset($_GET['cw'])) {
			$cw = $_GET['cw'];
		} else {
			$cw='NONE';
		}
		if(isset($_GET['pulsed'])) {
			$pulsed = $_GET['pulsed'];
		} else {
			$pulsed = 'NONE';
		}
		if(isset($_GET['pulsednp'])) {
			$pulsednp = $_GET['pulsednp'];
		} else {
			$pulsednp = 'NONE';
		}
		if(isset($_GET['quasi'])) {
			$quasi = $_GET['quasi'];
		} else {
			$quasi = 'NONE';
		}
		if(isset($_GET['ultrafastfp'])) {
			$ultrafastfp = $_GET['ultrafastfp'];
		} else {
			$ultrafastfp = 'NONE';
		}
		if(isset($_GET['ultrafastf'])) {
			$ultrafastf = $_GET['ultrafastf'];
		} else {
			$ultrafastf = 'NONE';
		}
		
		if(isset($_GET['wave_max'])) {
			$wave_max = $_GET['wave_max'];
		} else {
			$wave_max = "";
		}
		if(isset($_GET['wave_min'])) {
			$wave_min = $_GET['wave_min'];
		} else {
			$wave_min = "";
		}
		
echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';

	$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where_in($where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		
		$lb = $laser_query->result_array();
		
		echo '<pre>';
			var_dump($where);
			echo'</pre>';

Open in new window


And just to be clear In the Where statement I need it to be if any of those are true like this... Just to clarify...
$where = "t1.field_id_3731 IN $ids OR $wave_min IS NOT NULL OR $wave_min IS NOT NULL";
0
Marco GasiFreelancerCommented:
Forgive me for the misunderstanding and over all forgive me for my mistake: you have to use

->where($where)

instead of

->where_in($where)

Probably the error you get is caused by this...
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

rgranlundAuthor Commented:
When I make that change I get:
Fatal error: Call to a member function result_array() on a non-object in
The error is on the line:
->where($where)

this is the line generating it.
$where = "t1.field_id_3731 IN $ids OR $wave_min IS NOT NULL OR $wave_max IS NOT NULL";
0
rgranlundAuthor Commented:
Further code; I still get the error but I think this is closer:

		
	$where = "$wave_min IS NOT NULL OR $wave_max IS NOT NULL";
		
		if(isset($_GET['wave_max'])) {
			$wave_max = $_GET['wave_max'];
		} else {
			$wave_max = "";
		}
		if(isset($_GET['wave_min'])) {
			$wave_min = $_GET['wave_min'];
		} else {
			$wave_min = "";
		}
		
echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';

	$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where_in('t1.field_id_3731', $ids)
		->or_where_in($where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		

Open in new window

So what I am trying to say is if there is a value for $ids OR if there is a Value in $wave_max OR if there is a value in $wave_min.
The error: Fatal error: Call to a member function join() on a non-object in
is on the line:->or_where_in($where)
0
Julian HansenCommented:
Just to clarify some things as this is a follow on question

@Marco - the where_in is valid. rgranlund previously was using a bunch of or_where's to construct a query that had multiple or's on a single field
i.e
WHERE t1.field_id_3731= 5 OR t1.field_id_3731 =7 OR t1.field_id_3731 = 21

Open in new window

It was recommended that he rather use the CI function where_in on the field t1.field_id_3731 with an array of id's as the in clause.

If I understand the question correctly you want to check if
t1.field_id_3731 = $wave_min OR t1.field_id_3731 = $wave_max

Open in new window

So you can do this
$ids = array($wave_max, $wave_min);

Open in new window

Then use your where_in as before i.e.
...
->where_in(t1.field_id_3731, $ids);

Open in new window

Pleas correct me if I have misunderstood.
0
rgranlundAuthor Commented:
No that is not correct.
I want to Query if there is anything in $ids
OR
I want to query if the variable $wave_max has a value
OR
I want to query if $wave_min has a value.
The query runs via AJAX on form change.  On the form you can Check to search for one of the $ids
OR you can search for $wave_max OR $wave_min.  I don't want to compare the DB entry to see if it $field_id_3781 = $wave_max  I just want it to query IF $wave_max has a value, OR if $wave_min has a Value OR if $ids has a value.  So on the form you can pick one or all three
My Form
<form action="#" method="get" id="laser_finder">
  <!--  Operating Mode  -->
  	<div class="form-control main title">
  		Operating Mode
  	</div>
 <!--  START CW  -->
  	<div class="form-control">
  			<input type="checkbox" name="cw" id="cw" value="CW" >
  			<label for="cw">
  				CW (Continuous Wave)
  		</label>
  	</div>
<!--  START Pulsed  -->
  	<div class="form-control">
  				<input type="checkbox" name="pulsed" id="pulsed" value="Pulsed" >
  				<label for="pulsed">
  				Pulsed
  		</label>
  	</div>
 <!--  START Pulsed non-picosecond   -->
  	<div class="form-control">
  			<input type="checkbox" name="pulsednp" id="pulsednp" value="Pulsed non-picosecond" >
  			<label for="pulsednp">
  			Pulsed (non-picosecond)
  		</label>
  	</div>
  <!--  START Pulsed Modelocked  -->
  	<div class="form-control">
  			<input type="checkbox" name="quasi" id="quasi" value="Quasi-CW modelocked">
  			<label for="quasi">
  			Quasi-CW (modelocked)
  		</label>
  	</div>
  <!--  START Umtrafast femto-picosecond  -->
  	<div class="form-control">
  			<input type="checkbox" name="ultrafastfp" id="ultrafastfp" value="Ultrafast femto-picosecond">
  			<label for="ultrafastfp">
  			Ultrafast (femto-picosecond)
  		</label>
  	</div>  	
  <!--  START Umtrafast femtosecond  -->
  	<div class="form-control">
  			<input type="checkbox" name="lultrafastf" id="ultrafastf" value="Ultrafast (femtosecond)">
  			<label for="ultrafastf">
  				Ultrafast (femtosecond)
  		</label>
  	</div>    	
  	
  <!--  END Operating Mode	-->
  
  <!--  START Wavelength  -->
  	<div class="form-control main title">
  		Wavelength
  	</div>
 <!--  START Wavelength Search -->
  	<div class="form-control">
  		<input type="number" name="wave_max" id="wave_max" placeholder="Max" value="">
  		<input type="number" name="wave_min" id="wave_min" placeholder="Min" value="">		
  	</div>
  	
 <!--  END Wavelength  -->
 

Open in new window

My PHP so far:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
		$cw ='';
		$pulsed = '';
		$pulsednp = '';
		$quasi = '';
		$ultrafastfp = '';
		$ultrafastf = '';
		$wave_max = '';
		$wave_min = '';
		$wavelength = '';
		$ids = '';	
$action=$_GET["action"];
	if($action=="laser_finder") {
		
	$where = "$wave_min IS NOT NULL OR $wave_max IS NOT NULL";
		if(isset($_GET['cw'])) {
			$cw = $_GET['cw'];
		} else {
			$cw='NONE';
		}
		if(isset($_GET['pulsed'])) {
			$pulsed = $_GET['pulsed'];
		} else {
			$pulsed = 'NONE';
		}
		if(isset($_GET['pulsednp'])) {
			$pulsednp = $_GET['pulsednp'];
		} else {
			$pulsednp = 'NONE';
		}
		if(isset($_GET['quasi'])) {
			$quasi = $_GET['quasi'];
		} else {
			$quasi = 'NONE';
		}
		if(isset($_GET['ultrafastfp'])) {
			$ultrafastfp = $_GET['ultrafastfp'];
		} else {
			$ultrafastfp = 'NONE';
		}
		if(isset($_GET['ultrafastf'])) {
			$ultrafastf = $_GET['ultrafastf'];
		} else {
			$ultrafastf = 'NONE';
		}
		
		if(isset($_GET['wave_max'])) {
			$wave_max = $_GET['wave_max'];
		} else {
			$wave_max = "";
		}
		if(isset($_GET['wave_min'])) {
			$wave_min = $_GET['wave_min'];
		} else {
			$wave_min = "";
		}
		
echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';

	$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where_in('t1.field_id_3731', $ids)
		->or_where_in($where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		
		$lb = $laser_query->result_array();

Open in new window

0
Marco GasiFreelancerCommented:
@Julian: ok I understand now your point. But if I'm not wrong, where_in checks if a value is in a specified range/array, so where_in is good fto check if t1.field_id_3731 is among $ids, but for the new part of the where clause we should use the standard where, am I wrong?

So it could be:
$where = "$wave_min IS NOT NULL OR $wave_max IS NOT NULL";
...
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where_in('t1.field_id_3731', $ids)
		->or_where($where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();

Open in new window

0
rgranlundAuthor Commented:
That throws the same error:
My Code:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
		$cw ='';
		$pulsed = '';
		$pulsednp = '';
		$quasi = '';
		$ultrafastfp = '';
		$ultrafastf = '';
		$wave_max = '';
		$wave_min = '';
		$wavelength = '';
		$ids = '';	
$action=$_GET["action"];
	if($action=="laser_finder") {
		
	$where = "$wave_minIS NOT NULL OR $wave_max. IS NOT NULL";
		if(isset($_GET['cw'])) {
			$cw = $_GET['cw'];
		} else {
			$cw='NONE';
		}
		if(isset($_GET['pulsed'])) {
			$pulsed = $_GET['pulsed'];
		} else {
			$pulsed = 'NONE';
		}
		if(isset($_GET['pulsednp'])) {
			$pulsednp = $_GET['pulsednp'];
		} else {
			$pulsednp = 'NONE';
		}
		if(isset($_GET['quasi'])) {
			$quasi = $_GET['quasi'];
		} else {
			$quasi = 'NONE';
		}
		if(isset($_GET['ultrafastfp'])) {
			$ultrafastfp = $_GET['ultrafastfp'];
		} else {
			$ultrafastfp = 'NONE';
		}
		if(isset($_GET['ultrafastf'])) {
			$ultrafastf = $_GET['ultrafastf'];
		} else {
			$ultrafastf = 'NONE';
		}
		
		if(isset($_GET['wave_max'])) {
			$wave_max = $_GET['wave_max'];
		} else {
			$wave_max = "";
		}
		if(isset($_GET['wave_min'])) {
			$wave_min = $_GET['wave_min'];
		} else {
			$wave_min = "";
		}
		
echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';

	$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where_in('t1.field_id_3731', $ids)
		->or_where_in('t1.field_id_3781', $where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		
		$lb = $laser_query->result_array();
		
		echo '<pre>';
			var_dump($where);
			echo'</pre>';
			
		foreach($lb as $row){
   			$id = $row['id'];
   			$image = $row['image'];
   			$img  = end(explode('{filedir_51}', $image));
   			$title = $row['title'];
   			$url_title = $row['url_title'];
   			$wave = $row['wavelength'];
   			$wave = preg_replace('[\s]',',', $wave);
   			$wave = preg_replace('/[^0-9,]/','', $wave);
   			$wv = array_filter(explode(",", $wave));
   			$wave = implode(', ', $wv);
   				
   		 			   		
   		 		$laser_cat_query = ee()->db->select('t3.entry_id AS eid, t3.cat_id AS cat_id, t4.cat_url_title AS cat_url')
   		 	 	->from('exp_category_posts t3')
   		 	 	->where('t3.entry_id', $id)
   		 	 	->join('exp_categories t4', 't4.cat_id = t3.cat_id')
   		 	 	->limit(1)
   		 	 	->get();
   		 	 	
   		 		 foreach($laser_cat_query->result_array() as $row_b){
   		 	 		$cat_url = $row_b['cat_url'];	

   		 			if ($title !="") {
   		 				$wave = explode(', ', $wave);
   		 				
   		 				if(!isset($wave[1])) $wave[1] ='';

			$a = false;
   			$b = false; 

   			if($wave[0] <= $wave_max) $a = true; 
   			if($wave[1] >= $wave_min) $b = true;

   			
   			$wavelength = '';
   				
   			if($a && $b) $wavelength = true;
   			
   			if($wavelength == true) {
   					
   		 				echo '<div class="result-box" >
   		   				<div class="image">';
 						echo '<img src="{site_url}/assets/product_images/'.$img .'" /><br />';
						echo'</div>
								<div class="title">
									<a href="{site_url}/lasers/laser/'.$cat_url.'/'.$url_title.'">'.$title.'</a> 
								</div>
							</div>';
						}	//  END Loop
					}  //  END If Wavelength
				}	
			}
   		} //  END IF Form has been submitted  
?>

Open in new window

Link:
http://cohr-dev-ee01.azurewebsites.net/lasers
0
Julian HansenCommented:
@Marco,

I was going by the opening question - second code snippet
->where_in('t1.field_id_3781', A VALUE IS in $wave_min or in $wave_max)

Open in new window

Took that to mean he wants to check if the t1.field_id_3781 is either $wave_min or $wave_max - I read in haste so might have missed the requirement - if he is checking for empty then I would add checks for
$where = "({$wave_min} IS NOT NULL AND {$wave_min} != '')OR ({$wave_max} IS NOT NULL AND {$wave_max} != '')";

Open in new window

0
rgranlundAuthor Commented:
@julian that got me a lot closer.  The final part that is still lingering is this:
$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where_in('t1.field_id_3731', $ids)
		->or_where_in('t1.field_id_3781', $where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();

Open in new window

On line 3: ->where_in('t1.field_id_3731', $ids)

right now there has to be a value for that line for the query to return anything, what I want is if either of those two lines have a value.  Meaning if $ids is empty, it will still query based upon the values in $where.
0
Julian HansenCommented:
I don't think the or_where_in is right - should just be an or_where

But before we go there - can you explain again (I know you probably did it already) the rules for returning rows - currently I have
WHERE 
  t1.field_id_3731 IN ($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf) OR
  ($wave_max != '' AND $wave_max IS NOT NULL) OR 
  ($wave_min != '' AND $wave_min IS NOT NULL)

Open in new window

In other words - if t1.field_id_3731 is equal to any one of the (cw, pulsednp, quasi, ultrafastfp, ultrafastf) values OR wave_max is not empty OR wave_min is not empty - then return row - is this correct?
0
rgranlundAuthor Commented:
That is correct.  Also, just for clarification, they can all have a value too.
0
Julian HansenCommented:
Then I would just do constructed where clause and use ->where($where)

$where = <<< QUERY
 t1.field_id_3731 IN ({$cw}, {$pulsed}, {$pulsednp}, {$quasi}, {$ultrafastfp}, {$ultrafastf}) OR
  ({$wave_max} != '' AND {$wave_max} IS NOT NULL) OR 
  ({$wave_min} != '' AND {$wave_min} IS NOT NULL)
QUERY;

Open in new window

0
rgranlundAuthor Commented:
I get an error: Fatal error: Call to a member function result_array() on a non-object in  on the ->where($where)
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
		$cw ='';
		$pulsed = '';
		$pulsednp = '';
		$quasi = '';
		$ultrafastfp = '';
		$ultrafastf = '';
		$wave_max = '';
		$wave_min = '';
		$wavelength = '';
		$ids = '';	
$action=$_GET["action"];
	if($action=="laser_finder") {
		
		if(isset($_GET['cw'])) {
			$cw = $_GET['cw'];
		} else {
			$cw='NONE';
		}
		if(isset($_GET['pulsed'])) {
			$pulsed = $_GET['pulsed'];
		} else {
			$pulsed = 'NONE';
		}
		if(isset($_GET['pulsednp'])) {
			$pulsednp = $_GET['pulsednp'];
		} else {
			$pulsednp = 'NONE';
		}
		if(isset($_GET['quasi'])) {
			$quasi = $_GET['quasi'];
		} else {
			$quasi = 'NONE';
		}
		if(isset($_GET['ultrafastfp'])) {
			$ultrafastfp = $_GET['ultrafastfp'];
		} else {
			$ultrafastfp = 'NONE';
		}
		if(isset($_GET['ultrafastf'])) {
			$ultrafastf = $_GET['ultrafastf'];
		} else {
			$ultrafastf = 'NONE';
		}
		
		if($_GET['wave_max'] !="") {
			$wave_max = $_GET['wave_max'];
		} else {
			$wave_max = "";
		}
		if($_GET['wave_min'] !="") {
			$wave_min = $_GET['wave_min'];
		} else {
			$wave_min = "";
		}
		
		echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';
		
		$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
		
		$where = <<< QUERY
 t1.field_id_3731 IN ({$cw}, {$pulsed}, {$pulsednp}, {$quasi}, {$ultrafastfp}, {$ultrafastf}) OR
  ({$wave_max} != '' AND {$wave_max} IS NOT NULL) OR 
  ({$wave_min} != '' AND {$wave_min} IS NOT NULL)
QUERY;
echo '<pre>';
			var_dump($where);
			echo'</pre>';
			
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where($where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		
		$lb = $laser_query->result_array();

Open in new window

See for yourself here:
http://cohr-dev-ee01.azurewebsites.net/lasers
0
Julian HansenCommented:
Can we see what CI is generating in terms of a query.

Can you do the following before you do the result_array();
echo "Query: " . ee()->db->last_query();

Open in new window

Let's see what query it is is trying to run.
0
Marco GasiFreelancerCommented:
And I'm wondering why your var_dump doesn't print anything...
0
Julian HansenCommented:
@Marco - nice catch - looking at the html src - no evidence of the <pre> tags either - last output is the to <br/><br/>. The error refers to line 74 as the cause - however the result_array line is actually 80 so me thinks the running code is not the same as the code posted.
0
Marco GasiFreelancerCommented:
Yes, it looks so. For sure, the query fails and this is the reason because the error is raised on result_array()...
0
rgranlundAuthor Commented:
This is the Code.  Please refresh. @Julian,  I don't think the echo Query is going to give you what you want though cause of the CMS running it's own queries:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
		$cw ='';
		$pulsed = '';
		$pulsednp = '';
		$quasi = '';
		$ultrafastfp = '';
		$ultrafastf = '';
		$wave_max = '';
		$wave_min = '';
		$wavelength = '';
		$ids = '';	
$action=$_GET["action"];
	if($action=="laser_finder") {
		
		if(isset($_GET['cw'])) {
			$cw = $_GET['cw'];
		} else {
			$cw='NONE';
		}
		if(isset($_GET['pulsed'])) {
			$pulsed = $_GET['pulsed'];
		} else {
			$pulsed = 'NONE';
		}
		if(isset($_GET['pulsednp'])) {
			$pulsednp = $_GET['pulsednp'];
		} else {
			$pulsednp = 'NONE';
		}
		if(isset($_GET['quasi'])) {
			$quasi = $_GET['quasi'];
		} else {
			$quasi = 'NONE';
		}
		if(isset($_GET['ultrafastfp'])) {
			$ultrafastfp = $_GET['ultrafastfp'];
		} else {
			$ultrafastfp = 'NONE';
		}
		if(isset($_GET['ultrafastf'])) {
			$ultrafastf = $_GET['ultrafastf'];
		} else {
			$ultrafastf = 'NONE';
		}
		
		if($_GET['wave_max'] !="") {
			$wave_max = $_GET['wave_max'];
		} else {
			$wave_max = "";
		}
		if($_GET['wave_min'] !="") {
			$wave_min = $_GET['wave_min'];
		} else {
			$wave_min = "";
		}
		
		echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';
		
		$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
		
		$where = <<< QUERY
 t1.field_id_3731 IN ({$cw}, {$pulsed}, {$pulsednp}, {$quasi}, {$ultrafastfp}, {$ultrafastf}) OR
  ({$wave_max} != '' AND {$wave_max} IS NOT NULL) OR 
  ({$wave_min} != '' AND {$wave_min} IS NOT NULL)
QUERY;

echo '<pre>';
			var_dump($where);
			echo'</pre>';
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where($where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		
		echo "Query: " . ee()->db->last_query();
		
		$lb = $laser_query->result_array();
		
		echo '<pre>';
			var_dump($where);
			echo'</pre>';
			
		foreach($lb as $row){
   			$id = $row['id'];
   			$image = $row['image'];
   			$img  = end(explode('{filedir_51}', $image));
   			$title = $row['title'];
   			$url_title = $row['url_title'];
   			$wave = $row['wavelength'];
   			$wave = preg_replace('[\s]',',', $wave);
   			$wave = preg_replace('/[^0-9,]/','', $wave);
   			$wv = array_filter(explode(",", $wave));
   			$wave = implode(', ', $wv);
   				
   		 			   		
   		 		$laser_cat_query = ee()->db->select('t3.entry_id AS eid, t3.cat_id AS cat_id, t4.cat_url_title AS cat_url')
   		 	 	->from('exp_category_posts t3')
   		 	 	->where('t3.entry_id', $id)
   		 	 	->join('exp_categories t4', 't4.cat_id = t3.cat_id')
   		 	 	->limit(1)
   		 	 	->get();
   		 	 	
   		 		 foreach($laser_cat_query->result_array() as $row_b){
   		 	 		$cat_url = $row_b['cat_url'];	

   		 			if ($title !="") {
   		 				$wave = explode(', ', $wave);
   		 				
   		 				if(!isset($wave[1])) $wave[1] ='';

			$a = false;
   			$b = false; 

   			if($wave[0] <= $wave_max) $a = true; 
   			if($wave[1] >= $wave_min) $b = true;

   			
   			$wavelength = '';
   				
   			if($a && $b) $wavelength = true;
   			
   			if($wavelength == true) {
   					
   		 				echo '<div class="result-box" >
   		   				<div class="image">';
 						echo '<img src="{site_url}/assets/product_images/'.$img .'" /><br />';
						echo'</div>
								<div class="title">
									<a href="{site_url}/lasers/laser/'.$cat_url.'/'.$url_title.'">'.$title.'</a> 
								</div>
							</div>';
						}	//  END Loop
					}  //  END If Wavelength
				}	
			}
   		} //  END IF Form has been submitted  
?>

Open in new window

Please refresh
0
Marco GasiFreelancerCommented:
SELECT * FROM (`exp_sites`) WHERE `site_id` = 1 #EE_Config.php L:208 EE_Config::site_prefs()

That dosn't make sense to me: I use CodeIgniter and I think it would be similar to ee but I've never seen something like that...
0
rgranlundAuthor Commented:
That is why I said: @Julian,  I don't think the echo Query is going to give you what you want though cause of the CMS running it's own queries:

It's an EE thing.

I will change it to show the query.  Please refresh

It now looks like this:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
		$cw ='';
		$pulsed = '';
		$pulsednp = '';
		$quasi = '';
		$ultrafastfp = '';
		$ultrafastf = '';
		$wave_max = '';
		$wave_min = '';
		$wavelength = '';
		$ids = '';	
$action=$_GET["action"];
	if($action=="laser_finder") {
		
		if(isset($_GET['cw'])) {
			$cw = $_GET['cw'];
		} else {
			$cw='NONE';
		}
		if(isset($_GET['pulsed'])) {
			$pulsed = $_GET['pulsed'];
		} else {
			$pulsed = 'NONE';
		}
		if(isset($_GET['pulsednp'])) {
			$pulsednp = $_GET['pulsednp'];
		} else {
			$pulsednp = 'NONE';
		}
		if(isset($_GET['quasi'])) {
			$quasi = $_GET['quasi'];
		} else {
			$quasi = 'NONE';
		}
		if(isset($_GET['ultrafastfp'])) {
			$ultrafastfp = $_GET['ultrafastfp'];
		} else {
			$ultrafastfp = 'NONE';
		}
		if(isset($_GET['ultrafastf'])) {
			$ultrafastf = $_GET['ultrafastf'];
		} else {
			$ultrafastf = 'NONE';
		}
		
		if($_GET['wave_max'] !="") {
			$wave_max = $_GET['wave_max'];
		} else {
			$wave_max = "";
		}
		if($_GET['wave_min'] !="") {
			$wave_min = $_GET['wave_min'];
		} else {
			$wave_min = "";
		}
		
		echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';
		
		$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
		
		$where = <<< QUERY
 t1.field_id_3731 IN ({$cw}, {$pulsed}, {$pulsednp}, {$quasi}, {$ultrafastfp}, {$ultrafastf}) OR
  ({$wave_max} != '' AND {$wave_max} IS NOT NULL) OR 
  ({$wave_min} != '' AND {$wave_min} IS NOT NULL)
QUERY;

echo '<pre>';
			var_dump($where);
			echo'</pre>';
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where($where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		
		echo '<pre>';
			var_dump($laser_query );
			echo'</pre>';
			
		
		$lb = $laser_query->result_array();
		
		echo '<pre>';
			var_dump($lb);
			echo'</pre>';
			

Open in new window

0
Julian HansenCommented:
I don't think the echo Query is going to give you what you want though cause of the CMS running it's own queries:
That should echo out the last query executed.
Having said that the dump of your where clause tells an interesting story
string(118) " t1.field_id_3731 IN (CW, NONE, NONE, NONE, NONE, NONE) OR   ( != '' AND  IS NOT NULL) OR    ( != '' AND  IS NOT NULL)" 

Open in new window

Firstly, are those values in the IN() correct - if so they should be enclosed in ''
Secondly, If you look at the  !='' and IS NOT NULL there is no value before those - which raises the question

$wave_max and $wave_min must have field names in the database - you need to replace the $wave_max and $wave_min with those values
0
rgranlundAuthor Commented:
OK, Alot closer, however. I have updated my code but the error is still there.


<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
		$cw ='';
		$pulsed = '';
		$pulsednp = '';
		$quasi = '';
		$ultrafastfp = '';
		$ultrafastf = '';
		$wave_max = '';
		$wave_min = '';
		$wavelength = '';
		$ids = '';	
$action=$_GET["action"];
	if($action=="laser_finder") {
		
		if(isset($_GET['cw'])) {
			$cw = $_GET['cw'];
			$cw = '"'.$cw.'"';
		} else {
			$cw='"NONE"';
		}
		if(isset($_GET['pulsed'])) {
			$pulsed = $_GET['pulsed'];
			$pulsed  = '"'.$pulsed .'"';
		} else {
			$pulsed = '"NONE"';
		}
		if(isset($_GET['pulsednp'])) {
			$pulsednp = $_GET['pulsednp'];
			$pulsednp  = '"'.$pulsednp .'"';
		} else {
			$pulsednp = '"NONE"';
		}
		if(isset($_GET['quasi'])) {
			$quasi = $_GET['quasi'];
			$quasi = '"'.$quasi.'"';
		} else {
			$quasi = '"NONE"';
		}
		if(isset($_GET['ultrafastfp'])) {
			$ultrafastfp = $_GET['ultrafastfp'];
			$ultrafastfp = '"'.$ultrafastfp.'"';
		} else {
			$ultrafastfp = '"NONE"';
		}
		if(isset($_GET['ultrafastf'])) {
			$ultrafastf = $_GET['ultrafastf'];
			$ultrafastf = '"'.$ultrafastf.'"';
		} else {
			$ultrafastf = '"NONE"';
		}
		
		if($_GET['wave_max'] !="") {
			$wave_max = $_GET['wave_max'];
			$wave_max_alt ="t1.field_id_3781 != '' AND t1.field_id_3781 IS NOT NULL";
		} else {
			$wave_max = "";
			$wave_max_alt ="t1.field_id_3781 == '' OR t1.field_id_3781 IS NULL";
		}
		if($_GET['wave_min'] !="") {
			$wave_min = $_GET['wave_min'];
			$wave_min_alt ="t1.field_id_3781 != '' AND t1.field_id_3781 IS NOT NULL";
			
		} else {
			$wave_min = "";
			$wave_min_alt ="t1.field_id_3781 == '' OR t1.field_id_3781 IS NULL";
		}
		
		echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';
		
		$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
		
		$where = <<< QUERY
 t1.field_id_3731 IN ({$cw}, {$pulsed}, {$pulsednp}, {$quasi}, {$ultrafastfp}, {$ultrafastf}) OR
  ({$wave_max_alt}) OR ({$wave_min_alt})
QUERY;

echo '<pre>';
			var_dump($where);
			echo'</pre>';
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where($where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		
		echo '<pre>';
			var_dump($laser_query );
			echo'</pre>';
			
		
		$lb = $laser_query->result_array();
		
		echo '<pre>';
			var_dump($lb);
			echo'</pre>';
			
		foreach($lb as $row){
   			$id = $row['id'];
   			$image = $row['image'];
   			$img  = end(explode('{filedir_51}', $image));
   			$title = $row['title'];
   			$url_title = $row['url_title'];
   			$wave = $row['wavelength'];
   			$wave = preg_replace('[\s]',',', $wave);
   			$wave = preg_replace('/[^0-9,]/','', $wave);
   			$wv = array_filter(explode(",", $wave));
   			$wave = implode(', ', $wv);
   				
   		 			   		
   		 		$laser_cat_query = ee()->db->select('t3.entry_id AS eid, t3.cat_id AS cat_id, t4.cat_url_title AS cat_url')
   		 	 	->from('exp_category_posts t3')
   		 	 	->where('t3.entry_id', $id)
   		 	 	->join('exp_categories t4', 't4.cat_id = t3.cat_id')
   		 	 	->limit(1)
   		 	 	->get();
   		 	 	
   		 		 foreach($laser_cat_query->result_array() as $row_b){
   		 	 		$cat_url = $row_b['cat_url'];	

   		 			if ($title !="") {
   		 				$wave = explode(', ', $wave);
   		 				
   		 				if(!isset($wave[1])) $wave[1] ='';

			$a = false;
   			$b = false; 

   			if($wave[0] <= $wave_max) $a = true; 
   			if($wave[1] >= $wave_min) $b = true;

   			
   			$wavelength = '';
   				
   			if($a && $b) $wavelength = true;
   			
   			if($wavelength == true) {
   					
   		 				echo '<div class="result-box" >
   		   				<div class="image">';
 						echo '<img src="{site_url}/assets/product_images/'.$img .'" /><br />';
						echo'</div>
								<div class="title">
									<a href="{site_url}/lasers/laser/'.$cat_url.'/'.$url_title.'">'.$title.'</a> 
								</div>
							</div>';
						}	//  END Loop
					}  //  END If Wavelength
				}	
			}
   		} //  END IF Form has been submitted  

?>

Open in new window

0
Julian HansenCommented:
You still have $wave_max $wave_min in your where clause which does not make sense.

Either you have to test fields against these values but doing a check one the variables as you have them does not make sense.

What is it that $wave_max and $wave_min represents from a database perspective.
0
rgranlundAuthor Commented:
No, I created a new Variable $wave_max_alt and $wave_min_alt.  Please look at the above code again to see what I did
0
Julian HansenCommented:
I see - but you are using a double == in your WHERE - MySql uses single =
if($_GET['wave_min'] !="") {
			$wave_min = $_GET['wave_min'];
			$wave_min_alt ="t1.field_id_3781 != '' AND t1.field_id_3781 IS NOT NULL";
			
		} else {
			$wave_min = "";
                        // CHANGE TO SINGLE '='
			$wave_min_alt ="t1.field_id_3781 = '' OR t1.field_id_3781 IS NULL";
		}

Open in new window

0
rgranlundAuthor Commented:
The error is still there.  Hmmm.   I thought that was it.  I wonder if it is ->where and should be ->where_in ?
0
Julian HansenCommented:
No it should be right

where_in is only for when you are doing
WHERE field IN ('value', 'value', 'value', ...)

Open in new window


You have a complex WHERE with an IN() and OR - so rather than trying to figure which of CI's helper functions to use rather just use the plain where() which also accepts a string (Refer documentation here on ->where() https://ellislab.com/codeigniter/user-guide/database/active_record.html)

Let's go back to your where - this is what your var_dump is showing
string(179) " t1.field_id_3731 IN ("CW", "NONE", "NONE", "NONE", "NONE", "NONE") OR
  (t1.field_id_3781 = '' OR t1.field_id_3781 IS NULL) OR (t1.field_id_3781 = '' OR t1.field_id_3781 IS 
NULL)"

Open in new window

You have a check for t1.field_id_3781 = '' OR t1.field_id_3781 IS NULL (twice)
Which makes me think that we are not understanding the problem.

Lets try and shortcut this process - what does your data look like?
Can you posted a sample that shows the different values for field_id_3781
Then can you post what you expect to get back when only the CW box is checked - based on the data you have - what do you expect the query to return.

Let's take it from there.
0
rgranlundAuthor Commented:
I had to change th code a little bit so it would show results.  I had to change it to where_in and only use $ids
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
		$cw ='';
		$pulsed = '';
		$pulsednp = '';
		$quasi = '';
		$ultrafastfp = '';
		$ultrafastf = '';
		$wave_max = '';
		$wave_min = '';
		$wavelength = '';
		$ids = '';	
$action=$_GET["action"];
	if($action=="laser_finder") {
		
		if(isset($_GET['cw'])) {
			$cw = $_GET['cw'];
			$cw = '"'.$cw.'"';
		} else {
			$cw='';
		}
		if(isset($_GET['pulsed'])) {
			$pulsed = $_GET['pulsed'];
			$pulsed  = '"'.$pulsed .'"';
		} else {
			$pulsed = '';
		}
		if(isset($_GET['pulsednp'])) {
			$pulsednp = $_GET['pulsednp'];
			$pulsednp  = '"'.$pulsednp .'"';
		} else {
			$pulsednp = '';
		}
		if(isset($_GET['quasi'])) {
			$quasi = $_GET['quasi'];
			$quasi = '"'.$quasi.'"';
		} else {
			$quasi = '';
		}
		if(isset($_GET['ultrafastfp'])) {
			$ultrafastfp = $_GET['ultrafastfp'];
			$ultrafastfp = '"'.$ultrafastfp.'"';
		} else {
			$ultrafastfp = '';
		}
		if(isset($_GET['ultrafastf'])) {
			$ultrafastf = $_GET['ultrafastf'];
			$ultrafastf = '"'.$ultrafastf.'"';
		} else {
			$ultrafastf = '';
		}
		
		if($_GET['wave_max'] !="") {
			$wave_max = $_GET['wave_max'];
			$wave_max_alt ="t1.field_id_3781 != '' AND t1.field_id_3781 IS NOT NULL";
		} else {
			$wave_max = "";
			$wave_max_alt ="t1.field_id_3781 = '' OR t1.field_id_3781 IS NULL";
		}
		if($_GET['wave_min'] !="") {
			$wave_min = $_GET['wave_min'];
			$wave_min_alt ="t1.field_id_3781 != '' AND t1.field_id_3781 IS NOT NULL";
			
		} else {
			$wave_min = "";
			$wave_min_alt ="t1.field_id_3781 = '' OR t1.field_id_3781 IS NULL";
		}
	
		if(($_GET['wave_min'] !="") || ($_GET['wave_max'] !="")) {
			$wave_max_alt ="t1.field_id_3781 != '' AND t1.field_id_3781 IS NOT NULL";
		} else {
			$wave_max_alt ="t1.field_id_3781 = '' OR t1.field_id_3781 IS NULL";
		}
		echo $wave_max_alt;
		
		echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';
		
		$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
		
		$where = <<< QUERY
 t1.field_id_3731 IN ({$cw}, {$pulsed}, {$pulsednp}, {$quasi}, {$ultrafastfp}, {$ultrafastf})
QUERY;

echo '<pre>';
			var_dump($where);
			echo'</pre>';
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where_in('t1.field_id_3731', $ids)
	
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		
		echo '<pre>';
			var_dump($laser_query );
			echo'</pre>';
			
		
		$lb = $laser_query->result_array();
		
		echo '<pre>';
			var_dump($lb);
			echo'</pre>';
			
		foreach($lb as $row){
   			$id = $row['id'];
   			$image = $row['image'];
   			$img  = end(explode('{filedir_51}', $image));
   			$title = $row['title'];
   			$url_title = $row['url_title'];
   			$wave = $row['wavelength'];
   			$wave = preg_replace('[\s]',',', $wave);
   			$wave = preg_replace('/[^0-9,]/','', $wave);
   			$wv = array_filter(explode(",", $wave));
   			$wave = implode(', ', $wv);
   				
   		 			   		
   		 		$laser_cat_query = ee()->db->select('t3.entry_id AS eid, t3.cat_id AS cat_id, t4.cat_url_title AS cat_url')
   		 	 	->from('exp_category_posts t3')
   		 	 	->where('t3.entry_id', $id)
   		 	 	->join('exp_categories t4', 't4.cat_id = t3.cat_id')
   		 	 	->limit(1)
   		 	 	->get();
   		 	 	
   		 		 foreach($laser_cat_query->result_array() as $row_b){
   		 	 		$cat_url = $row_b['cat_url'];	

   		 			if ($title !="") {
   		 				$wave = explode(', ', $wave);
   		 				
   		 				if(!isset($wave[1])) $wave[1] ='';

			$a = false;
   			$b = false; 

   			if($wave[0] <= $wave_max) $a = true; 
   			if($wave[1] >= $wave_min) $b = true;

   			
   			$wavelength = '';
   				
   			if($a && $b) $wavelength = true;
   			
   			if($wavelength == true) {
   					
   		 				echo '<div class="result-box" >
   		   				<div class="image">';
 						echo '<img src="{site_url}/assets/product_images/'.$img .'" /><br />';
						echo'</div>
								<div class="title">
									<a href="{site_url}/lasers/laser/'.$cat_url.'/'.$url_title.'">'.$title.'</a> 
								</div>
							</div>';
						}	//  END Loop
					}  //  END If Wavelength
				}	
			}
   		} //  END IF Form has been submitted  
?>

Open in new window

0
Julian HansenCommented:
I still am in the dark as to the link between $wave_max and $wave_min and your database.

You obviously are wanting to make selection choices based on these values but I need to know against what these must be matched in the data.
0
Julian HansenCommented:
I just had a look at the output from your site and I think I understand what is going on but I have a question.

From your form it looks like you are trying to find records that have a field_id_3781 as one of the $ids or one of the wave length values - however - in reference to the latter - what I need to know - is when you select your data do you need to find those records that are exactly equal to the $wave_max and $wave_min values OR do you want to find records with a wavelength BETWEEN those values.

My second question is - must records with filed_id_3781 = NULL or '' (empty) be returned with any request along with any other records that match the selection made?
0
rgranlundAuthor Commented:
@Julian; Thanks for your continued help!
"From your form it looks like you are trying to find records that have a field_id_3781 as one of the $ids or one of the wave length values"
Correct.

"- however - in reference to the latter - what I need to know - is when you select your data do you need to find those records that are exactly equal to the $wave_max and $wave_min values OR do you want to find records with a wavelength BETWEEN those values."
Between.
 However, I have taken care of that later on in the script due to the way the Data is stored in the field. This is on line: 134-147 below.

This table / field is part of a Matrix plugin for my CMS (EE) and it stores data in one field with no commas, only spaces.  So what I have done is taken the field and turned it into an Array cause it's always the first two numbers that need to be compared. The first number is always the Max and the Second number is always the Min.

"My second question is - must records with filed_id_3781 = NULL or '' (empty) be returned with any request along with any other records that match the selection made?"
No.
if the field is empty and a value has been entered for either Max or Min or Both, than it should not be returned.
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
		$cw ='';
		$pulsed = '';
		$pulsednp = '';
		$quasi = '';
		$ultrafastfp = '';
		$ultrafastf = '';
		$wave_max = '';
		$wave_min = '';
		$wavelength = '';
		$ids = '';	
$action=$_GET["action"];
	if($action=="laser_finder") {
		
		if(isset($_GET['cw'])) {
			$cw = $_GET['cw'];
			$cw = '"'.$cw.'"';
		} else {
			$cw='';
		}
		if(isset($_GET['pulsed'])) {
			$pulsed = $_GET['pulsed'];
			$pulsed  = '"'.$pulsed .'"';
		} else {
			$pulsed = '';
		}
		if(isset($_GET['pulsednp'])) {
			$pulsednp = $_GET['pulsednp'];
			$pulsednp  = '"'.$pulsednp .'"';
		} else {
			$pulsednp = '';
		}
		if(isset($_GET['quasi'])) {
			$quasi = $_GET['quasi'];
			$quasi = '"'.$quasi.'"';
		} else {
			$quasi = '';
		}
		if(isset($_GET['ultrafastfp'])) {
			$ultrafastfp = $_GET['ultrafastfp'];
			$ultrafastfp = '"'.$ultrafastfp.'"';
		} else {
			$ultrafastfp = '';
		}
		if(isset($_GET['ultrafastf'])) {
			$ultrafastf = $_GET['ultrafastf'];
			$ultrafastf = '"'.$ultrafastf.'"';
		} else {
			$ultrafastf = '';
		}
		
		if($_GET['wave_max'] !="") {
			$wave_max = $_GET['wave_max'];
			$wave_max_alt ="t1.field_id_3781 != '' AND t1.field_id_3781 IS NOT NULL";
		} else {
			$wave_max = "";
			$wave_max_alt ="t1.field_id_3781 = '' OR t1.field_id_3781 IS NULL";
		}
		if($_GET['wave_min'] !="") {
			$wave_min = $_GET['wave_min'];
			$wave_min_alt ="t1.field_id_3781 != '' AND t1.field_id_3781 IS NOT NULL";
			
		} else {
			$wave_min = "";
			$wave_min_alt ="t1.field_id_3781 = '' OR t1.field_id_3781 IS NULL";
		}
	
		if(($_GET['wave_min'] !="") || ($_GET['wave_max'] !="")) {
			$wave_max_alt ="t1.field_id_3781 != '' AND t1.field_id_3781 IS NOT NULL";
		} else {
			$wave_max_alt ="t1.field_id_3781 = '' OR t1.field_id_3781 IS NULL";
		}
		echo $wave_max_alt;
		
		echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';
		
		$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
		
		$where = <<< QUERY
 t1.field_id_3731 IN ({$cw}, {$pulsed}, {$pulsednp}, {$quasi}, {$ultrafastfp}, {$ultrafastf})
QUERY;

echo '<pre>';
			var_dump($where);
			echo'</pre>';
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where_in('t1.field_id_3731', $ids)
	
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		
		echo '<pre>';
			var_dump($laser_query );
			echo'</pre>';
			
		
		$lb = $laser_query->result_array();
		
		echo '<pre>';
			var_dump($lb);
			echo'</pre>';
			
		foreach($lb as $row){
   			$id = $row['id'];
   			$image = $row['image'];
   			$img  = end(explode('{filedir_51}', $image));
   			$title = $row['title'];
   			$url_title = $row['url_title'];
   			$wave = $row['wavelength'];
   			$wave = preg_replace('[\s]',',', $wave);
   			$wave = preg_replace('/[^0-9,]/','', $wave);
   			$wv = array_filter(explode(",", $wave));
   			$wave = implode(', ', $wv);
   				
   		 			   		
   		 		$laser_cat_query = ee()->db->select('t3.entry_id AS eid, t3.cat_id AS cat_id, t4.cat_url_title AS cat_url')
   		 	 	->from('exp_category_posts t3')
   		 	 	->where('t3.entry_id', $id)
   		 	 	->join('exp_categories t4', 't4.cat_id = t3.cat_id')
   		 	 	->limit(1)
   		 	 	->get();
   		 	 	
   		 		 foreach($laser_cat_query->result_array() as $row_b){
   		 	 		$cat_url = $row_b['cat_url'];	

   		 			if ($title !="") {
   		 				$wave = explode(', ', $wave);
   		 				
   		 				if(!isset($wave[1])) $wave[1] ='';

			$a = false;
   			$b = false; 

   			if($wave[0] <= $wave_max) $a = true; 
   			if($wave[1] >= $wave_min) $b = true;

   			
   			$wavelength = '';
   				
   			if($a && $b) $wavelength = true;
   			
   			if($wavelength == true) {
   					
   		 				echo '<div class="result-box" >
   		   				<div class="image">';
 						echo '<img src="{site_url}/assets/product_images/'.$img .'" /><br />';
						echo'</div>
								<div class="title">
									<a href="{site_url}/lasers/laser/'.$cat_url.'/'.$url_title.'">'.$title.'</a> 
								</div>
							</div>';
						}	//  END Loop
					}  //  END If Wavelength
				}	
			}
   		} //  END IF Form has been submitted  
?>

Open in new window

0
Julian HansenCommented:
So what you are wanting is
select fields from table where t.field_id_3731 in ('CW','Pulsed',...)

Then if wave_max > 0 or wave_min > 0 you want to add to the above where clause
OR t.field_id_3731 between $wave_min and $wave_max

In other words you only add the second OR if there are values for those fields.

Do the ->where_in( ... )

If $wave_max > 0 then do the
->or_where('t.field_id_3731>', $wave_min)
->or_where('t.field_id_3731<=', $wave_max);

I don't have a way of testing this so just painting a general picture.
0
rgranlundAuthor Commented:
Is this what you are saying?
$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
	->from('exp_channel_data t1')
	->where_in('t1.field_id_3731', $ids)
        ->or_where('t.field_id_3781>', $wave_min)
         ->or_where('t.field_id_3781<=', $wave_max)
	->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
	->get();

Open in new window


Also, in field_id_3781 sometimes it has AlphaNumerica Characters.  Does that matter
0
Julian HansenCommented:
Not really but I think I just spotted why there is a bit of disconnect in communications.

field_id_3731 is not the same as field_id_3781 - my apologies but I misread the 8 as a 3 and vice versa and assumed they were the same field.

To confirm you are checking 3781 against $ids and 3731 against wave max and min?
0
rgranlundAuthor Commented:
No.  The opposite.
checking 3731 against $ids and 3781 against wave max and min
0
Julian HansenCommented:
If so you what you want is
Using a WHERE string
Using BETWEEN
// implode the $ids into a comma separated list of quoted strings
$idstr = implode("','", $ids);
$where = "t.field_id_3731 IN ('{$idstr}') OR t.field_id_3781 BETWEEN {$wave_min} AND {$wave_max}"

Open in new window

Alternatively USING >=, <=
$idstr = "'" . implode("','", $ids) . "'";
$where = "t.field_id_3731 IN ({$idstr}) OR (t.field_id_3781 >= {$wave_min} AND t.field_id_3781 <={$wave_max})";

Open in new window

And then do a straight
->where($where);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rgranlundAuthor Commented:
@Julian, thanks.  Let me tinker for a little bit and I'll let you know.

Code Now:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
		$cw ='';
		$pulsed = '';
		$pulsednp = '';
		$quasi = '';
		$ultrafastfp = '';
		$ultrafastf = '';
		$wave_max = '';
		$wave_min = '';
		$wavelength = '';
		$ids = '';	
$action=$_GET["action"];
	if($action=="laser_finder") {
		
		if(isset($_GET['cw'])) {
			$cw = $_GET['cw'];
		} else {
			$cw='';
		}
		if(isset($_GET['pulsed'])) {
			$pulsed = $_GET['pulsed'];
		} else {
			$pulsed = '';
		}
		if(isset($_GET['pulsednp'])) {
			$pulsednp = $_GET['pulsednp'];
		} else {
			$pulsednp = '';
		}
		if(isset($_GET['quasi'])) {
			$quasi = $_GET['quasi'];
		} else {
			$quasi = '';
		}
		if(isset($_GET['ultrafastfp'])) {
			$ultrafastfp = $_GET['ultrafastfp'];
		} else {
			$ultrafastfp = '';
		}
		if(isset($_GET['ultrafastf'])) {
			$ultrafastf = $_GET['ultrafastf'];
		} else {
			$ultrafastf = '';
		}
		
		if($_GET['wave_max'] !="") {
			$wave_max = $_GET['wave_max'];
		} else {
			$wave_max = "";
		}
		if($_GET['wave_min'] !="") {
			$wave_min = $_GET['wave_min'];
			
		} else {
			$wave_min = "";
		}
	
		echo '<h2>Wave Max:'. $wave_max.'<br />Wave Min: '.$wave_min.'</h2><br /><br />';
		
		$ids = array($cw, $pulsed, $pulsednp, $quasi, $ultrafastfp, $ultrafastf);
		
		$idstr = "'" . implode("','", $ids) . "'";
		$where = "t.field_id_3731 IN ('{$idstr}') OR t.field_id_3781 BETWEEN {$wave_min} AND {$wave_max}";
			
			
			echo '<pre>';
			var_dump($where);
			echo'</pre>';
	
	$laser_query = ee()->db->select('t1.entry_id AS id, t1.field_id_1091 AS title, t1.field_id_1141 AS image, t1.field_id_3781 AS wavelength, t2.url_title AS url_title')
		->from('exp_channel_data t1')
		->where($where)
		->join('exp_channel_titles t2', 't2.entry_id = t1.entry_id')
		->get();
		
			echo '<pre>';
			var_dump($laser_query );
			echo'</pre>';
			
		
		$lb = $laser_query->result_array();
		
		echo '<pre>';
			var_dump($lb);
			echo'</pre>';
			
		foreach($lb as $row){
   			$id = $row['id'];
   			$image = $row['image'];
   			$img  = end(explode('{filedir_51}', $image));
   			$title = $row['title'];
   			$url_title = $row['url_title'];
   			$wave = $row['wavelength'];
   			$wave = preg_replace('[\s]',',', $wave);
   			$wave = preg_replace('/[^0-9,]/','', $wave);
   			$wv = array_filter(explode(",", $wave));
   			$wave = implode(', ', $wv);
   				
   		 			   		
   		 		$laser_cat_query = ee()->db->select('t3.entry_id AS eid, t3.cat_id AS cat_id, t4.cat_url_title AS cat_url')
   		 	 	->from('exp_category_posts t3')
   		 	 	->where('t3.entry_id', $id)
   		 	 	->join('exp_categories t4', 't4.cat_id = t3.cat_id')
   		 	 	->limit(1)
   		 	 	->get();
   		 	 	
   		 		 foreach($laser_cat_query->result_array() as $row_b){
   		 	 		$cat_url = $row_b['cat_url'];	

   		 			if ($title !="") {
   		 				$wave = explode(', ', $wave);
   		 				
   		 				if(!isset($wave[1])) $wave[1] ='';

			$a = false;
   			$b = false; 

   			if($wave[0] <= $wave_max) $a = true; 
   			if($wave[1] >= $wave_min) $b = true;

   			
   			$wavelength = '';
   				
   			if($a && $b) $wavelength = true;
   			
   			if($wavelength == true) {
   					
   		 				echo '<div class="result-box" >
   		   				<div class="image">';
 						echo '<img src="{site_url}/assets/product_images/'.$img .'" /><br />';
						echo'</div>
								<div class="title">
									<a href="{site_url}/lasers/laser/'.$cat_url.'/'.$url_title.'">'.$title.'</a> 
								</div>
							</div>';
						}	//  END Loop
					}  //  END If Wavelength
				}	
			}
   		} //  END IF Form has been submitted  
?>

Open in new window

0
rgranlundAuthor Commented:
This specific answer was not the exact answer but the entire process helped me to get things correct.  Thanks for all of your help.  I learned alot.
0
Julian HansenCommented:
You are welcome - good luck with your project.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.