mysql_query...... Question

NTGre

i use this

$result = @mysql_query( "SELECT photo_id,photo_caption,photo_filename,enddate FROM gallery_photos WHERE photo_category='".addslashes($cid)."' LIMIT $from, $max_results");

and this to display the results..

 $nr = @mysql_num_rows( $result );

while( $row = mysql_fetch_array( $result ) )
				         {
                        $result_array[] = "<a href='index.php?cid=$cid&pid=
						".$row[0]."'><img src='".$images_dir."/tb_".$row[2]."' border='0' alt='"
						.$row[1]."' />".$row[1]."
						".$row[3]." 
						</a>";

row[3] looks this way 20051126214150 and i want to make it look like
($hour,$minute,$second,$month,$day,$year)
to use later this
$secdiff = mktime($hour,$minute,$second,$month,$day,$year) - time();
cause i want to calculate the time remain.

what can i do???

Thnx for your Time

Trs2988

For that, you'd need to use substr(). It's used for taking substrings out of the strings, or separating just a part of the string.

The date appears to be in the format YYYYMMDDMMSS. Therefore, you could do something like this (but remember that with substr, the first character isn't #1 - it's #0);

$year = substr($row[3],0,4); // take 4 characters, starting at #0
$month = substr($row[3],4,2); // take 2 characters, starting at #4
$day = substr($row[3],6,2); // take 2 characters, starting at #6
// etc etc

That should work. Let me know how it goes.

rheinen

What Trs suggested should work, however, if you simply want the UNIX timestamp value then don't bother going through the trouble of parsing the year, month, day, etc out of the string and then using mktime(), just call UNIX_TIMESTAMP in the query like this:

$result = @mysql_query( "SELECT photo_id,photo_caption,photo_filename, UNIX_TIMESTAMP(enddate) FROM gallery_photos WHERE photo_category='".addslashes($cid)."' LIMIT $from, $max_results");

That way $row[3] will be a UNIX timestamp that you can subtract the current time from or do whatever you need to do with it. Of course you could also do the subtraction right in the SQL:

$result = @mysql_query( "SELECT photo_id,photo_caption,photo_filename, UNIX_TIMESTAMP(enddate) AS end, UNIX_TIMESTAMP(enddate) - UNIX_TIMESTAMP() as difference FROM gallery_photos WHERE photo_category='".addslashes($cid)."' LIMIT $from, $max_results");

Now $row[3] contains the timestamp for end date and $row[4] contains the difference between that value and the current time.

Have a look at the Date and Time Functions for MySQL. UNIX_TIMESTAMP is down the page a ways.

Hope that helps,

Ryan

NTGre

Yep is working but..... 😕

i v done it this way

$result_array[] = "<a href='index.php?cid=$cid&pid=				".$row[0]."'><img src='".$images_dir."/tb_".$row[2]."' border='0' alt='".$row[1]."' />".$row[1]." ".$year = substr($row[3],0,4)."/".$month = ubstr($row[3],4,2)."/".$day = substr($row[3],6,2)."
						</a>";

its OK ???

and if its ok what i must do to display the countdown result instead of this???

".$year = substr($row[3],0,4)."/".$month = ubstr($row[3],4,2)."/".$day = substr($row[3],6,2)."

the countdown code is ....

$hour = 15;   // 0 ... 24
	$minute = 7;  // 0 ... 60
	$second = 5;  // 0 ... 60
	$month = 12;   // 1 ... 12
	$day = 8;     // 1 ... 28/29/30/31
	$year = 2005; // four digits

// time in seconds between now and then
$secdiff = mktime($hour,$minute,$second,$month,$day,$year) - time();


$a = floor ($secdiff/86400); // number of days
$b = floor ($secdiff%86400/3600); // + number of hours
$c = floor ($secdiff%86400%3600/60); // + number of minutes
$d = floor ($secdiff%86400%3600%60); // + number of seconds

// define extra text
$text1 = " Still "; $text2 = " days "; $text3 = " hours "; 
$text4 = " minutes "; $text5 = " and "; $text6 = " seconds "; $text7 = " to go! ";

//  if the  date you are counting down to is more then x days down the road  
//  you may not want hours($b), minutes($c) and seconds($d) to be displayed 
//  to activate this feature, remove '#' at the beginning of the next line
#   if ($secdiff > 864000) $b = " " . $c = " " . $d = " ";  // (864000 seconds = 10 days)

// change displayed text and numbers to get the grammar right 
// and avoid to display "0" days, hours etc.
if ($a  < 2) $text2 = " day ";
if ($a < 1) $a = " " . $text2 = " ";
if ($b  < 2) $text3 = " hour ";
if ($b < 1) $b = " " . $text3 = " "; 
if ($c < 2) $text4 = " minute ";
if ($c < 1) $c = " " . $text4 = " ";
if ($d < 2) $text6 = " second ";
if ($d < 1) $d = " " . $text6 = " ";
if ($d  == 0) $text5 = " ";

// the countdown message
if ($secdiff > 0) echo $text1 . $a . $text2 . $b . $text3 . $c . $text4 . $text5 . $d . $text6 . $text7;

//after countdown is up this message will be displayed
else echo "The event we counted down to already has occured. ";

????????????????????

Thnx for your time 🙂

NTGre

Thnx rheinen...for your post
but the result im taking is not human readable
That i want to display to my users is the same but the date must be readable and the diference counted down the way the countdown code i v post in my second reply...

and PLS DONT FORGET I M NEWBIE (35yo) !!!

Thnx again for your time.

rheinen

NTGre wrote:

Yep is working but..... 😕

i v done it this way

$result_array[] = "<a href='index.php?cid=$cid&pid=				".$row[0]."'><img src='".$images_dir."/tb_".$row[2]."' border='0' alt='".$row[1]."' />".$row[1]." ".$year = substr($row[3],0,4)."/".$month = ubstr($row[3],4,2)."/".$day = substr($row[3],6,2)."
						</a>";

its OK ???

It's OK, but quite confusing. If you would assign $year, $month, $day before you built that string it may be more clear.

Based on the SQL I posted earlier:

$result = @mysql_query( "SELECT photo_id,photo_caption,photo_filename,
UNIX_TIMESTAMP(enddate) AS end, UNIX_TIMESTAMP(enddate) - UNIX_TIMESTAMP() as difference
FROM gallery_photos WHERE photo_category='".addslashes($cid)."'
LIMIT $from, $max_results");

You could write code similar to the following, using the date function to help format the date:

$photoId = $row[0];
$caption = $row[1];
$filename = $row[2];
$endDate = $row[3];

$result_array[] = "<a href='index.php?cid=$cid&pid=$photoId'><img src='$images_dir/tb_$filename' border='0' alt='$caption' />
$caption " . date("Y/m/d") . "</a>";

I've used used variable interpolation within that string to clean up a lot of the excess punctuation you were using and I've used the date() function to display the year, month, and day. Check the date function manual page for more details on the different formatting characters available to you and the string parsing section for more information on variable interpolation.

NTGre wrote:

and if its ok what i must do to display the countdown result instead of this???

".$year = substr($row[3],0,4)."/".$month = ubstr($row[3],4,2)."/".$day = substr($row[3],6,2)."

the countdown code is ....

$hour = 15;   // 0 ... 24
	$minute = 7;  // 0 ... 60
	$second = 5;  // 0 ... 60
	$month = 12;   // 1 ... 12
	$day = 8;     // 1 ... 28/29/30/31
	$year = 2005; // four digits

// time in seconds between now and then
$secdiff = mktime($hour,$minute,$second,$month,$day,$year) - time();


$a = floor ($secdiff/86400); // number of days
$b = floor ($secdiff%86400/3600); // + number of hours
$c = floor ($secdiff%86400%3600/60); // + number of minutes
$d = floor ($secdiff%86400%3600%60); // + number of seconds

For this section you could write something like:

$secdiff = $row[4];

$nDays = floor ($secdiff/86400); // number of days
$nHours = floor ($secdiff%86400/3600); // + number of hours
$nMinutes = floor ($secdiff%86400%3600/60); // + number of minutes
$nSeconds = floor ($secdiff%86400%3600%60); // + number of seconds

It's up to you how you want to name your variables, but something like $nDays is much more meaningful than $a and when you come back two your code in a few weeks you'll find that you'll be able to get a quicker grasp of it.

Anyways, your code from this point to the end should work just fine. Good luck,

Ryan

NTGre

i vv try to create a new php file countdown.inc.php to use it as include in my page..
but when im trying to test if its working drives me CRAZY :queasy:

/set a future date
	//$hour = 15;   // 0 ... 24
  //  $minute = 10;  // 0 ... 60
	//$second = 00;  // 0 ... 60
	//$month = 01;   // 1 ... 12
	//$day = 01;     // 1 ... 28/29/30/31
	//$year = 2006; // four digits

///////////////////////////////////////////////Second Part///////////////////////////////////

   $enddate=20060101010101;

				 $year = substr($enddate,0,4);
				 $month = substr($enddate,4,2);
				 $day = substr($enddate,6,2);
				 $hour = substr($enddate,8,2);
				 $minute = substr($enddate,10,2);
				 $second = substr($enddate,12,2);

the original code is whith the // in front.
when i remoove the // working fine.

but when i use the second part insted of
the count down working but displays wrong result

for $enddate=20060101010101; i see this
Still 375 days 12 hours 21 minutes and 53 seconds to go!
:eek:

what im doing wrong???

Thnx for your help .