[[SOLVED] Replacing UNIX timestamp with MYSql timestamp]

Just wanted to post what I used so others might benefit.

Your suggestion worked. Here's the code:

 

INSERT INTO new date(member_id,user_id,member_number, fn, joined,renewed,expires)

SELECT member_id, user_id, member_number, fn, FROM_UNIXTIME(joined), FROM_UNIXTIME(renewed), FROM_UNIXTIME(expires) FROM old_date;

 

Thanks for the help. Ireally appreciate it and I learned a lot getting this to work.

[[SOLVED] Replacing UNIX timestamp with MYSql timestamp]

I'm using mysql 5.0.45 and php 5.1.4

Yes, both tables are in the same db.

[[SOLVED] Replacing UNIX timestamp with MYSql timestamp]

I inherited a database that has a table of registration dates in unix format, for example 1200590610

, I want to change these dates to mysql timestamp format and insert them into a new table. I have a PHP script that does the conversion

<?php

$intime = (1200590610);

$read_in = date("Y-m-d H:i:s", $intime);

?>

The original table (members) has 3 fields: fn, ln, reg_date. reg_date is in unix format. I want to select the record, convert the date and insert it into a new table (mem) with the same 3 fields.

Can you help?

[Does CURDATE work with UNIX time in MySQL?]

It's a good question. we inherited the database that way. and i don't know how to go about converting them to mysql.

[Does CURDATE work with UNIX time in MySQL?]

I have a table where the dates are stored in UNIX timestamp format. I'm trying to use the CURDATE term for a select query but it isn't working. Does CURDATE work with the UNIX time stamp?
Any help is appreciated.
Here's the query:
select first_name, last_name, date_expires, title
from cl_member, `user`, cl_chapter
where cl_member.user_id=`user`.user_id
and cl_member.chapter_id=cl_chapter.chapter_id
and cl_member.chapter_id=14
and month(date_expires)=month(CURDATE())
ORDER BY last_name

[How do I query to get an ongoing range of dates]

I want to select records that have an expiration date of 1 month before the current date on.
The query looks something like this:
SELECT first_name, last_name
FROM members
WHERE exp_date >

The exp_date is stored as a UNIX timestamp.
Any help is appreciated.

[[SOLVED] How to insert a session variable into a record]

I can’t figure out how to insert the value passed by a session variable into a record.

I have a form I want to use to insert a record. I pass session variables to this page and want to insert one of them into the record. The session information is:

session_start();
{
$_SESSION['usename'] = $_POST['username'];
$idvar = $_SESSION['id'];
}

$_SESSION[‘id’] is the user_id that I want to insert into the record.

I know $idvar is getting to the page with the form because I can echo it.

The insert code looks like this:
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "register")) {
$insertSQL = sprintf("INSERT INTO h_genres (user_id, username, contrib_to, Advertising, Annual_Reports, Leader_Content, Member_Content, Brochures) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['user_id'], "int"),
GetSQLValueString($_POST['username'], "text"),
GetSQLValueString($_POST['contrib_to'], "text"),
GetSQLValueString(isset($_POST['Advertising']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString(isset($_POST['Annual_Reports']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString(isset($_POST['Leader_Content']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString(isset($_POST['Member_Content']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString(isset($_POST['Brochures']) ? "true" : "", "defined","'Y'","'N'"));

mysql_select_db($database_hauw, $hauw);
$Result1 = mysql_query($insertSQL, $hauw) or die(mysql_error());
}

I have a hidden field for the user_id:
<input name="user_id" type="hidden" id="user_id" />

I think I need to get the value in $idvar into user_id but I don’t know how.
Any help is appreciated.

[Counting records by date]

Thanks for responding. I found a different way to do this:
SELECT chapter.title,
(SELECT COUNT(*)
FROM member
WHERE member.chapter_id = chapter.chapter_id
AND member.date_expires > 1146441599) AS members_as_of_Mar31,
(SELECT COUNT(*)
FROM member
WHERE member.chapter_id = chapter.chapter_id
AND member.date_expires BETWEEN 1146441600 AND 1162339199) AS expired_after_Mar31,
(SELECT COUNT(*)
FROM member
WHERE member.chapter_id = chapter.chapter_id
AND member.date_joined BETWEEN 1146441600 AND 1162339199) AS joined_after_Mar31,
(SELECT COUNT(*)
FROM member
WHERE member.chapter_id = chapter.chapter_id
AND member.date_renewed BETWEEN 1146441600 AND 1162339199) AS renewed_after_Mar31
FROM chapter
ORDER BY chapter.title;

The results in MySQL Query Browser look correct. The problem is that this query works in MySQL Query Browser but not in my live version of MySQL. 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 '' at line 1

Any Ideas?
Thnaks again

[Counting records by date]

I have a table full of members who have joined our organization over the past several years and continue to join and renew. Their membership lasts for a specific amount of time, usually 1 year or 6 months. I need to write a query that will tell me how many members there were at the end of each month by chapter, for each month of this year.

All the records are stored in two tables called member and chapter. The fields in member are: member_id, first_name, last_name, date_joined, date_renewed, date_expires, chapter_id. The fields in chapters are: chapter_id, member_id, title.
I tried using this:
SELECT chapter.title,
(SELECT COUNT(*)
FROM member
WHERE member.chapter_id = chapter.chapter_id
AND member.date_expires > 1138751999) AS members
FROM chapter
ORDER BY chapter.title;
  but then realized that I was counting all the people who joined or renewed after Jan 31 too.
I sure would appreciate some help.
Thanks