Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Mod note: This discussion was split from a serious question about MySQL located here


    The folks on this and many other forum sare fascinated with the deprication of mysql_*. They don't care about the quality of MySQl as a database, but I guess we can't have everything :-)

    PDO is a choice you can make, it you feel a need. it is probably mych easier to translate your code to mysqli_* (note the 'i') because the syntax is much closer to that of the outdated mysql_* functions. PDO just pretends to be cool but doesn't have any actual benefits.

    As for your actual problem; have you tried running this query from a database tool, ie: using something other than PHP?
    because MySQL uis probably not allowed to access files at any reandom location on disk. Under linux for example you have to add special permissons to allow it to go outside of it's own working directories (which makes sense, but gives a very cryptic error...)

    Comments on this post

    • gw1500se disagrees : Of course PDO has benefits. The main being portability between database engines with trivial code change.
    Last edited by ManiacDan; October 15th, 2013 at 02:58 PM. Reason: explaining split
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,867
    Rep Power
    6351
    PDO has plenty of benefits, but since your code uses mysql-specific functionality, there's no reason to switch all the way to PDO. However, they mysql_* functions have been deprecated for 10 years, and have been officially replaced with mysqli_*. That's the change I was talking about, and I have no fascination with PDO at all (and don't use it in my own projects). I prefer MySQL, but I also prefer to use a database library which didn't stop being relevant a decade ago.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    PDO has plenty of benefits
    Off topic but; Name one. :-)

    but I also prefer to use a database library which didn't stop being relevant a decade ago.
    Another odd reasoning; mysql_ stopped being relevant ten years ago, yet it's only now that PHP has marked it as deprecated that people start recommending mysqli...

    But I don't want to hijack this topic.
    Last edited by ManiacDan; October 15th, 2013 at 02:59 PM. Reason: Formatted the quotes properly. Please take care to use the proper tags.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by Vinny42
    The folks on this and many other forums... don't care about the quality of MySQl as a database, but I guess we can't have everything :-)
    you're fairly new here, so we'll cut you some slack

    you keep disparaging mysql on the mysql forum

    please... stop

    Comments on this post

    • codergeek42 agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,867
    Rep Power
    6351
    After re-reading Vinny's off-topic ranting I decided to split this entire false argument about MySQL and the mysql_* family of functions out into its own thread in the lounge. Vinny, continue here if you want.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,867
    Rep Power
    6351
    Originally Posted by Vinny42
    Off topic but; Name one. :-)
    Aside from the one already presented on your post? I prefer the syntax for bound params in PDO more than MySQLi. I also think the documentation is better. Of course there's the actual point of PDO in general, but I'm sure you already know that...since it's part of the name, right?


    Originally Posted by Vinny42
    Another odd reasoning; mysql_ stopped being relevant ten years ago, yet it's only now that PHP has marked it as deprecated that people start recommending mysqli...
    So your complaint seems to be that we're now...too late? We shouldn't bother talking about it now since it was only officially deprecated in 5.5.0? I think we should be talking about it more now since it's officially on the deprecation list, and has moved from "poor practice" to "officially deprecated." But if you want to not talk about it, by all means...stop talking about it.

    Originally Posted by Vinny42
    But I don't want to hijack this topic.
    That's been taken care of.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  12. #7
  13. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,936
    Rep Power
    4033
    Originally Posted by Vinny42
    ... yet it's only now that PHP has marked it as deprecated that people start recommending mysqli...
    People on the forums and IRC chatrooms and heck the PHP community in general have been recommending people move away from mysql_* for a long time. It's only know that it's finally become officially deprecated that everyone is starting to listen.

    As for why I find PDO to be better than MySQLi:
    - It provides the same API for multiple databases. Should your project need DB flexability this is great. Even if it doesn't, this is still great though because you might have multiple projects using different DBs. For example at work we use SQL Server. For my personal stuff I use Mysql. On a few side-jobs I've had to use SQLite. Different projects, different databases, same API.
    - Names parameters are much nicer than an ocean of ?'s
    - No having to jack around with references when doing prepared statements (bind_param, bind_result). Instead you can stick with the more familiar while ($row=$stmt->fetch()) loop.
    - A lot of convinence features such as PDO::FETCH_COLUMN. Need to just get a list of IDs or unique names, etc:
    Code:
    /* PDO: */
    $ids = $db->query('SELECT ID FROM blah WHERE x=123')->fetchAll(PDO::FETCH_COLUMN,0);
    
    /* MySQLi: */
    $stmt = $db->query('SELECT ID FROM blah WHERE x=123');
    $ids=[];
    while ($row=$stmt->fetch_row()) $ids[] = $row[0];
    - What you can do w/ MySqli can vary significantly depending on whether you use libmysql or mysqlnd (ie, no mysqli_fetch_all unless using mysqlnd). PDO does not suffer from this problem nearly as much.
    - PDO is being used more more in various applications and libraries due to it's flexibility and ease of use.


    I nearly always recommend someone switch to PDO rather than mysqli. I find it to be a much nicer and easier to learn API compared to mysqli and it provides a more practical foundation as it is becoming a standard for PHP projects.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    I'm flattered to have sparked such interest over such a trivial thing :-)

    you keep disparaging mysql on the mysql forum

    please... stop

    I'll try, but if someone is trying to do something in MySQL that is trivial in another database, can I still tell them that, of should I pretend that MySQL is the only option?
    Because on phpfreaks I was told that "Most of us MySQL users just don't care how things are done in other databases" which is an odd thing to say in a topic about a problem in MySQL :-)


    So your complaint seems to be that we're now...too late?
    Not at all, I just found it interesting how every single topic that mentions mysql is full of comments about the deprecation. It's good, it just sounds panicy.

    That's been taken care of.
    Thanks for that!


    Different projects, different databases, same API.
    That is exactly my problem with PDO; databases don't have the same API. You can pretend that they do, but they dont.
    That leads to caveats like http://php.net/manual/en/pdo.lastinsertid.php
    "This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences."

    So the API doesn't work the same for all databases.

    And that brings me to the most common mistake which is that PDO helps for portability. PDO doesn't and cannot translate queries, so when you move to a different database you will have to check and modify all your queries anyway.
    So, if you design your application by the common rules DRY you will have a separate class that actualkly executes the queries and that class will hold a handfull of calls to the API. The fact that that API is pretty much identical per database seems a trivial advantage when you only use it in a couple of screenfulls of code.

    - Names parameters are much nicer than an ocean of ?'s
    True for MySQL, in PostgreSQL there's already a $1, $2 etc notation which makes it easier to find parameters than to count questionmarks. Nevertheless I still wrap that so I can use names.

    - No having to jack around with references when doing prepared statements (bind_param, bind_result). Instead you can stick with the more familiar while ($row=$stmt->fetch()) loop.
    Again, didn't have that problem with PgSQL to begin with, perhaps my dislike of PDO is based more on the fact that it solves problems for MySQL that PgSQL didn't have in the first place.

    - A lot of convinence features such as PDO::FETCH_COLUMN. Need to just get a list of IDs or unique names, etc:
    And again: pg_fetch_all() and fetch_all_columns(), so I never had the problem in pgsql.
    (and for consistency I'd never just return a plain array from a query, always a set of records)

    - What you can do w/ MySqli can vary significantly depending on whether you use libmysql or mysqlnd (ie, no mysqli_fetch_all unless using mysqlnd). PDO does not suffer
    from this problem nearly as much.
    I did not know that. Needless to say that with pgsql... :-)

    - PDO is being used more more in various applications and libraries due to it's flexibility and ease of use.
    So is PostgreSQL but I still get told off for saying that :-)


    Of course there's the actual point of PDO in general, but I'm sure you already know that...since it's part of the name, right?
    You sound quite angry, did I strike a nerve? :-)
    But no, I don't get the point of PDO. Databases are always implemented using some database access object that wraps the calls to mysql_, mysqli_, PDO, PgSQL_ etc. So the only change I see in real life is that a handfull of calls to mysql_/mysqli_ is replaced by a handfull of calls to PDO, and the output is always an array of recorddata.

    So enlighten me, how do you do it that is better than using the plain APIs?
    Last edited by ManiacDan; October 16th, 2013 at 07:59 AM. Reason: Fixing quote tags...AGAIN.
  16. #9
  17. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,867
    Rep Power
    6351
    I'll try, but if someone is trying to do something in MySQL that is trivial in another database, can I still tell them that, of should I pretend that MySQL is the only option?
    Because on phpfreaks I was told that "Most of us MySQL users just don't care how things are done in other databases" which is an odd thing to say in a topic about a problem in MySQL :-)
    If someone has a question about how to drive a car, would you tell them about motorcycles? It seems like a waste of everyone's time. Most car drivers don't care how motorcycle maintenance works, because they don't use motorcycles, they use cars. In the MySQL forum, with a MySQL question, postgres information is off-topic.

    So the API doesn't work the same for all databases.
    True, database engines are different. There are edge cases for everything. However, the basics of SQL are still the same. Sure, oracle can't do paginated limits and other engines don't do auto-inc, but in companies with objects in multiple databases or code that can cross database engines, a single interface for "select * from theTable where theId = 123" is crucial to rapid development. If you need auto-inc, paginated limits, or LOAD DATA INFILE obviously you can choose a more appropriate solution, but that doesn't make PDO useless.

    True for MySQL, in PostgreSQL there's already a $1, $2 etc notation which makes it easier to find parameters than to count questionmarks. Nevertheless I still wrap that so I can use names.
    So you emulate some PDO features yourself, and you don't think that PDO is useful?

    So is PostgreSQL but I still get told off for saying that :-)
    Do you work for Postgres? Are you honestly saying that Postgres is more flexible, more powerful, and more applicable to different businesses than all other databases langues? Why not just stick to the postgres forum then? Nobody is going to rewrite the core of their application to use different database technologies based entirely on the say-so of some random evangelist on the internet.

    Databases are always implemented using some database access object that wraps the calls to mysql_, mysqli_, PDO, PgSQL_ etc. So the only change I see in real life is that a handfull of calls to mysql_/mysqli_ is replaced by a handfull of calls to PDO, and the output is always an array of recorddata.

    So enlighten me, how do you do it that is better than using the plain APIs?
    Ah-ha! Here's the problem. In your universe, everyone always uses a wrapper object around the core database functions and never calls them directly outside of that object. In everyone else's universe, that's not true, and database functions are called directly throughout most applications. We do, however, have a centralized class which is used to wrap the native database-specific functions so that regardless of underlying structure we have the same interface. You may use Vinny_db_object, everyone else just uses PDO.

    Comments on this post

    • codergeek42 agrees
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    I think this discussion is heading in the wrong direction.

    Yeah, we can compare PDO and MySQLi. We can argue forever about the advantages and disadvantages of either one. But in the end, it's not really important. Both extensions are fine.

    However, it is important that people finally get away from the old mysql_* functions. Why? Because of security!

    The old mysql_* functions don't have any security features at all. Yes, there's mysql_real_escape_string(). But it's little-known, difficult to use and easy to forget. I'd say that a majority of PHP programmers does not know how to escape values correctly. You can find a new SQL injection vulnerability in some PHP application every day.

    This has to stop. We need to promote prepared statements as a secure alternative to stuffing values into query strings. And since the old extensions doesn't support prepared statements, it must die. It's as simple as that.

    Do you understand now why we promote PDO and MySQLi? It's not because they're new and fancy. It's because they could finally put an end to the SQL injection plague.

    If you don't care about anything outside of your happy PostgreSQL world, that's fine. But other people do care. Answering every database question with "As a PostgreSQL user, I don't have that problem. Nah, nah, nah, nah, nah, nah!" isn't very helpful.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    If someone has a question about how to drive a car, would you tell them about motorcycles?
    A better analogy would be; if someone *** how they can setup a service to transport 150 people from A to B every ten minutes, you don't tell them to stick to their car, you tell them to use a bus instead.

    but in companies with objects in multiple databases or code that can cross database engines, a single interface for "select * from theTable where theId = 123" is crucial to rapid development.
    True, that's why those companies create their own classes that hide the actual API from the rest of the code. They also write separate classes with separate queries for each database, to prevent bugfixes on one database from messing up another. The controllers only know that they are dealing with some sort of storage, and don't care what it is.


    So you emulate some PDO features yourself, and you don't think that PDO is useful?
    Well, the more replies I read, the more I get the idea that MySQL users prefer PDO mainly because the MySQL and MySQLi API's are crap.
    It's not so much "PDO is great" as "PDO is better than the alternative".

    I've never had any problems with PgSQL's API. The named parameters are nice to have but even numbered parameters are better than questionmarks.
    So I guess that answers my question about why PDO is so popular amung MySQL users.


    Are you honestly saying that Postgres is more flexible, more powerful, and more applicable to different businesses than all other databases langues?
    Not all other databases, obviously, but as a replacement for MySQL it's definately a very viable choice.
    For some reason this makes many MySQL users angry... :-)

    Nobody is going to rewrite the core of their application to use different database technologies based entirely on the say-so of some random evangelist on the internet.
    Of course not, that would be stupid. But it wouldn't be particularly clever to pretend that MySQL is your only option either.



    Ah-ha! Here's the problem. In your universe, everyone always uses a wrapper object around the core database functions and never calls them directly outside of that object. In everyone else's universe, that's not true, and database functions are called directly throughout most applications.
    That sounds like a very bad design choice, or I'm not getting what you are saying...

    We do, however, have a centralized class which is used to wrap the native database-specific functions so that regardless of underlying structure we have the same interface. You may use Vinny_db_object, everyone else just uses PDO.
    So you wrap some of the functions, but not all? Does that mean that you use PDO "all over the place" and some database specific stuff through some other class? Then how do you know what's database-specific?
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    I think this discussion is heading in the wrong direction.
    It's going where I wanted it to go, and it's my discussion, so there :-)

    I wanted to know why people think PDO is better, and that's what I'm finding out.


    We need to promote prepared statements as a secure alternative to stuffing values into query strings. And since the old extensions doesn't support prepared statements, it must die. It's as simple as that.
    Hmmm.. prepared statements are not a magic bullet. Yes they prevent injection but they cause performance issues through bad queryplans, which can be pretty nasty, to the point where they can't realistically be used anymore. Generaly they're ok, but the one time you reallt need them will be the one time you can;t use them.

    Speaking of security, how do you feel, from a security point of view, about the lack of proper consistency control in MYSQL? The fact that NOT NULL is not always NOT NULL, dividing by zero, transactions that are committed when re-opened?

    If you don't care about anything outside of your happy PostgreSQL world, that's fine. But other people do care. Answering every database question with "As a PostgreSQL user, I don't have that problem. Nah, nah, nah, nah, nah, nah!" isn't very helpful.
    I guess you haven't noticed that I always give (or at least try to give) the MySQL solution to MYSQL problems.
  24. #13
  25. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,615
    Rep Power
    4247
    Personally speaking, my biggest gripe about MySQL is that at an enterprise level, it is more expensive than Oracle (and this was *before* Oracle acquired Sun/MySQL) and they tried to impose some really crappy licensing terms too (and this to a company that funded a MySQL release or two and even employed Monty for a little while). To be fair, all this started as lawyers started to get involved around the time that Sun was trying to acquire them.

    PDO is nice in that it provides a DBI like feel to PHP (placeholders for SQL statements). However, it must be noted that cross-DB capability is not really possible because SQL stands for Structured Query Language, *NOT* Standard Query Language and every DB engine has its own dialect and features.
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Personally speaking, my biggest gripe about MySQL is that at an enterprise level, it is more expensive than Oracle
    That's just weird. :-)
  28. #15
  29. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,615
    Rep Power
    4247
    Originally Posted by Vinny42
    That's just weird. :-)
    Unfortunately it is true. Company had an enterprise MySQL license for years. When it came time to renew that license, MySQL suddenly upped their prices and charged a heck of a lot more to use their enterprise tools. In fact, it cost even more than Oracle, when you did the math for how many instances, hardware etc. were required. On top of that, they tried to insert a clause that if we stopped using their enterprise tools, they had a right to audit us *any time* (and multiple times if they wished) for the next 2 years at *our* cost.

    This happened around the time that Sun was trying to acquire them (don't quite remember if it was before or after). Your mileage may vary.
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo