[PHP-DB] timestamp problem
I'm trying to format a MySQL Timestamp Column Type with PHP, and it's not going well. In reading up on it, I see that the idea is to convert the Timestamp type to Unix format using the strtotime() function, then using the date() function to format that result. However, this only appears to work with MySQL versions above 4.1 - where the Timestamp has been modified to have the : symbol in between the approprate numbers, as opposed to the traditional extended format (i.e. 20041117104300), which can be found on Mysql versions 4.0xx and below. I have MySQL 4.1 on my local machine, but MySQL 4.0 something on the box I'm publishing to - which is obviously the more important box. So I need to get it working using the regular format. And I don't want to just parse the characters because I have a bunch of timestamp columns and don't really think it's that great of a resolution. Any ideas? thanks... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] timestamp problem
Ok, thanks for the input. I knew that was an option as well, the problem is
that I have about eight different dates in the query, and I'd rather do a
query like SELECT projects.* FROM projects... and then format those
results, rather than do SELECT DATE_FORMAT('%m %d %Y', projects.Date1) AS
Date1, DATE_FORMAT('%m %d %Y', projects.Date2) AS Date2,... etc
any other suggestions?
John Holmes [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Doug Parker wrote:
I'm trying to format a MySQL Timestamp Column Type with PHP, and it's
not
going well. In reading up on it, I see that the idea is to convert the
Timestamp type to Unix format using the strtotime() function, then using
the
date() function to format that result. However, this only appears to
work
with MySQL versions above 4.1 - where the Timestamp has been modified to
have the : symbol in between the approprate numbers, as opposed to the
traditional extended format (i.e. 20041117104300), which can be found on
Mysql versions 4.0xx and below. I have MySQL 4.1 on my local machine,
but
MySQL 4.0 something on the box I'm publishing to - which is obviously
the
more important box. So I need to get it working using the regular
format.
And I don't want to just parse the characters because I have a bunch of
timestamp columns and don't really think it's that great of a
resolution.
Use DATE_FORMAT() in your query to format the timestamp, TO_UNIXTIME()
to retrieve a unix timestamp instead of a MySQL timestamp in your query,
or use a couple substr() calls to pull out the pieces of the MySQL
timestamp you need and then pass them to mktime().
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Selecting between using letters
How would I create a select statement in MySQL that would return a range of records from the LastName field where the value starts with a designated letter - for example, returning the range where the first letter of LastName is between A and E... Any help would be greatly appreciated. http://www.phreshdesign.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] query problem
I'm having a problem with a specific query. SELECT DISTINCT(contents_.company_id), companies.* FROM contents_, companies WHERE contents_.Products LIKE '%heating%' OR contents_.Manufacturer LIKE '%heating%' AND contents_.company_id = companies.id The query works fine without the OR segment, for example: SELECT DISTINCT(contents_.company_id), companies.* FROM contents_, companies WHERE contents_.Products LIKE '%heating%' AND contents_.company_id = companies.id works fine. But I need the query to search 2 columns, and it's returnng a vast number of records when I do the first search. Any help would be greatly appreciated. http://www.phreshdesign.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] retrieve last n records
OK, I would like to do it with the method you suggested. However, I'm having a few problems. Firstly, I can't seem to figure out how to sort rows returned from mysql_fetch_array. sort() or any other array function won't work because each row is an array, and I need to sort by the column. Secondly, which PHP classes website are you referring to? I don't even know how I would use a PHP class in conjunction with it if I did find the site. Thirdly, I do need to re-sort the data on-the-fly. Would this essentially be accomplished if I were just sorting the table data via PHP functions? Thanks for your help. Jim Hunter wrote: If it were me I would tackle this in two parts. First create a SQL statement that returns the data you want, you say you can do this so the first part is already finished. Then stuff the data into an array and when you go to display the data to the user, that is when you sort it into the chosen sort order. It is not that hard to sort an array and you might be able to find a class already written to do this on the PHP classes web site. You might even go so far as to display the info in a table that allows the user to click on a column heading and re-sort the data on-the-fly without a round trip to the server. This might add a nice touch to your UI without too much effort. Jim ---Original Message--- From: drparker Date: Thursday, March 06, 2003 14:23:29 To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] retrieve last n records I need the last n records when, say user_id = 3. So it returns all rows where user_id = 3, then, on top of that I need only the n most recently entered records. The problem with the sorting mechanism I have existing is essentially that those last n rows need to be sorted ASC or DESC by every field. So basically, I need to be able to retrieve those last n rows where user_id = whatever, then be able to sort those last 3 rows ASC or DESC by any field. Jim Hunter wrote: I guess I am confused, how does one query effect a query that you have in an other part of the page? The two queries are not related. The second thing I am confused on is: Do you want the last n records that were added to the table or do you want the last n records in a given index? Jim ---Original Message--- From: drparker Date: Thursday, March 06, 2003 13:46:58 To: [EMAIL PROTECTED] Subject: [PHP-DB] retrieve last n records I need to retrieve the last n records of a table, n will probably be 2 or 3. I know that I can accomplish this by: select * from table order by field DESC Limit n, but i would prefer not to do this way because it interferes with my sorting mechanism on the main page. Is there any other way to do it? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] export query
I need to essentially take a query and make it into a table. I'll first settle for the ability to export the query into a text file, somehow delimited. Can anyone help me with this? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] join query/relational database question
I have a relational database issue that I'll simplify for the sake of this post. I have one table, called clients, that has an id and the company name. For example: id | company --- 46 Jones Inc. 54 Baker Inc. etc. I have another table called projects, that looks like this: id | company_id | status --- 1 46 Active 2 54 Inactive etc. In this case, it is relational in that company_id is the key to the clients table, for naming purposes. My question is, is it possible to do a query across two tables that could retrieve all of the information in the projects table AND have the 'company' field in the clients table come up as well. So it would be like this: id | company_id | status | company 1 46 Active Jones Inc. 2 54 Inactive Baker Inc. any help would be greatly appreciated... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: approaching a relational database
The grid for each course looks like this (excluding CourseName, State, County, Street, City, State) Hole1 | Hole2 | Hole 3 | . | Hole18 Par 9 47 .. 6 WH 8 44 .. 5 MH 5 44 .. 5 Tees1 4 43 .. 5 Tees2 3 32..5 Tees3 etc... Tees4 Tees5 For every course, I have to enter in all of these variables. It isn't really keeping score, it's just a table of all the information for a given course - par, wh (women's handicap), mh, etc. What i did was pretty much make a table for every row. Each table (par, for example) has a course_id that I set in the courses table (that holds the CourseName, State, County, Street, City, State), and then 18 more fields - one for each hole. So it looks like: par table course_id | Hole1 | Hole2 | Hole 3 | . | Hole18 165 4 45 .. 3 Is that alright? it was the most reasonable thing I could think of, but i'm not too experienced in database design, so i figured i'd ask. Thanks, Doug Stas Trefilov wrote: Doug Parker wrote: I'm about to embark on a project where I have to enter many, many fields into a MySQL database, and I don't know how to approach the database structure. The data is statistics for a golf course. There are 18 holes, and each hole has a Red Tee Par, White Tee Par, and Blue Tee Par, which is 54 inputs already (18 x 3). Then, there are about 5 more statistics I have to keep for each hole, making the total amount of inputs 144 (18 x 8). There's about 7 more fields pertaining to the Course name, location, etc. How the heck do I approach this? I've successfully done relational databases before, but not to this scale. Does anyone have any suggestions as to how I should approach this? I attached a .jpg of the form I'm using to input, just to clarify things. Any suggestions would be greatly appreciated... table could look like this: hole_no, rtp, wtp, btp, stat1, stat2, stat3, stat4, stat5 --- --- --- --- - - - - - 1 2 3 . . . 18 what is Course name, location, etc? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: approaching a relational database
Gotcha. thanks to everyone for the advice. I'm going to switch it to the 2 table format. Thanks again, Doug David Eisenhart wrote: I would go along the lines that Stas suggests. You appear to have a two table database here: Table1: tblHole hole_id, hole_no, course_id, par, WH, MH, Trees1, Trees2, Trees3 etc... --- -- - --- - 1 1 1 2 2 1 3 3 1 ... .. 18 18 1 19 12 20 22 21 32 (Note the course_id field above acting as the foreign key to the table tblCourse) (You could make the hole_no, course_id pair of fields a unique index - afterall there should never be more than one entry for each hole_no, course_id pair) Table2: tblCourse: Course_id Course_Name State County ---- - --- 1 xyz 2 abc ... ... 10yyy (The use of course_id in your description below is good (you are thinking along the correct lines by introducing the foreign key field that references the course table) However, if I understand it correctly you are proposing to have a separate table for each hole attribute (1 table for par, 1 table for WH etc.. etc..) which is messy and unnecessary.) Good luck David Eisenhart Doug Parker [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... The grid for each course looks like this (excluding CourseName, State, County, Street, City, State) Hole1 | Hole2 | Hole 3 | . | Hole18 Par 9 47 .. 6 WH 8 44 .. 5 MH 5 44 .. 5 Tees1 4 43 .. 5 Tees2 3 32..5 Tees3 etc... Tees4 Tees5 For every course, I have to enter in all of these variables. It isn't really keeping score, it's just a table of all the information for a given course - par, wh (women's handicap), mh, etc. What i did was pretty much make a table for every row. Each table (par, for example) has a course_id that I set in the courses table (that holds the CourseName, State, County, Street, City, State), and then 18 more fields - one for each hole. So it looks like: par table course_id | Hole1 | Hole2 | Hole 3 | . | Hole18 165 4 45 .. 3 Is that alright? it was the most reasonable thing I could think of, but i'm not too experienced in database design, so i figured i'd ask. Thanks, Doug -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] approaching a relational database
I'm about to embark on a project where I have to enter many, many fields into a MySQL database, and I don't know how to approach the database structure. The data is statistics for a golf course. There are 18 holes, and each hole has a Red Tee Par, White Tee Par, and Blue Tee Par, which is 54 inputs already (18 x 3). Then, there are about 5 more statistics I have to keep for each hole, making the total amount of inputs 144 (18 x 8). There's about 7 more fields pertaining to the Course name, location, etc. How the heck do I approach this? I've successfully done relational databases before, but not to this scale. Does anyone have any suggestions as to how I should approach this? I attached a .jpg of the form I'm using to input, just to clarify things. Any suggestions would be greatly appreciated... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
