Posted by The Natural Philosopher on 01/18/08 16:09

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.

[Back to original message]