My contribution to the insanity INSERT statements made easy:
$genericQY = "INSERT INT MD_LMGR_Leads ("; $genericQYvalues = " VALUES (";
$genericQY .= " FirstName,"; $genericQYvalues .= " 'John',";
$genericQY .= " LastName"; $genericQYvalues .= " 'Smith'";
$genericQY .= " )"; $genericQYvalues .= " );";
$genericQY .= $genericQYvalues;
$genericRS = mysql_query($genericQY);
I use this structure so if I decide that I don't need certain data I can comment out a single line to remove the column name and corresponding value. Also helpful for making updates to column/value pairs and not worry about the dreaded error involve # of columns not matching.
things you have to watch for:
1. Make sure you don't have a comma on the last item
2. Make sure you have spaces where appropriate so when it concatenates the strings, you don't get stuff crammed together (not really an issue with the INSERT statement, but I try to keep a consistant practice with all my queries so I don't slip up SELECT columnsFRM tableWHERE something = something is where it really gets ya if you forget spaces just as an example)
3. Make sure to remember to concatenate the "query" and "values" parts
I like to think this is a little "outside the box" thinking since common practice is "one command, one line" or "total chaos" hah.
Any comments on improving this or other unique stylistic ways people like to design their code?
-TG
= = = message = = =
Wed, January 24, 2007 8:07 pm, Robert Cummings wrote:
Wed, 2007-01-24 at 18:23 -0600, Richard Lynch wrote:
>Wed, January 24, 2007 7:41 am, Roman Neuhauser wrote:
># sancar.saran (AT) evodot (DOT) com / 2007-01-24 13:57:03 +0200:
>>and also in these days I'm looking for 19 inch (or more) wide LCD
>>sceerns to able to fit longer lines in my screen
>>
>Number of reading errors people make grows with line length,
>this has been known for as long as I remember. You're increasing
>the
>probability of bugs in the code, and get tired sooner because
>following
>long lines requires more energy.
>>
>I believe those results are specific to what is being read.
>>
>Surely it's easier to read:
>>
>SELECT blah, blah, blah, blah, blah, blah, blah, blah, blah
>>
>if it's all on one line, no matter how many fields there are, while
>trying to read the code as a whole.
>>
>Sure, it can be "hard" to find/read the individual field names, on
>the
>rare occasion that you need to do that
>
Dear Mr Lynch, normally I highly respect your commentary on the list,
but today I think you've been-a-smoking the crackpipe a tad too much.
There is no way in hell one long line of SQL is easier to read than
formatted SQL that clearly delineates the clause structure.
SELECT A.field1 AS afield1, A.field2 AS afield2, B.field1 AS bfield1,
B.field2 AS bfield2, C.field1 AS cfield1, C.field2 AS cfield2,
D.field1
AS dfield1, D.field2 AS dfield2 FRM tableA as A LEFT JIN tableB AS B
N B.fee = A.foo LEFT JIN tableC AS C N C.fii = B.fee LEFT JIN
tableD
AS D N D.fuu = C.fii WHERE A.foo = 'someValue' RDER BY afield1 ASC,
cfield2 ASC
The above line "should" be on one line, but my email client might
autowrap it. Either way, the following is formatted and is much
clearer.
SELECT
A.field1 AS afield1,
A.field2 AS afield2,
B.field1 AS bfield1,
B.field2 AS bfield2,
C.field1 AS cfield1,
C.field2 AS cfield2,
D.field1 AS dfield1,
D.field2 AS dfield2
FRM
tableA as A
LEFT JIN tableB AS B N
B.fee = A.foo
LEFT JIN tableC AS C N
C.fii = B.fee
LEFT JIN tableD AS D N
D.fuu = C.fii
WHERE
A.foo = 'someValue'
RDER BY
afield1 ASC,
cfield2 ASC
--
While the above is contrived, most of us know such examples happen
quite
often in the wild. Not only is it easier to read, but the task of
adding
or removing selected fields is trivial.
I meant NLY the SELECT part on a single line.
a moron would cram the FRM and all that into the same line.
:-)
$query = "SELECT blah1, blah2, blah3, blah147 ";
$query .= " FRM table1 ";
$query .= " LEFT UTER JIN table2 ";
$query .= " N blah7 = blah42 ";
$query .= " WHERE blah16 ";
$query .= " AND blah42 ";
$query .= " RDER BY blah9, blah8 desc, blah6 ";
is what I go for.
The SELECT line is the only one that ever gets all that long, really

tg-php (AT) gryffyndevelopment (DOT) com wrote:
My contribution to the insanity INSERT statements made easy:

can't stand long var names if they're not absolutely necessary (JM).
although I follow TG's logic here I don't find it that readable, too many
dots, [double]quotes, etc for my taste.

$genericQY = "INSERT INT MD_LMGR_Leads ("; $genericQYvalues = " VALUES (";
$genericQY .= " FirstName,"; $genericQYvalues .= " 'John',";
$genericQY .= " LastName"; $genericQYvalues .= " 'Smith'";
$genericQY .= " )"; $genericQYvalues .= " );";
$genericQY .= $genericQYvalues;
$genericRS = mysql_query($genericQY);

// init
$data = array();

// get (or make up) data
$data['FirstName'] = 'John';
$data['LastName'] = 'Smith';
//$data['Prefers'] = 'bitter';
//$data['promotedby'] = 'Jack Dee';

// build it
if (!empty($data)) {
$flds = join(',', array_keys($data));
$vals = join(',', $data);
$qry = "INSERT INT MD_LMGR_Leads ($flds) VALUES ($vals)";
} else {
die("ya think I'm stupid enough to insert nothing?");
}

// run it
$res = mysql_query($qry);

that was kind of step one of building a generic qry builder, of which there
are many :-) (and many ways to go about it)

when writing specific/custom queries I find the example below to
be the most readable way:


>>
>SELECT
>A.field1 AS afield1,
>A.field2 AS afield2,
>B.field1 AS bfield1,
>B.field2 AS bfield2,
>C.field1 AS cfield1,
>C.field2 AS cfield2,
>D.field1 AS dfield1,
>D.field2 AS dfield2
>FRM
>tableA as A
>LEFT JIN tableB AS B N
>B.fee = A.foo
>LEFT JIN tableC AS C N
>C.fii = B.fee
>LEFT JIN tableD AS D N
>D.fuu = C.fii
>WHERE
>A.foo = 'someValue'
>RDER BY
>afield1 ASC,
>cfield2 ASC
>>
>>

tg-php (AT) gryffyndevelopment (DOT) com wrote:
My contribution to the insanity INSERT statements made easy:

$genericQY = "INSERT INT MD_LMGR_Leads ("; $genericQYvalues = " VALUES (";
$genericQY .= " FirstName,"; $genericQYvalues .= " 'John',";
$genericQY .= " LastName"; $genericQYvalues .= " 'Smith'";
$genericQY .= " )"; $genericQYvalues .= " );";
$genericQY .= $genericQYvalues;
$genericRS = mysql_query($genericQY);

You call that readable?

$vals = array();
$vals['FirstName'] = 'John';
$vals['LastName'] = 'Smith';
$query = mysql_query(BuildInsert('MD_LMGR_Leads', $vals));

function BuildInsert($table, $values)
{
foreach (array_keys($values) as $key)
$values[$key] = mysql_real_escape_string($values[$key]);

$sql = 'insert into `'.$table.'` (`';
$sql.= implode('`,`', array_keys($values));
$sql.= '`) values ("';
$sql.= implode('","', array_values($values));
$sql.= '")';

return $sql;
}

Note that this is a *very* cut down and untested version of BuildInsert.
-Stut

This may not be an option for many people, 'cause ISPs and web hosts may
not be forward-thinking enough to install PD or recent PHP, but

PD can do do this in a very database independant way, without having to
do the equivalent of "mysql_real_escape_string":

$table = 'xyz';
$data = array(
'Field1' ="Data1",
'Field2' ="Data2"
);

$fields = implode(',',array_keys($data));
$placeholders = ':' . implode(',:',array_keys($data));
$stmt = $dbh->prepare("INSERT INT $table ($fields) VALUES($placeholders)");
$stmt->execute($data);

With the added bonus that you can insert multiple rows quickly without
having to rebuild any queries

$stmt->execute($data1);
$stmt->execute($data2);

$stmt->execute($dataN);

(And PD is super-fast compared to some other similar PHP-based libraries.)

jon
tg-php (AT) gryffyndevelopment (DOT) com wrote:
>My contribution to the insanity INSERT statements made easy:
>>
>$genericQY = "INSERT INT MD_LMGR_Leads ("; $genericQYvalues = "
>VALUES (";
>$genericQY .= " FirstName,"; $genericQYvalues .= "
>'John',";
>$genericQY .= " LastName"; $genericQYvalues .= "
>'Smith'";
>$genericQY .= " )"; $genericQYvalues .= "
>);";
>$genericQY .= $genericQYvalues;
>$genericRS = mysql_query($genericQY);
>
You call that readable?

$vals = array();
$vals['FirstName'] = 'John';
$vals['LastName'] = 'Smith';
$query = mysql_query(BuildInsert('MD_LMGR_Leads', $vals));

function BuildInsert($table, $values)
{
foreach (array_keys($values) as $key)
$values[$key] = mysql_real_escape_string($values[$key]);

$sql = 'insert into `'.$table.'` (`';
$sql.= implode('`,`', array_keys($values));
$sql.= '`) values ("';
$sql.= implode('","', array_values($values));
$sql.= '")';

return $sql;
}

Note that this is a *very* cut down and untested version of BuildInsert.

-Stut

Jon Anderson wrote:
This may not be an option for many people, 'cause ISPs and web hosts may
not be forward-thinking enough to install PD or recent PHP, but

PD can do do this in a very database independant way, without having to
do the equivalent of "mysql_real_escape_string":

$table = 'xyz';
$data = array(
'Field1' ="Data1",
'Field2' ="Data2"
);

$fields = implode(',',array_keys($data));
$placeholders = ':' . implode(',:',array_keys($data));
$stmt = $dbh->prepare("INSERT INT $table ($fields)
VALUES($placeholders)");
$stmt->execute($data);

With the added bonus that you can insert multiple rows quickly without
having to rebuild any queries

$stmt->execute($data1);
$stmt->execute($data2);

$stmt->execute($dataN);

(And PD is super-fast compared to some other similar PHP-based libraries.)

unless your using firebird (http://php.net/ibase), in which case PD is useless.
not that that matters because the ibase extension does this (and has done this for
longer than PD has existed) natively (as in the DB engine does the real parameter
related lifting, as opposed to some php extension - no offence to php devs but I'd
rather entrust this to the people who developed the data base engine) and additionally
the ibase extension is much more intuitive when it comes parameterized queries.

$res = ibase_query('INSERT INT foo (first, last) VALUES (?, ?)', $first, $last);

[no that wasn't very helpful was it :-P]

Message
From: "Stut" <stuttle (AT) gmail (DOT) com>
You call that readable?

$vals = array();
$vals['FirstName'] = 'John';
$vals['LastName'] = 'Smith';
$query = mysql_query(BuildInsert('MD_LMGR_Leads', $vals));

function BuildInsert($table, $values)
{
foreach (array_keys($values) as $key)
$values[$key] = mysql_real_escape_string($values[$key]);

$sql = 'insert into `'.$table.'` (`';
$sql.= implode('`,`', array_keys($values));
$sql.= '`) values ("';
$sql.= implode('","', array_values($values));
$sql.= '")';

return $sql;
}

I use to build SQL statements with a BuildSql function, which you can see
at:

It is commented in PhpDoc format.

For example:

echo BuildSql('Insert into ?ptable
(?s,?ns,?mi,?d,?ni,?i,?t)','Something','',5,time() ,0,null,mktime(3,4,5)-
mktime(0,0,0));

Will return:

Insert into wp_table ('Something',null,5,'2007-01-21 15:54:27',null,0,'0
04:04:05')

It is not only meant to build inserts but it is more like a sort of
SQL-oriented sprintf(), like it does proper handling of null values, such as
avoiding puting the text 'null' (notice the quotes) instead of the value
null. It also has a ?p 'prefix' modifier to use a fixed prefix on all table
names.

As for formatting, I usually put the SQL statement in one line and the
arguments in the next one with spaces to align them vertically, which I
won't show here since the formatting of the message will ruin it anyway.

Fri, 2007-01-26 at 16:30 +0000, Stut wrote:
tg-php (AT) gryffyndevelopment (DOT) com wrote:
My contribution to the insanity INSERT statements made easy:

$genericQY = "INSERT INT MD_LMGR_Leads ("; $genericQYvalues = " VALUES (";
$genericQY .= " FirstName,"; $genericQYvalues .= " 'John',";
$genericQY .= " LastName"; $genericQYvalues .= " 'Smith'";
$genericQY .= " )"; $genericQYvalues .= " );";
$genericQY .= $genericQYvalues;
$genericRS = mysql_query($genericQY);

You call that readable?

$vals = array();
$vals['FirstName'] = 'John';
$vals['LastName'] = 'Smith';
$query = mysql_query(BuildInsert('MD_LMGR_Leads', $vals));

Geee, you call that readable?

$vals = array
(
'FirstName' ='John',
'LastName' ='Smith',
);

$query = mysql_query( BuildInsert( 'MD_LMGR_Leads', $vals ) );

;) ;)

Cheers,
Rob.

I have long since given up on raw insert/update/delete statements as the
syntax is all kinds nasty. These days I just do this, which is even easier
and more powerful:

Friday 26 January 2007 10:03 am, tg-php (AT) gryffyndevelopment (DOT) com wrote:
My contribution to the insanity INSERT statements made easy:

$genericQY = "INSERT INT MD_LMGR_Leads ("; $genericQYvalues = " VALUES
("; $genericQY .= " FirstName,"; $genericQYvalues .= "
'John',"; $genericQY .= " LastName"; $genericQYvalues
.= " 'Smith'"; $genericQY .= " )";
$genericQYvalues .= " );"; $genericQY .= $genericQYvalues;
$genericRS = mysql_query($genericQY);
--
I use this structure so if I decide that I don't need certain data I can
comment out a single line to remove the column name and corresponding
value. Also helpful for making updates to column/value pairs and not worry
about the dreaded error involve # of columns not matching.

things you have to watch for:

1. Make sure you don't have a comma on the last item
2. Make sure you have spaces where appropriate so when it concatenates the
strings, you don't get stuff crammed together (not really an issue with the
INSERT statement, but I try to keep a consistant practice with all my
queries so I don't slip up SELECT columnsFRM tableWHERE something =
something is where it really gets ya if you forget spaces just as an
example) 3. Make sure to remember to concatenate the "query" and "values"
parts

I like to think this is a little "outside the box" thinking since common
practice is "one command, one line" or "total chaos" hah.

Any comments on improving this or other unique stylistic ways people like
to design their code?

-TG
--
= = = message = = =

Wed, January 24, 2007 8:07 pm, Robert Cummings wrote:
Wed, 2007-01-24 at 18:23 -0600, Richard Lynch wrote:
>Wed, January 24, 2007 7:41 am, Roman Neuhauser wrote:
># sancar.saran (AT) evodot (DOT) com / 2007-01-24 13:57:03 +0200:
>>and also in these days I'm looking for 19 inch (or more) wide LCD
>>sceerns to able to fit longer lines in my screen
>>
>Number of reading errors people make grows with line length,
>this has been known for as long as I remember. You're increasing
>>
>the
>>
>probability of bugs in the code, and get tired sooner because
>following
>long lines requires more energy.
>>
>I believe those results are specific to what is being read.
>>
>Surely it's easier to read:
>>
>SELECT blah, blah, blah, blah, blah, blah, blah, blah, blah
>>
>if it's all on one line, no matter how many fields there are, while
>trying to read the code as a whole.
>>
>Sure, it can be "hard" to find/read the individual field names, on
>the
>rare occasion that you need to do that
>
Dear Mr Lynch, normally I highly respect your commentary on the list,
but today I think you've been-a-smoking the crackpipe a tad too much.

There is no way in hell one long line of SQL is easier to read than
formatted SQL that clearly delineates the clause structure.

SELECT A.field1 AS afield1, A.field2 AS afield2, B.field1 AS bfield1,
B.field2 AS bfield2, C.field1 AS cfield1, C.field2 AS cfield2,
D.field1
AS dfield1, D.field2 AS dfield2 FRM tableA as A LEFT JIN tableB AS B
N B.fee = A.foo LEFT JIN tableC AS C N C.fii = B.fee LEFT JIN
tableD
AS D N D.fuu = C.fii WHERE A.foo = 'someValue' RDER BY afield1 ASC,
cfield2 ASC

The above line "should" be on one line, but my email client might
autowrap it. Either way, the following is formatted and is much
clearer.

SELECT
A.field1 AS afield1,
A.field2 AS afield2,
B.field1 AS bfield1,
B.field2 AS bfield2,
C.field1 AS cfield1,
C.field2 AS cfield2,
D.field1 AS dfield1,
D.field2 AS dfield2
FRM
tableA as A
LEFT JIN tableB AS B N
B.fee = A.foo
LEFT JIN tableC AS C N
C.fii = B.fee
LEFT JIN tableD AS D N
D.fuu = C.fii
WHERE
A.foo = 'someValue'
RDER BY
afield1 ASC,
cfield2 ASC
--
While the above is contrived, most of us know such examples happen
quite
often in the wild. Not only is it easier to read, but the task of
adding
or removing selected fields is trivial.

I meant NLY the SELECT part on a single line.

a moron would cram the FRM and all that into the same line.

:-)

$query = "SELECT blah1, blah2, blah3, blah147 ";
$query .= " FRM table1 ";
$query .= " LEFT UTER JIN table2 ";
$query .= " N blah7 = blah42 ";
$query .= " WHERE blah16 ";
$query .= " AND blah42 ";
$query .= " RDER BY blah9, blah8 desc, blah6 ";

is what I go for.

The SELECT line is the only one that ever gets all that long, really

Message
From: "Larry Garfield" <larry (AT) garfieldtech (DOT) com>
To: <php-general (AT) lists (DOT) php.net>
Sent: Saturday, January 27, 2007 12:18 AM
Subject: Re: [PHP] SQL Readability (was Re: most powerful php editor)


>I have long since given up on raw insert/update/delete statements as the
syntax is all kinds nasty. These days I just do this, which is even
easier
and more powerful:

I tried the following:

insert('sometable',array('when' =mktime(0,0,0,2,1,2007),'if' =true));

which produced the following SQL statement:

INSERT INT sometable (when) VALUES (1170284400,1170284400)

The problem is that PHP types do not correspond to SQL types. Though a
boolean does identify itself as such, a date never does. Your switch() on
the gettype() of the value misses the type 'boolean' so it falls through the
default: case which then appends whatever was left from the previous pass.
However, even adding a case for type boolean there is no way to recognize
dates since they are no more than integers for all PHP cares. Finally, what
happens with an expression that produces a sort-of boolean, like anything
non-zero for true?

Those are the reasons I used type modifiers in my BuildSql function
(), I couldn't rely on PHP figuring
them out correctly. This also allowed me to expand those modifiers to
optional positional modifiers and null handling ones.

I even tried to query the SQL engine to report them back, but that was also
unreliable, MySql for one, reports the type of what it used to store it, not
what you declared them to be. Thus, for a boolean field it will report
integer, but if you try to store a number other than 0 or 1 it then
complains. So, unable to get reliable information from either end, I
decided on stating the type explicitly on the query string.

Satyam

Friday 26 January 2007 10:03 am, tg-php (AT) gryffyndevelopment (DOT) com wrote:
>My contribution to the insanity INSERT statements made easy:
>>
>$genericQY = "INSERT INT MD_LMGR_Leads ("; $genericQYvalues = "
>VALUES
>("; $genericQY .= " FirstName,"; $genericQYvalues .= "
>'John',"; $genericQY .= " LastName"; $genericQYvalues
>.= " 'Smith'"; $genericQY .= " )";
>$genericQYvalues .= " );"; $genericQY .= $genericQYvalues;
>$genericRS = mysql_query($genericQY);
>>
>>
>I use this structure so if I decide that I don't need certain data I can
>comment out a single line to remove the column name and corresponding
>value. Also helpful for making updates to column/value pairs and not
>worry
>about the dreaded error involve # of columns not matching.
>>
>things you have to watch for:
>>
>1. Make sure you don't have a comma on the last item
>2. Make sure you have spaces where appropriate so when it concatenates
>the
>strings, you don't get stuff crammed together (not really an issue with
>the
>INSERT statement, but I try to keep a consistant practice with all my
>queries so I don't slip up SELECT columnsFRM tableWHERE something =
>something is where it really gets ya if you forget spaces just as an
>example) 3. Make sure to remember to concatenate the "query" and "values"
>parts
>>
>I like to think this is a little "outside the box" thinking since common
>practice is "one command, one line" or "total chaos" hah.
>>
>Any comments on improving this or other unique stylistic ways people like
>to design their code?
>>
>-TG
>>
>>
>= = = message = = =
>>
>Wed, January 24, 2007 8:07 pm, Robert Cummings wrote:
>Wed, 2007-01-24 at 18:23 -0600, Richard Lynch wrote:
>>Wed, January 24, 2007 7:41 am, Roman Neuhauser wrote:
>># sancar.saran (AT) evodot (DOT) com / 2007-01-24 13:57:03 +0200:
>>>and also in these days I'm looking for 19 inch (or more) wide LCD
>>>sceerns to able to fit longer lines in my screen
>>>
>>Number of reading errors people make grows with line length,
>>this has been known for as long as I remember. You're increasing
>>>
>>the
>>>
>>probability of bugs in the code, and get tired sooner because
>>following
>>long lines requires more energy.
>>>
>>I believe those results are specific to what is being read.
>>>
>>Surely it's easier to read:
>>>
>>SELECT blah, blah, blah, blah, blah, blah, blah, blah, blah
>>>
>>if it's all on one line, no matter how many fields there are, while
>>trying to read the code as a whole.
>>>
>>Sure, it can be "hard" to find/read the individual field names, on
>>the
>>rare occasion that you need to do that
>>
>Dear Mr Lynch, normally I highly respect your commentary on the list,
>but today I think you've been-a-smoking the crackpipe a tad too much.
>>
>There is no way in hell one long line of SQL is easier to read than
>formatted SQL that clearly delineates the clause structure.
>>
>SELECT A.field1 AS afield1, A.field2 AS afield2, B.field1 AS bfield1,
>B.field2 AS bfield2, C.field1 AS cfield1, C.field2 AS cfield2,
>D.field1
>AS dfield1, D.field2 AS dfield2 FRM tableA as A LEFT JIN tableB AS B
>N B.fee = A.foo LEFT JIN tableC AS C N C.fii = B.fee LEFT JIN
>tableD
>AS D N D.fuu = C.fii WHERE A.foo = 'someValue' RDER BY afield1 ASC,
>cfield2 ASC
>>
>The above line "should" be on one line, but my email client might
>autowrap it. Either way, the following is formatted and is much
>clearer.
>>
>SELECT
>A.field1 AS afield1,
>A.field2 AS afield2,
>B.field1 AS bfield1,
>B.field2 AS bfield2,
>C.field1 AS cfield1,
>C.field2 AS cfield2,
>D.field1 AS dfield1,
>D.field2 AS dfield2
>FRM
>tableA as A
>LEFT JIN tableB AS B N
>B.fee = A.foo
>LEFT JIN tableC AS C N
>C.fii = B.fee
>LEFT JIN tableD AS D N
>D.fuu = C.fii
>WHERE
>A.foo = 'someValue'
>RDER BY
>afield1 ASC,
>cfield2 ASC
>>
>>
>While the above is contrived, most of us know such examples happen
>quite
>often in the wild. Not only is it easier to read, but the task of
>adding
>or removing selected fields is trivial.
>>
>I meant NLY the SELECT part on a single line.
>>
>a moron would cram the FRM and all that into the same line.
>>
>:-)
>>
>$query = "SELECT blah1, blah2, blah3, blah147 ";
>$query .= " FRM table1 ";
>$query .= " LEFT UTER JIN table2 ";
>$query .= " N blah7 = blah42 ";
>$query .= " WHERE blah16 ";
>$query .= " AND blah42 ";
>$query .= " RDER BY blah9, blah8 desc, blah6 ";
>>
>is what I go for.
>>
>The SELECT line is the only one that ever gets all that long, really
>>
>--
>Some people have a "gift" link here.
>Know what I want?
>I want you to buy a CD from some starving artist.
>
>Yeah, I get a buck. So?
>>
>
>Sent by ePrompter, the premier email notification software.
>Free download at http://www.ePrompter.com.
>

Larry Garfield wrote:
I have long since given up on raw insert/update/delete statements as the
syntax is all kinds nasty. These days I just do this, which is even easier
and more powerful:

a quick look at those funcs gives me the impression that they are woefully
inadequate for any level of complex realworld use.

query builders are alot more fiddly to get 'right' than one might imagine,
dealing with NULLs, booleans and dates for example (as Satyam pointed out)
can be a right PITA.

perfect automated CRUD (it's an acronym!) is kind a holy grail - and
that is, I think, the driving force behind most attempts to crteate query builders.

also I don't really agree with the sentiment that SQL syntax is nasty,
personally I find it, mostly, very easy to read and powerful but as this
thread shows there is no accounting for taste! :-)

Sat, 2007-01-27 at 14:43 +0100, Jochem Maas wrote:
also I don't really agree with the sentiment that SQL syntax is nasty,
Hear, hear :)
Cheers,
Rob.

Saturday 27 January 2007 7:43 am, Jochem Maas wrote:
Larry Garfield wrote:
I have long since given up on raw insert/update/delete statements as the
syntax is all kinds nasty. These days I just do this, which is even
easier and more powerful:

a quick look at those funcs gives me the impression that they are woefully
inadequate for any level of complex realworld use.

That's interesting, because I've been using variants of that for a year now
with much success in a dozen projects.

query builders are alot more fiddly to get 'right' than one might imagine,
dealing with NULLs, booleans and dates for example (as Satyam pointed out)
can be a right PITA.

I actually almost never use native date types in the SQL database. I just
store unix timestamps and do the math in PHP. Dates are completely
unportable anyway. I also tend to use ints for booleans, too, although
beefing up the switch statements in the code to handle native booleans should
be trivial.

perfect automated CRUD (it's an acronym!) is kind a holy grail - and
that is, I think, the driving force behind most attempts to crteate query
builders.

persistence is, yes. The goal here was simply to make dealing with
arbitrary insert and update statements easier, which in practice I've found
to be a huge success. Full arbitrary CRUD and orthogonal persistence is much
harder. That's why there's a dozen RMs out there, all of which have some
major flaw. :-)

also I don't really agree with the sentiment that SQL syntax is nasty,
personally I find it, mostly, very easy to read and powerful but as
this thread shows there is no accounting for taste! :-)

What bugs me most about SQL syntax is INSERT vs. UPDATE. I don't know the
underlying implementation details of the engine, but from the level I work at
(sending SQL to a database from a web app) I see no legitimate reason why
those two very-similar statements should have ridiculously different syntax.

Larry Garfield wrote:
Saturday 27 January 2007 7:43 am, Jochem Maas wrote:
>Larry Garfield wrote:
I have long since given up on raw insert/update/delete statements as the
syntax is all kinds nasty. These days I just do this, which is even
easier and more powerful:


>a quick look at those funcs gives me the impression that they are woefully
>inadequate for any level of complex realworld use.

That's interesting, because I've been using variants of that for a year now
with much success in a dozen projects.

I was nitpicking - I'm quite sure they are useful within the bounds of the
intended scope and wielded by a pair of hands that knows the code intimately
(including any limitations).

I run plenty of stuff that falls in the same category :-)


>query builders are alot more fiddly to get 'right' than one might imagine,
>dealing with NULLs, booleans and dates for example (as Satyam pointed out)
>can be a right PITA.

I actually almost never use native date types in the SQL database. I just
store unix timestamps and do the math in PHP. Dates are completely
unportable anyway. I also tend to use ints for booleans, too, although
beefing up the switch statements in the code to handle native booleans should
be trivial.

mysql doesn't have booleans does it? at least not versions I have to use.
with regard to date stuff, many people take the opposite approach and do most of
the date math inside SQL - most DBs have kickass date calculation functions btw.

and for the times when you need/want unix timestamps, mysql atleast, gives you
UNIX_TIMSTAMP().

(just some loose thoughts)


>perfect automated CRUD (it's an acronym!) is kind a holy grail - and
>that is, I think, the driving force behind most attempts to crteate query
>builders.

persistence is, yes. The goal here was simply to make dealing with
arbitrary insert and update statements easier, which in practice I've found
to be a huge success. Full arbitrary CRUD and orthogonal persistence is much
harder. That's why there's a dozen RMs out there, all of which have some
major flaw. :-)

including mine :-) (not released because it, well, needs a big manual that
only exists in my head - besides is firebird/ibase specific and I'm one of
about 5 people who actually use php+firebird :-)


>also I don't really agree with the sentiment that SQL syntax is nasty,
>personally I find it, mostly, very easy to read and powerful but as
>this thread shows there is no accounting for taste! :-)

What bugs me most about SQL syntax is INSERT vs. UPDATE. I don't know the
underlying implementation details of the engine, but from the level I work at
(sending SQL to a database from a web app) I see no legitimate reason why
those two very-similar statements should have ridiculously different syntax.

granted it's not perfect, somebody made a design 'fault' way back when and we're
stuck with it. maybe someone else has some real info about why this is so.

Saturday 27 January 2007 1:14 pm, Jochem Maas wrote:

>query builders are alot more fiddly to get 'right' than one might
>imagine, dealing with NULLs, booleans and dates for example (as Satyam
>pointed out) can be a right PITA.
>
I actually almost never use native date types in the SQL database. I
just store unix timestamps and do the math in PHP. Dates are completely
unportable anyway. I also tend to use ints for booleans, too, although
beefing up the switch statements in the code to handle native booleans
should be trivial.

mysql doesn't have booleans does it? at least not versions I have to use.
with regard to date stuff, many people take the opposite approach and do
most of the date math inside SQL - most DBs have kickass date calculation
functions btw.

and for the times when you need/want unix timestamps, mysql atleast, gives
you UNIX_TIMSTAMP().

At least as of MySQL 4.1 (haven't played with MySQL 5 much yet), yes, MySQL
has no native boolean data type that I know of. The standard alternative is
TINYINT(1), which technically gives you values 0-9.

And yes, I agree that MySQL has fairly decent date manipulation routines. But
at work we do try for database independence when possible, so except on
specific projects we try to avoid it.

Larry Garfield wrote:
Saturday 27 January 2007 1:14 pm, Jochem Maas wrote:

query builders are alot more fiddly to get 'right' than one might
imagine, dealing with NULLs, booleans and dates for example (as Satyam
pointed out) can be a right PITA.
I actually almost never use native date types in the SQL database. I
just store unix timestamps and do the math in PHP. Dates are completely
unportable anyway. I also tend to use ints for booleans, too, although
beefing up the switch statements in the code to handle native booleans
should be trivial.
>mysql doesn't have booleans does it? at least not versions I have to use.
>with regard to date stuff, many people take the opposite approach and do
>most of the date math inside SQL - most DBs have kickass date calculation
>functions btw.
>>
>and for the times when you need/want unix timestamps, mysql atleast, gives
>you UNIX_TIMSTAMP().

At least as of MySQL 4.1 (haven't played with MySQL 5 much yet), yes, MySQL
has no native boolean data type that I know of. The standard alternative is
TINYINT(1), which technically gives you values 0-9.

And yes, I agree that MySQL has fairly decent date manipulation routines. But
at work we do try for database independence when possible, so except on
specific projects we try to avoid it.

again we differ :-) I have never bought the 'data independence' story - in practice
it's of little value imho most of the time (granted certain products do benefit - but
what I build doesn't fall into that category) and I find it crazy to end up with
a situation where the most advanced peice of data manipulation software in a given stack
is dumbed down to the lowest common denominator [of DB engines]. more complex project
I try to cram as much of the data intregity and business logic in to the database itself
(for which I use firebird mostly) because it means being able to create different clients
to the data without replicating [as much] business logic (e.g. website and desktop app).
besides which the required stored procedures and triggers are usually hundreds of lines less
than their php equivalent AND more importantly they are intrinsically atomic (in the sense that
database transaction 'should' be).

rgds :-)

Message
From: "Jochem Maas" <jochem (AT) iamjochem (DOT) com>
To: "Larry Garfield" <larry (AT) garfieldtech (DOT) com>
Cc: <php-general (AT) lists (DOT) php.net>
Sent: Sunday, January 28, 2007 12:55 PM
Subject: Re: [PHP] SQL Readability (was Re: most powerful php editor)

Larry Garfield wrote:
>Saturday 27 January 2007 1:14 pm, Jochem Maas wrote:
>>
query builders are alot more fiddly to get 'right' than one might
imagine, dealing with NULLs, booleans and dates for example (as Satyam
pointed out) can be a right PITA.
I actually almost never use native date types in the SQL database. I
just store unix timestamps and do the math in PHP. Dates are
completely
unportable anyway. I also tend to use ints for booleans, too, although
beefing up the switch statements in the code to handle native booleans
should be trivial.
mysql doesn't have booleans does it? at least not versions I have to
use.
with regard to date stuff, many people take the opposite approach and do
most of the date math inside SQL - most DBs have kickass date
calculation
functions btw.

and for the times when you need/want unix timestamps, mysql atleast,
gives
you UNIX_TIMSTAMP().
>>
>At least as of MySQL 4.1 (haven't played with MySQL 5 much yet), yes,
>MySQL
>has no native boolean data type that I know of. The standard alternative
>is
>TINYINT(1), which technically gives you values 0-9.
>>
>And yes, I agree that MySQL has fairly decent date manipulation routines.
>But
>at work we do try for database independence when possible, so except on
>specific projects we try to avoid it.
>
again we differ :-) I have never bought the 'data independence' story - in
practice
it's of little value imho most of the time (granted certain products do
benefit - but
what I build doesn't fall into that category) and I find it crazy to end
up with
a situation where the most advanced peice of data manipulation software in
a given stack
is dumbed down to the lowest common denominator [of DB engines]. more
complex project
I try to cram as much of the data intregity and business logic in to the
database itself
(for which I use firebird mostly) because it means being able to create
different clients
to the data without replicating [as much] business logic (e.g. website and
desktop app).
besides which the required stored procedures and triggers are usually
hundreds of lines less
than their php equivalent AND more importantly they are intrinsically
atomic (in the sense that
database transaction 'should' be).

rgds :-)

Hear!, hear! (or something to that effect)

Satyam


>>
>

Sunday 28 January 2007 5:55 am, Jochem Maas wrote:

And yes, I agree that MySQL has fairly decent date manipulation routines.
But at work we do try for database independence when possible, so except
on specific projects we try to avoid it.

again we differ :-) I have never bought the 'data independence' story - in
practice it's of little value imho most of the time (granted certain
products do benefit - but what I build doesn't fall into that category) and
I find it crazy to end up with a situation where the most advanced peice of
data manipulation software in a given stack is dumbed down to the lowest
common denominator [of DB engines]. more complex project I try to cram
as much of the data intregity and business logic in to the database itself
(for which I use firebird mostly) because it means being able to create
different clients to the data without replicating [as much] business logic
(e.g. website and desktop app). besides which the required stored
procedures and triggers are usually hundreds of lines less than their php
equivalent AND more importantly they are intrinsically atomic (in the sense
that database transaction 'should' be).

rgds :-)

Well, business reasons dictate that we keep our code portable when possible at
work. I'm not the business person. I just write the code. :-)