0

I'm able to pull the correct state from the field concatenation which reads: "City, State - Name County - zipcode" using the following code:

$sql="SELECT trim(substring(SUBSTRING_INDEX(county_city,',',-1),1,3)) as state
          FROM
            " . $this->table_name . "
          WHERE
            admid = 45";

If I use the following code to try and get a row count, it ends up being 0 rows found:

$sql="SELECT trim(substring(SUBSTRING_INDEX(county_city,',',-1),1,3)) as state,
            COUNT(*) as total_rows
          FROM
            " . $this->table_name . "
          WHERE
            DATE_FORMAT(refer_date,'%m') = ?
          AND
            DATE_FORMAT(refer_date, '%Y') = ?
          AND
            unit = ?
          AND
            state = ?";

            // prepare query statement
            $stmt = $this->conn->prepare( $sql );

            // bind variable values
            $stmt->bindParam(1, $refer_month);
            $stmt->bindParam(2, $refer_year);
            $stmt->bindParam(3, $unit);
            $stmt->bindParam(4, $state);

            $stmt->execute();
            $row = $stmt->fetch(PDO::FETCH_ASSOC);

            return $row['total_rows'];

Any idea why?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
user12399861
  • 1
  • 1

1 Answers1

1

https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

You need to do:

$sql="SELECT trim(substring(SUBSTRING_INDEX(county_city,',',-1),1,3)) as state,
        COUNT(*) as total_rows
      FROM
        " . $this->table_name . "
      WHERE
        DATE_FORMAT(refer_date,'%m') = ?
      AND
        DATE_FORMAT(refer_date, '%Y') = ?
      AND
        unit = ?
      AND
        trim(substring(SUBSTRING_INDEX(county_city,',',-1),1,3)) = ?";

or, alternatively something like:

"SELECT * FROM (SELECT trim(substring(SUBSTRING_INDEX(county_city,',',-1),1,3)) as state,
        COUNT(*) as total_rows
      FROM
        " . $this->table_name . "
      WHERE
        DATE_FORMAT(refer_date,'%m') = ?
      AND
        DATE_FORMAT(refer_date, '%Y') = ?
      AND
        unit = ?
   ) as q1 WHERE q1.state = ?";

where my subselect syntax is probably a little off but you get the idea.

dave
  • 62,300
  • 5
  • 72
  • 93
  • Awesome! Thanks for the quick response! Sometimes I look too far into an issue and miss something a little more simplistic. I did wonder if it was because the column value wasn't determined. I used your first example. Only because I already had it written within SELECT. (Lazy me!) – user12399861 Nov 19 '19 at 20:20