Posted by Rik Wasmus on 01/18/08 16:15

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

[Back to original message]