user998507 user998507 - 6 months ago 19
MySQL Question

Trying to find an easy way for PHP and Mysql DB to do math (AVG MIN MAX) for PHP5.5

I currently have PHP 5.3 and need to upgrade to at least PHP 5.5/5.6. I have a page that post averages for rental properties. For a while i got away with using mysql but when I upgrade, in need to use mysqli. Below is a code I tried writing in PDO.

$hostname = "your_hostname";
$username = "your_username";
$password = "your_password";
$db = "database";

$dbh = new PDO("mysql:host=$server;dbname=$db", $user, $pass);

foreach ($dbh->query("SELECT MAX(rent) FROM table WHERE PType ='$PType' AND PState = '$state'") as $row) {
$SMR = "" . $row['MAX(rent)'] . "";
}


That works in PHP 5.3, but not PHP 5.5/5.6. I have tried changing the
$dbh
line to this:

$dbh = new PDO("mysqli:host=$server;dbname=$db", $user, $pass);


The error log came up with a driver error.

Is there a better way?

Answer

There seems to be some confusion here as far as what's required to upgrade. The mysql_query() function, and related mysql_*() functions, were deprecated starting with PHP 5.5, and were removed in PHP7.

However, this has nothing to do with the word mysql in PDO's initialization. The syntax shown here in PDO's documentation is correct. Usage of the legacy mysql_*() functions should be replaced with MySQLi, but PDO is not affected.

Now, I'm not sure whether or not you actually tried the old PDO code and it failed, or if you're just thinking you need to make changes for best-practices. But if $row['MAX(rent)'] isn't working for you, and even if it is, for clarity, you might want to just assign it an alias:

foreach ($dbh->query("SELECT MAX(rent) as MaxRent FROM table WHERE PType ='$PType' AND PState = '$state'") as $row) { 
    $SMR = "" . $row['MaxRent'] . "";
}