1 Reply Latest reply on Sep 22, 2008 1:42 PM by Newsgroup_User

    sql help variable php

    lotpb
      How do I set up a variable in my sql to run of dates, where it say 2008 0r 2007 to do year, year-1, year-2. I am using php

      $var1_fmAvgCustGraph = "%";
      if (isset($_GET['Datefrom'])) {
      $var1_fmAvgCustGraph = $_GET['Datefrom'];
      }
      $var2_fmAvgCustGraph = "%";
      if (isset($_GET['Dateto'])) {
      $var2_fmAvgCustGraph = $_GET['Dateto'];
      }
      mysql_select_db($database_addressbook, $addressbook);
      $query_fmAvgCustGraph = sprintf("SELECT '2008', AVG(Customer.Amount) as Amount, '2008' as Windows FROM Customer WHERE Customer.`Date` >= %s AND Customer.`Date` <= %s UNION SELECT '2007', AVG(Customer.Amount) as Amount,'2007' as Windows FROM Customer WHERE Customer.`Date` >= %s AND Customer.`Date` <= %s UNION SELECT '2006', AVG(Customer.Amount) as Amount,'2006' as Windows FROM Customer WHERE Customer.`Date` >= %s AND Customer.`Date` <= %s UNION SELECT '2005', AVG(Customer.Amount) as Amount,'2005' as Windows FROM Customer WHERE Customer.`Date` >= %s AND Customer.`Date` <= %s UNION SELECT '2004', AVG(Customer.Amount) as Amount,'2004' as Windows FROM Customer WHERE Customer.`Date` >= %s AND Customer.`Date` <= %s UNION SELECT '2003', AVG(Customer.Amount) as Amount,'2003' as Windows FROM Customer WHERE Customer.`Date` >= %s AND Customer.`Date` <= %s", GetSQLValueString($var1_fmAvgCustGraph, "date"),GetSQLValueString($var2_fmAvgCustGraph, "date"),GetSQLValueString($var3_fmAvgCustGraph, "date"),GetSQLValueString($var4_fmAvgCustGraph, "date"),GetSQLValueString($var5_fmAvgCustGraph, "date"),GetSQLValueString($var6_fmAvgCustGraph, "date"),GetSQLValueString($var7_fmAvgCustGraph, "date"),GetSQLValueString($var8_fmAvgCustGraph, "date"),GetSQLValueString($var9_fmAvgCustGraph, "date"),GetSQLValueString($var10_fmAvgCustGraph, "date"),GetSQLValueString($var11_fmAvgCustGraph, "date"),GetSQLValueString($var12_fmAvgCustGraph, "date"));
      $fmAvgCustGraph = mysql_query($query_fmAvgCustGraph, $addressbook) or die(mysql_error());
      $row_fmAvgCustGraph = mysql_fetch_assoc($fmAvgCustGraph);
      $totalRows_fmAvgCustGraph = mysql_num_rows($fmAvgCustGraph);
        • 1. Re: sql help variable php
          Level 7
          .oO(lotpb)

          >How do I set up a variable in my sql to run of dates, where it say 2008 0r 2007
          >to do year, year-1, year-2. I am using php
          >
          > $var1_fmAvgCustGraph = "%";
          > if (isset($_GET['Datefrom'])) {
          > $var1_fmAvgCustGraph = $_GET['Datefrom'];
          > }
          > $var2_fmAvgCustGraph = "%";
          > if (isset($_GET['Dateto'])) {
          > $var2_fmAvgCustGraph = $_GET['Dateto'];
          > }
          > mysql_select_db($database_addressbook, $addressbook);
          > $query_fmAvgCustGraph = sprintf("SELECT '2008', AVG(Customer.Amount) as
          >Amount, '2008' as Windows FROM Customer WHERE Customer.`Date` >= %s AND
          >Customer.`Date` <= %s UNION SELECT '2007', AVG(Customer.Amount) as
          >Amount,'2007' as Windows FROM Customer WHERE Customer.`Date` >= %s AND
          >Customer.`Date` <= %s UNION SELECT '2006', AVG(Customer.Amount) as
          >Amount,'2006' as Windows FROM Customer WHERE Customer.`Date` >= %s AND
          >Customer.`Date` <= %s UNION SELECT '2005', AVG(Customer.Amount) as
          >Amount,'2005' as Windows FROM Customer WHERE Customer.`Date` >= %s AND
          >Customer.`Date` <= %s UNION SELECT '2004', AVG(Customer.Amount) as
          >Amount,'2004' as Windows FROM Customer WHERE Customer.`Date` >= %s AND
          >Customer.`Date` <= %s UNION SELECT '2003', AVG(Customer.Amount) as
          >Amount,'2003' as Windows FROM Customer WHERE Customer.`Date` >= %s AND
          >Customer.`Date` <= %s", GetSQLValueString($var1_fmAvgCustGraph,
          >[...]

          Uuh ... this is really ugly. You should learn how to use date and time
          functions and the GROUP BY clause. You only need a single query to get
          all the data you want, no UNIONs required.

          Micha