Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: SELECT * query stalls rest of script

    Hi there,

    this is my first post, so please bear with me... perhaps the solution I'm looking for is really really easy?

    I've got a database holding the address and contact details of 3500 businesses. Instead of querying the db every time for a certain business detail, I want to write the query results for ("SELECT * FROM tablename") to a file with php using fopen/fwrite.

    For some reason, the query is taking too long so the file doesn't get written. When I limit the query to 100 or so, it works perfectly. I wouldn't think 3500 rows was too many... can you think of a reason why this is happening, or how I can optimize my query?

    Any insight into my problem would be greatly appreciated. Thank you!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i cannot help you with the php part of this, but i can move the thread to the php forum for you

    optimizing SELECT * FROM tablename is not possible, because -- by design -- it is supposed to return all rows from the table

    why in the world would you want to use a flat file anyway?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    Hm, is that not the right thing to do? My end purpose is to create a facility that emails each business and displays their address/details in the body of the email for them to approve or amend. I don't know how else to get the info to display each business's unique details?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mysql doesn't do emails

    moving thread to php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142
    Hello! As to why it works for 100 and not for all is probably because PHP has a configurable setting to time out on queries (php.ini) so for whatever reason, it's not able to finish in time. As for sending customizing e-mail messages, PHP can do this easily with the mail() function. You would SELECT your records, loop through them, and send mail() to each using each record's data. The only downside is that the e-mails are auto-generated by the server, so some users' mail clients may suspect it is SPAM. I do not think I would use flat text files, however.

    Hope this helps!
    --ST

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Pulling 3500 rows from a table isn't too hazardous, so it might be worth looking at your MySQL/PHP setup to determine if there is anything holding this back. If you run the query from SQL command line does it return all the results in a reasonable time?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •