How to automate SQL query/queries on database

[sperko]

Is it possible to run an automatic job/SQL query/queries on my database every 24 hours?

I'm trying to reset my ID column to keep the numbers in order

At the moment my database looks like this:

ID       Name

2  random entry here
4  random entry here
9  random entry here
15 random entry here

As you can see the id's for each entry are getting messy when the old ones are deleted and new ones are added, it just keeps counting up

I have the script needed to delete and recreate the ID column with auto_increment, but I don't want to run it manually every day

Thanks!

 

[sperko]

Another SQL/PHP related question

The time/date stored next to each entry in the database is showing the server time like this: 2016-03-22 21:37:05

But in my country I'm 4 hours in the future: 2016-03-23 01:37:05

How do I adjust the server time to match my local time? (GMT)

 

[Skizzerz]

I would strongly recommend against pruning your database to keep the numbers "in order" -- you will be demolishing any referential integrity for tables that refer back to that one. Not reusing ids is actually a Good Thing. If you're dead set on it though, you can accomplish this via a cron job. Just keep in mind that for free hosting you cannot have more than one cron job run per 5 minutes and your jobs cannot load external resources per our Terms of Service.

For your second question, make use of the UTC/GMT date/time functions in PHP and SQL instead of the ones that operate on the local timezone.

 

[sperko]

Thank you, there are only a few entries kept in the database and nothing important is stored there, as for the date/time I am not very knowledgeable in PHP and don't know the correct function to use but I will take a look

 

[sperko]

I think I know what I need, but I have no idea how to put this information into my PHP code

The date and time is stored in the 'dt' column in this format: 2016-03-23 21:51:26

Here is the code which fetches the stored date and time in the database

<?php echo
"" . $row["dt"] . ""
?>

I would like to adjust/offset the date and time by 4 hours so it displays the stored entry in GMT on my page

I hope you can help!

 

[essellar]

While it can be set directly on the server, it's better in this environment to set it in your code. All you need to do is use the default_date_timezone_set() function before calling any code that uses date/time values. See the PHP.net documentation for more info.

 

[sperko]

I am sorry for my endless questions I'm starting to learn the great potential of using PHP but I'm still trying to figure out how things work

I tried adding that function before the connection details at the top of my PHP, like this

<?php
date_default_timezone_set('Europe/London');

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "db_name";

I cleared the database and put in a fresh entry and then fetched it, but it was still displaying the stored time and not London time

Maybe I'm doing this wrong?

 

[teksoulx]

<?php
date_default_timezone_set('Europe/London');
echo date_default_timezone_get(); //check output
?>

 

[sperko]

I used the second code and it said Europe/London so I can see it's trying to output the date properly but it's still showing as the server time

EDIT: A user on x10 was having the same problem as me in 2013 but he fixed his issue with a code, which I don't fully understand

See the thread here on x10

 

[teksoulx]

<?php
date_default_timezone_set('Europe/London');
echo date("m/d/Y H:i",time());
?>

 

[sperko]

I don't think that is quite what I'm looking for

I need to convert the stored database time into my local time when it is fetched and displayed on my page (it will only be viewed by me)

 

[teksoulx]

<?php
// if you set date_default_timezone_set('Europe/London');
// your storing datetime base on 'Europe/London'
date_default_timezone_set('Europe/London');
$dt = date("Y-m-d H:i:s",time());

$query = mysql_query('INSERT INTO database (reg_datetime) VALUES ("'.$dt.'")');
$query = mysql_query('INSERT INTO database (reg_datetime) VALUES ("'.date("Y-m-d H:i:s").'")');

$query - mysql_query('SELECT * FROM database');
$row = mysql_fetch_assoc($query);
echo date("m-d-Y h:i:s", strtotime($row['reg_datetime']));

?>

 

[sperko]

Sorry for the slow reply, this is the code I use to store the date/time with an IP address and I'm not sure how to add your code since they vary slightly (The PHP code was created by someone else)

mysql_query("UPDATE my_table_name SET dt=NOW() WHERE ip=".$intIp);

 

[essellar]

What matters, then, is how you're pulling the date/time out of MySQL, since you're using the MySQL date functions rather than PHP's. (Makes sense, since your SQL queries can work much better with native dates rather than foreign data if you're doing any sorting - ORDER BY or what have you.) You'd need to translate the MySQL date/time into a PHP date/time in order to get the timezone stuff to work as advertised. Start by doing a var_dump of the raw value to see what you're getting before it's prettified. You should at least be able to see whether there's any timezone data present at all.

 

[sperko]

As frustrating as this is for a newbie like me to figure out I'm going to leave this one for another day

it's not really important, just something I wanted to try to change

But thank you all for the suggestions!