Universal SQL query via Cron

software development

#1

Hi everyone. I’ve got two questions, sort of. I’m trying to run a Cron Job that will automatically set something in my DB to a certain number at 7 PM eastern. Now, I’ve never used Cron before but I’m a fast learner. That isn’t my issue though, I’ve read the DHWiki on Cron and think I understand how to use that. My problem is the query, I’m unsure of how to do one query for the entire DB. I imagine something along the lines of:

Emphasis on where I’m stuck. Usually I put a users ID but this is for the entire table. Any ideas?


#2

WHERE condition defines which records you are going to manipulate. If you want to update the entire table, simply ignore where condition. It will be something like

$dbquery = update users set column = whatever

It will update column to ‘whatever’ for all the records in table users.

$50 off and 3 free domains with code: [color=#CC0000]DH3[/color] Sign Up NOW or More Codes Here


#3

Yep just leave out the WHERE clause and you’ll be set. It’s also normally the type of thing I LOVE to avoid as I’ve seen many update queries without a where clause go wrong.

Just be careful what you’re updating and you’ll be fine. I only mention this as this seems to be your first experience with a no where clause update query.

-Sarah

Prime Promo Codes | DreamHost Promo Codes


#4

Well I did it just fine. :smiley:

Another question, in the Cron box, do I just write out the SQL query? Or is PHP required?


#5

Well, I suppose you can write out the query if you really want to, but it’s probably not a good idea.

With PHP (or any other script), the password to the DB is stored in a script file that you can easily control the permissions of to deny anyone the ability to sniff your password. With cron, the password will be in plain view everytime the job runs, so it’s possible someone will steal it.

Also, if you find yourself needing to do some sort of other maintenance job, it’s a lot easier to encapsulate all of your maintenance into a single script than to put it into a bunch of separate cron jobs. You may never need to add other queries, but using a script is a very low cost to make your work more futureproof.


#6

So use PHP in Cron? :stuck_out_tongue:


#7

you can write sql query in PHP and call the PHP file in crontab.

$50 off and 3 free domains with code: [color=#CC0000]DH3[/color] Sign Up NOW or More Codes Here


#8

I tried following the Wiki instructions and entered this:

[quote]/dh/cgi-
system/php5.cgi /home/.miwa/mystuff/mysite.php[/quote]

into the crontab but it didn’t work. What am I doing wrong?


#9

I think the path to PHP is wrong.

Here is a article on how to run PHP script with Crontab.
http://wiki.dreamhost.com/Crontab#Executing_a_PHP_Script_with_Crontabs

It should solve your problem.

Alternative way to run your PHP file with Crontab is via DH panel --> Goodies --> Crontab

$50 off and 3 free domains with code: [color=#CC0000]DH3[/color] Sign Up NOW or More Codes Here


#10

That’s where I entered it. The wiki page you linked shows exactly what I did. What do I put in the ‘command to run’ field?


#11

Let’s try

/usr/local/php5/bin/php /home/youruser/yourdomain.com/yourfilename

or

/usr/local/bin/php /home/youruser/yourdomain.com/yourfilename

in the “command to run” field.

$50 off and 3 free domains with code: [color=#CC0000]DH3[/color] Sign Up NOW or More Codes Here


#12

Lol. Neither worked. Am I supposed to be using the .miwa thing I see in errors? Originally I was but I took it out just recently to see if anything would change.


#13

What is “.miwa thing you saw”?

If I were you, I would first test the crontab. Create a simple PHP script to either write something to a file or send an email. Run it with crontab. If it is working, the problem is on your PHP scripts.

If the problem is on youor PHP script, revise your scrip and write error to a log file if possible because there is no way for you to see “echo” message.

Hopefully by looking at the error log file, you can filter out the problem.

$50 off and 3 free domains with code: [color=#CC0000]DH3[/color] Sign Up NOW or More Codes Here


#14

Maybe the “.miwa” thing is probably the physical mount point for the NFS device? If so, you should remember to use the “normal” logical path to your files - /home/username/blahblahblah.

Use the [color=#CC0000]3DOM50[/color] promo code for 3 extra lifetime domains and $50 off
More Dreamhost coupons here!


#15

That “.miwa” thing is a “dataglob”, and “Yes!” you should always omit it when referring to the path to your files. :wink:

–rlparker


#16

I’ve run out of ideas. I tested the code itself and it works.

http://img441.imageshack.us/img441/3655/painif8.jpg

That’s my crontab, minus the time to be set, which is correct.


#17

Well, that picture is a big help … :wink:

I can’t see any of the code at all, only a part of the path.

–rlparker


#18

[quote]<?php

UPDATE ‘fusion_users’ SET Tax= ‘0’;

?>[/quote]

This is the code after numerous changes. I’ve tried regular $dbqueries and pretty much everything, heh.