help with MySQL query,sort and limit

Alith7 · #1 (**permalink**) 01-01-07, 11:56 AM

I'm just starting to learn PHP and MySQL for our company website. We have a quoting database, but it's getting to be too long of a list, I want to limit the query to only post the last 30 days of quoting, and I'm not sure how to do that.

the table data currently stores the original created date and also any date that the quote was updated, the update lists as NULL if it hasn't been updated. I would also like to add an if statement in that would figure in the updated date over the created date if not null.

I'm really struggling with this and would appreciate any help.

Thank you!!

dkc1657 · #2 (**permalink**) 01-01-07, 01:48 PM

That depends on how you set up your database table fields. What kind of data-types did you use to enter the date information? Let us know and maybe I can help out.

If you're using a SQL database which I assume you are, searching a table for specific data is super easy and simple, for instance:

PHP Code:


			
$query = "    SELECT month(dateMonth) as month, details, ID

                 FROM $tableNames[calendarData] 

                WHERE status 

                LIKE 'APPROVED'";

    

    // Connect to the calendarData table as specified above

    $result = mysql_query($query) or die('getEventDatesForMonth function error - ' . mysql_error());

This query selects 2 fields ("details, and ID) from all events from a certain month (dateMonth) from the database table $tableNames[calendarData]. In addition it only returns results from the records where the 'status' field is 'APPROVED' and ignores everything else.

The next piece of code simply places all that data into an array which is ready to be manipulated / displayed as needed.

PHP Code:


			
while ($row = mysql_fetch_array($result))     

        {     

        if (($month == $row['month']) && ($row[status] == "APPROVED"))

            {            

            $dateArray[] = $row['dateEvent'];         

            }

         }

Your code will look different depending on what datatypes you entered the date with in your database tables.

Alith7 · #3 (**permalink**) 01-01-07, 02:26 PM

I really need to go take some classes I think, most of that was all greek to me....

I am using MySQL with a data table formated as below:

1. quote_id = auto-increment, integer, primary key
2. sales_id = integer joined with secondary sales rep table
3. title = variable text
4. updated = date, auto update date/time stamp when record is changed after initial creation, default/create value NULL
5. created = date, auto input with date/time stamp when record is first saved

This is the query that I have so far, but it doesn't limit to the last 30 days, and I'm not sure how to write an if/then to check if there is a value for updated and if there is, use that date/time stamp instead of the created date/ time stamp.

Code:

SELECT quote.quote_id, quote.sales_id, quote.cust_id, quote.title,
DATE_FORMAT(quote.updated, '%b %e, %Y') AS updated,
DATE_FORMAT(quote.created, '%b %e, %Y') AS created
FROM quote
ORDER BY quote.created, quote.cust_id, quote.title

dkc1657 · #4 (**permalink**) 01-01-07, 09:27 PM

Then you're using a nearly identical setup as me except my date doesn't auto update, that;s just a minor detail.

I'm brand new to PHP and web programming too, I don't know the answer off the top of my head, but maybe I can point you in the right direction.

First of all, I think what you need to do is to break up your problem into many small achievable steps. Then you need to do your homework and search out solutions to each of the smaller steps. Once each tiny step is achieved, your overall goal should also work.

1st Step - Get the current date and put in array variable
==========================================

I wouldn't use a single variable, I'd use an array if I were you.
Consider using PHP date() function (http://ca.php.net/date)
ie: $dateCurrent[year] = current month, $dateCurrent[month], $dateCurrent[day]
Print the variables to test

2nd Step - Calculate the date exactly 1 month prior to current date
==================================================

Again, I would put it in an array instead of a single variable
Use the PHP strtotime() function (http://ca.php.net/manual/en/function.strtotime.php) in conjunction with the date() function
Ie: $dateLastMonth[year], $dateLastMonth[month], $dateLastMonth[day]
Print the variables to test

3rd Step - Build a SQL query to retrieve dates within a certain range
================================================== =

I think you can use your above query with the addition of a well constructed WHERE phrase. Here;s what you need to do:
1. Retrieve the month and date from your date fields in your database table
ie: "... WHERE month(dateMonth) > $dateLastMonth[month] ..."
2. Construct a WHERE clause using the variable arrays you setup in step 2.
Study these pages:
http://dev.mysql.com/doc/refman/5.0/...functions.html
http://dev.mysql.com/doc/refman/5.0/en/select.html
3. Test your query (print it out) to see if it works

4th Step - Put your results into an array
==============================

Use mysql_fetch_array($result)
Read up on it here: http://ca3.php.net/mysql_fetch_array
Example: $row = mysql_fetch_array($result)
where $row is an array that contains all of the SQL query results.
You can pass that $row array or use it as needed (pass it onto another functin, print it, further edit it etc...)

5th Step - Use PHP conditional statements to modify $row as needed
================================================== =

Before you continue, make sure that the $row array contains all the database table that you need.
Once you verified this then you can use PHP's conditional statements to manipulate the data as needed using if ... then ... else, switch ... case ... break, == != > < etc...
Once you've completed this step, print results to test it works to your satisfaction

6th Step - UPDATE sql database
========================

Finally, once step 5 works, then you're ready to UPDATE your SQL table.
Built a query as needed using UPDATE
Read up on MySQL "UPDATE" here:
http://dev.mysql.com/doc/refman/5.0/en/update.html

================================================
Anyways, hopefully this points you in the right direction. I still think the key break it down into as many time steps as possible. If you problem-solve each little step on it's own, and make sure each one works before you proceed to the next, I think you'll find that the bigger goal is achievable.

PS when working with dates I try to make it a habit to use only numerical data and not alpha, (ie: "12" instead of "December"). It's easier to use conditional statements with numerical data. All I do is simply convert a numerical date data to alpha when it's time to display it.

Alith7 · #5 (**permalink**) 01-02-07, 07:27 AM

darn.....ok, I have a LOT of reading to do!
aside from that, it sounds like this should all work once I figure what I'm doing.
here's my question though, I'm using MySQL's built in date/time stamp abilities, unadjusted, it looks something like this: 2006-10-26 10:00:26. For the updated dates, if the quote hasn't been updated, the field posts like this: 0000-00-00 00:00:00. I think I'm answering my own question, but I think the query needs to go something like this, the red parts being what I can't figure out:

SELECT quote.quote_id, quote.sales_id, quote.cust_id, quote.title, quote.updated, quote.created
FROM quote
ORDER BY quote.created, quote.cust_id, quote.title
LIMIT NOW-Last 30 days

If quote.updated = 0000-00-00 00:00:00 then ignore
else replace quote.created with quote.updated and re-sort

atleast, that's what I want it to do but I'm stuck on the how, since for the limit to work, you would have to run the if/then statement first or the query would eliminate all quotes that were created before the 30-day mark, but updated after.

Am I making any sense at all?
I'll start with going through your suggestions, much appreciated!
if anyone has some more definite ideas, it would also be appreciated.

dkc1657 · #6 (**permalink**) 01-02-07, 10:04 PM

Sorry I couldn't help you more, I'm only a few months into PHP and MySQL. But within that short time period, I was able to build a couple of applications for my school (I'm a teacher there, not a student) by chopping up big steps into many small ones.

But regarding your sql request, how would SQL know which field to apply your limit / condition?? I could be wrong but I thought LIMIT was referring to a range of table rows that is to be returned.

If you're a beginner with PHP and MySQL, I would really suggest that you try to accomplish your task in many small steps instead of one big one. It's not as efficient, but at least it'll work. Once you get better at it, you can simply go back and re-write your code to improve it's efficiency.

Quote:

Originally Posted by Alith7

darn.....ok, I have a LOT of reading to do!
aside from that, it sounds like this should all work once I figure what I'm doing.
here's my question though, I'm using MySQL's built in date/time stamp abilities, unadjusted, it looks something like this: 2006-10-26 10:00:26. For the updated dates, if the quote hasn't been updated, the field posts like this: 0000-00-00 00:00:00. I think I'm answering my own question, but I think the query needs to go something like this, the red parts being what I can't figure out:

SELECT quote.quote_id, quote.sales_id, quote.cust_id, quote.title, quote.updated, quote.created
FROM quote
ORDER BY quote.created, quote.cust_id, quote.title
LIMIT NOW-Last 30 days

If quote.updated = 0000-00-00 00:00:00 then ignore
else replace quote.created with quote.updated and re-sort

atleast, that's what I want it to do but I'm stuck on the how, since for the limit to work, you would have to run the if/then statement first or the query would eliminate all quotes that were created before the 30-day mark, but updated after.

Am I making any sense at all?
I'll start with going through your suggestions, much appreciated!
if anyone has some more definite ideas, it would also be appreciated.