MySQL, How can i automate a SQL update query?

[Tr1d3nt]

Hey guys,

 

I have a script which ideally i'd like to run every 5 minutes.

 

The script basically connects to MySQL database, changes some ID's and thats it... But to save me from having to do it every time a customer places an order, i'd like something that would run the script for me, every 5 minutes.

 

I'm not familiar with cron jobs which someone suggested so i was just wondering if anyone could help me out here with some in-depth information? or as a colleague most lightly puts it a "Dummy guide to ease database management".

 

The script i run is currently in a .php file which i have bookmarked on my FireFox bar, and everytime i recieve an email, i click that button and it'll update the ID's.. but obviously over x-mas, when i'm not here, ill or whatever else and i dont run the script, it will generate a security issue so it's very important the script is run every 5 minutes atleast.

 

Thanks in advance!

Andy

[web-project]

you can automate all scripts run via linux cron

[Tr1d3nt]

you can automate all scripts run via linux cron

 

 

Right, do i need to create an extra file for this?

 

I tried setting a cron job this morning to run the php file, but it returned errors like:

 

/home/************************.php: line 1: ?php: No such file or directory

/home/************************.php: line 2: =localhost: command not found

/home/************************.php: line 3: =********: command not found

/home/************************.php: line 4: =********: command not found

/home/************************.php: line 5: =********: command not found

/home/************************.php: line 7: //: is a directory

/home/************************.php: line 8: syntax error near unexpected token `$hostname,'

/home/************************.php: line 8: `mysql_connect($hostname, $username, $password) OR DIE("Unable to connect");'

 

(obviously * represents data )

 

So i'm guessing, from looking at this... you can't just tell cPanel CronJob to run this script directly? or have i done something very stupid?

[Kawazu]

So the script runs every time an order is placed? Why not just have it run when the customer confirms an order? I'm not sure how you want everything to work, exactly, but I'm pretty sure that would be easier and make more sense. If that's what you want, it shouldn't be all that difficult... all you would have to do is find other processes performed on order confirmation and just stick it in there. For instance

 

BACKUP!

 

in catalog/checkout_process.php find:

// unregister session variables used during checkout
 tep_session_unregister('sendto');
 tep_session_unregister('billto');
 tep_session_unregister('shipping');
 tep_session_unregister('payment');
 tep_session_unregister('comments');

and insert after:

include('WHEREVER/YOUR/SCRIPT/IS.php');

 

That should do it if that's all you need... it would run the script AFTER everything else has been done, but right BEFORE the page redirects to checkout success. hope that helps.

[Tr1d3nt]

So the script runs every time an order is placed? Why not just have it run when the customer confirms an order? I'm not sure how you want everything to work, exactly, but I'm pretty sure that would be easier and make more sense. If that's what you want, it shouldn't be all that difficult... all you would have to do is find other processes performed on order confirmation and just stick it in there. For instance

 

BACKUP!

......

 

Hey Kawazu that gave me an idea.

 

Basically... I have "Purchase Without Account" contrib installed on my site, and there is a security issue with it, where anyone who is set as guest (customer id of 0) they can all see each others orders if they dont leave the website immediatly.

 

So to solve this, once the customer has confirmed the order, gone through worldpay and come back to our site - the last page is the checkout_success.php, so if i plant the code there.. it should be fine.

 

At the moment, i have done this inside of a file which i will then include into checkout_success:

 

<?php
include('includes/application_top.php');

tep_db_query="UPDATE `orders` SET `customers_id` = '-1' WHERE `customers_id` = '0'";

?>

 

does that look right?

As you can see my strength isnt with php :P but trying to learn :)

 

I've got that code in the success page now, but it doesn't seem to update the database so i'm guessing the code is wrong somewhere.

 

So then, the customer is classed as a guest all the way through the process, then finally on the last page (checkout success) - just before they can click my account to check their order (which they shouldnt be able to anyway if they havent registered) or leave the website, the code will change all entries in the database with a customer id of 0 to -1 so then the order can not be looked up because the id has been changed and we have our... probably too complicated for its job, security fix :P

[Tr1d3nt]

I have managed to fix this now :)

 

Thanks alot for the help!