Retrieving data from MySQL db

Panorama X
1

I am successfully writing data to my remote MySQL database. Now I must retrieve data from that database. If I use SELECT in my phpMyAdmin, I can see the results I want, but how do I pull that data back into Panorama? (The below works for editing info in the remote database.)

Local LSource, LResult

LSource = 
||
<<Connection data goes here>>

$sqlQuery = "UPDATE `club` SET `facilityID` = '|| + FacilityID + ||' WHERE `club`.`id` = '|| + ClubID + 
||'";

if ($mysqli->query($sqlQuery) === false) {
echo('The query failed: ' . $mysqli->error);
}
$mysqli->close();
||

PHP LSource,LResult

If LResult = ""
    NSNotify "Set Facility"
Else        
    Message LResult
EndIf
2

I think you are 90% of the way there. You’ll need to figure out the PHP source that will do the SELECT you want. I assume then the result will come back as text in LResult, which you can then do whatever you want with it. I can’t give you any specific assistance as I am not an expert in either MySQL or PHP (or even a beginner, really).

3

The SELECT is not a problem as that works while in phpMyAdmin.

The problem is that data is not returned back in the LResult variable.

4

Well that’s down to the PHP code you have written. I’m afraid I can’t help you with that. What you need to do is get the PHP code to send the results to standard output. If you can get it to work in Terminal.app, it will work in Panorama.

5

The SELECT was

$sqlQuery = "SELECT `lastVerify` FROM `club` WHERE `club`.`id` = '|| + ClubID + ||'";

After doing the SELECT, this is what I needed.

if($result = $mysqli->query($sqlQuery)) {
    while($row = $result->fetch_array()) {
      printf("%s\n", $row["lastVerify"]);
 }
}
print_r($result);

As the SELECT command typically results in a multi row, multi column table, it was necessary to parse the ‘array’ to then retrieve the value in Row 1 Column 1 to then get the single value that was returned.

1 Like