Solved

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

Posted on 2008-06-19
15
202 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now