SQL, PHP & Applescript

[Paralysis]

Good Afternoon All!

Ive been working on a project where I am building an Xcode app with Applescript behind it to pull/push data to/from SQL. Now the main body of it is working but im having some challeges with the esacpe quotes symbol ""

Basically to keep the code triggers a php script to pull the data and pre format it as a record string.
Then I plan to use Applescript to grab info from that by saying:

set variableOne to sqlVariable3 of fullSqlRecord

Now the issue im getting is that within the string its leaving in "" hidden in the string… But not when I do:

display dialog fullSqlRecord

Please see below full code:

PHP Script:

<?php
// Set login details to the DB
$username="USER";
$password="PASS";
$database="DB";

$pref=$_REQUEST['pref'];
$id=$_REQUEST['id'];

//Create connection
$conn = new mysqli('localhost',$username,$password,$database);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

//Set SQL Replace TABLE_NAME
$sql = "SELECT * FROM PF_Data WHERE idNum = '$id' AND pref = '$pref'";

//Run the SQL
$result = $conn->query($sql);

foreach($result as $key => $row) {
       foreach($row as $field => $value) { 
           $recNew[$field][] = $value;
       }
}

//Loops through each key and value to create the record
foreach ($recNew as $key => $values)
{
    $sqlString = $sqlString . $key . ':"';
    
        foreach ($values as $cell)
        {
           $sqlString = $sqlString . $cell . '", ';
        }
}
//Removes the trailing comma and space and returns the full string
$sqlString = substr($sqlString, 0, -1);
echo stripslashes($sqlString);
?>

set urlPath to "Url Address"

set idNumber to "5567"
set preFix to "P"
set newList to {}

set myURL to urlPath & "dbconnection.php?id=" & idNumber & "&pref=" & preFix & ""
set checkit to (do shell script "curl -k " & quoted form of myURL)
display dialog checkit
--> idNum:"5567", pref:"P", created_on:"2017-06-14 00:00:00", created_by:"Me", status:"", last_modified:"", last_modified_by:"", next_status:""

set end of newList to checkit
--I think the problem is here
-- I cant seem to get this to create a record properly

idNum of newList
--> Can't get idNum of {"idNum:\"5567\", pref:\"P\", created_on:\"2017-06-14 00:00:00\", created_by:\"Me\", status:\"\", last_modified:\"\", last_modified_by:\"\", next_status:\"\""}.

Any help would be appreiciated.
I think the problem lies in the creation of the record.
If I run the below code it runs fine:

set newList to {idNum:"5567", pref:"P", created_on:"2017-06-14 00:00:00", created_by:"Me", status:"", last_modified:"", last_modified_by:"", next_status:""}
-- This is the exact same of the string returned

set idNum to idNum of newList
--> 5567

Model: Macbook Pro
AppleScript: Xcode 8.2.1
Browser: Firefox 51.0
Operating System: Mac OS X (10.10)

[DJ_Bazzie_Wazzie]

@Paralysis

Ive been working on a project where I am building an Xcode app with Applescript behind it to pull/push data to/from SQL.

Does this has to work with PHP per se? I can understand that the MySQL can only be accessed locally for security reasons but if not what is the reason to use PHP. You can use MySQL on the command line with do shell script script have been using it successfully for years in the AppleScript-Studio era.

You can PM me because I have never released it but I’m using an self written MySQL osax to directly connect AppleScript with MySQL. I don’t want to release it officially because of all the responsibility it comes with (like other osaxen I’ve written). edit: The osax can return records whose keys matches the column names.

If you prefer using PHP code my advise would be to use $result->fetch_assoc() instead so you don’t need an foreach loop in an foreach loop and save yourself some time.

@Paralysis

Now the issue im getting is that within the string its leaving in "" hidden in the string… But not when I do:

To answer your question: From what I can see is that with calling and returning you need to double escape the characters. That means when the data contains an double quote or backslash you need to escape those characters so it can be evaluated by AppleScript later which is required to do later to turn the string into an record.

[Paralysis]

Hi DJ,

Thanks for the reply.

@DJ Bazzie Wazzie

Does this has to work with PHP

Unfortunalty yes - It needs to be PHP as the SQL server is not located on the same network (whereas the PHP script is)
I need that setup his way so the PHP is the middle man if you like - Id prefer to have an all in one solution.

@DJ Bazzie Wazzie

From what I can see is that with calling and returning you need to double escape the characters. That means when the data contains an double quote or backslash you need to escape those characters so it can be evaluated by AppleScript later which is required to do later to turn the string into an record.

So this sounds like its more to do with my PHP script than Applescript if it cannot convert it to a list.
Ive been looking online for a while trying to figure out how to remove/manage the escaped characters with no luck so far - PHP unfortunatly is not my strong suit.

ive tried combinations of addslashes() and removeslashes()
Do you have any pointers on how to escape the quotes within the below code:

//Loops through each key and value to create the record
foreach ($recNew as $key => $values)
{
$sqlString = $sqlString . $key . ':"';

foreach ($values as $cell)
{
$sqlString = $sqlString . $cell . '", ';
}
}
//Removes the trailing comma and space and returns the full string
$sqlString = substr($sqlString, 0, -1);
echo stripslashes($sqlString);

Thanks again

[DJ_Bazzie_Wazzie]

Here an example script that shows how you should escape the string in PHP so it can be used as an list of records in AppleScript:

set PHPTestScript to "<?php $lst = array();

$lst[0] 		= array();
$lst[0]['field1'] 	= 'fake text';
$lst[0]['field2'] = 'fake \"text\"';
$lst[0]['field3'] = 'fakse \\\\text\\\\';

$lst[1] 		= array();
$lst[1]['field1'] 	= 'more fake text';
$lst[1]['field2'] = 'more fake \"text\"';
$lst[1]['field3'] = 'more fakse \\\\text\\\\';


$rows = array();
foreach ($lst as $row){
	$fields = array();
	foreach ($row as $field => $value){
		$fields[] =  \"|\" . $field . \"|:\" . ASValue($value);
	}
	$rows[] = '{' . join(', ', $fields) . '}';
}

$ASRecordAsText  = '{' . join(', ', $rows) . '}';

echo $ASRecordAsText;

function ASValue($str){
	$str = str_replace('\\\\',  '\\\\\\\\', $str);
	$str = str_replace('\"',  '\\\"', $str);
	return '\"' . $str . '\"';
}
?>"

set recordsAsText to do shell script "PHP <<<" & quoted form of PHPTestScript

set theRecords to run script recordsAsText

return field2 of item 1 of theRecords

[Paralysis]

Hi DJ,

Thanks for your reply.
I had a go at doing it your way but couldnt get anything to work I only understood what about 70% of the code did.

I did however solve it myself so I though id share incase anyone else is having the same issues:

I basically concatenated both the key (column headers) and the cell value together then let Applescript sort out whats what:

PHP:

<?php // Set login details to the DB $username="USER"; $password="PASS"; $database="DB"; $pref=$_REQUEST['pref']; $id=$_REQUEST['id']; //Create connection $conn = new mysqli('localhost',$username,$password,$database); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } //Set SQL Replace TABLE_NAME $sql = "SELECT * FROM PF_Data WHERE idNum = '$id' AND pref = '$pref'"; //Run the SQL $result = $conn->query($sql); foreach($result as $key => $row) { foreach($row as $field => $value) { $recNew[$field][] = $value; } } //Loops through each key and value to create the record foreach ($recNew as $key => $values) { $sqlString = $sqlString . $key . '|'; } foreach ($recNew as $key => $values) { foreach ($values as $cell) { $sqlString = $sqlString . $cell . '|'; } } //Removes the trailing comma and space and returns the full string $sqlString = substr($sqlString, 0, -2); echo $sqlString; ?>

APPLESCRIPT:

set urlPath to "url address"

set idNumber to "5567"
set preFix to "P"
set newList to {}

set myURL to urlPath & "dbconnection.php?id=" & idNumber & "&pref=" & preFix & ""
try
	set checkit to (do shell script "curl -k " & quoted form of myURL)
on error
	display dialog "Cannot connect to Database - Ensure you are connected via VPN if you're not on site"
end try

set created_on to pullData("created_on", checkit) --This sets whatever variable you want and finds the quoted text in the string
display dialog created_on

on pullData(searchText, searchString)
	set astid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to "|"
	set pipeCount to 25 - This is the number of headers you have
	set theList to every text item of searchString
	repeat with i from 1 to the count of theList
		if item i of theList is equal to searchText then set itemNum to i -- Returns the item number of where the header is found
	end repeat
	set VariableReturn to item (itemNum + pipeCount) of theList
	set AppleScript's text item delimiters to astid
	return VariableReturn
end pullData

Hopefully this is useful for others.
Thanks!

[DJ_Bazzie_Wazzie]

@Paralysis

I had a go at doing it your way but couldnt get anything to work I only understood what about 70% of the code did.

What it does it created an array of associative arrays in PHP to mimic the MySQL results like you would normally do with:

[format]// getting results an put it in an array of associative arrays
while ($row = $result->fetch_assoc()) {
$lst[] = $row;
}[/format]

After that it creates an string that can be eval’ed into an record directly instead of getting the results using text item delimiters as you do. As proof of concept I added quotes in the string to show it worked properly and the string returned by PHP is then:

"{{|field1|:\"fake text\", |field2|:\"fake \\\"text\\\"\", |field3|:\"fakse \\\\text\\\\\"}, {|field1|:\"more fake text\", |field2|:\"more fake \\\"text\\\"\", |field3|:\"more fakse \\\\text\\\\\"}}"

This string can be eval’ed into an AppleScript object using the run script command and the returned object is then:

{{field1:"fake text", field2:"fake \"text\"", field3:"fakse \\text\\"}, {field1:"more fake text", field2:"more fake \"text\"", field3:"more fakse \\text\\"}}

This object is an list of records representing the rows and fields of the MySQL result in PHP.

That’s all the example code does. Including the mysqli part would only be redundant because that part you’re already written correctly.