ebmigue

I know that this is a forgotten topic by now, and I apologise for reviving it. But it was only recently that I had free time and I just have this impulse to share an alternative implementation. Here goes: http://www.reetudes.com/samples/ctg-fight-game.php The unique character of this implementation is that it uses re (http://www.reetudes.com), a library that I started months ago (sorry if this seems like a plug ). Thus, its approach is relational -- minimal use of loops through the use of relational operators; players and player types are treated as data (not classes and objects), and hence the implementation could easily be revised to accommodate the possibility of additional players and player types, while still maintaining the basic "gist" of the game. Further, the current implementation uses regular PHP variables; but it could be easily replaced by db table references (w/c are conceptually variables) by using the facilities provided by re. Hence, the need to integrate it w/ existing DBMS becomes a less daunting task -- w/c is desirable if we want to persist a gamer's data, or provide a feature for end-users to edit and save the basic settings of the game (such as the abilities of player types). P.S.: I admit that performance is peculiarly bad when both player types are "brawlers". IMO, this has something to do w/ re's "un-optimised" form. I am optimistic though that things could still be improved. Thank you and hope it interests you.

You can query the INFORMATION_SCHEMA database, much like how you would query your own database. Google "information_schema mysql". Hope it helps.

Try a book by Chris Date, titled Introduction to Database Systems (8e). It has been around for 30 years already (eight editions in all). So you are assured that a wide range of topics and problems are covered by that book when it comes to DBMS. As a bonus, the book has a chapter devoted to the discussion of "object-oriented programming" and how it translates to the relational model. Doubtful. I doubt that doubt.

Change: SELECT Date, TempMax FROM wx_daily ORDER BY TempMax DESC LIMIT 0 , 10 To: SELECT Date, TempMax FROM wx_daily ORDER BY Date DESC LIMIT 1

@OP Your problem is one of the "MySQL to PHP relationship" w/c I detest. It causes extra work. As a workaround, create a generic function that will "wrap" the need to query the results. In that function, you can use PHP's builtin casting functions or constructs, such as 'settype' or (<type>)$variable, respectively. For the metadata of the table (i.e., what are the respective types of the table's attributes), there are mysql_* functions available for use; please refer to the manual.

I'd just like to note that: I understand that what were posted were mere examples. However, the use of some hypothetical class named 'SQL' may not be very "framework-minded." For there is the possibility that a database is not SQL-based. And there is some movement in just that direction --- there are attempts to replace SQL, making way for a more "relationally-oriented" database programming language. And if that happens, would that mean that we have to edit all code referencing/using the 'SQL' class? I hope not.

Again, if being lazy translates to efficiency (saved time, money, effort) then I will go w/ it. There you have it. Remember that "SELECT *" is merely an innocent syntactic construct. It is the user who is responsible. Your comments are giving the impression that "SELECT *" is inherently "evil", where in fact it is the user's fault (not knowing when to use it). That is my issue: using SELECT * is not "bad/evil"; if you know what you're doing, of course, you'll be fine. But then again, IF there are optimisers who just can't optimise code that contains "SELECT *", well they are to be blame too. Again, NOT the problem of "SELECT *", as such, but of lack of code documentation and all such matters. This was addressed in my follow-up post, immediately before this. Please read it. I used the term "universe" because a pun was intended. However, it seemed not to work. Kidding aside -- but still, such scenario IS still possible, right? And besides, there are many scenarios (real) w/c are close to what was being conjured (say, 10 tables that needed to be modified, and 3 fields to be added to each). Read the article on wikipedia (relational algebra). You can also purchase a book by Chris Date (Introduction to Databases). Agreed. But my issue, again, is that it is often viewed that "SELECT *" in itself is "bad/evil/dangerous". But in reality it is the user who is to be blamed for misusing it. I hope that that point is made clear: not the syntax (SELECT *) but the user (and/or limited DB optimiser) is ultimately responsible for the undesirable side-effects of using SELECT *. The solution, therefore, is not to ban the use of "SELECT *" but to educate the user of its (SELECT *) proper use. It can still be used but only when it's applicable, rather than type the whole set of fields (w/c can be 20 fields w/ average of 6 characters each: daunting and inefficient!)

Just to clarify. I am not suggesting to use "SELECT *" in all of your queries. If you need only 2 fields of a table w/ 150 fields, why would you do so, right? But, if you need all those fields (w/c is possible in many applications), it is not logically incorrect to use "SELECT *" rather than typing those field names one-by-one (w/c is a cumbersome undertaking). If there is a performance penalty in using "SELECT *", that is not the fault of "SELECT *" as such, but of the current optimizer of the database. "SELECT *" is a mere syntactic construct w/ is in the end processed by the DBMS's optimizer. Some DBMS might handle it alright, and some might not. So, the dogma that "SELECT * is evil (performance-wise)" is not absolute (i.e., it is false in many other instances). Caveat lector. Caveat emptor.

Why exactly is it a "shock horror"? You see, the designers of SQL introduced "*" as a convenient shorthand to reference all the attributes of a relation (or table reference). As such, it is practically meant to reduce typing. No other benefit should be expected to be derived from it, as intended. If the optimizer of a DB is reliant upon such construct to improve performance, well that is it's "limitation", w/c ought to be improved in future releases. Any "dogma" w/c says to avoid the "*" construct is contingent upon the current state of affairs of optimisers. If in the future optimisers are improved such dogma will no longer be true. I understand of course that we are limited with what we have. Thus avoiding the "*" is justified for performance reasons; but only that, in essence they are not "evil" or some plague that ought to be avoided. Logically speaking. What exactly are you referring? The efficiency I was hinting is quantifiable. Thus I presented the "typing that can be avoided." Well, I was sorry for not stating my assumptions. IMO, such assumption is obvious. Why would you want to add a field if it will not be referenced (one way or another)? Why is it ludicrous? Did you experience all scenarios that every developer in this universe did experience to be able to say that such scenario is ludicrous? I doubt that. Again, Why would you want to add a field if it will not be referenced (one way or another)? You have not grasped what I meant, and thus it would be a waste to explain it again. Understand it first before making any opinions about it. That would be wise. Thank you and hope it helps.

W/c means that it is not the fault "SELECT *" but of "incorrectly designed tables." Of course, avoiding "SELECT *" in those cases will make "sense". But that is merely a "workaround" w/c ought to be temporary. It's most practical if we do not address the effect of a problem but the cause of it (the problem itself). Because by then further problems are avoided because the main problem was addressed. In short, DB design also plays a role here.

Ah, there was a fundamental error in my answer: It says: But really: Case 1: Using SELECT * a.) If you added a new field, you need NOT modify your query AND you will modify code that references such fields = 1 Edit b.) If you removed/changed a field, you need NOT modify your query AND you will modify code that references such fields = 1 Edit Thus: 2 "edits." all in all. Case 2: Not Using SELECT * (explicitly typing) a.) If you added a new field, you need to modify your query AND you will modify code that references such fields = 2 Edits b.) If you removed/changed a field, you will modify your query AND you will modify code that references such fields = 2 Edits Thus: 4 "edits." all in all. So Case 1 is more efficient by two units (2 edits) than Case 2. Now if you need to remove/modify/add fields of 100 tables, Case 1 is more efficient by 200 query-edits than Case 2 (100 queries X 2 edits). IMO, your question is adequately answered by now.

Practical Argument Case 1: Using SELECT * a.) If you added a new field, you need NOT modify your query AND you will modify code that references such fields = 1 Edit b.) If you removed/changed a field, you will modify your query AND you will modify code that references such fields = 2 Edits Thus: 3 "edits." all in all. Case 2: Not Using SELECT * (explicitly typing) a.) If you added a new field, you need to modify your query AND you will modify code that references such fields = 2 Edits b.) If you removed/changed a field, you will modify your query AND you will modify code that references such fields = 2 Edits Thus: 4 "edits." all in all. So Case 1 is more efficient by a unit (1 edit) than Case 2. Now if you need to modify/add fields of 100 tables, Case 1 is more efficient by 100 units than Case 2 (100 X 1). IMO, your question is adequately answered by now. "SELECT *" is SQL's implementation of the relational PROJECT operator/project (again, SQL was almost successful in implementing it). Meaning "SELECT *" is an operator/function for type RELATION. "+" is most Programming Languages' implementation of the arithmetic ADDITION. Meaning, "+" is an operator/function for type RATIONAL (or REAL). Thus they are the same in the sense that they are operators for a certain data type. Thus, if they are used (PROJECT and +) in a computer program, in essence, they are the same: in the sense that they are operator/function invocation. They take an input and spits an output. In the case of "SELECT *"/PROJECT, it takes an input of type RELATION/TABLE, and spits an ouput of type RELATION/TABLE. In the case of "+", it takes an input of type RATIONAL(REAL), and spits an ouput of type RATIONAL(REAL). That is the sense in w/c I "likened" them, FYI. All this is because of relational algebra, not me. If you have a problem w/ that, you can always air your complaints to the authors of relational algebra. No I don't. I'm a technical guy, so I like being lazy. If there is an efficient way to do things, I will do it, so that my employer will be happy, and he will pay me more. If laziness == more profit then I like being lazy. I hope that is a better argument. Thank you and hope it helps.

@OP SELECT * is just fine. SELECT * is equivalent (almost) to the PROJECTION operator in the original relational algebra. Advice that warns against its use, is like advice not to use the "+" operator. I don't see what is the basis (logical) of that advice. If in production SELECT * gives problems, it is not the fault of SELECT * per se, but the fault of non-updated existing code. Using it is not being lazy; it is about using features that were placed there because of its practicality.

Sorry about that, if I took a part in it. Yes, I have an opinion. I assume that you want your code to still work, even if the underlying DB is changed from say MySQL to PostGre. That you do NOT want to modify your source code when you migrate from one DB to another? Is that right? If "no" stop reading. If "yes" I will explain my suggestion to you. So to answer your question: "Abstraction Layers?" My answer: Use a common language and data construct. Adopt what the pioneers did 40 years ago. You see, the main reasoning is this: The original intention of the pioneers in DBMS is to create an abstract programming language that is suited for data manipulation. Their idea was that instead of contenting ourselves w/ the usual data types (INTEGERS, STRINGS), why not add a data type that is suited for manipulating data? E.F. Codd then introduced the RELATION data type. Further, additional operators/functions are then introduced to manipulate values (including values in a variable reference) of the type RELATION: JOIN, UNION, MINUS, etc. Analogously, if there is a "+" operator for INTEGERS, there is a UNION operator for RELATIONs, and so on. Thus the relational algebra was developed (the relation data type plus the operators). The nice consequence of having an abstract language of manipulating data is that users/programmers are rid of technical details that are involved when manipulating data. Data manipulation then is practically "abstracted". For instance, if you want to compute the tuples/rows in relation variable (relvar/table) A that are not in relvar/table B, you simply: A MINUS B (Exercise for the reader: implement this in SQL) You need not worry on indexes, the kind of loop to use, the source of such data, etc, etc. ( Of course in practice you will concern yourself w/ performance; but that would be in administration/maintenance part; not on the actual product development and modelling. In practice, too, the DBMS optimizer will automatically help the programmer in optimizing his/her code In theory, if you want to improve performance, there is no need to adjust and recompile your source code! Simply adjust the settings "under the hood" so to speak, w/c is the Physical Layer of the DBMS ). So, how did they do it in the field of relational DBMS 40 years ago? They created an abstract language w/c is now usually referred as Relational Algebra. All you have to do is to implement such abstract language, say, in PHP (or in any language), and you will have an abstraction layer between/across databases or sources of data (say, one of the web services by Google) I posted this reply for future for your future reference.. Thank you and hope it helps.

"Closest" branch to what? Being "close to something" is a relative relation. Thus, I would assume that you have a "base" location (serving as the reference point.) You can start by modeling all locations as three-attribute relation (table), say: name_of_place string, x_axis decimal, y_axis decimal The idea is to represent the possible as points in a Cartesian plane. Finding the closest location from a given reference point is probably solve-able using techniques in geometry. And application of geometric techniques can be achieved, IMO, in SQL. You might also want to look at existing algorithms for "The Traveling Salesman Problem" and/or "Shortest path problem". Hope it helps.