Re: Concept help required

> > =ex-Navy guys are always "at sea" - however they never let me near the > ships, but I guess that's another > > story... > ex RAF myself (almost 30 years ago though), so I suppose I might be all 'up > in the air'? =there's a bunch of fast-mover jockies and helo boys who never have to pay for their drinks when any of my guys are around... > > =correct MySQL does not CURRENTLY have stored procedures, but it is under > active discussion (too late for us, > > but then...). > > > > =many people have a very shallow understanding of SQL - particularly [he > says generalising like crazy] people > > who 'fall into it' from (say) PHP programming. Indeed my own initial > training course [mumble, mumble] years ago > > majored on SELECT, charged through INSERT and DELETE, and settled lightly > on DML. However there is enormous > > power in the SELECT statement that belies the usual course topics of > SELECT *... and SELECT colName, colName, > > ... and a bit of format control/changing the column names/labels. In my > training course, and many others I've > > seen since, token gestures are made so that even throwing in MAX(), MIN(), > and AVG() seems more an illustration > > of (the more narrow) GROUP BY clause than it does of the SELECT statement. > [rant, rave,...] This shallow > > understanding means that 'they' will tend to do too much in PHP (assuming > they know it better) in preference to > > SQL - at a cost of efficiency/execution time. > > > > =let's make this answer a 'game of two halves': firstly, if you followed > my earlier point, after producing ELH > > diagrams, (my)/the next step is to start writing SQL queries. Thus one > tries to pack as much functionality into > > the SQL statement, as is possible. Each SQL query will feed some response > 'back' to the PHP code (that in the > > finished product, will first call it). Thus if you throw together the > system's SQL calls in a previous > > development step, the only PHP functionality required is that which cannot > be accomplished within MySQL - so my > > terminology may be flawed/deceptive, it is not that I'm taking stuff out > of the PHP code (I haven't written any > > yet/at this stage), it's that it never gets in there in the first place! > Remember the mantra: prevailing wisdom > > says that if you have a choice of doing something in SQL or PHP, do it in > SQL. > > > > =there's an interesting problem on the list (in fact both PHP and PHP-DB) > posed by Brian Tully "need help > > looping through each record with a query -stumped". It is a much > smaller/self-contained example than your own. > > He has presented his 65-line, mainly-PHP code in his statement of the > problem. It provided a brain-starting > > challenge for me this morning, and I have opened my big mouth to suggest > that we could get it down to a much > > less complex single SQL call and one or two nested loops of PHP. To do > this, I have requested some clarification > > of the business rules governing his case. If it suits you, and assuming he > gets back to me, I will work through > > it. Could we then use this as an example of how to shift functionality out > of PHP (the 'middle box') and into > > SQL (the 'left-hand box')? > > Yes, please do. But if I could interject with a sub-concept question. Much > of what I will be scratching my head about can probably be achieved with SQL > as it pertains to data held. Can this fuctionality be built into MySQL or is > it more a case of still doing it all in SQL but the SQL 'script' resides in > the .php page? Just trying to see the trees instead of the wood. =Sorry, misunderstood the 'level' of your question/comment... =Yes you are correct, the SQL code 'resides'/is kept within the PHP code. Borrowing an example (and editing a little) from Brian's post: $query = "SELECT Month, Score FROM scores WHERE Username = '$Username' " ; $result = mysql_query($query2) or die( "Cannot execute query" . mysql_error () ) ; =This is PHP code. The first line of which builds a SQL SELECT statement where the contents of the PHP variable $Username will be substituted inside the single quotes, eg SELECT Month, Score FROM scores WHERE Username = 'George Pitcher' =The second line throws the query at MySQL and receives two results by return. Firstly the logical: "did the call work or not?" which may fire the 'or' clause (returning an error msg and number); and secondly the handle of the MySQL resultset. (the next step being to retrieve the actual data, as required/appropriate from the resultset) =So at the code-level, the SQL commands/script is/are contained within the .php page. =At the logic level, the functionality to retrieve only the single record (that fulfills the stated criteria) from amongst all those in the database, is contained within the SQL command. =but I'm still not sure which one is "wood", and which "trees"! =Ok now? =dn