|
Doctor rear end in a top hat posted:This might be of use if you're going to be using everything that gets POSTed (you could do GET or PUT or whatever, too) in a query: Thanks for the laugh.
|
# ? Sep 5, 2010 21:00 |
|
|
# ? May 31, 2024 22:38 |
|
Now that I've made myself look like an idiot, I should mention that there's only one scenario I've used it for. User posts to something like this using AJAX: php:<? $conn = mysql_connect(); foreach($_POST as $key => $value) $$key = mysql_real_escape_string($value); $query = "SELECT * FROM users WHERE name = '$name' AND email = '$email'" $result = mysql_query($query, $conn); /*output results or some poo poo*/ ?>
|
# ? Sep 7, 2010 14:42 |
|
Doctor rear end in a top hat posted:Now that I've made myself look like an idiot, I should mention that there's only one scenario I've used it for. The fact that your example is too trivial to expose much of an attack vector doesn't change the fact that it's a terrible habit with the potential to open crippling security holes. It is not safer than turning on register_globals. Please do not recommend it.
|
# ? Sep 7, 2010 15:02 |
|
DaTroof posted:The fact that your example is too trivial to expose much of an attack vector doesn't change the fact that it's a terrible habit with the potential to open crippling security holes. It is not safer than turning on register_globals. Please do not recommend it. Sorry, but I don't have the file any longer. It was very similar to the example, but with more variables. I even specified that it would be used in the case of escaping query variables that were POSTed. And I should have specified that it's "safer" than register_globals because you can elect to just register POST variables and not GET, but a user who is interested in dicking with things will probably be able to muck with the POST just as easily as they can add to the querystring. At any rate, all of you are right. Don't use it. I had a specific case where it happened to do what I needed.
|
# ? Sep 7, 2010 15:22 |
If everybody just used PDO we could all get along better and not have to have these same discussions over and over:php:<? $db = DB:get(); $query = $db->prepare("SELECT * FROM users WHERE name = :name AND email = :email"); $query->bindParam(":name", $_POST["name"]); $query->bindParam(":email", $_POST["email"]); if ($query->execute()) { $rows = $query->fetchAll(PDO::FETCH_ASSOC); } ?>
|
|
# ? Sep 7, 2010 18:41 |
|
fletcher posted:If everybody just used [thing I favour] we could all get along better and not have to have these same discussions over and over: Yes, of course; that is true about a great many things. code:
|
# ? Sep 7, 2010 19:33 |
|
Also, I don't get what choice of database abstraction method has to do with that ill-advised pseudo-register-globals thing.
|
# ? Sep 7, 2010 19:35 |
Hammerite posted:Also, I don't get what choice of database abstraction method has to do with that ill-advised pseudo-register-globals thing. We are talking about escaping parameters for SQL, which PDO handles for you. How might I use dbquery() to execute that same sql statement with different parameters? Does it cache the optimization and query plan? What if I want to fetch one row at a time? How do I get the error code if a query fails? Dare I even ask about transactions? You only shave a few characters off the syntax and you severely cripple your ability to interact with the database. Why would you ever want to do that? fletcher fucked around with this message at 19:50 on Sep 7, 2010 |
|
# ? Sep 7, 2010 19:39 |
|
fletcher posted:We are talking about escaping parameters for SQL, which PDO handles for you. Ok, I guess I let that get away from me amidst all the horror. fletcher posted:How might I use dbquery() to execute that same sql statement with different parameters? I'm not sure what you mean by this. It is evident that the even-numbered arguments may be varied to substitute in different parameters. fletcher posted:Does it cache the optimization and query plan? No. That was not a design goal. In principle, certainly that functionality could be added. fletcher posted:What if I want to fetch one row at a time? Do you mean that you want to run a query that you expect to return at most one row, or do you mean that you want to run a query and then loop through the returned rows? Both of these may be done. For the former, specify DBQUERY_READ_SINGLEROW as the first argument. If any rows were found, then an associative array is returned, containing the data from the first row found. For the latter, a (very simple) wrapper function is available for fetching rows from the resultset as associative arrays. fletcher posted:How do I get the error code if a query fails? If a query fails then script execution is halted immediately. The user is shown an error message. Depending on a certain configuration setting, this message will be either a friendly "Sorry we screwed up" message for end-users or a detailed message containing the actual error message given by the RDBMS. In either case, the error message is logged. fletcher posted:Dare I even ask about transactions? You may specify DBQUERY_START_TRANSACTION, DBQUERY_COMMIT or DBQUERY_ROLLBACK as the first argument. If you do so, you should give no other arguments. fletcher posted:You only shave a few characters off the syntax and you severely cripple your ability to interact with the database. You have no basis for making this assumption.
|
# ? Sep 7, 2010 20:36 |
|
If you are genuinely interested in what my function can do and what it cannot, I am happy to post the current version of the function along with the documentation I wrote.
|
# ? Sep 7, 2010 20:40 |
|
fletcher posted:How might I use dbquery() to... This post and the next few replies discuss this problem directly. I'd advise just walking away, it's not worth the aggravation. Please, please don't post that code, I like my current sanity level. Maybe if you post it in the Coding Horrors thread instead...
|
# ? Sep 7, 2010 20:44 |
|
McGlockenshire posted:This post and the next few replies discuss this problem directly. Yes, a previous version of the code is there and gives an idea of the capabilities, although I have refactored it and added additional functionality since then.
|
# ? Sep 7, 2010 20:46 |
Hammerite posted:I'm not sure what you mean by this. It is evident that the even-numbered arguments may be varied to substitute in different parameters. I'm talking about the performance benefits from using prepared statements. Your dbquery() appears to throw that out the window. code:
quote:No. That was not a design goal. In principle, certainly that functionality could be added. Why are you so intent on defending this? I'm arguing for using a well documented standard library that does a lot more than your limited functionality solution offers. Passing in global constants? An API designed by somebody that doesn't "get" OOP? Somebody that doesn't care how other database abstraction APIs are designed? Are you just trolling me or something? quote:Do you mean that you want to run a query that you expect to return at most one row, or do you mean that you want to run a query and then loop through the returned rows? Both of these may be done. For the former, specify DBQUERY_READ_SINGLEROW as the first argument. If any rows were found, then an associative array is returned, containing the data from the first row found. For the latter, a (very simple) wrapper function is available for fetching rows from the resultset as associative arrays. If I wanted the query to return at most one row I would specify a LIMIT 1. I'm talking about fetching the rows one at a time, not looping through returned rows one at a time. What if a query returns a million rows? I certainly don't need them all to be in an associative array at once. quote:If a query fails then script execution is halted immediately. The user is shown an error message. Depending on a certain configuration setting, this message will be either a friendly "Sorry we screwed up" message for end-users or a detailed message containing the actual error message given by the RDBMS. In either case, the error message is logged. I'm talking about me as a programmer, how do I get the error code for a failed query? What if the application is supposed to do something else if there is a certain error # that is returned? How does dbquery() even know how to display an error message to the user? How does it know if the request is supposed to return JSON or HTML? quote:You may specify DBQUERY_START_TRANSACTION, DBQUERY_COMMIT or DBQUERY_ROLLBACK as the first argument. If you do so, you should give no other arguments. quote:You have no basis for making this assumption. Yes, I do. Especially when you are trying to give people lovely advice while saying things like "how other APIs are designed are not important to me" or how you don't understand OOP. I would hate for somebody new to PHP to read your posts and think what you are doing is a good idea. Just stop man.
|
|
# ? Sep 7, 2010 21:07 |
|
fletcher posted:or how you don't understand OOP To be fair, PHP doesn't understand OOP very well, either.
|
# ? Sep 7, 2010 21:33 |
|
fletcher posted:I'm talking about the performance benefits from using prepared statements. Your dbquery() appears to throw that out the window. I'm aware of the performance benefits of prepared statements, and that my function as it is currently implemented does not make use of them. (I will reiterate that in principle, it could do so). fletcher posted:Why are you so intent on defending this? I'm arguing for using a well documented standard library that does a lot more than your limited functionality solution offers. All I have been doing for a good while (and for the entirety of the argument we are currently having, regardless of what I may have argued in our several past arguments - I cannot remember) is defending my own use of it. Yes, it has limited functionality, but it does offer all of the functionality that I require for my own projects. You tout PDO as a universal solution that will work for any use case, and I agree that it is just that, but even were I to use it (and at the risk of labouring the point, I will observe again that my function could, in principle, be using PDO internally) it's a bit prescriptive, isn't it, to say "everybody must use PDO explicitly in their code and nobody should wrap the functionality it offers in any way"? You ask me, "Why are you so intent on defending this?" Pardon me, but you yourself come off as very intent on promoting PDO, indeed you do so with almost evangelical fervour whenever anyone posts code that has a whiff of not-PDO about it. And yes, PDO is very nice, but that's not the point. The point is it's not the only way of presenting the functionality it offers. fletcher posted:Are you just trolling me or something? No, although I can understand why you might think so, as you were under the impression that I was arguing for the adoption of my code by others. Since others would require functionality that I do not require and therefore have not provided for, this would not be wise. fletcher posted:If I wanted the query to return at most one row I would specify a LIMIT 1. I was thinking along the lines of a query that you expect to return at most one row because it selects against a unique key. Anyway, this is not what you meant. fletcher posted:I'm talking about fetching the rows one at a time, not looping through returned rows one at a time. What if a query returns a million rows? I certainly don't need them all to be in an associative array at once. I'm not sure how I gave you the impression that all of the rows were fetched into the same associative array. They are not. Anyway, the ability to fetch rows one by one does happen to be available functionality. fletcher posted:I'm talking about me as a programmer, how do I get the error code for a failed query? What if the application is supposed to do something else if there is a certain error # that is returned? How does dbquery() even know how to display an error message to the user? How does it know if the request is supposed to return JSON or HTML? I think these questions would be important and valid if I were arguing (as you were under the impression I am) that everybody should use my code. As I have explained, this is not what I'm arguing, so the answer "It's not important to me personally" becomes valid. But writing words for the sake of writing words, 1) The error code is not logged, only the error message, but in principle the error code could be logged as well. 2) I do not require that functionality. 3) I require only HTML functionality. fletcher posted:I cannot offer any response to this, because you provide no specific reasons for your distaste. fletcher posted:Yes, I do. What is it? fletcher posted:Especially when you are trying to give people lovely advice... For a while now, I've not bothered to respond to any posts in this thread or the SQL thread that ask questions about (say) how to accomplish something through PHP and MySQL. It's not worth the hassle because it just seems to result in you (yes, it is generally you) attempting to start an argument by chiming in with a comment along the lines of "Why would you do this? You must be mad! Just use PDO and everything will work so much better!" You have a tendency to do this regardless of whether or not the specific problem that was posted is related to the supposed limitations of whichever other database abstraction method is in use (either in the question asked or solution offered). I find it annoying that I might post a suggestion of how to resolve a problem that actually doesn't have anything to do with the database abstraction method, and then get upbraided for failing to evangelise for PDO. So I generally haven't responded to database-related questions for a while now. It's not worthwhile. This being the case, I'm not sure what lovely advice I'm supposed to have been giving people. If saying that slavish adherence to PDO is not the cure for all your ills qualifies as giving lovely advice, then yes, I've been giving lovely advice. fletcher posted:I would hate for somebody new to PHP to read your posts and think what you are doing is a good idea. The response I would give to this really depends on what specifically you mean by "what you are doing". If by this sentence you mean that I am suggesting that others should copy and use the database code I have written, then as I have said that is not what I am advocating. If by "what you are doing" you mean "presenting the functionality that is required by me in a fashion that is easy to make use of, and makes for easy-to-read code", then I dispute that that is a bad idea. If by "what you are doing" you mean "not using PDO", then well, I think it's clear both that you feel PDO is the one true database abstraction method and that I don't agree.
|
# ? Sep 7, 2010 22:08 |
|
Hammerite posted:The response I would give to this really depends on what specifically you mean by "what you are doing". By "what you are doing" he means "pointlessly reinventing the wheel".
|
# ? Sep 7, 2010 23:28 |
|
KuruMonkey posted:By "what you are doing" he means "pointlessly reinventing the wheel". OK, I hadn't thought of that. For the record I don't feel it was pointless or unhelpful, I am quite pleased with the way it tidies up my code and it would now annoy me if I no longer had it available, but I can appreciate how it would seem like unnecessary work to someone else.
|
# ? Sep 7, 2010 23:55 |
|
Hammerite posted:OK, I hadn't thought of that. I can understand you wanting to clean up your code and make things easier, we all want that. But at the same time you always need to keep your code easy for others to maintain. I think if you were to die in a bus accident and I was the one that had to maintain your code, the first thing I would do is ditch your db library. I am sure your code works fine and does the job, but if there was a problem its not like I could just go to a support forum where there are a bunch of other people using it and get answers. Not to keep beating a dead horse here, and not to sound like some sort of PDO zealot, but you should look at PDO again and consider extending the PDO class to do what you want it to do. Take a look at Zend_Adapter_PDO for an example of what you can do. By extending PDO you can add your own functionality, yet still be open to use it for other things without having to go back and reinvent more wheels. I don't think it would be too hard for you to have something like $myPdo->dbQuery() and have it work exactly like your existing library, yet it would still be PDO and you could easily do prepared statements or transactions or what have you without having to go through and gut your library. Also, it still makes it easy for anyone else to work with your code. As far as reinventing the wheel, I have been there before. I wasted a lot of hours on my own crazy libraries. Came here to show people and they told me I was out of my loving mind and to stop wasting my time. I was offended and a little hurt, but in the end they were absolutely right.
|
# ? Sep 8, 2010 00:26 |
|
Begby posted:By extending PDO you can add your own functionality, yet still be open to use it for other things without having to go back and reinvent more wheels. I don't think it would be too hard for you to have something like $myPdo->dbQuery() and have it work exactly like your existing library... I've been considering adapting my code to use PDO, or at any rate prepared statements, instead of what it currently uses for a while; since as Fletcher points out they do provide better performance and when it's all tucked away behind a function call, why not? I don't have to bother myself with the internal details of how it works when I'm writing code that makes use of it. Of course, what I have currently works fine and has the functionality I currently require, so it's not something that would be very high priority. .... To be honest, I'm annoyed with myself that I posted at all. These arguments are always a waste of time. You [Fletcher] were making a point about how simple PDO is to use, which is valid; I was trying to make the point that in principle, other methods can be just as simple and that simplicity isn't something that PDO has a monopoly on. But I did a bad loving job of it and gave the impression that I was trying to persuade everybody else that they should use code that I wrote for my own personal use, which wasn't what I was trying to argue at all. I decided quite a while ago that I was done arguing about database abstraction methods in here, which is why I'm irritated that I rose to the bait. I'm not going to post in the PHP thread about this subject any more. Please ban me if I do.
|
# ? Sep 8, 2010 00:36 |
Hammerite posted:I've been considering adapting my code to use PDO, or at any rate prepared statements, instead of what it currently uses for a while; since as Fletcher points out they do provide better performance and when it's all tucked away behind a function call, why not? I don't have to bother myself with the internal details of how it works when I'm writing code that makes use of it. Of course, what I have currently works fine and has the functionality I currently require, so it's not something that would be very high priority. I'd really like to see how you modify your function to use prepared statements to mimic this functionality: php:<? $query = $db->prepare("SELECT * FROM user WHERE name = :name"); $query->bindParam(":name", $someName); if ($query->execute()) { //do stuff } $query->bindParam(":name", $anotherName); if ($query->execute()) { //do stuff } ?>
|
|
# ? Sep 8, 2010 01:04 |
|
Oh come on, stop baiting him, he said he was done.
|
# ? Sep 8, 2010 03:27 |
|
fletcher posted:I'd really like to see how you modify your function to use prepared statements to mimic this functionality: Wait, you mean you can't use functionality that's built into prepared statements when you're using something other than prepared statements? Amazing.
|
# ? Sep 8, 2010 08:23 |
|
Begby posted:I think if you were to die in a bus accident and I was the one that had to maintain your code, the first thing I would do is ditch your db library.
|
# ? Sep 8, 2010 14:46 |
|
I need to develop a web app that does the following: -Allows a user to upload a csv -Takes the data from the csv and looks for headers to define the columns and asks for them if they dont exist -Puts said data into a mysql table -presents the first few rows and asks the user to define what they need to do to each column, the purpose here is to clean up the data, so if I have a column with someone's full name I will split it into first and last Any framework recommendations to make manipulating huge amounts of data easier? I'm looking at datasets of up to 250k rows.
|
# ? Sep 8, 2010 18:01 |
|
Yay posted:That's quite a luxury, you being given the time to rip out the very internals of something and refactor it to use something else. Even assuming its internal usage only (no client not wanting to foot the bill), I can't see anyone wanting to let you spend time on basically 'prettifying' something that is (presumably) working. Work at a single company is worlds apart from working with individual clients. In this case it wouldn't be 'prettifying' but making it easier for me and others at the company maintain.
|
# ? Sep 8, 2010 18:14 |
|
Begby posted:Work at a single company is worlds apart from working with individual clients. In this case it wouldn't be 'prettifying' but making it easier for me and others at the company maintain. And there's the beginning of the uphill struggle. If you're lucky enough to work somewhere where thats not a concern, then that is quite the luxury, as I said.
|
# ? Sep 8, 2010 18:26 |
Sylink posted:I need to develop a web app that does the following: On the upload form I would just have a checkbox that indicates whether or not headers are included. Unless you know what the columns would be named it may be difficult to determine if the first row is in fact a header. MySQL can load data directly from CSV files: http://dev.mysql.com/doc/refman/5.1/en/load-data.html PHP has built in functions to manipulate CSV files: fgetcsv() & fputcsv() As far as cleaning up the data after they upload, that could be rather difficult, depending on what you want the user to be able to do. Is having the user use Excel to clean up the data before they upload not an option?
|
|
# ? Sep 8, 2010 18:31 |
|
I am basically recreating some ancient data cleanup scripts. They are mostly pretty simple, like capitalizing the first letter and splitting up one column into several. The problem I see coming up is PHP timing out trying to go through 250k rows.
|
# ? Sep 8, 2010 18:33 |
Sylink posted:I am basically recreating some ancient data cleanup scripts. This is probably due to the max_execution_time or memory_limit configuration settings in php.ini
|
|
# ? Sep 8, 2010 18:37 |
|
Sylink posted:I am basically recreating some ancient data cleanup scripts. If that is your concern then maybe try using set_time_limit( $seconds ); and ini_set( 'memory_limit', $some_high_number_of_mb ); and set the time limit to something really long and the memory limit really high.
|
# ? Sep 8, 2010 18:41 |
|
I am creating a calendar application for a website. The last thing I need to create is time zone conversions. Now I want to punch myself in the face. I have spent hours on test scripts and different methods, but it is just completely frustrating me. Here is my current test script: php:<? date_default_timezone_set('GMT'); $gmt_time_zone = new DateTimeZone('Europe/London'); $est_time_zone = new DateTimeZone('America/New_York'); $pst_time_zone = new DateTimeZone('America/Los_Angeles'); // User Submitted Date in EST $event_date = new DateTime('@1284570000'); echo 'Submitted Date:'.date('g:i A', $event_date->format('U')).' [EST]<br />'; // Convert time to GMT and store in database $offset = $gmt_time_zone->getOffset($event_date); echo 'Offset: '.$offset.' Minutes<br />'; echo 'Stored Time:'.date('g:i A', $event_date->format('U') + $offset).' [GMT]<br />'; // Convert time back to EST $offset = $est_time_zone->getOffset($event_date); echo 'Offset: '.$offset.' Minutes<br />'; echo 'Submitted Date Again:'.date('g:i A', $event_date->format('U') + $offset).' [EST]<br />'; // Display Date in User's Time Zone $offset = $pst_time_zone->getOffset($event_date); echo 'Offset: '.$offset.' Minutes<br />'; echo 'Time Zone Correction:'.date('g:i A', $event_date->format('U') + $offset).' [PST]<br />'; ?> What I am trying to do: 1) User submits date of event and time zone it is in. 2) Convert that to GMT and store in database. 3) Retrieve from database and display in any time zone I need.
|
# ? Sep 8, 2010 21:29 |
|
BondGamer posted:1) User submits date of event and time zone it is in. Which database? If you're using PostgreSQL or another database that has a TIMESTAMP WITH TIME ZONE column type, you don't need to do the hoop jumping. If you're using MySQL or another database that only has a simple DATETIME column type, your best bet is not going out of your way to store in GMT, but to store in whatever the database's default timezone is, which is usually the local time, then storing the desired timezone separately as a timezone name/string, not an offset. (Alternatively, if you want to do the hoop jumping, you can store the time in the database as GMT, but that can get annoying really fast.) Alternatively, epoch timestamps are GMT by their definition, so you can just store one of those and the desired timezone name/string and avoid the column type mess. Do not under any circumstances store the offset without the timezone name. It is not always going to be possible to get the actual user intent back from an offset alone, especially when DST goes into effect.
|
# ? Sep 8, 2010 22:07 |
|
Sylink posted:I am basically recreating some ancient data cleanup scripts. MySQL can import from CSV files directly into a table pretty quickly. You may want to look into the LOAD DATA INFILE command.
|
# ? Sep 8, 2010 22:15 |
|
BondGamer posted:
As suggested, try to let the database handle it. If you are on mysql, and the user sets their own timezone (like if they set it in their preferences or something) then you can use timestamps and set the timezone on the connection and mysql will take care of the conversions for you. Timestamps on mysql are automatically stored in UTC and converted on the fly to the timezone set on the connection, and if there is no timezone set, then it defaults to the server timezone. If that is not an option though, and a single user wants to use multiple timezones, well I dunno.
|
# ? Sep 9, 2010 01:12 |
|
Thanks for the tips. I think my major issue was I did not verify the time being stored was correct, so I was just walking around with a blindfold when attempting to use numbers which were not good to begin with. Once I made sure the storing algorithm was sound, the retrieval was easy to tweak. I never want to do anything involving time zones again.
|
# ? Sep 9, 2010 18:04 |
|
Is it possible to sort an array by a specific column (a date) ? I have a PHP array of a bunch of records from different databases, all with their own timestamp, and I need to sort the array by date. I basically have : $array[] = array("date"=>"2010-08-10",$data=>"Last month"); $array[] = array("date"=>"2010-09-09",$data=>"Yesterday"); $array[] = array("date"=>"2010-09-10",$data=>"Today); $array[] = array("date"=>"2010-09-11",$data=>"Tommorow"); I'd like a function to do a SortBy("date",$data,DESC) which would give me "Tommorow, Today, Yesterday, Last month"). What should I use ? I've searched and tried different functions but nothing seems to work.
|
# ? Sep 10, 2010 13:21 |
|
Yossarko posted:Is it possible to sort an array by a specific column (a date) ? I think you'll have to make a custom function http://www.php.net/manual/en/function.uasort.php Here's the other sorting functions http://www.php.net/manual/en/array.sorting.php edit: there is an example on the uasort page that does almost exactly what you want. butt dickus fucked around with this message at 20:12 on Sep 10, 2010 |
# ? Sep 10, 2010 14:10 |
|
I've again been told, after some vague begging on my part, not to use OO PHP. This is making me sad.
|
# ? Sep 10, 2010 19:57 |
|
I'd love to hear the justification behind that one.
|
# ? Sep 10, 2010 21:46 |
|
|
# ? May 31, 2024 22:38 |
|
McGlockenshire posted:I'd love to hear the justification behind that one. I believe it's, the other 25,000 lines of code on the site are all procedural, so why start splitting it up into two camps?
|
# ? Sep 10, 2010 22:03 |