1

Have an odd one for you...

I have a dashboard which displays data from a BMS system, it's written in php, html & css with a splattering of java. I have a MySQL query which I can run in MySQL workbench and it returns the expected result. However when I run the same MySQLi query from my webpage no value is returned? The Table is called:- currentValues and the column with the BMS values in it is called:- currentValue, again out of my control [designed by BMS vendor] referencePath is the column with the BMS endpoints in. The database is selected as part of my $conn string...

Here is my MySQL Workbench query:

SELECT SUBSTRING(currentValue, 1, 3) FROM currentValues WHERE referencePath LIKE '%CO2_Level'

Here is my webpage query:

$sql = "SELECT SUBSTRING(currentValue, 1, 3) FROM currentValues WHERE referencePath LIKE '%CO2_Level'";
        $result = $conn->query($sql);
                $row = mysql_fetch_object($result);
                echo "<h1>" . $row . " PPM</h1>";

The reason I am using SUBSTRING is because the value has 4 decimal places and the column is a VARCHAR [which I can't change], I only want the whole number [well, technically it's a string].

I have also tried the alternative to SUBSTRING -> LEFT(currentValue, 3) but this behaves in the same way? Any suggestions would be greatly received!!!

EDIT...

However if I use the query without the SUBSTRING()

$sql = "SELECT * FROM currentValues WHERE referencePath LIKE '%CO2_Level'";
        $result = $conn->query($sql);
                $row = mysql_fetch_object($result);    
                echo "<h1>" . $row["currentValue"] . " PPM</h1>";

All works as expected except I have a silly value in my Dashboard like 627.1498 PPM

WORKAROUND

This question was closed as DUPLICATE to an unrelated question so I am unable to post this as the answer. Although really it's a workaround not the answer for the above, but may be helpful for others, I simply used the php operator ROUND to round the value returned in my MySql query.

echo "<h1>" . round($row["currentValue"]) . "PPM</h1>";
Root User
  • 62
  • 13
  • Identical queries against tables with identical data should always return the same result set, period. If this be not the case, then maybe your underlying data isn't the same in both places. – Tim Biegeleisen Jan 23 '20 at 16:30
  • BTW if I remove the SUBSTRING() command and just use SELECT currentValue everything works perfectly but I have a silly figure like 627.1498 PPM on my dashboard... – Root User Jan 23 '20 at 16:34
  • 1
    Have you confirmed that the PHP query is actually receiving no data? It could be you're just not retrieving the data from the $sql object correctly. – R Mac Jan 23 '20 at 18:03
  • Show us your PHP code where you are retrieving data. – Amanjot Kaur Jan 24 '20 at 07:27
  • Try: `SELECT TRUNCATE(currentValue, 0) FROM currentValues...` – forpas Jan 24 '20 at 12:02
  • You are trying to echo an object. If you had checked your logs you'd see a warning about object to string conversion. Assign the column an alias and reference it directly as in the duplicate. – miken32 Jan 27 '20 at 02:30

1 Answers1

0

You are using SUBSTRING(currentValue, 1, 3) in select, So it will give you SUBSTRING(currentValue, 1, 3) in your result.

You can use it by $row->SUBSTRING(currentValue, 1, 3).

But instead, you should use an alias for it.

Try - 

$sql = "SELECT SUBSTRING(currentValue, 1, 3) AS curr_value FROM currentValues WHERE referencePath LIKE '%CO2_Level'";
$result = $conn->query($sql);
$row = mysql_fetch_object($result);    
echo "<h1>" . $row->curr_value . " PPM</h1>";
Alok Mali
  • 2,821
  • 2
  • 16
  • 32