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

Barand · November 1, 2021

Fair enough. What about Plan C?

halfsparrow · November 1, 2021

On 10/29/2021 at 8:35 PM, Psycho said:

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.

What do you want to do if neither gpm nor ewt match any records?

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?

1) If neither GPM or EWR match any records in that case i will do average of both of them, also i dont want average in query i will execute query and then add maths in that by saving the things i want in a variable

2) It will not be possible as user will never enter value that low or that high, to clear things out it is a real world situation web page where GPM is gallons per minute and ewt is entering water temperature so user will only enter from desired range

halfsparrow · November 1, 2021

On 10/29/2021 at 9:32 PM, Psycho said:

OK, here is how I would tackle this problem.

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
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
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}";
}

?>

This looks perfect to me but just a few changes instead of getting getting average in query i want simple query to select * from table and then will do maths in if statement, also for when neither records are matched can you help me with query, i want that query to be treated same as before like when we did'nt found gpm we took one value below or above it so same way we want one value up and one value down for both gpm and ewt

halfsparrow · November 1, 2021

On 10/30/2021 at 5:24 AM, Barand said:

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

Didn't quite understood what you did here

Barand · November 1, 2021

1 hour ago, halfsparrow said:

Didn't quite understood what you did here

If the user enters a gpm value between 1.37 and 1.74, then the record with a gpm of 1.5 is selected. If the user didn't enter 1.5 it is flagged with an * as not being an exact match.

gpm_range                  
+------+--------+--------+
| gpm  | gpm_lo | gpm_hi |
+------+--------+--------+
| 1.00 | 0.01   | 1.11   |
| 1.25 | 1.12   | 1.36   |
| 1.50 | 1.37   | 1.74   |
| 2.00 | 1.75   | 2.99   |
+------+--------+--------+

Same with other values, the record with closest matches being returned.

Psycho · November 1, 2021

2 hours ago, halfsparrow said:

This looks perfect to me but just a few changes instead of getting getting average in query i want simple query to select * from table and then will do maths in if statement, also for when neither records are matched can you help me with query, i want that query to be treated same as before like when we did'nt found gpm we took one value below or above it so same way we want one value up and one value down for both gpm and ewt

The first part in bold is easy enough. But, you need to provide more details to do the second item. If no records match ewt or gpm, then you need to define how you want to determine which records to return. For example, if the user enters gpm = 1.75 and ewt = 35, which records do you want returned from the sample data below? Do you want two records or four?

image.png.d3e1d695632d5e268e3e5e3ce97f32c7.png

halfsparrow · November 1, 2021

2 minutes ago, Psycho said:

The first part in bold is easy enough. But, you need to provide more details to do the second item. If no records match ewt or gpm, then you need to define how you want to determine which records to return. For example, if the user enters gpm = 1.75 and ewt = 35, which records do you want returned from the sample data below? Do you want two records or four?

If user enters gpm, = 1.75 and ewt as 35 i will have to do calculation for totCool for 1.5 gpm and 2 gpm and 30 and 40 ewt

Basically the value below and the value above

Edited November 1, 2021 by halfsparrow

halfsparrow · November 2, 2021

21 hours ago, Psycho said:

The first part in bold is easy enough. But, you need to provide more details to do the second item. If no records match ewt or gpm, then you need to define how you want to determine which records to return. For example, if the user enters gpm = 1.75 and ewt = 35, which records do you want returned from the sample data below? Do you want two records or four?

Can you please look into this?

Psycho · November 2, 2021

On 11/1/2021 at 12:54 PM, halfsparrow said:

If user enters gpm, = 1.75 and ewt as 35 i will have to do calculation for totCool for 1.5 gpm and 2 gpm and 30 and 40 ewt

Basically the value below and the value above

That really doesn't answer the question. What is the LOGIC to be implemented to determine which records to get? The sample values I copied are probably too "easy" for that question. Since there is a 'logical' record for both the low values and both the high records. But, taking the same example values of gpm, = 1.75 and ewt = 35, lets say these values exist

gpm | ewt
1.5 | 50 --- Lower gpm but higher ewt
2.0 | 10 --- Higher gpm but very low ewt
4.5 | 30 --- Much higher gpm but ewt is only a little lower

If you can explain the logic you want to use to determine which records to pull, I can look at providing something.

halfsparrow · November 3, 2021

17 hours ago, Psycho said:

That really doesn't answer the question. What is the LOGIC to be implemented to determine which records to get? The sample values I copied are probably too "easy" for that question. Since there is a 'logical' record for both the low values and both the high records. But, taking the same example values of gpm, = 1.75 and ewt = 35, lets say these values exist

gpm | ewt
1.5 | 50 --- Lower gpm but higher ewt
2.0 | 10 --- Higher gpm but very low ewt
4.5 | 30 --- Much higher gpm but ewt is only a little lower

If you can explain the logic you want to use to determine which records to pull, I can look at providing something.

By lower i meant next lowest, and by higher i meant next highest,

so for example if ewt is anywhere between 30-40, lets say 36 then lowest ewt should be 30 and highest ewt should be 40.

We can get this by using max and min statement, like select * from table where ewt =(select max ewt from table where ewt<entered ewt) : this is for the lower ewt .

so its like

GPM LOWER GPM HIGHER GPM

1.2 1 1.25

1.3 1.25 1.5

Same for EWT

EWT LOWER EWT HIGHER EWT

35 30 40

38 30 40

46 40 50

Hope it makes clear now

Barand · November 3, 2021

Plan D, staying with ranges but setting the lo/hi of range to extend from the value below to the value above

gpm_range                           ewt_range;
+------+--------+--------+          +-----+--------+--------+
| gpm  | gpm_lo | gpm_hi |          | ewt | ewt_lo | ewt_hi |
+------+--------+--------+          +-----+--------+--------+
| 1.00 |   0.00 |   1.25 |          |  30 |      0 |     40 |
| 1.25 |   1.00 |   1.50 |          |  40 |     30 |     50 |
| 1.50 |   1.25 |   2.00 |          |  50 |     40 |     60 |
| 2.00 |   1.50 |   3.00 |          |  60 |     50 |     70 |
+------+--------+--------+          |  70 |     60 |     80 |
                                    |  80 |     70 |     90 |
                                    +-----+--------+--------+

then

SELECT DISTINCT
        gpm.gpm
      , ewt.ewt
FROM (
     SELECT u.gpm
     FROM
         uniluxmodel u
         JOIN
         (
            SELECT gpm
            FROM gpm_range
            WHERE 1.75 BETWEEN gpm_lo AND gpm_hi
         ) g USING (gpm)
     ) gpm
     JOIN
     (
     SELECT u.ewt
     FROM
         uniluxmodel u
         JOIN
         (
            SELECT ewt
            FROM ewt_range
            WHERE 35 BETWEEN ewt_lo AND ewt_hi
         ) e USING (ewt)
     ) ewt;

+------+------+
| gpm  | ewt  |
+------+------+
| 1.50 |   30 |
| 2.00 |   30 |
| 1.50 |   40 |
| 2.00 |   40 |
+------+------+

Edited November 3, 2021 by Barand

Psycho · November 3, 2021

1 hour ago, halfsparrow said:

Hope it makes clear now

No, it is not. I completely understand how to get min/max values from a database. There is a logic problem that I guess I am not doing well at explaining.

Quote

so for example if ewt is anywhere between 30-40, lets say 36 then lowest ewt should be 30 and highest ewt should be 40

Yes, determining the lower and upper bounds for ewt is trivial. However, you are not specifically looking for the ewt values - you are after the totCool values. And there are multiple records where ewt = 30 and multiple records where ewt = 40. So, what is the logic to determine which record where ewt = 30 (or where ewt = 40) should be retrieved?

@Barand provides one solution where four records are retrieved. It finds:

The record with the highest gpm value below the target AND the highest ewt value below the target
The record with the lowest gpm value above the target AND the highest ewt value below the target
The record with the highest gpm value below the target AND the lowest ewt value above the target
The record with the lowest gpm value above the target AND the lowest ewt value above the target

You would then have to determine in the code how you want to use those to calculate a relevant totCool value.

halfsparrow · November 3, 2021

22 minutes ago, Psycho said:

No, it is not. I completely understand how to get min/max values from a database. There is a logic problem that I guess I am not doing well at explaining.

Yes, determining the lower and upper bounds for ewt is trivial. However, you are not specifically looking for the ewt values - you are after the totCool values. And there are multiple records where ewt = 30 and multiple records where ewt = 40. So, what is the logic to determine which record where ewt = 30 (or where ewt = 40) should be retrieved?

@Barand provides one solution where four records are retrieved. It finds:

The record with the highest gpm value below the target AND the highest ewt value below the target

The record with the lowest gpm value above the target AND the highest ewt value below the target

The record with the highest gpm value below the target AND the lowest ewt value above the target

The record with the lowest gpm value above the target AND the lowest ewt value above the target

You would then have to determine in the code how you want to use those to calculate a relevant totCool value.

oh now i got it,

So what i want is if, Gpm is 1.75 and ewt is 45 then i want totcool value for:

1) The record with lower value for gpm and lower value for ewt. for this example, totcool for 1.5 gpm and 40 ewt

2) The record with higher value for gpm and higher value for ewt. for this example, totcool for 2.0 gpm and 50 ewt

And for @Barand solution, i appreciate his solution but i am in search of solution that does not require me to create another table, because i just have upload a small snippet of my database,it is really large and i will not be able to create a range table for that many entries.

Edited November 3, 2021 by halfsparrow

Barand · November 3, 2021

If you can use fixed value range offsets for gmp and ewt (in this example they are gmp ± 0.4, ewt ± 9 ) then extra tables not required

mysql> SELECT gpm, ewt
    -> FROM uniluxmodel
    -> WHERE gpm BETWEEN 1.75 - 0.4 AND 1.75 + 0.4
    ->       AND ewt BETWEEN 35 - 9 AND 35 + 9;
+------+------+
| gpm  | ewt  |
+------+------+
| 1.50 |   30 |
| 1.50 |   40 |
| 2.00 |   30 |
| 2.00 |   40 |
+------+------+

halfsparrow · November 3, 2021

yeah but the issue here will be i will have to hard code for every value for gpm, because the uniluxmodel we are using here is the name of table and i have total 15 tables and the difference between each gpm varies according to table and its not something that follows a particular trend.

So the best query will be the one that @Psycho suggested.

Psycho · November 3, 2021

1 hour ago, halfsparrow said:

oh now i got it,

So what i want is if, Gpm is 1.75 and ewt is 45 then i want totcool value for:

1) The record with lower value for gpm and lower value for ewt. for this example, totcool for 1.5 gpm and 40 ewt

2) The record with higher value for gpm and higher value for ewt. for this example, totcool for 2.0 gpm and 50 ewt

But, there is not necessarily a logical determination for those. Let's just take option #1 to illustrate the problem. The target values the user has entered are gpm = 1.5 and ewt = 40. Now, let's say there are the following records in the database

id | gpm | ewt
--------------
A    1.3  36
B    1.4  34

Which one do you choose for condition #1? Both of these records are below the two target values and are candidates for the record needed for #1. Record A has an ewt value that is closer to the target (40) than record B. But, the ewt value of record B is closer to the target (1.5) than record A

That was why one solution @Barand provided resulted in four records. It has a deterministic approach based on the two variables and the two conditions.

Barand · November 3, 2021

BTW, What is the mathematical relationship between GMP, EWT and totCool?

halfsparrow · November 3, 2021

5 minutes ago, Psycho said:
But, there is not necessarily a logical determination for those. Let's just take option #1 to illustrate the problem. The target values the user has entered are gpm = 1.5 and ewt = 40. Now, let's say there are the following records in the database
id | gpm | ewt
--------------
A    1.3  36
B    1.4  34
Which one do you choose for condition #1? Both of these records are below the two target values and are candidates for the record needed for #1. Record A has an ewt value that is closer to the target (40) than record B. But, the ewt value of record B is closer to the target (1.5) than record A

That was why one solution @Barand provided resulted in four records. It has a deterministic approach based on the two variables and the two conditions.

No, such data is not possible. If gpm is increasing ewt will also follows the same trend,

lets forget about the case when bpth are not present i will write it down myself, can you just provide me with the other query

I can make query for neither case myself by referring to query you will provide me for when either of the gpm or ewt matches

halfsparrow · November 3, 2021

12 minutes ago, Barand said:

BTW, What is the mathematical relationship between GMP, EWT and totCool?

there's no mathematical connectio between them.

it the value that is universally proven and i have a excel sheet for it

Psycho · November 4, 2021

You can give this a try. I think it's probably a little more verbose than it needs to be, but should do what you are asking. Not going to guarantee it is what you want, so run it through some tests first

<?php

//Var to hold result
$result = false;

//Query for an exact match on both gpw and ewt
$query = "SELECT gpm, ewt, 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 two closest records matching gpm and high/low for ewt
if (!$result)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm = :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm = :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
              )";
    $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 two closes records matching ewt and high/low for gpm
if (!$result)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm < :uniluxGpm1 AND ewt = :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm > :uniluxGpm2 AND ewt = :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
              )";
    $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 get the two closest records that are both above gpm and ewt and both below gpm and ewt
if (!$result)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm < :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC, ewt DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm > :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC, ewt ASC
                  LIMIT 1
              )";
    $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
{
    //Do something with the data
}

?>

halfsparrow · November 4, 2021

46 minutes ago, Psycho said:

You can give this a try. I think it's probably a little more verbose than it needs to be, but should do what you are asking. Not going to guarantee it is what you want, so run it through some tests first

<?php

//Var to hold result
$result = false;

//Query for an exact match on both gpw and ewt
$query = "SELECT gpm, ewt, 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 two closest records matching gpm and high/low for ewt
if (!$result)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm = :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm = :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
              )";
    $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 two closes records matching ewt and high/low for gpm
if (!$result)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm < :uniluxGpm1 AND ewt = :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm > :uniluxGpm2 AND ewt = :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
              )";
    $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 get the two closest records that are both above gpm and ewt and both below gpm and ewt
if (!$result)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm < :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC, ewt DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm > :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC, ewt ASC
                  LIMIT 1
              )";
    $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
{
    //Do something with the data
}

?>

Thankyou, It was life saving, it worked.

halfsparrow · November 4, 2021

3 hours ago, Psycho said:

You can give this a try. I think it's probably a little more verbose than it needs to be, but should do what you are asking. Not going to guarantee it is what you want, so run it through some tests first

<?php

//Var to hold result
$result = false;

//Query for an exact match on both gpw and ewt
$query = "SELECT gpm, ewt, 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 two closest records matching gpm and high/low for ewt
if (!$result)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm = :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm = :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
              )";
    $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 two closes records matching ewt and high/low for gpm
if (!$result)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm < :uniluxGpm1 AND ewt = :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm > :uniluxGpm2 AND ewt = :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
              )";
    $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 get the two closest records that are both above gpm and ewt and both below gpm and ewt
if (!$result)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm < :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC, ewt DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM wxyz
                  WHERE gpm > :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC, ewt ASC
                  LIMIT 1
              )";
    $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
{
    //Do something with the data
}

?>

Last thing, how can i add totcool to a variable so that i can use it in calculation?

Psycho · November 4, 2021

<?php

//Var to hold totCool result
$totCool = false;

//Query for an exact match on both gpw and ewt
$query = "SELECT gpm, ewt, 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
    $totCool = $stmt->fetchColumn()
}
//If no result try getting the two closest records matching gpm and high/low for ewt
if (!$totCool)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm = :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm = :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
              )";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was a result, get results into an array
        $result = $stmt->fetchAll();
        //Debug line to see the results
        echo "Results from DB: <pre>".print_r($result, true)."</pre>";
        //Add logic here to calculate the totCool value from the results
        $totCool = "Need to calculate totCool value value";
    }
}
//If no result try getting the two closes records matching ewt and high/low for gpm
if (!$totCool)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm < :uniluxGpm1 AND ewt = :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm > :uniluxGpm2 AND ewt = :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
              )";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was a result, get results into an array
        $result = $stmt->fetchAll();
        //Debug line to see the results
        echo "Results from DB: <pre>".print_r($result, true)."</pre>";
        //Add logic here to calculate the totCool value from the results
        $totCool = "Need to calculate totCool value value";
    }
}
//If no result get the two closest records that are both above gpm and ewt and both below gpm and ewt
if (!$totCool)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm < :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC, ewt DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm > :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC, ewt ASC
                  LIMIT 1
              )";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was a result, get results into an array
        $result = $stmt->fetchAll();
        //Debug line to see the results
        echo "Results from DB: <pre>".print_r($result, true)."</pre>";
        //Add logic here to calculate the totCool value from the results
        $totCool = "Need to calculate totCool value value";
    }
}

//Determine if there were results
if(!$totCool)
{
    //No records that match either value
    echo "Not enough data to compute";
}
else
{
    //Do something with the retrieved\calculated value
    echo "totCool value = {$totCool}";
}

?>

halfsparrow · November 8, 2021

On 11/4/2021 at 2:27 PM, Psycho said:

<?php

//Var to hold totCool result
$totCool = false;

//Query for an exact match on both gpw and ewt
$query = "SELECT gpm, ewt, 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
    $totCool = $stmt->fetchColumn()
}
//If no result try getting the two closest records matching gpm and high/low for ewt
if (!$totCool)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm = :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm = :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
              )";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was a result, get results into an array
        $result = $stmt->fetchAll();
        //Debug line to see the results
        echo "Results from DB: <pre>".print_r($result, true)."</pre>";
        //Add logic here to calculate the totCool value from the results
        $totCool = "Need to calculate totCool value value";
    }
}
//If no result try getting the two closes records matching ewt and high/low for gpm
if (!$totCool)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm < :uniluxGpm1 AND ewt = :uniluxEwt1
                  ORDER BY gpm DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm > :uniluxGpm2 AND ewt = :uniluxEwt2
                  ORDER BY gpm ASC
                  LIMIT 1
              )";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was a result, get results into an array
        $result = $stmt->fetchAll();
        //Debug line to see the results
        echo "Results from DB: <pre>".print_r($result, true)."</pre>";
        //Add logic here to calculate the totCool value from the results
        $totCool = "Need to calculate totCool value value";
    }
}
//If no result get the two closest records that are both above gpm and ewt and both below gpm and ewt
if (!$totCool)
{
    $query = "(
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm < :uniluxGpm1 AND ewt < :uniluxEwt1
                  ORDER BY gpm DESC, ewt DESC
                  LIMIT 1
              )
              UNION
              (
                  SELECT gpm, ewt, totCool
                  FROM {$uniluxModel}
                  WHERE gpm > :uniluxGpm2 AND ewt > :uniluxEwt2
                  ORDER BY gpm ASC, ewt ASC
                  LIMIT 1
              )";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was a result, get results into an array
        $result = $stmt->fetchAll();
        //Debug line to see the results
        echo "Results from DB: <pre>".print_r($result, true)."</pre>";
        //Add logic here to calculate the totCool value from the results
        $totCool = "Need to calculate totCool value value";
    }
}

//Determine if there were results
if(!$totCool)
{
    //No records that match either value
    echo "Not enough data to compute";
}
else
{
    //Do something with the retrieved\calculated value
    echo "totCool value = {$totCool}";
}

?>

This is not helping me to store value in variable,

What i want is for eg lets take 2nd query where gpm match and ewt does not. so ffor that i will get 2 values for totcool

so i want to store that in variable where totcool1 = xxxx and totcool2 = xxxx

OR

The method you show gives me array i have attached the ss of it, so basically what want is the value of totcool from array[0] should be stored in variable totcool1 and the value of totcool from array[1] should be stored in totcool2

Edited November 8, 2021 by halfsparrow

Barand · November 8, 2021

Your 2 variables are

$results[0]['totcool']
$results[1]['totcool']

For example

$average = ( $results[0]['totcool'] + $results[1]['totcool'] ) / 2;

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

Psycho

Barand

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information