Putting PHP MySQL query output into SSH

My program originally called a mySQL query over http. The result was returned in JSON form:

 $query  开发者_运维技巧= "SELECT xaxis, yaxis FROM table ";
$result = mysql_query($query);

$rows = array();
$counter=1;

while ($r = mysql_fetch_array($result, MYSQL_NUM))
{

     echo json_encode($r[0]), "\n";
     echo json_encode($r[1]), "\n";

    $counter++;

} 


?>  

It works ok (granted not the best PHP script in the world), but now I'm doing everything in SSH and I was thinking about doing the SQL query through bash and then I thought, why don't I just call the PHP script in SSH?

However I'm not sure how to do this, given that the PHP returns an array. If it returned a simple file I could, in theory just do:

spc username@hostname:filename  localfilename

How should I return get an array from a script in SSH?

The PHP does not return an array. It returns a JSON-format string: remember, HTTP can only ever give you text.

So there is no difference. Just invoke the PHP script in your terminal and you'll see the same text.

(Actually, it looks like you have two distinct JSON structures, separated by a newline. You might want to fix that by encoding $r instead:)

[user@host: ~]$ cat myscript.php
<?php
$query  = "SELECT `xaxis`, `yaxis` FROM `table`";
$result = mysql_query($query);

$rows = Array();
while ($r = mysql_fetch_assoc($result)) {
     $rows[] = $r;
}

echo json_encode($rows);
?>

[user@host: ~]$ php myscript.php
[
   { "xaxis" : 0, "yaxis" : 0 },
   { "xaxis" : 3, "yaxis" : 2 },
   { "xaxis" : 8, "yaxis" : 5 }
]

Or something like that.

1) You could just run the PHP script on the server you want if you allow that user to connect to MySQL from an external server. That way you wont have to transfer the file or anything (Seeing that you wrote "spc" while I'm guessing it's supossed to be "scp".

2) Update the while loop to the following code if you want it to output a single array:

$output = array();

while ($r = mysql_fetch_array($result, MYSQL_NUM))
{
    $output[] = $r;
    $counter++; //Im guessing you need this :P
}

echo json_encode($output);

This script can be called from everywhere but if you want to run it from SSH you can simply run the following command*:

php yourfile.php

*Make sure that php-cli is installed ;)

Hope this helped you out ;)