...

View Full Version : fetch the date from timestamp



mrjameer
06-07-2007, 08:39 PM
hi,


i have a mysql table with 3 fields

1.id 2.time 3.approved

values

1 1181237691 0
2 1181237703 0
3 1181237711 0

i want to fetch the distinct dates from this timestamp values using select statement.
i want to display like this
1 2007-06-07


second thing i want to update the approved value=1 where these distinct dates are equal to the above timestamps

any of your help will be surely appreciated.
thanks
mrjameer

Coastal Web
06-07-2007, 10:51 PM
Greetings mrjameer,

Though l couldn't test it (because l don't have a copy of your database; and l didn't really want to spend the time to set one up just for the sake of testing this.. but something like this should work. (see comments)

If you get an error message, post the error and l will correct it.
But in a nutshell this is basically what you're asking, no?


<?php
/*connect to database*/
$Host = "localhost";
$dbname = "DB_NAME";
$User = "DB_USERNAME";
$Password = "DB_PASSWORD";

$link = mysql_connect($Host, $User, $Password);
mysql_db_query($dbname, $sql, $link);


$date_to_approve = "2007-06-07"; // date to approve the entries...

define("TABLE_NAME", "table_name", true); // edit with name of your table...

// first we go through all of the rows in the database, and stick them into an array...
//if you only want "unapproved" rows, you'd add "WHERE `approved` = '0'" to the select statment obviously..
$sql = "SELECT `id` FROM `" . TABLE_NAME . "` ORDER BY `id` ASC LIMIT 10000";
$result = mysql_query($sql) or die('Queryproblem: '.mysql_error ());
while($row = mysql_fetch_array($result)){
$tableArray = $row[id];
}

//next we loop through the array and do your bidding...
$i=0;
while($i<count($tableArray)){

$sql = "SELECT `time`, `approved` FROM `" . TABLE_NAME . "` WHERE `id` = '" . $tableArray[$i] . "' LIMIT 1";
$result = mysql_query($sql) or die('Queryproblem: '.mysql_error ());
$row = mysql_fetch_array($result);

//now that we have the time stamp, lets format it...
$time = $row[time];
$timeFormat = date(Y-m-j, $row[time]);
$approved = $row[approved];

//if this entry isn't approved, lets go ahead and approve it..
if(!$approved && $timeFormat == $date_to_approve){
$sql = "UPDATE `" . TABLE_NAME . "` SET `approved` = '1' WHERE `id` = '" . $tableArray[$i] . " LIMIT 1;";
$result = mysql_query($sql) or die('Queryproblem: '.mysql_error ());
}

//echo the results per your request..
echo $i. ".) " . $timeFormat . " <br />";

$i++;
}

?>

mrjameer
06-09-2007, 06:43 PM
hi




<?php
/*connect to database*/
$Host = "localhost";
$dbname = "DB_NAME";
$User = "DB_USERNAME";
$Password = "DB_PASSWORD";

$link = mysql_connect($Host, $User, $Password);
mysql_db_query($dbname, $sql, $link);


$date_to_approve = "2007-06-07"; // date to approve the entries...

define("TABLE_NAME", "table_name", true); // edit with name of your table...

// first we go through all of the rows in the database, and stick them into an array...
//if you only want "unapproved" rows, you'd add "WHERE `approved` = '0'" to the select statment obviously..
$sql = "SELECT `id` FROM `" . TABLE_NAME . "` ORDER BY `id` ASC LIMIT 10000";
$result = mysql_query($sql) or die('Queryproblem: '.mysql_error ());
while($row = mysql_fetch_array($result)){
$tableArray = $row[id];
}

//next we loop through the array and do your bidding...
$i=0;
while($i<count($tableArray)){

$sql = "SELECT `time`, `approved` FROM `" . TABLE_NAME . "` WHERE `id` = '" . $tableArray[$i] . "' LIMIT 1";
$result = mysql_query($sql) or die('Queryproblem: '.mysql_error ());
$row = mysql_fetch_array($result);

//now that we have the time stamp, lets format it...
$time = $row[time];
$timeFormat = date(Y-m-j, $row[time]);
$approved = $row[approved];


after the above code i want to display the distinct dates.once i display the distinct dates which are fetched from timestamps,then only i can upadte them.

thanks
mrjameer



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum