SQL Statement
| From: | Thompson, Jimi | Date: | Tue, 18 May 2004 23:37:33 +0000 |
| Subject: | SQL Statement | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-34465@lists.php.net to get a copy of this message | ||
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