Solved

PHP to display most recent entries using time stamp less than 10 days

Posted on 2008-06-19
15
205 Views
Last Modified: 2013-12-12
Someone lend me some knowledge on this statement i am trying to display the most recent entries less than or equal to 10 days

$sql = "SELECT id, businessname, primarycontact, primaryphoneareacode, primaryphoneprefix, primaryphonenumber, primarycontactemail from vendor WHERE stamp is less than or equal to currenttimestamp -10 days ORDER BY stamp ASC";

here is my format:

2008-06-05 09:15:18
0
Comment
Question by:digarati
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
15 Comments
 
LVL 49

Accepted Solution

by:
Roonaan earned 500 total points
ID: 21824582
SELECT .. FROM vendor WHERE stamp < DATE_ADD(NOW(),INTERVAL -10 DAYS) ORDER BY stamp ASC;
0
 
LVL 1

Author Comment

by:digarati
ID: 21824676
error:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/content/h/a/l/site/html/beta/home.php on line 34

line 34:

$sql = "SELECT id, businessname, primarycontact, primaryphoneareacode, primaryphoneprefix, primaryphonenumber, primarycontactemail FROM vendor WHERE stamp < DATE_ADD(NOW(),INTERVAL -10 DAYS) ORDER BY stamp ASC";
0
 
LVL 1

Author Comment

by:digarati
ID: 21824688
keep in mind my format
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 49

Expert Comment

by:Roonaan
ID: 21825005
Do you supply a link_id to your mysql_query?

Like mysql_query($sql, $linkId);

The error indicates that the second argument is incorrect. Did your mysql_connect and mysql_select_db go without errors?
0
 
LVL 1

Author Comment

by:digarati
ID: 21825365
Here is a snap of what I have:


if (mysql_errno()) {
      printf("Connect failed: %s\n", mysql_errno());
      exit();
} else {
      $sql = "SELECT id, businessname, primarycontact, primaryphoneareacode, primaryphoneprefix, primaryphonenumber, primarycontactemail FROM vendor WHERE stamp < DATE_ADD(NOW(),INTERVAL -10 DAYS) ORDER BY stamp ASC";
      $res = mysql_query($sql, $mysql);
	  
 if (($res)>1) {
						while ($newArray = mysql_fetch_array($res, MYSQL_ASSOC)) {
                                                $link2="<a href=\"vendor_details.php?id={$newArray['id']}\">$detailsimage</a>";
						$businessname = $newArray['businessname'];
						$primarycontact = $newArray['primarycontact'];
						$primarycontactemail = $newArray['primarycontactemail'];
						$primaryphoneareacode = $newArray['primaryphoneareacode'];
						$primaryphoneprefix = $newArray['primaryphoneprefix'];
						$primaryphonenumber = $newArray['primaryphonenumber'];
						$stamp = $newArray['stamp'];

Open in new window

0
 
LVL 49

Expert Comment

by:Roonaan
ID: 21825435
The error indicates that your $mysql variable is not a valid mysql link. I find it strange though that your code doesn't act on the mysql_errno() clause.

Can you try (for debugging purposes) an on die(mysql_error()) after your mysql_connect and your mysql_select_db() ?

$mysql = mysql_connect($host, $user, $pass) or die(mysql_error()));
mysql_select_db($db, $mysql) or die(mysql_error());

Alternatively you could use
if (mysql_errno()) {
      printf("Connect failed: %s\n", mysql_errno());
      exit();
} else if(!is_link($mysql)) {
      printf("Connect failed for an unknown reason");
      exit();
} else {
0
 
LVL 1

Author Comment

by:digarati
ID: 21825499
did not work: 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 'DAYS) ORDER BY stamp ASC' at line 1 please contact technical support
0
 
LVL 1

Author Comment

by:digarati
ID: 21825515
sorry i left off the database connection earlier I currently have this error:

did not work: 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 'DAYS) ORDER BY stamp ASC' at line 1 please contact technical support
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 21825567
Sorry, try and change DAYS into DAY.
0
 
LVL 1

Author Comment

by:digarati
ID: 21825694
when i switch it to 2 days it still gives me entries beyond the cutoff?
0
 
LVL 1

Author Comment

by:digarati
ID: 21830766
Increasing points for someone who can help me out here
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 21832235
What is the type of stamp field? Timestamp or datetime?
0
 
LVL 2

Expert Comment

by:bcmeyer1983
ID: 21832844
time stamp
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 21832855
I ran the query locally and didn't find any error in it. I don't know what else to tell you when no errors occur.
0
 
LVL 1

Author Closing Comment

by:digarati
ID: 31468906
had to switch this > to this <
thank you
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question