For some background, I've been tasked with building a marketing tracking application 
by the PHB's who think that being able to write SQL means you can code.  I know how to 
get data into a database and I can do thing with it once it's in there, but this is 
one of my first attempts at extracting anything remotely end-user-ish.  Since the only 
server I can get is an old cobalt RAQ 2, the only database I can run is MySQL.  Iâm 
not terribly familiar with MySQL (spent more time working with commercial databases) 
and Iâm a complete newbie at PHP, so please donât flame me yet â  

Iâm not even sure what information Iâll need to provide you so here goes:
Platform â Red Hat 9.0 Linux on a BogoMIPS CPU
PHP Version - 4.3.3
Apache Version - 1.3.28
MySQL Version - 4.0.14

Hereâs my SQL statement which works fine from a DBA perspective (meaning that I can 
execute it from the command line against the database and obtain the desired results), 
but Iâm obviously missing something in the syntax in converting this to an 
acceptable PHP SQL statement.  

I know that I can connect to the database and can extract other records, but I keep 
getting âunable to parseâ error message and donât know enough to know which 
thing Iâm doing is wrong.

SELECT 
  phone_reports.pk_phone_reports,
  SUM(phone_reports.calls) AS total_calls,
  phone_reports.fk_ph_num,
  phone_reports.`date`,
  phone_reports.calls
FROM
  phone_reports
WHERE
  (phone_reports.fk_ph_num = 1) AND 
  (phone_reports.`date` BETWEEN '2004/05/17' AND '2004/07/05')
GROUP BY
  phone_reports.pk_phone_reports,
  phone_reports.fk_ph_num,
  phone_reports.`date`,
  phone_reports.calls


Hereâs the PHP SQL statement built from the SQL statement above â

<?php
if ($fk_phone != NULL) {
        $sqlwrk = "SELECT `pk_phone_reports`, `date`, `calls` , `fk_ph_num` FROM 
`phone_reports`";
        $sqlwrk .= " WHERE `pk_phone_number` = " . $fk_phone;
        $rswrk = mysql_query($sqlwrk);
        if ($rswrk && $rowwrk = mysql_fetch_array($rswrk)) {
                echo $rowwrk["number"];
        }
        @mysql_free_result($rswrk);
}
?>

This seems to work ok, but doesnât return any results (which I expected) but it does 
parse!  So then I try do this â 

<?php
if ($fk_phone != NULL) {
        $sqlwrk = "SELECT `pk_phone_reports`, SUM(`calls`) AS `total_calls`,
 `date`, `calls` , `fk_ph_num` FROM `phone_reports`";
        $sqlwrk .= " WHERE `pk_phone_number` = " . $fk_phoneâ;
        $rswrk = mysql_query($sqlwrk);
        if ($rswrk && $rowwrk = mysql_fetch_array($rswrk)) {
                echo $rowwrk["number"];
        }
        @mysql_free_result($rswrk);
}
?>

Note that this shouldnât work since it isnât a valid SQL statement.  I'm not sure 
why PHP doesn't return some kind of an error message.  I know that the database does!  
You cannot execute SUM without its required âGROUP BYâ statement (at least against 
the database directly) but it at least parses as PHP.  So then I expand by statement 
to include the rest of the âWHEREâ clause.  

<?php
if ($fk_phone != NULL) {
        $sqlwrk = "SELECT `pk_phone_reports`, SUM(`calls`) AS `total_calls`,`date`, 
`calls` , `fk_ph_num` FROM `phone_reports`";
        $sqlwrk .= " WHERE (`pk_phone_number` = " . $fk_phoneâ) AND (`date` BETWEEN 
â'$my_startdate'â AND â'$my_enddate'â)â;
$sqlwrk .= " GROUP BY `pk_phone_reports`, `fk_ph_num`, `date`, `calls`;
        $rswrk = mysql_query($sqlwrk);
        if ($rswrk && $rowwrk = mysql_fetch_array($rswrk)) {
                echo $rowwrk["number"];
        }
        @mysql_free_result($rswrk);
}
?>

  

Which brings me to my lovely parse error "Parse error: parse error in ad_report.php on 
line 138."  What I want this to do is to return to me the "SUM" of all the calls to a 
specific phone number between two dates (the date the advertisement started running 
and the date it ended) and this display this in a cell in table on a web page.



Any help or pointers greatly appreciated.

Thanks,

Ms. Jimi Thompson, CISSP
Manager, Web Operations
Cox School of Business
Southern Methodist University

"If we want women to do the same work as men, we must teach them the same things." - 
Plato


Reply via email to