PHP DB - Re: [PHP-DB] SQL Statement

Author

Re: [PHP-DB] SQL Statement

Cal Evans

2004-05-18, 9:30 pm

Jimi,

PHP does not return an error because it knows nothing about valid sql.
It's just knows if it's a valid PHP statement. (which it is because
you've got the "'s in the right place and a ; at the end. ) :)

It's up to MySQL to return an error.

As to your statement.
1: It's easier and valid in PHP to write it:
$sqlwrk = "SELECT pk_phone_reports,
SUM(calls) AS total_calls,
date,
calls ,
fk_ph_num
FROM phone_reports
WHERE (pk_phone_number = {fk_phone}) AND
(date BETWEEN '{$my_startdate}' AND
'$my_enddate')”
GROUP BY pk_phone_reports,
fk_ph_num,
date,
calls";

In reformatting the string I found a couple of PHP things that are
probably what's tripping you up. PHP used the "." as a string
concatonator. You were missing several of them. Notice that I remove
them all. Because we enclosed the entire statement in a sing " pair, we
can use {$variable} for substitution. Makes life a lot easier when
building sql statements.

Also, I removed all the back-tiks. Not because they were wrong but they
annoy my and in 99% of the cases are not necessary.

Oh and welcome to PHP/MySQL. I hope you'll find the language easy and
the people friendly.

Finally, a could of things that will make life easier for you if you are
going to be doing much database work in PHP/MySQL.

http://php.weblogs.com/
This is the best database abstraction layer I've found. Even if you
only use MySQL, it's worth the investment in time to learn it. (mainly
for the debug feature.)

www.sqlyog.com
Best FE for MySQL on Windows I've ever found. It's $49.00 (I think...I
forget) but it's worth it. It's got it's bugs but overall it's a killer
tool.

http://www.fabforce.net/dbdesigner4/
A killer, open source tool for designing databases. If you are used to
the commercial tools costing $4k+ then you'll feel right at home with
this. (It's my understanding that MySQL has purchased this project, but
I may have my story wrong.)

Finally, you've already found the greatest tool for debugging MySQL/php,
the lists.

See ya round.
=C=

:
: Cal Evans
: Evans Internet Construction Company
: 615-360-3385
: http://www.eicc.com
: Building web sites that build your business
:

Thompson, Jimi wrote:
> 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 datab
ases) 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 acc
eptable 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 a
gainst 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 runn
ing 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
>
>

John W. Holmes

2004-05-18, 10:30 pm

Thompson, Jimi wrote:
> 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.

PHP does return an error message, you're just not displaying it.

$rswrk = mysql_query($sqlwrk) or die(mysql_error());

> $sqlwrk .= " WHERE (`pk_phone_number` = " . $fk_phone”) AND
> (`date` BETWEEN “'$my_startdate'” AND “'$my_enddate'”)”;

> Which brings me to my lovely parse error "Parse error:

You're not concatinating your string correctly.

$sqlwrk .= " WHERE (`pk_phone_number` = " . $fk_phone . ") AND
(`date` BETWEEN '" . $my_startdate . "' AND '" . $my_enddate . "')";

or

$sqlwrk .= " WHERE (`pk_phone_number` = $fk_phone) AND
(`date` BETWEEN '$my_startdate' AND '$my_enddate')";

--
---John Holmes...

Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/

php|architect: The Magazine for PHP Professionals – www.phparch.com

Sponsored Links