Zikula Community

• **unknown user**
  
  

  • Rank: Softmore
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 379

  Posted: Jun 14, 2005 - 06:25 AM

  I've been working on my project tracker again, and I've come up against a brick wall. I have a start date in the form of YYYY-MM-DD, a length (a number), and a length type. I really would rather just calculate the expected end date based on this information when I grab everything from the query, rather than doing bizarre and unpleasant things in PHP, but this seems to be beyond my skills with MySQL. (Many things are, really.) :)
  
  Here's the code I have:
  

  Code

  $sql = "SELECT $milescolumn[mileid],
                  $milescolumn[projid],
                  $milescolumn[deliverable],
                  $milescolumn[start_date],
                  $milescolumn[compdate],
                  $milescolumn[length_num],
                  $milescolumn[length_type],
                  $milescolumn[per_comp],
                  $milescolumn[est_due],
                  $milescolumn[approx_compdate],
                  DATE_ADD($milescolumn[start_date], INTERVAL $milescolumn[length_num] $milescolumn[length_type])
              FROM $milestable
              WHERE $milescolumn[projid] = '" . (int)pnVarPrepForStore($projid) ."'
              ORDER BY $milescolumn[start_date] ASC";

  
  
  This gives me the following:
  

  Code

  SELECT nuke_project_milestones.mileid, nuke_project_milestones.projid, nuke_project_milestones.deliverable, nuke_project_milestones.start_date, nuke_project_milestones.compdate, nuke_project_milestones.length_num, nuke_project_milestones.length_type, nuke_project_milestones.per_comp, nuke_project_milestones.est_due, nuke_project_milestones.approx_compdate, DATE_ADD(nuke_project_milestones.start_date, INTERVAL nuke_project_milestones.length_num nuke_project_milestones.length_type) FROM nuke_project_milestones WHERE nuke_project_milestones.projid = '3' ORDER BY nuke_project_milestones.start_date ASC

  
  
  However, THAT gives me this error:
  

  Quote

  #1064 - 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 'nuke_project_milestones.length_type) FROM nuke_project_mileston

  
  
  Am I trying to accomplish the impossible here, or do I just have a typo somewhere that I can't seem to find?

  • 
  •  
• **unknown user**
  
  

  • Rank: Registered User
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 8

  Posted: Jun 14, 2005 - 03:57 PM

  Happened to see this link :
  http://archives.neohapsis.com/archives/mysql/2004-q3/0833.html
  
  
  
  

  Quote

  
  With DATE_ADD(date,INTERVAL expr type), date and expr can be dynamic, but
  type must be literally one of the types in the list. It cannot come from a
  column or function. It would be nice if it could, but it cannot.
  

  
  
  A work around would be to introduce a switch case in the code and convert the $milescolumn[length_type] to the type value and then use that value in the query

  • 
  •  
• **unknown user**
  
  

  • Rank: Softmore
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 379

  Posted: Jun 14, 2005 - 11:08 PM

  Thanks! *beats head on desk* So much for spending an afternoon on a bit of code that could work except that the underlying code in this one case won't let it....
  
  But look at all the fancy new SQL I'm learning! :D

  • 
  •  
•  
•  

Users on-line

• designerdogs

This list is based on users active over the last 60 minutes.