Scripting a MySQL query in Unix using daemon in PHP

Question

I'm trying to make an "at" job work at a given time, for testing purposes I'm using $time but this will be a datetime that I get from a separate MySQL query. In the command line I go like this:

echo "mysql -e 'UPDATE admin SET row1=row2 WHERE id=1;'" | at now

And I get a "job 36 at 2010-10-28 15:05". in PHP I tried going like this:

exec("\"mysql -e 'UPDATE admin SET row1=row2 WHERE id=1'\" | at $time");

But the query doesn't run. Worse, I have no idea what is happening.

echo exec('whoami'); 

returns "daemon" though. How can I echo whatever response I'm getting from the exec command? ideally I guess it would say "job 36 at 2010-10-28 15:05" or something similar.

Also, I have a .my.cnf file in my dir that specifies the db, login and password to use, does the daemon need to have one also for these to work?

[from the answers I can tell I wasn't clear about what I am trying to do. I need to

• A. Run a mySQL query to get a date/time and an id
• B. Schedule an update to take place to rows that match the id at the date/time

I'd already done "A" and was using "1" for the id and "now" for the time while testing. I'll look into PDO.

Answer 1

I'm not that familiar with the at command, but it seems to help run commands at a certain time.

If you're trying to write a scheduled script in PHP there are better ways to do it. Just write a CLI script and use the cron to schedule it. As Svisstack notes, if you're running MySQL queries use an in-built function such as PDO rather than system commands.

If you're just running systems commands, I'm not sure why you're using PHP ;)

Answer 2

Are you perhaps in safe_mode? If yes then your | is getting escaped automatically. Per the manual:

With safe mode enabled, the command string is escaped with escapeshellcmd(). Thus, echo y | echo x becomes echo y \| echo x.

Answer 3

You can get more information by using the other parameters for the exec command. Try running this and see the output.

$output = array();
$return = false;
$last_line = exec("\"mysql -e 'UPDATE admin SET row1=row2 WHERE id=1'\" | at now", $output, $return);

var_dump($last_line);
var_dump($output);
var_dump($return);

Also, it looks like when you ran it at the command line, you echo'ed the MySQL command, but when you put it in the PHP code, it's not doing the echo anymore. I'm not sure if that makes a difference since I'm not to familiar with at, but I thought that I could offer some troubleshooting help.

$last_line = exec("echo \"mysql -e 'UPDATE admin SET row1=row2 WHERE id=1'\" | at now", $output, $return);

Answer 4

A missing echo in your second statement / exec? (and I'd rather use popen / proc_open the at $time, and fwrite the command for at to execute (after which you close the input stream, then the program. Use atq to verify wether it worked, and be aware the current use may be disallowed at jobs (normally found in files like /etc/at.allow or /etc/at.deny)