Member Avatar for bradly.spicer

Hey all,

Just some questions regarding queries and the logic behind what I'm trying to achieve.
So basically, I have these rows of information inside a MSQL table called Customers. The columns are as follow:

id  customer_name   customer_name_letterhead    customer_notes  systype     status  signaltype  verification    address     postcode    telephone   mobile  mobiletwo   email   mainarea    installation    Contract    expiration  SPA     nservice    maintenance     monitoring  MS  certdate

Now if we think about it, nservice is a date and is shortened for next service.
I want to query it so that IF nextservice is BEHIND the date we are currently at that it presents the following information of that customer:

Name, Mobile, Mobiletwo, Email, Address, nservicedate and How many days past it is.

I want this to be for each customer at said date, e.g

Bob | 123456 | 123456 | bob@email.com | 25bob avenue | 28/01/2013 | 3 Days late |

Just really wanting to know about how these methods are done, and how to make it automatic when viewing that page? Also in the future to have CronJobs e.g If the day late is >10 then send them an email etc.

Thanks guys!

Edited by bradly.spicer
  • 3 Contributors
  • 5 Replies
  • 127 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by Squidge

Recommended Answers

All 5 Replies

Member Avatar for pritaeas

You can use something like:

SELECT * FROM Customers WHERE DATEDIFF(NOW(), nservice) > 0

More functions in the manual.

Member Avatar for bradly.spicer

And how would I go about echoing out all of these values?
Also I'm specifying which row so I'm getting a weird error:

SELECT id, customer_name, address, postcode, telephone, mobile, mobiletwo, email, nservice, FROM Customers WHERE DATEDIFF (NOW(), nservice) > 0
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 'FROM Customers WHERE DATEDIFF (NOW(), nservice) > 0' at line 10

Here is what I have so far:

<?PHP       
            $query = "SELECT id,
                    customer_name,
                    address,
                    postcode,
                    telephone,
                    mobile,
                    mobiletwo,
                    email,
                    nservice,
                    FROM Customers 
                           WHERE DATEDIFF (CURDATE(), nservice) > 0";

            $result = mysql_query($query) or die('<p>' . $query . '</p><div>' . 
                                   mysql_error() . '</div>');

            $customer = mysql_fetch_assoc($result);  

?>

echo $Result;
Edited by bradly.spicer because: Changed Now() to CURDATE() so it didn't have time included
Member Avatar for pritaeas

Works for me, but my column is a datetime. Works on dates too, make sure your column has the right type.

UPDATE: remove the comma before FROM...

Edited by pritaeas
Member Avatar for bradly.spicer

I've got mine set as DATE. Still getting errors, Not sure I'm querying this correctly.

This is my whole page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<style>
input, textarea {  
    padding: 9px;  
    border: solid 1px #E5E5E5;  
    outline: 0;  
    font: normal 13px/100% Verdana, Tahoma, sans-serif;  
    width: 200px;  
    background: #FFFFFF;  
    }  
textarea {  
    width: 400px;  
    max-width: 400px;  
    height: 150px;  
    line-height: 150%;  
    }  
input:hover, textarea:hover,  
input:focus, textarea:focus {  
    border-color: #C9C9C9; 
    }  
.form label {  
    margin-left: 10px;  
    color: #999999;  
    }  
.submit input {  
    width: auto;  
    padding: 9px 15px;  
    background: #617798;  
    border: 0;  
    font-size: 14px;  
    color: #FFFFFF;  
    }

.Update {

   font-size: 20px;


}
</style>
<?php require '../header.php'; ?> 

        <div class="clear">
        </div>
        <div class="grid_12">
            <div class="box round first fullpage">
    <?PHP
    $query = "SELECT id,
    customer_name,
    address,
    postcode,
    telephone,
    mobile,
    mobiletwo,
    email,
    nservice,
    FROM Customers
    WHERE DATEDIFF (CURDATE(), nservice) > 0";
    $result = mysql_query($query) or die('<p>' . $query . '</p><div>' .
    mysql_error() . '</div>');
    $customer = mysql_fetch_assoc($result);
    ?>
 <h2>Queries </h2>
                <div class="block ">

                <?PHP echo $customer; ?>


                </div>

            </div>
        </div>
        <div class="clear">
        </div>
    </div>
    <div class="clear">
    </div>
    <div id="site_info">
        <p>
            Copyright <a href="#">Rhino Admin</a>. All Rights Reserved.
        </p>
    </div>
</body>
</html>

I think from here I'm not following something correctly haha

In header.php I simply have the navigation divs and a connection to the DB.

Member Avatar for Squidge

As pritaeas has said:

remove the comma before FROM...

$query = "SELECT id, customer_name, address, postcode, telephone, mobile, mobiletwo, email, nservice
    FROM Customers WHERE DATEDIFF (CURDATE(), nservice) > 0";
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.