I have AND operator in Where statement of query, how can i know which statement did not returned

[halfsparrow]

I have a php code SQL QUERY GOES LIKE

 $command = "SELECT * from {$uniluxModel} where gpm = {$uniluxGpm} AND ewt = {$uniluxEwt}";

where uniluxGpm and uniluxEwt are the variable user will enter in the form. i have a database where there are certain values for gpm and certain values for ewt For example gpm has value ranging from 1-5 with difference of 0.5 eg: 1,1.5,2,2.5 etc and ewt has values ranging from 30-100 with differerence of 10 eg: 30,40,50

so if users enters gpm = 1 and ewt as 30 it returns me values from databse but if user enters gpm =1.75 and ewt as 30 it returns me not found How can i know that database did'nt found gpm and it found ewt

Thanks in advance

[gw1500se]

Try removing the {}. Not sure why you think you need that.

[halfsparrow]

1 minute ago, gw1500se said:

Try removing the {}. Not sure why you think you need that.

That's not giving me errors so its fine, i am just confused about the solution. also i am not using mssqli i am using PDO

[ginerjm]

Maybe you want to use an OR instead of an AND?  That will give your records with one or the other.  The AND only gives the ones with both values.

And - those {} are really not needed, although you should begin to use prepared queries.

Edited October 29, 2021 by ginerjm

[halfsparrow]

I have tried using OR but the issue is if both are correct it won't return me the value for both, so it doesn't work as well

[gw1500se]

I think I understand the problem. You want the records where either condition is true, right? I think you need 2 where clauses separated by an or.

[halfsparrow]

Just now, gw1500se said:

I think I understand the problem. You want the records where either condition is true, right? I think you need 2 where clauses separated by an or.

No what i want is if query finds both the value then i need to print out values from database, if query finds only gpm and ewt is not present then i will have to interpolate ewt, similarly if ewt is found and gpm is not found then i need to interolate values for gpm. i have everyhing figured out. just confused about how to determine which value is not obtained by the query

[ginerjm]

If you use an OR you will get any record that matches either one.  Simply by looking at the results you will know which one was found.

Not really sure exactly what you are trying to do .   HOw about explaining in english, not in the form of a query.

[halfsparrow]

Okay so basically, i am working on a webpage where there is form and user will enter value accordingly.

i have 2 fields that is GPM and EWT where user will enter value of their choice

now i have a database which looks like the picture i have attached.

Now if user enters 1 for gpm and 30 ewt its simple because its in the database, i will simply fetch totCool and print it out

but if user enters 1.75 for gpm and 30 for ewt i will have to take average of totCool for 1.5 and 2 for 30 ewt because 1.75 is not in the table

similarly if user enters 1.5 for gpm and 35 for ewt i will have to take average of totCool for 30 and 40 for 1.5 gpm because 35 is not in the table

Similarly if user enters 1.75 and 35 i will have to take average for both and calculate

 

SO the question here how can i know that database cannot find gpm or ewt or both

 

Hope this helps to understand you guys

[ginerjm]

When you process the query results of an OR your code will KNOW what was found.  Your logic after that will handle the calcs.

[halfsparrow]

Just now, ginerjm said:

When you process the query results of an OR your code will KNOW what was found.  Your logic after that will handle the calcs.

What happens for or statement is 

If i enter 1 for gpm and 50 for ewt it shows only gpm is found as it will find gpm first and stop processing afterwards

[ginerjm]

You are not telling us something.  IF you run an OR query it will return ANY RECORD matching one or the other and will not stop until it searches the whole table.  Not sure what you think is happening.

[halfsparrow]

Its not working

[ginerjm]

HOw about showing us the exact query that you are running?  It apparently is not what you posted.

[halfsparrow]

Just now, ginerjm said:

HOw about showing us the exact query that you are running?  It apparently is not what you posted.

 

$commandCount = "SELECT COUNT(*) as 'countRow' from {$uniluxModel} where gpm = {$uniluxGpm} OR ewt = {$uniluxEwt}";
         $command = "SELECT * from {$uniluxModel} where gpm = {$uniluxGpm} AND ewt = {$uniluxEwt}";
     
      echo " Command is ".$command;
       $stmtCount = $dbConn->prepare($commandCount);
       $exeOkCount = $stmtCount->execute();
    //print_r($stmt->fetchColumn());
    
    
    $stmt =  $dbConn->prepare($command);
    $exeOk = $stmt->execute();
  
      if ($stmtCount->fetchColumn() == 1) {
          echo "inside";
    
         if($exeOk){
         echo "<br>";
         while ($row = $stmt->fetch()){
         echo "gpm is : " .$row['gpm'] ."    "." ewt is " .$row['ewt'] ."    "."<br> tot cool is " .$row['totCool']. "<br>" ;
         }
       }
         else
         echo "error executing query";
    
     }
    
     else{
       echo "Not Found!";
     }

it gives me correct result just not show me which one is not found. suppose i enter 1.75 and 30 i t shows me not found directly i just want that it shows me that gpm is not found or ewt is not found or both are not found

[ginerjm]

You are misleading us.   You have TWO DIFFERENT QUERIES here.  Why????

As I already said - run the OR query and then go thru the records examining the values of your two columns to see if you matched both or not.  Then do whatever you need to do.  It is that simple.  Don't know what the count thing is doing for you but that is a whole different question.

Good bye.

Edited October 29, 2021 by ginerjm

[halfsparrow]

Count thing counts the number of column fetched that helped me to determine how many columns are fetched and can do further calc

[ginerjm]

You can do your count while you are processing the results of the or query.  Logic. Logic.  The Or query gets you the records you want to look at.  Count them up any way you want.

You seem to want things both ways.  You want to get the ones what match both and you also want to get the ones that don't match both.   Query for the ones that match EITHER and then let your logic decide what to do with each one (and count it) as you loop thru the results

[ginerjm]

IF I understand you at all here is how I would do this exercise:

//  assuming that you are using PDO
//  hint:  good practice is to replace the * with just the field names that you need to retrieve
$q = "SELECT * from $uniluxModel 
		where gpm = :gpm or ewt = :ewt";
$qst = $dbConn->prepare($q);
$parms = array(
		'gpm'=>$uniluxGpm,
		'ewt'=>$uniluxEwt
		);
$qst->execute($parms);
if ($qst)
{
	$rowcnt = $qst->rowCount();
	echo "Found $rowcnt records matching at least one of the inputs<br>";
	while ($row = $qst->fetch())
	{
		if ($row['gpm'] == $uniluxGpm &&
			$row['ewt'] == $uniluxEwt)
		{
			(process a record that matches both values and prepare the output)
		}
		elseif($row['gpm'] == $uniluxGpm)
		{
			(process a record that only matches the gpm value and prepare the output)
		}
		else
		{
			(process the records that only match the ewt value and prepare the output)
		}
		//
		// now output what you prepared from the logic above.
		//
	}
}
else
	echo "Error executing query";
exit();

Hope this is clear.  YOu simply need to add some code/calcs to the if clauses.

[Barand]

Or give your users option menus of values that do exist so they aren't guessing

[Barand]

Here's a way...

$sql = "SELECT DISTINCT gpm, ewt 
        FROM
            (
            SELECT id, gpm FROM uniluxmodel WHERE gpm = ?
            ) gpm1
            LEFT JOIN
            (
            SELECT id, ewt FROM uniluxmodel WHERE ewt = ?
            ) ewt1 USING (id)
           
        UNION
        SELECT  gpm, ewt FROM
            (
            SELECT id, ewt FROM uniluxmodel WHERE ewt = ?
            ) ewt2
            LEFT JOIN
            (
            SELECT id, gpm FROM uniluxmodel WHERE gpm = ?
            ) gpm2 USING (id)
        ORDER BY gpm IS NULL, ewt IS NULL
        LIMIT 1
        ";
$stmt = $db->prepare($sql);

$gpm = 1.2;
$ewt = 40;
$stmt->execute( [ $gpm, $ewt, $ewt, $gpm ] );
//        +-----+-----+
//        | gpm | ewt |
//        +-----+-----+
//        |     | 40  |
//        +-----+-----+

$gpm = 1.5;
$ewt = 45;
$stmt->execute( [ $gpm, $ewt, $ewt, $gpm ] );
//        +------+-----+
//        | gpm  | ewt |
//        +------+-----+
//        | 1.50 |     |
//        +------+-----+


$gpm = 2.0;
$ewt = 60;
$stmt->execute( [ $gpm, $ewt, $ewt, $gpm ] );
//        +------+-----+
//        | gpm  | ewt |
//        +------+-----+
//        | 2.00 | 60  |
//        +------+-----+


$gpm = 2.2;
$ewt = 61;
$stmt->execute( [ $gpm, $ewt, $ewt, $gpm ] );
//        NO RECORDS FOUND

 

[Psycho]

OK, let me see if I understand the problem correctly.

If the user enters values for gpm and ewt AND there is a record that exactly matches both of those values, then you want the totCool value for that record.

However, if there is no exact match, but either gpm or ewt does match records, then you want the average of totCool for the two records above and below the unmatched value.

There are two scenarios that are unclear.

1. What do you want to do if neither  gpm nor ewt match any records?
2. What should happen if one value matches, but for the unmatched value there is no record above (or no record below) to use to calculate an average?

 

[Psycho]

OK, here is how I would tackle this problem.

1. Run a query with an AND condition. If a record is returned, then it is an exact match and use the totCool value. If no record is returned, then there was no record matching both. Proceed to step 2
2. Run a query to get the two records matching gpm with the closest (high and low) values for ewt and return the average of totCool for those two record. If no record is returned, then there was were not records matching gpm  with one above and one below the ewt value. Proceed to step 3
3. Run a query to get the two records matching ewt with the closest (high and low) values for gpm and return the average of totCool for those two record. If no record is returned, then there was were not records matching ewt  with one above and one below the gpm value. Exit with error condition.

<?php

//Var to hold result
$result = false;

//See if there is an exact match on both gpw and ewt
$query = "SELECT totCool from {$uniluxModel} where gpm = :uniluxGpm AND ewt = :uniluxEwt";
$stmt = $dbConn->prepare($queryStr);
$stmt->execute(array(':uniluxGpm' => $uniluxGpm, ':uniluxEwt' => $uniluxEwt));
if($stmt->rowCount() > 0)
{
    //There was an exact match
    $result = $stmt->fetchColumn()
}
//If no result try getting the avereage records matching gpm and high/low ewt
if (!$result)
{
    $query = "SELECT AVG(totCool)
              FROM 
              (
                (
                  SELECT totCool
                  FROM wxyz
                  WHERE gpm = :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
                )
                UNION
                (
                  SELECT totCool
                  FROM wxyz
                  WHERE gpm = :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
                )
              ) as avgTable";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was a result
        $result = $stmt->fetchColumn()
    }
}
//If no result try getting the avereage records matching ewt and high/low gpm
if (!$result)
{
    $query = "SELECT AVG(totCool)
              FROM 
              (
                (
                  SELECT totCool
                  FROM wxyz
                  WHERE gpm < :uniluxGpm1 AND ewt = :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
                )
                UNION
                (
                  SELECT totCool
                  FROM wxyz
                  WHERE gpm > :uniluxGpm2 AND ewt = :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
                )
              ) as avgTable";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was a result
        $result = $stmt->fetchColumn()
    }
}

if(!$result)
{
    //No records that match either value
    echo "Not enough data to compute";
}
else
{
    echo "totValue: {$result}";
}

?>

 

[Barand]

Plan C - add a couple of "range" tables

gpm_range                         ewt_range
+------+--------+--------+        +-----+--------+--------+
| gpm  | gpm_lo | gpm_hi |        | ewt | ewt_lo | ewt_hi |
+------+--------+--------+        +-----+--------+--------+
| 1.00 | 0.01   | 1.11   |        | 30  | 1      | 34     |
| 1.25 | 1.12   | 1.36   |        | 40  | 35     | 44     |
| 1.50 | 1.37   | 1.74   |        | 50  | 45     | 54     |
| 2.00 | 1.75   | 2.99   |        | 60  | 55     | 64     |
+------+--------+--------+        | 70  | 65     | 74     |
                                  | 80  | 75     | 85     |
                                  +-----+--------+--------+

code

$sql = "SELECT CASE u.gpm WHEN ?
                THEN u.gpm
                ELSE concat(u.gpm, '*')
        END as gpm
     , CASE u.ewt WHEN ?
                THEN u.ewt
                ELSE concat(u.ewt, '*')
        END as ewt
FROM uniluxmodel u 
     JOIN gpm_range g USING (gpm)
     JOIN ewt_range e USING (ewt)
WHERE ? BETWEEN g.gpm_lo AND g.gpm_hi
  AND ? BETWEEN e.ewt_lo AND e.ewt_hi
        ";
$stmt = $db->prepare($sql);

$gpm = 1.2;
$ewt = 40;
$stmt->execute( [ $gpm, $ewt, $gpm, $ewt ] );
//        +-------+-----+
//        | gpm   | ewt |
//        +-------+-----+
//        | 1.25* | 40  |                          * denotes not exact match
//        +-------+-----+

$gpm = 1.5;
$ewt = 45;
$stmt->execute( [ $gpm, $ewt, $gpm, $ewt ] );
//        +------+-----+
//        | gpm  | ewt |
//        +------+-----+
//        | 1.50 | 50* |
//        +------+-----+


$gpm = 2.0;
$ewt = 60;
$stmt->execute( [ $gpm, $ewt, $gpm, $ewt ] );
//        +------+-----+
//        | gpm  | ewt |
//        +------+-----+
//        | 2.00 | 60  |
//        +------+-----+



$gpm = 2.2;
$ewt = 90;
$stmt->execute( [ $gpm, $ewt, $gpm, $ewt ] );
//        NO RECORDS FOUND

 

[halfsparrow]

On 10/29/2021 at 3:46 PM, Barand said:

Or give your users option menus of values that do exist so they aren't guessing

i Cant do this, because user will also demand to enter value that are not in the database