Solved

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

Posted on 2008-06-19
15
206 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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 
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

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The viewer will learn how to count occurrences of each item in an array.
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 …

717 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