I am new to PHP/MySQL. I would like some of your thoughts on when to
use php code and when to use mysql queries.

In the case I am working on, a learning project for myself, I am writing
a contact management application. Basically a phone book with a many
to many relationship between individuals and organizations, and one to
many between individuals/organizations and the relevant data such as
phone numbers.

I am working on a page which lists all the individuals. I have a
horizontal listing at the top containing all the unique surname
initials, which link to headings as the list progresses. ie:

-----
A B N

A
Abnew, Georgina
Adwit, Dave

B
Biltmore, Garth

N
Niles, Paulina
Norton, Alicia
-----

Currently, I have MySQL select all the unique initials and process that
into an array. The array is dumped to the screen as the index at the
top. Next the array is used for MySQL selects of surnames with that
initial.

In the above example that would result in 6 MySQL queries. Assuming
only English based names, no foreign character sets, no numbers, no
symbols, that would result in a maximum of 27 queries.

I could rewrite the PHP code to select all the names in a single query .
Then use PHP to process the list to make the index, headings, and list
the names.

For a small private phone book, either way, so what - big deal. In a
public database, there could conceivably be a large amount of network
overhead processing the Mysql queries. However, from the reading I have
been doing, MySQL is very fast, PHP being interpreted, may end up being
slower.

So, anyone care to enlighten me? I would love your thoughts, comments,
experiences.

Thanks, Carolyn

[Carolyn's post left intact and comp.lang.php added]

On Fri, 18 Jan 2008 09:27:47 -0500, Carolyn Marenger
<cajunk@marenger.com> wrote:

>I am new to PHP/MySQL. I would like some of your thoughts on when to
>use php code and when to use mysql queries.
>
>In the case I am working on, a learning project for myself, I am writing
>a contact management application. Basically a phone book with a many
>to many relationship between individuals and organizations, and one to
>many between individuals/organizations and the relevant data such as
>phone numbers.
>
>I am working on a page which lists all the individuals. I have a
>horizontal listing at the top containing all the unique surname
>initials, which link to headings as the list progresses. ie:
>
>-----
>A B N
>
>A
> Abnew, Georgina
> Adwit, Dave
>
>B
> Biltmore, Garth
>
>N
> Niles, Paulina
> Norton, Alicia
>-----
>
>Currently, I have MySQL select all the unique initials and process that
>into an array. The array is dumped to the screen as the index at the
>top. Next the array is used for MySQL selects of surnames with that
>initial.
>
>In the above example that would result in 6 MySQL queries. Assuming
>only English based names, no foreign character sets, no numbers, no
>symbols, that would result in a maximum of 27 queries.
>
>I could rewrite the PHP code to select all the names in a single query .
> Then use PHP to process the list to make the index, headings, and list
>the names.
>
>For a small private phone book, either way, so what - big deal. In a
>public database, there could conceivably be a large amount of network
>overhead processing the Mysql queries. However, from the reading I have
>been doing, MySQL is very fast, PHP being interpreted, may end up being
>slower.
>
>So, anyone care to enlighten me? I would love your thoughts, comments,
>experiences.
>

I've added the crosspost because I believe this is an extremely good
question that doesn't get the attention it deserves. You should get a
lot of opinions on this.

Here's mine:

One query to sql is better than 6 and it's certainly better than 27.
PHP could process the list quite quickly. If your DB is on a
different server than your web site (good practice to keep your DB
behind a firewall>, then you're crossing your network up to 27 times
instead of just once.


--
gburnore at DataBasix dot Com
---------------------------------------------------------------------------
How you look depends on where you go.
---------------------------------------------------------------------------
Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
| ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
Official .sig, Accept no substitutes. | ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
| ÝÛ 0 1 7 2 3 / Ý³Þ 3 7 4 9 3 0 Û³
Black Helicopter Repair Services, Ltd.| Official Proof of Purchase
================================================== =========================

Gary L. Burnore wrote:
> [Carolyn's post left intact and comp.lang.php added]
>
> On Fri, 18 Jan 2008 09:27:47 -0500, Carolyn Marenger
> <cajunk@marenger.com> wrote:
>
>> I am new to PHP/MySQL. I would like some of your thoughts on when to
>> use php code and when to use mysql queries.
>>
>> In the case I am working on, a learning project for myself, I am writing
>> a contact management application. Basically a phone book with a many
>> to many relationship between individuals and organizations, and one to
>> many between individuals/organizations and the relevant data such as
>> phone numbers.
>>
>> I am working on a page which lists all the individuals. I have a
>> horizontal listing at the top containing all the unique surname
>> initials, which link to headings as the list progresses. ie:
>>
>> -----
>> A B N
>>
>> A
>> Abnew, Georgina
>> Adwit, Dave
>>
>> B
>> Biltmore, Garth
>>
>> N
>> Niles, Paulina
>> Norton, Alicia
>> -----
>>
>> Currently, I have MySQL select all the unique initials and process that
>> into an array. The array is dumped to the screen as the index at the
>> top. Next the array is used for MySQL selects of surnames with that
>> initial.
>>
>> In the above example that would result in 6 MySQL queries. Assuming
>> only English based names, no foreign character sets, no numbers, no
>> symbols, that would result in a maximum of 27 queries.
>>
>> I could rewrite the PHP code to select all the names in a single query .
>> Then use PHP to process the list to make the index, headings, and list
>> the names.
>>
>> For a small private phone book, either way, so what - big deal. In a
>> public database, there could conceivably be a large amount of network
>> overhead processing the Mysql queries. However, from the reading I have
>> been doing, MySQL is very fast, PHP being interpreted, may end up being
>> slower.
>>
>> So, anyone care to enlighten me? I would love your thoughts, comments,
>> experiences.
>>
>
> I've added the crosspost because I believe this is an extremely good
> question that doesn't get the attention it deserves. You should get a
> lot of opinions on this.
>
> Here's mine:
>
> One query to sql is better than 6 and it's certainly better than 27.
> PHP could process the list quite quickly. If your DB is on a
> different server than your web site (good practice to keep your DB
> behind a firewall>, then you're crossing your network up to 27 times
> instead of just once.
>
>
I've done this.

Itseasierto do one big query, sort by whatever, iterate through the
array and write a new header everytime the name changes

e.g. select surname,... from mytable, order by name gets the lot in teh
write order, and then a simple loop with something like posting a
varablee for the current first letter, and if it DIOES'T match the
initial of the name you are about to print, executing a bit of code that
prints a new header, aand set it to the current first name.

PHP may be slow, but not as slow as the overhead on an SQL call with the
attendant file system shuffling.

On Fri, 18 Jan 2008 15:35:24 +0100, Gary L. Burnore
<gburnore@databasix.com> wrote:

> [Carolyn's post left intact and comp.lang.php added]
>
> On Fri, 18 Jan 2008 09:27:47 -0500, Carolyn Marenger
> <cajunk@marenger.com> wrote:
>
>> I am new to PHP/MySQL. I would like some of your thoughts on when to
>> use php code and when to use mysql queries.
>>
>> In the case I am working on, a learning project for myself, I am writing
>> a contact management application. Basically a phone book with a many
>> to many relationship between individuals and organizations, and one to
>> many between individuals/organizations and the relevant data such as
>> phone numbers.
>>
>> I am working on a page which lists all the individuals. I have a
>> horizontal listing at the top containing all the unique surname
>> initials, which link to headings as the list progresses. ie:
>>
>> -----
>> A B N
>>
>> A
>> Abnew, Georgina
>> Adwit, Dave
>>
>> B
>> Biltmore, Garth
>>
>> N
>> Niles, Paulina
>> Norton, Alicia
>> -----
>>
>> Currently, I have MySQL select all the unique initials and process that
>> into an array. The array is dumped to the screen as the index at the
>> top. Next the array is used for MySQL selects of surnames with that
>> initial.
>>
>> In the above example that would result in 6 MySQL queries. Assuming
>> only English based names, no foreign character sets, no numbers, no
>> symbols, that would result in a maximum of 27 queries.
>>
>> I could rewrite the PHP code to select all the names in a single query .
>> Then use PHP to process the list to make the index, headings, and list
>> the names.
>>
>> For a small private phone book, either way, so what - big deal. In a
>> public database, there could conceivably be a large amount of network
>> overhead processing the Mysql queries. However, from the reading I have
>> been doing, MySQL is very fast, PHP being interpreted, may end up being
>> slower.
>>
>> So, anyone care to enlighten me? I would love your thoughts, comments,
>> experiences.
>>
>
> I've added the crosspost because I believe this is an extremely good
> question that doesn't get the attention it deserves. You should get a
> lot of opinions on this.
>
> Here's mine:
>
> One query to sql is better than 6 and it's certainly better than 27.
> PHP could process the list quite quickly. If your DB is on a
> different server than your web site (good practice to keep your DB
> behind a firewall>, then you're crossing your network up to 27 times
> instead of just once.

With a limited amount of users/entries in the database, you're going to
display them all anyway, So query the lot. A simple count query could tell
you which one you are going to use, a SELECT DISTINCT
SUBSTRING(fieldname,1,1) FROM tablename; (or group by) could tell you all
starting letters. Then depending on what you want query all entries, or
just by starting letter. Seems like 3 queries either case to me.
--
Rik Wasmus

Rik Wasmus wrote:
> On Fri, 18 Jan 2008 15:35:24 +0100, Gary L. Burnore
> <gburnore@databasix.com> wrote:
>
>> [Carolyn's post left intact and comp.lang.php added]
>>
>> On Fri, 18 Jan 2008 09:27:47 -0500, Carolyn Marenger
>> <cajunk@marenger.com> wrote:
>>
>>> I am new to PHP/MySQL. I would like some of your thoughts on when to
>>> use php code and when to use mysql queries.
>>>
>>> In the case I am working on, a learning project for myself, I am writing
>>> a contact management application. Basically a phone book with a many
>>> to many relationship between individuals and organizations, and one to
>>> many between individuals/organizations and the relevant data such as
>>> phone numbers.
>>>
>>> I am working on a page which lists all the individuals. I have a
>>> horizontal listing at the top containing all the unique surname
>>> initials, which link to headings as the list progresses. ie:
>>>
>>> -----
>>> A B N
>>>
>>> A
>>> Abnew, Georgina
>>> Adwit, Dave
>>>
>>> B
>>> Biltmore, Garth
>>>
>>> N
>>> Niles, Paulina
>>> Norton, Alicia
>>> -----
>>>
>>> Currently, I have MySQL select all the unique initials and process that
>>> into an array. The array is dumped to the screen as the index at the
>>> top. Next the array is used for MySQL selects of surnames with that
>>> initial.
>>>
>>> In the above example that would result in 6 MySQL queries. Assuming
>>> only English based names, no foreign character sets, no numbers, no
>>> symbols, that would result in a maximum of 27 queries.
>>>
>>> I could rewrite the PHP code to select all the names in a single query .
>>> Then use PHP to process the list to make the index, headings, and list
>>> the names.
>>>
>>> For a small private phone book, either way, so what - big deal. In a
>>> public database, there could conceivably be a large amount of network
>>> overhead processing the Mysql queries. However, from the reading I have
>>> been doing, MySQL is very fast, PHP being interpreted, may end up being
>>> slower.
>>>
>>> So, anyone care to enlighten me? I would love your thoughts, comments,
>>> experiences.
>>>
>>
>> I've added the crosspost because I believe this is an extremely good
>> question that doesn't get the attention it deserves. You should get a
>> lot of opinions on this.
>>
>> Here's mine:
>>
>> One query to sql is better than 6 and it's certainly better than 27.
>> PHP could process the list quite quickly. If your DB is on a
>> different server than your web site (good practice to keep your DB
>> behind a firewall>, then you're crossing your network up to 27 times
>> instead of just once.
>
> With a limited amount of users/entries in the database, you're going to
> display them all anyway, So query the lot. A simple count query could
> tell you which one you are going to use, a SELECT DISTINCT
> SUBSTRING(fieldname,1,1) FROM tablename; (or group by) could tell you
> all starting letters. Then depending on what you want query all entries,
> or just by starting letter. Seems like 3 queries either case to me.
> --Rik Wasmus

Setting the mysql_query_cache would also speed things up since you're
calling the whole lot. Even after additions the result set will be
cached after the first call (SELECT) and MySQL won't need to go to disk.



--
Norman
Registered Linux user #461062

Gary L. Burnore wrote:
> [Carolyn's post left intact and comp.lang.php added]
>
> On Fri, 18 Jan 2008 09:27:47 -0500, Carolyn Marenger
> <cajunk@marenger.com> wrote:
>
>> I am new to PHP/MySQL. I would like some of your thoughts on when to
>> use php code and when to use mysql queries.
>>
>> In the case I am working on, a learning project for myself, I am writing
>> a contact management application. Basically a phone book with a many
>> to many relationship between individuals and organizations, and one to
>> many between individuals/organizations and the relevant data such as
>> phone numbers.
>>
>> I am working on a page which lists all the individuals. I have a
>> horizontal listing at the top containing all the unique surname
>> initials, which link to headings as the list progresses. ie:
>>
>> -----
>> A B N
>>
>> A
>> Abnew, Georgina
>> Adwit, Dave
>>
>> B
>> Biltmore, Garth
>>
>> N
>> Niles, Paulina
>> Norton, Alicia
>> -----
>>
>> Currently, I have MySQL select all the unique initials and process that
>> into an array. The array is dumped to the screen as the index at the
>> top. Next the array is used for MySQL selects of surnames with that
>> initial.
>>
>> In the above example that would result in 6 MySQL queries. Assuming
>> only English based names, no foreign character sets, no numbers, no
>> symbols, that would result in a maximum of 27 queries.
>>
>> I could rewrite the PHP code to select all the names in a single query .
>> Then use PHP to process the list to make the index, headings, and list
>> the names.
>>
>> For a small private phone book, either way, so what - big deal. In a
>> public database, there could conceivably be a large amount of network
>> overhead processing the Mysql queries. However, from the reading I have
>> been doing, MySQL is very fast, PHP being interpreted, may end up being
>> slower.
>>
>> So, anyone care to enlighten me? I would love your thoughts, comments,
>> experiences.
>>
>
> I've added the crosspost because I believe this is an extremely good
> question that doesn't get the attention it deserves. You should get a
> lot of opinions on this.
>
> Here's mine:
>
> One query to sql is better than 6 and it's certainly better than 27.
> PHP could process the list quite quickly. If your DB is on a
> different server than your web site (good practice to keep your DB
> behind a firewall>, then you're crossing your network up to 27 times
> instead of just once.
>
>

I would agree that this may work in this one situation, however, when
those datasets get too large - with large numbers of request for that
data, you can do more harm than good.

I recently fixed some code that was causing over 400MB per request to be
returned for processing - and literally crippled an IBM mainframe. to
the point that once we shut off the web app - it took the frame more
than 7 hours to catch up on it's batch processing. One of those - let's
let the application process the data. Again, in a very small number of
situations this **may** be okay, in others, it can be crippling.

Things to remember:
1) KNOW YOUR DATA - what it is and how much you have.
2) KNOW YOUR "customer" - how many, how they might use the data.
3) Your "customer" will ALWAYS use your application in ways you never
dreamed of...

Be careful what you ask for - you might just get it...

Michael Austin.

On Fri, 18 Jan 2008 20:46:01 +0100, Michael Austin
<maustin@firstdbasource.com> wrote:

> Gary L. Burnore wrote:
>> [Carolyn's post left intact and comp.lang.php added]
>> On Fri, 18 Jan 2008 09:27:47 -0500, Carolyn Marenger
>> <cajunk@marenger.com> wrote:
>>
>>> I am new to PHP/MySQL. I would like some of your thoughts on when to
>>> use php code and when to use mysql queries.
>>>
>>> In the case I am working on, a learning project for myself, I am
>>> writing a contact management application. Basically a phone book
>>> with a many to many relationship between individuals and
>>> organizations, and one to many between individuals/organizations and
>>> the relevant data such as phone numbers.
>>>
>>> I am working on a page which lists all the individuals. I have a
>>> horizontal listing at the top containing all the unique surname
>>> initials, which link to headings as the list progresses. ie:
>>>
>>> -----
>>> A B N
>>>
>>> A
>>> Abnew, Georgina
>>> Adwit, Dave
>>>
>>> B
>>> Biltmore, Garth
>>>
>>> N
>>> Niles, Paulina
>>> Norton, Alicia
>>> -----
>>>
>>> Currently, I have MySQL select all the unique initials and process
>>> that into an array. The array is dumped to the screen as the index at
>>> the top. Next the array is used for MySQL selects of surnames with
>>> that initial.
>>>
>>> In the above example that would result in 6 MySQL queries. Assuming
>>> only English based names, no foreign character sets, no numbers, no
>>> symbols, that would result in a maximum of 27 queries.
>>>
>>> I could rewrite the PHP code to select all the names in a single query
>>> . Then use PHP to process the list to make the index, headings, and
>>> list the names.
>>>
>>> For a small private phone book, either way, so what - big deal. In a
>>> public database, there could conceivably be a large amount of network
>>> overhead processing the Mysql queries. However, from the reading I
>>> have been doing, MySQL is very fast, PHP being interpreted, may end up
>>> being slower.
>>>
>>> So, anyone care to enlighten me? I would love your thoughts,
>>> comments, experiences.
>>>
>> I've added the crosspost because I believe this is an extremely good
>> question that doesn't get the attention it deserves. You should get a
>> lot of opinions on this.
>> Here's mine:
>> One query to sql is better than 6 and it's certainly better than 27.
>> PHP could process the list quite quickly. If your DB is on a
>> different server than your web site (good practice to keep your DB
>> behind a firewall>, then you're crossing your network up to 27 times
>> instead of just once.
>
> I would agree that this may work in this one situation, however, when
> those datasets get too large - with large numbers of request for that
> data, you can do more harm than good.
>
> I recently fixed some code that was causing over 400MB per request to be
> returned for processing - and literally crippled an IBM mainframe. to
> the point that once we shut off the web app - it took the frame more
> than 7 hours to catch up on it's batch processing. One of those - let's
> let the application process the data. Again, in a very small number of
> situations this **may** be okay, in others, it can be crippling.
>
> Things to remember:
> 1) KNOW YOUR DATA - what it is and how much you have.
> 2) KNOW YOUR "customer" - how many, how they might use the data.
> 3) Your "customer" will ALWAYS use your application in ways you never
> dreamed of...

4) Never ask for more data then you're really sure you're going to use.
The numerous times I've seen 'SELECT *' in production code...
--
Rik Wasmus

On Fri, 18 Jan 2008 20:51:38 +0100, "Rik Wasmus"
<luiheidsgoeroe@hotmail.com> wrote:

>On Fri, 18 Jan 2008 20:46:01 +0100, Michael Austin
><maustin@firstdbasource.com> wrote:
>
>> Gary L. Burnore wrote:
>>> [Carolyn's post left intact and comp.lang.php added]
>>> On Fri, 18 Jan 2008 09:27:47 -0500, Carolyn Marenger
>>> <cajunk@marenger.com> wrote:
>>>
>>>> I am new to PHP/MySQL. I would like some of your thoughts on when to
>>>> use php code and when to use mysql queries.
>>>>
>>>> In the case I am working on, a learning project for myself, I am
>>>> writing a contact management application. Basically a phone book
>>>> with a many to many relationship between individuals and
>>>> organizations, and one to many between individuals/organizations and
>>>> the relevant data such as phone numbers.
>>>>
>>>> I am working on a page which lists all the individuals. I have a
>>>> horizontal listing at the top containing all the unique surname
>>>> initials, which link to headings as the list progresses. ie:
>>>>
>>>> -----
>>>> A B N
>>>>
>>>> A
>>>> Abnew, Georgina
>>>> Adwit, Dave
>>>>
>>>> B
>>>> Biltmore, Garth
>>>>
>>>> N
>>>> Niles, Paulina
>>>> Norton, Alicia
>>>> -----
>>>>
>>>> Currently, I have MySQL select all the unique initials and process
>>>> that into an array. The array is dumped to the screen as the index at
>>>> the top. Next the array is used for MySQL selects of surnames with
>>>> that initial.
>>>>
>>>> In the above example that would result in 6 MySQL queries. Assuming
>>>> only English based names, no foreign character sets, no numbers, no
>>>> symbols, that would result in a maximum of 27 queries.
>>>>
>>>> I could rewrite the PHP code to select all the names in a single query
>>>> . Then use PHP to process the list to make the index, headings, and
>>>> list the names.
>>>>
>>>> For a small private phone book, either way, so what - big deal. In a
>>>> public database, there could conceivably be a large amount of network
>>>> overhead processing the Mysql queries. However, from the reading I
>>>> have been doing, MySQL is very fast, PHP being interpreted, may end up
>>>> being slower.
>>>>
>>>> So, anyone care to enlighten me? I would love your thoughts,
>>>> comments, experiences.
>>>>
>>> I've added the crosspost because I believe this is an extremely good
>>> question that doesn't get the attention it deserves. You should get a
>>> lot of opinions on this.
>>> Here's mine:
>>> One query to sql is better than 6 and it's certainly better than 27.
>>> PHP could process the list quite quickly. If your DB is on a
>>> different server than your web site (good practice to keep your DB
>>> behind a firewall>, then you're crossing your network up to 27 times
>>> instead of just once.
>>
>> I would agree that this may work in this one situation, however, when
>> those datasets get too large - with large numbers of request for that
>> data, you can do more harm than good.
>>
>> I recently fixed some code that was causing over 400MB per request to be
>> returned for processing - and literally crippled an IBM mainframe. to
>> the point that once we shut off the web app - it took the frame more
>> than 7 hours to catch up on it's batch processing. One of those - let's
>> let the application process the data. Again, in a very small number of
>> situations this **may** be okay, in others, it can be crippling.
>>
>> Things to remember:
>> 1) KNOW YOUR DATA - what it is and how much you have.
>> 2) KNOW YOUR "customer" - how many, how they might use the data.
>> 3) Your "customer" will ALWAYS use your application in ways you never
>> dreamed of...
>
>4) Never ask for more data then you're really sure you're going to use.
>The numerous times I've seen 'SELECT *' in production code...

Right. As I read her example, she planned on using the data, but
parsing it out up to 27 ways. (& btw, we all know how little it
takes to bog an IBM mainframe)
--
gburnore at DataBasix dot Com
---------------------------------------------------------------------------
How you look depends on where you go.
---------------------------------------------------------------------------
Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
| ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
Official .sig, Accept no substitutes. | ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
| ÝÛ 0 1 7 2 3 / Ý³Þ 3 7 4 9 3 0 Û³
Black Helicopter Repair Services, Ltd.| Official Proof of Purchase
================================================== =========================

"The Natural Philosopher" <a@b.c> wrote in message
news:1200672543.35803.0@iris.uk.clara.net...

> I've done this.
>
> Itseasierto do one big query, sort by whatever, iterate through the array
> and write a new header everytime the name changes
>
> e.g. select surname,... from mytable, order by name gets the lot in teh
> write order, and then a simple loop with something like posting a varablee
> for the current first letter, and if it DIOES'T match the initial of the
> name you are about to print, executing a bit of code that prints a new
> header, aand set it to the current first name.
>
> PHP may be slow, but not as slow as the overhead on an SQL call with the
> attendant file system shuffling.

phil...i've never, ever seen *any* scripting language that can out-perform a
database. sorry, i don't buy that. that one (whatever is meant by that)sql
query can run cached and be optimized by almost any db. whatever system
shuffling is to be done, it certainly will be mechanically LESS than hitting
the db n times to get the same result...especially considering that php is
involved between calls rather than just one process.

make a believer of me, though, and i will happily change my mind.

"Gary L. Burnore" <gburnore@databasix.com> wrote in message
news:fmr27l$666$4@blackhelicopter.databasix.com...
> On Fri, 18 Jan 2008 20:51:38 +0100, "Rik Wasmus"
> <luiheidsgoeroe@hotmail.com> wrote:
>
>>On Fri, 18 Jan 2008 20:46:01 +0100, Michael Austin
>><maustin@firstdbasource.com> wrote:
>>
>>> Gary L. Burnore wrote:
>>>> [Carolyn's post left intact and comp.lang.php added]
>>>> On Fri, 18 Jan 2008 09:27:47 -0500, Carolyn Marenger
>>>> <cajunk@marenger.com> wrote:
>>>>
>>>>> I am new to PHP/MySQL. I would like some of your thoughts on when to
>>>>> use php code and when to use mysql queries.
>>>>>
>>>>> In the case I am working on, a learning project for myself, I am
>>>>> writing a contact management application. Basically a phone book
>>>>> with a many to many relationship between individuals and
>>>>> organizations, and one to many between individuals/organizations and
>>>>> the relevant data such as phone numbers.
>>>>>
>>>>> I am working on a page which lists all the individuals. I have a
>>>>> horizontal listing at the top containing all the unique surname
>>>>> initials, which link to headings as the list progresses. ie:
>>>>>
>>>>> -----
>>>>> A B N
>>>>>
>>>>> A
>>>>> Abnew, Georgina
>>>>> Adwit, Dave
>>>>>
>>>>> B
>>>>> Biltmore, Garth
>>>>>
>>>>> N
>>>>> Niles, Paulina
>>>>> Norton, Alicia
>>>>> -----
>>>>>
>>>>> Currently, I have MySQL select all the unique initials and process
>>>>> that into an array. The array is dumped to the screen as the index at
>>>>> the top. Next the array is used for MySQL selects of surnames with
>>>>> that initial.
>>>>>
>>>>> In the above example that would result in 6 MySQL queries. Assuming
>>>>> only English based names, no foreign character sets, no numbers, no
>>>>> symbols, that would result in a maximum of 27 queries.
>>>>>
>>>>> I could rewrite the PHP code to select all the names in a single query
>>>>> . Then use PHP to process the list to make the index, headings, and
>>>>> list the names.
>>>>>
>>>>> For a small private phone book, either way, so what - big deal. In a
>>>>> public database, there could conceivably be a large amount of network
>>>>> overhead processing the Mysql queries. However, from the reading I
>>>>> have been doing, MySQL is very fast, PHP being interpreted, may end up
>>>>> being slower.
>>>>>
>>>>> So, anyone care to enlighten me? I would love your thoughts,
>>>>> comments, experiences.
>>>>>
>>>> I've added the crosspost because I believe this is an extremely good
>>>> question that doesn't get the attention it deserves. You should get a
>>>> lot of opinions on this.
>>>> Here's mine:
>>>> One query to sql is better than 6 and it's certainly better than 27.
>>>> PHP could process the list quite quickly. If your DB is on a
>>>> different server than your web site (good practice to keep your DB
>>>> behind a firewall>, then you're crossing your network up to 27 times
>>>> instead of just once.
>>>
>>> I would agree that this may work in this one situation, however, when
>>> those datasets get too large - with large numbers of request for that
>>> data, you can do more harm than good.
>>>
>>> I recently fixed some code that was causing over 400MB per request to be
>>> returned for processing - and literally crippled an IBM mainframe. to
>>> the point that once we shut off the web app - it took the frame more
>>> than 7 hours to catch up on it's batch processing. One of those - let's
>>> let the application process the data. Again, in a very small number of
>>> situations this **may** be okay, in others, it can be crippling.
>>>
>>> Things to remember:
>>> 1) KNOW YOUR DATA - what it is and how much you have.
>>> 2) KNOW YOUR "customer" - how many, how they might use the data.
>>> 3) Your "customer" will ALWAYS use your application in ways you never
>>> dreamed of...
>>
>>4) Never ask for more data then you're really sure you're going to use.
>>The numerous times I've seen 'SELECT *' in production code...
>
> Right. As I read her example, she planned on using the data, but
> parsing it out up to 27 ways. (& btw, we all know how little it
> takes to bog an IBM mainframe)

can you say, as400? :^)