Solved

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

Posted on 2008-06-19
15
201 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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…
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 look for a specific file type in a local or remote server directory using PHP.

746 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

11 Experts available now in Live!

Get 1:1 Help Now