HELP NEEDED! SQL Programmers $x = mysql_query("UPDATE yourTable SET Begin_Date

[Caesard]

Anyone who is knowledgeable in SQL and SQL syntax (coding). I have a minor problem with a database table, and asked the question on Stackoverflow. Even though I got some answers, I cannot make head or tail of it as I always get errors on trying to execute. Here is the question.

 

My MySQL database Table has the following columns:

 

 

Primary_ID, Begin_Date, End_Date, Timestamp

 

How do I update using phpmyadmin, selected rows with randomly generated begin_dates and timestamp within a specified date range (eg: 30 days in a month).

E.g of desired outcome

 

Primary_id--- Begin_Date -------------Timestamp

 

1.------------2008-09-02--------------2008-09-02 21:48:09

 

2.------------2008-09-03--------------2008-09-03 15:19:01

 

3.------------2008-09-14--------------2008-09-14 01:23:12

 

4.------------2008-09-27--------------2008-09-27 19:03:59

 

Date Range between 2008-09-01 and 2008-09-31.

Time is variable 24 hrs

 

I am a newbie, so a syntax that will work in phpmyadmin will help greatly.

 

We are making a presentation for a gym site with 500 members but the added member values all have the same begin date and time. Trying to separate them into different monthly registrations in the database, eg 50 people registered in August at different days and times, 35 people in October, etc.

 

When I try some of the answers here, I get this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$randomDate = rand(1,31)' at line 1.

So ideally, a code I can copy and paste into phpmyadmin with minimal editing will be appreciated. In sequence if possible. For a total dummy to understand and execute with as close to one or two clicks as possible.

 

 

Here is the answer I got.

 

I'd start with something like this. A bunch of these can be combined, but I split it up so you can see what I'm doing.

 

To get random numbers, you can use rand(). Get one for the date, hour, minute, and second

 

$randomDate = rand(1,31);

$randomHour = rand(1,24);

$randomMinute = rand(0,59);

$randomSecond = rand(0,59);

 

You will want leading zeros (03 instead of 3) so you can use str_pad to add them, if required

 

$randomDate = str_pad($randomDate, 2, '0',STR_PAD_LEFT);

//The '2' is how many characters you want total

//The '0' is what will be added to the left if the value is short a character

 

Do the same with all your other random values.

Just because I like neat queries, you should make up your final update strings next.

 

$newDate = '2008-09-'.$randomDate;

$newTime = $randomHour.':'.$randomMinute.':'.$randomSecond;

 

Now I don't know how you're determining which rows you want to update, so I will leave that up to you. For an example, I will show you a query if you wanted to do this with Primary_id 3:

 

$x = mysql_query("UPDATE yourTable SET Begin_Date=\"$newDate\", Timestamp=\"$newTime\" WHERE Primary_id = 3");

 

How do I get it working in phpmyadmin. Willing to buy thank you beers for any help.

[Caesard]

Also got this answer:

 

"Get Database information from MySQL with PHP:

 

$result = //result from mysql query IE. "SELECT*FROM `database`" which results in an array of information.

 

Put that array through a "date randomizer" (in quotes because YOU make the function:

 

$num = mysql_num_rows($result);

for($i=0; $i

{

//run your "date randomizer" function for each date key

}

 

Update your Mysql with the new array that you got from your "date randomizer":

 

//run your MySQL query to UPDATE each rows date and information by ID

 

Very simple. I can't really break it down any further for you."

 

But I can't code, so can't execute it.

[grahamc]

Random in a DB is not the easiest thing. Where did you get the data? I could write something for SQL server but that woul be version dependant and would probably not port across to MySQL very well and I have no idea on php.

[SupraShaun]

So do you want to update every row with a random(ish) date between 2008-09-01 and 2008-09-31? Or have I read this wrong

[Caesard]

The data is from my database. I am running rudimentary sql commands from inside phpmyadmin. Just need a way to interpret the above answers in a form that would work inside phpmyadmin.

[Caesard]

So do you want to update every row with a random(ish) date between 2008-09-01 and 2008-09-31? Or have I read this wrong

 

Yes, exactly. A random(ish) date and time.

[SupraShaun]

Ok, there is probably a better way of doing it but try this:

 

UPDATE tblname SET Timestamp = FROM_UNIXTIME(RAND() * (UNIX_TIMESTAMP('2008-09-30 23:59:00') - UNIX_TIMESTAMP('2008-09-01 00:00:00')) + UNIX_TIMESTAMP('2008-09-01 00:00:00'));

 

UPDATE tblname SET Begin_Date = Timestamp;

 

Change tblname to the name of your table and make sure you have a backup

[Caesard]

Wow, worked (to an extent). The column Begin_Date is updated correctly, but the Timestamp column has attribute "on update CURRENT_TIMESTAMP", and TYPE "TIMESTAMP", so it changes the entire column to 2012-05-29 17:15:48, which is the time I executed the command.

 

How to get around that?

 

Ideally need each row from Column "begin_date" and "timestamp" to be the same, like this

 

Primary_id--- Begin_Date -------------Timestamp

 

1.------------2008-09-02--------------2008-09-02 21:48:09

 

2.------------2008-09-03--------------2008-09-03 15:19:01

 

3.------------2008-09-14--------------2008-09-14 01:23:12

 

4.------------2008-09-27--------------2008-09-27 19:03:59

 

but at the moment, what I am getting is this:

 

 

Primary_id--- Begin_Date -------------Timestamp

 

1.------------2008-09-02--------------2012-05-29 17:15:48

 

2.------------2008-09-03--------------2012-05-29 17:15:48

 

3.------------2008-09-14--------------2012-05-29 17:15:48

 

4.------------2008-09-27--------------2012-05-29 17:15:48

 

Do I make sense? Really appreciating your help.

[SupraShaun]

Ahh ok, didnt realise that - thought it was just DATETIME. You could try taking that attribute off, run the query and then put the attribute back on again.

[Caesard]

Yeah, but I am trying, rather unsuccessfully to take the attribute off, but it is not going off. Rather stubborn fellow, lol.

 

A bit like this chaps problem: http://stackoverflow.com/questions/5126374/why-wont-mysql-let-me-remove-attribute-on-update-current-timestamp

 

(the bit about it refusing to budge or go away)

[SupraShaun]

ALTER TABLE tblname CHANGE `Timestamp` `Timestamp` TIMESTAMP NULL DEFAULT NULL

[Caesard]

Damn. You sir, are a genius. Worked perfectly, and after running the earlier query again, has now worked as needed. 8 pints of beer for you, and a girl of your choice.

 

Thanks a million.

[SupraShaun]

No problem mate