Solved

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

Posted on 2008-06-19
15
204 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
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 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

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.

Question has a verified solution.

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

Suggested Solutions

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 …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

809 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