|
|
Posted by Steve on 11/08/07 20:12
"Darko" <darko.maksimovic@gmail.com> wrote in message
news:1194548159.951566.11580@k35g2000prh.googlegroups.com...
> Steve wrote:
>> > - snip -
>> >
>> > I must agree with you on this one. I do format my queries in code, but
>> > it looks something like this:
>> >
>> > $query = sprintf(
>> > "INSERT INTO " .
>> > "main " .
>> > "(name, img, descr, from, size, format, cat, host, link, date)
>> > " .
>> > "VALUES " .
>> > "('%s', '%s', '%s', '%s', %f, '%s', '%s', '%s', '%s', '%s')",
>> > mysql_real_escape_string( $name ),
>> > ...
>> > );
>> >
>> > $queryResult = @mysql_query( $query );
>> > if ( $queryResult === FALSE ) {
>> > throw new Exception( "..." );
>> > ...
>> >
>> > ...with exception, of course, that I would try to avoid the "from"
>> > sort of variable
>> > names. This is pretty shorter, and still good enough to understand (at
>> > least for me)
>>
>> right. it is short. but, why apply formatting in one instance and not
>> another? the inconsistency kills me. further, should your now simple
>> query
>> become not so simple in the future, you'll have to re-write. and second,
>> if
>> your columns name are numerous and long, how would you visually be able
>> to
>> say this value is being inserted into this column?
>>
>> ot for a second... why not:
>>
>> function prepare(&$value)
>> {
>> return $value = "'" . mysql_real_escape_string($value) . "'";
>> }
>>
>> $values = array(
>> $value1 ,
>> $value2 ,
>> ...
>> );
>> array_walk('prepare', $values);
>> $sql = "
>> insert into sample
>> (
>> column1 ,
>> column2 ,
>> )
>> values
>> (
>> " . implode(", \r\n", $values) . "
>> )
>> ";
>> echo '<pre>' . print_r($sql, true) . '</pre>';
>>
>> if you utilize array_walk, you've got only one spot to maintain the
>> preparation of your values. further, if you make the function NOT utilze
>> mysql_* functions but still provide the same functionality, you can move
>> from db to db without ever having to do a re-write.
>
> Of course, I utilize a DBFactory that produces an implementation of
> DBConnection interface (which is either PGConnection (for Postgres) or
> MySQLConnection (for MySQL). That way they have a common interface,
> addSlashes() being amongst them.
>
>> further, notice that
>> even when using implode(), i can still get a nicely formatted query to
>> the
>> browser should i need to debug it...AND (sorry, *and*)
>
> Don't worry :)
>
>> i can easily,
>> visually tell what value goes with what column. just a thought.
>
> Yes, of course, you tend to simplify debugging queries to the point
> where majority of us don't.
i spend the majority of my programming efforts with inventory, billing, and
invoicing. my queries tend to be large and could get complex quickly. i
build them up in pieces so i can test every portion. when each works, i pull
them all together. the formatting is a life saver in making sure everything
works...and when it doesn't, i can copy just the ill-effected portion out to
test it.
while i don't dogmatically follow a standard of formatting, i am consistent.
i stray little...even when it's an insert statement as we've seen here. to
me, the most important portion of a script that deals with a db is the
queries it employs. when i see people wanting to save virtual screen space
by improperly, or not at all, formatting...it just indicates to me that the
query itself was not given it's due merit. that means that a lot of things
surrounding the data processing could have been overlooked. that's just me.
not everyone is accounting for revenues that could be drastically
over/understated if a query is not written correctly (formatting aside).
> This is probably because, me personally, I don't like multi hundred or
> multi thousand lines
> long files because I get confused in them.
i have functions that isolate much of the data summarization that i need.
that's how i separate out the bigger queries. i'll call on the summarized
data with quite simple and manageable sql statements. and those don't span
more than 10 to 20 lines even with my affinity to format the way i do.
> And I still get quite along
> with queries formatted
> the way described above.
as do most of us. if there is *some* formatting, that's great. it was
obvious, given the error message pointing to line 1 even though the sql
wrapped in the post, that the op just slopped the mess together...and
wonders why it won't work. of course his problem was with reserved words,
but spotting other errors may not be so easily overcome next time.
>>
>> ok, back on topic. can you tell me what the aim of this query is?
>>
>
> No, I can't :)
it breaks out totals for different charges (dollars and hours) based on a
data segment...say, who the estimator was, or what insurance company was
used...basically, a totalling for any field in either of the two tables
(estimates/repairs).
again, how ever you or anyone else formats it, i don't care. the point is
*to* format.
cheers.
Navigation:
[Reply to this message]
|