Jump to content

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


halfsparrow
Go to solution Solved by Psycho,

Recommended Posts

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.

  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?

 

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

Link to comment
Share on other sites

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

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

?>

 

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 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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?

image.png.d3e1d695632d5e268e3e5e3ce97f32c7.png

 

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 by halfsparrow
Link to comment
Share on other sites

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?

image.png.d3e1d695632d5e268e3e5e3ce97f32c7.png

 

Can you please look into this?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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:

  1. The record with the highest gpm value below the target AND the highest ewt value below the target
  2. The record with the lowest gpm value above the target AND the highest ewt value below the target
  3. The record with the highest gpm value below the target AND the lowest ewt value above the target
  4. 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.

Link to comment
Share on other sites

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:

  1. The record with the highest gpm value below the target AND the highest ewt value below the target
  2. The record with the lowest gpm value above the target AND the highest ewt value below the target
  3. The record with the highest gpm value below the target AND the lowest ewt value above the target
  4. 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 by halfsparrow
Link to comment
Share on other sites

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 |
+------+------+
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

  • Solution

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
}

?>

 

  • Like 1
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

?>

 

Link to comment
Share on other sites

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

Upload.png

Edited by halfsparrow
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.