Results 1 to 9 of 9

Thread: Best mysql querying... protocol?

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2008
    Location
    Texas
    Posts
    1,288

    Best mysql querying... protocol?

    When creating a connection to perform a query or non-query. Is it best to open a single connection at the beginning of the page and use it
    until the last operation and then close or create a connection and mysql_free_result()/mysql_close() before and after each query?

    Is there a big difference in performance between the two? I guess it would really depend on the volume of users...

    Thanks,

    Justin
    You down with OOP? Yeah you know me!
    MCAD and MCMICKEYMOUSE (vb.net)

    ----

    If it even kinda helps... rate it : )

    Edit a Multi-page .tif file and save.

  2. #2
    Addicted Member Pc Monk's Avatar
    Join Date
    Feb 2010
    Posts
    188

    Re: Best mysql querying... protocol?

    Hi,
    i didnt understand exactly what you mean , but , its better to make a php file like config and include the file when ever u need to be connect to database or even u can include it in a header so its always ready , btw writing a query everytime will make the process slow so use a variable for it
    but if u just going to use the connection for only a specific page include the config in there and close it after the last operation so you can make sure that theres no way for an error or access in any other pages.
    hope it helps

  3. #3
    Frenzied Member TheBigB's Avatar
    Join Date
    Mar 2006
    Location
    *Stack Trace*
    Posts
    1,511

    Re: Best mysql querying... protocol?

    Quote Originally Posted by Pc Monk View Post
    Hi,
    i didnt understand exactly what you mean , but , its better to make a php file like config and include the file when ever u need to be connect to database or even u can include it in a header so its always ready , btw writing a query everytime will make the process slow so use a variable for it
    but if u just going to use the connection for only a specific page include the config in there and close it after the last operation so you can make sure that theres no way for an error or access in any other pages.
    hope it helps
    Sorry, but that is completely false.

    MonkOFox:
    When you execute a query that returns huge amounts of data it is all stored in PHP's memory space. mysql_free_result() lets you free up that memory, once you're done with it.
    There is usually a limit set to the number of connections your database server can handle. mysql_close() closes the connection to the database, allowing other scripts and instances to connect to the database.

    BUT, usually these functions don't need to be called, because they will be called automatically when the script terminates. Only when you're dealing with a daemon/service or repeatedly are retrieving huge sets of data, you should be worried about freeing and disposing resources.

    Another thing, the mysql_* functions are deprecated since PHP-5.5.0 and will be completely removed in future PHP versions. Also, the functions need to used carefully, because they're very susceptible to things like injection, if you don't handle them properly. An alternative for this set of functions is PDO. I'd advise you to look up some tutorials to get started with PDO.
    Delete it. They just clutter threads anyway.

  4. #4
    Addicted Member Pc Monk's Avatar
    Join Date
    Feb 2010
    Posts
    188

    Re: Best mysql querying... protocol?

    Quote Originally Posted by TheBigB View Post
    Only when you're dealing with a daemon/service or repeatedly are retrieving huge sets of data, you should be worried about freeing and disposing resources.
    i guess what i said its only for this situation so its not completely wrong is it ?

  5. #5
    Addicted Member Pc Monk's Avatar
    Join Date
    Feb 2010
    Posts
    188

    Re: Best mysql querying... protocol?

    i dont know why i posted my 2nd post tiwce and since i couldnt delete it i decide to add something...
    you are right about mysql_free_result() but writing the connection for everypage is something that usualy no one would do that.. it saves your time by adding the config..and writing a query everytime is the same thing and as you said for some when the page gets heavy like when theres 1000 people using the same page and the programmer writes the query instead of using a variable it could cause overflow.. i had it before..thats why i'm saying it and it doesnt even make sence to write a query twice when you have it up there somewhere does it ?
    Last edited by Pc Monk; Apr 13th, 2013 at 04:57 PM.

  6. #6
    Frenzied Member TheBigB's Avatar
    Join Date
    Mar 2006
    Location
    *Stack Trace*
    Posts
    1,511

    Re: Best mysql querying... protocol?

    Alright, I think I'm starting to understand what you're saying, but you're not using the right terms here. Apologies for being so harsh.

    If I understand correctly you were trying to say the following.
    Quote Originally Posted by Pc Monk View Post
    its better to make a php file like config and include the file when ever u need to be connect to database or even u can include it in a header so its always ready , btw opening a connection everytime will make the process slow so use a variable for it
    But the last part still doesn't make sense to me. Care to elaborate?
    Quote Originally Posted by Pc Monk View Post
    close it after the last operation so you can make sure that theres no way for an error or access in any other pages.
    Delete it. They just clutter threads anyway.

  7. #7
    Addicted Member Pc Monk's Avatar
    Join Date
    Feb 2010
    Posts
    188

    Re: Best mysql querying... protocol?

    Quote Originally Posted by TheBigB View Post
    Alright, I think I'm starting to understand what you're saying, but you're not using the right terms here. Apologies for being so harsh.

    If I understand correctly you were trying to say the following.


    But the last part still doesn't make sense to me. Care to elaborate?
    i dont know why that mistake happend but here it is : its better to make a php file like config and include the file when ever u need to be connect to database or even u can include it in a header so its always ready , btw writing the same macro everytime will make the process slow so use a variable for it

    last part is : mysql_close();

  8. #8
    Addicted Member Pc Monk's Avatar
    Join Date
    Feb 2010
    Posts
    188

    Re: Best mysql querying... protocol?

    Quote Originally Posted by TheBigB View Post
    Alright, I think I'm starting to understand what you're saying, but you're not using the right terms here. Apologies for being so harsh.

    If I understand correctly you were trying to say the following.


    But the last part still doesn't make sense to me. Care to elaborate?
    mistake happens but here the statement : its better to make a php file like config and include the file when ever u need to be connect to database or even u can include it in a header so its always ready , btw writing the same macro everytime will make the process slow so use a variable for it

    and i talked about what i mean in 3rd

    last part is : mysql_close();
    i have no idea why i posted it twice BUT ....I dont have anything to add honeslty and emm since i couldnt delete it i just let it be
    Last edited by Pc Monk; Apr 14th, 2013 at 04:41 PM. Reason: I HAVE NO IDEA WHY I POSTED TWICE

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2008
    Location
    Texas
    Posts
    1,288

    Re: Best mysql querying... protocol?

    Awesome, thanks for the info. I'll definitely look up PDO since the mysql_* stuff will be gone shortly.
    I did some extra research and create a single connection at the beginning of the page load seems to be the best practice, with some suggesting that Persistent Connections are even better but there is some debate.

    Thanks again for all the info. For some reason I was notified (by email) about the thread conversation until the last comment.

    Justin
    You down with OOP? Yeah you know me!
    MCAD and MCMICKEYMOUSE (vb.net)

    ----

    If it even kinda helps... rate it : )

    Edit a Multi-page .tif file and save.

Posting Permissions

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



Click Here to Expand Forum to Full Width