Re: [PHP-DB] Concept help required
David, =I'm feeling a little 'at sea' here, because we had established that you want to learn more about joining, yet you recognise the word normalisation straight off. I don't want to insult you by 'talking down'/teaching grandma to suck eggs... Please be sure it is not another one of those words that has a particular definition in the relational world, but was subjugated by wiley marketing people at FM to mean something 'just a little different' (alternatively that my ignorance of the product is the issue). Feel free to realign my approach... No need to feel at sea. After an earlier response from either you or Miles, I read up on normalisation. =Continuing on, in such a situation I am always inclined to try to 'cut corners' (against the 'standard' structured analysis/book approach) - bet this comment gets some of our list colleagues gagging, already thinking I'm long-winded in my methods. Because, as an outsider, I would not have such an intimate understanding of the data items and their inter-relationships as you do, I would probably attempt to take the existing data structures (the tables, and the list of fields/columns in each) and restate/extend these into something called a ELH diagram (entity life history) - the theory of which you will find in any competent structured analysis and design or SSADM text (the latter relevant to your location, SSADM being a British Government initiative, if you'll pardon the oxymoron). I have done these flow charts in the past to demonstrate how my current system should work at a higher level. =The purpose of an ELH diagram is to take a piece of data (in your case, because we are assuming/checking normalisation, I'd 'cheat' and work at the table level - rather than something more atomic like the data-item level). Once again we draw boxes (I have some wonderful s/w for doing these tasks, but it is M$). A single Visio? label/box at the top, featuring the name of the data-unit, and I would guess a minimum of three (must be my favorite number!) boxes in the next row, representing the arrival/creation of the data, its use within the system, and the removal of the data from the system once its usefulness has subsided, respectively. The third row of boxes represents the actual, individual events in the life-cycle/daily operation of the system, and how they alter/update/use the data. Lines drawn between the boxes show how these events relate and where there may be some iteration. =This is an analysis/checking tool. As such it bores the socks off most techies. However it is most useful to ensure that data is being used properly, and for a consistent purpose. In theory it can also be used to check the data structure because by following the uses to which a piece of data is put during its life, you can ensure that it is being represented using the 'best' data type for the purpose. It can certainly be used to ensure that you have the best design of relationships between multiple data items - both in the single table/normalisation sense, but most especially in the relationships between tables. As I go, my diagrams accumulate a bunch of notes around the margins with lead-out lines heading back into/from a point in the diagram - reminding me to check things as the design (or in your case, verification) proceeds. Methodically iterating across dozens of diagrams, the notes are removed - and the system data coalesces. =One of the great things about doing this, is that in examining where the data is coming from, going to, and how it is being used; you are also making a list of all of the db queries that you will need to contain within your system - yes you've guessed it, I have another bunch of boxes scattered around my diagrams (in a different color, just to be petty, er, pretty) which note these needs. If you 'get into' a design book, they will talk about other related diagrams, eg data-flow diagrams, which would normally be used to contain much of the info I'm putting into margin-notes - like I said, I'm short-cutting and I think you'll also get away with it because of your level of knowledge. Feel free to disagree (either way). I will be going through the stages you have mentioned to map out the processes and data requirements. I think that I know the process enough to do this without reference to the 'users' considering that at present this is for purely self-development purposes. =Once the diagrams are finished, or for a little light relief (of the diagram boredom) as I go along, I then use a db admin tool for MySQL to start building the SQL code, and testing them against some sample data. Yes, I could use the MySQL command line, but I am a fan of MySQL-Front because as well as the stark content of diagrams and the code-view of the SQL DDL statements, the package gives me a visual representation of the columns/data - another opportuntity for any incongruities to penetrate this thick skull... Oh, and you've probably guessed
Re: [PHP-DB] Concept help required
You wouldn't happen to have a spare ELH diagram (or two) lying around that one could take a look at, would you? Hah TIM now you've got me! I 'do' diagrams. I don't 'do' neat! [no need to move over Matisse] The longer answer is that I have diagrams coming out of my ears (well, computer), but they are very much working documents, rather than something fit for publication - indeed fit for showing to someone else at all... I (somewhat feebly) suggest that you would do better to grab a book on structured analysis and design from a convenient library. In the 'old days' I would have recommended Yourdon or Jackson, but many have entered the field/published since. Such will have example diagrams chosen for the simplicity of the subject matter, and carefully/logically explained - that's three times better than anything I can (quickly) offer... In addition, they will mention the three or four main diagram types that can be employed in SA and D, which may be yield additional benefits. I fear my stuff, assuming I can weed out something that is both reasonably straightforward and that will 'stand' on its own, will not be quite so 'inspirational'. With apologies... Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
Olinux, Haven't been following and not sure if this is what you're after, but these are some great data models: http://www.databaseanswers.com/data_models/index.htm Thanks for this. I came across this site/these diagrams before, but thought them to be too 'introductory' and vague for use 'in anger', and without commentary not particularly useful as a tutorial (unless one is really determined) Unfortunately they [that I looked at again today] are also in the form of ERDs (entity relationship diagrams) rather than ELH diagrams (which we had been discussing). So I Googled entity life history and diagram... The first hit took me to SmartDraw.com. Their site was good because it shows examples of different types of diagrams, but it is all academic-theoretical/no worked real-life examples. Poking around the site (or duh! using the second Google hit) I did find a fairly simple worked example of an ELH diagram. Tim: these guys are pushing SSADM, a British government initiative from 15-20 years back (no less valid for its age). You (and others in the States) may or may not find it easy to get hold of SSADM stuff, simply because it didn't become popular in the States. A bit further down, Google offers http://www.cscs.wmin.ac.uk/~ssadm/elh/elhs.html which is a lecture from the University of Westminster and part of the/an SSADM organisation's site. It has the sort of worked-descriptions that I mentioned (to Tim) earlier. Rumbling around on their site will also enable the study of other useful design tool/method tutorials - and may save/justify a trip to the library! Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
Hi George, =I'm feeling a little 'at sea' here, because we had established that you want to learn more about joining, yet you recognise the word normalisation straight off... Feel free to realign my approach... No need to feel at sea. After an earlier response from either you or Miles, I read up on normalisation. =ex-Navy guys are always at sea - however they never let me near the ships, but I guess that's another story... level). Once again we draw boxes (I have some wonderful s/w for doing these tasks, but it is M$). A single Visio? =yes - but as part of my venturing into LAMPs, I have been pointed to an equivalent package under Linux. =One of the great things about doing this, is that in examining where the data is coming from, going to, and how it is being used; you are also making a list of all of the db queries that you will need to contain within your system ... I will be going through the stages you have mentioned to map out the processes and data requirements. I think that I know the process enough to do this without reference to the 'users' considering that at present this is for purely self-development purposes. =understood - and so whilst you may not 'improve' the system design (the primary objective when developing on a 'green field' site) it will help in your conversion to SQL/relational technology, as per below. =Once the diagrams are finished, or for a little light relief (of the diagram boredom) as I go along, I then use a db admin tool for MySQL to start building the SQL code, and testing them against some sample data... One area where I remain a bit thick is when it comes to moving the functionality from the middle (php) box to the left (MySQL) box. How is it done in MySQL? Is it a case of restructuring the queries to handle the calculation or is there a pseudo-Stored Procedures (cos I know that MySQL doesn't use Stored Procedures)? =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')? However, my job here depends on the service succeeding in raisng funding for continuation beyond July this year else I'll be looking for a company willing to employ someone with a good overview and no extensive skills! =powerful motivation indeed. Let's see what we can do... =dn -- PHP Database Mailing List (http://www.php.net/) To
Re: [PHP-DB] Concept help required
David, =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'? level). Once again we draw boxes (I have some wonderful s/w for doing these tasks, but it is M$). A single Visio? =yes - but as part of my venturing into LAMPs, I have been pointed to an equivalent package under Linux. =understood - and so whilst you may not 'improve' the system design (the primary objective when developing on a 'green field' site) it will help in your conversion to SQL/relational technology, as per below. I'm sure it will. =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. George --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.307 / Virus Database: 168 - Release Date: 11/12/01 _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL
Re: [PHP-DB] Concept help required
David, =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... Air Traffic Control was my area, though we did have to put up with some of your lot when Ark Royal was in dock as we were a Buccaneer base. =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? Yes, that clarifies a lot. I can stop looking for some place where it might exist in MySQL and concentrate on the design until you are ready for the SQL building. I think that some of my queries might be a bit of a challenge and might require revisiting the design and data structure. I'm not looking for anyone to solve my problems, just to help me along while I learn the basics and then the tricks. George --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.307 / Virus Database: 168 - Release Date: 11/12/01 _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
** Reply to note from DL Neil [EMAIL PROTECTED] Thu, 10 Jan 2002 14:48:02 - many people have a very shallow understanding of SQL - particularly [he says generalising like crazy] people who 'fall into it' from (say) PHP programming... 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. WOW! I was beginning to think I was the only one around here who thought that way. Here is a sample query that does most of the work on generating invoices for a job search site. Lines in the code that don't have around them are comments I just added... $R = query( SELECT Managers.ManagerID, NameFirst, NameLast, . Managers.Email, Managers.Phone, Managers.Fax, . PayDesc, PayApproved, . Employers.EmployerID, Name, Motif, . Positions.PositionID, PONumber, Memo, . IF( Title = '', 'No Title', Title ) AS Title, . if the title field is blank, replace it with 'No Title' IF( ''=Managers.Mail, Managers.Phys, Managers.Mail ) . AS Address, . if the manager has a mailing address use it, else use the physical address. DATE_FORMAT( DateActive, '%b-%e-%y' ) AS DateActive, . IF( DateClosed, . DATE_FORMAT( DateClosed, '%b-%e-%y' ), . '--Cont--' ) AS DateClosed, . If DateClosed is blank, say the position is continuing in the closed field. TO_DAYS( DateActive ) AS Active, . TO_DAYS( DateClosed ) AS Closed . COUNT(*) AS Count . Count how many job seekers have responded to the ad so we can brag about it on the bill. FROM PaymentMethods . LEFT JOIN Managers USING( PayMethod ) . LEFT JOIN Employers USING( ManagerID ) . LEFT JOIN Positions USING( EmployerID ) . LEFT JOIN Links USING( PositionID ) . WHERE PayMethod = 1 . Don't bill credit card custmers. (Bill Me only) AND (( TO_DAYS( Positions.DateCreate ) = $EOM . AND TO_DAYS( DateClosed ) = $BOM ) . Only bill for positions that were visible this month. BOM = TO_DAYS() of the first of this month, EOM = end of month. I do a query before this one just to get these values as I didn't want to try to reverse engineer the TO_DAYS() function in MySQL. OR ( PositionID IS NULL ) . Ignore entries which have no positions at all OR ( Employers.EmployerID IS NULL )) ; Ignore entries with no employer record GROUP BY NameLast, NameFirst, Name, Title . ORDER BY NameLast, NameFirst, Name, Title . '' ); I like the way this is coded into PHP because it allows me to look at the SQL and ignore the PHP code around it very easily. After this query I run thru the data with control breaks on Manager (NameLast, NameFirst), the employers they manage (Name) and the name of the position I am billing on. The data is formatted as an IIF file for import into QuickBooks which prints fancy invoices for each manager. Earlier when we billed by the days the ad was up and calculated the billing amount in the query, but we changed to billing with a two week block followed by exess days and it ended up being easier to figure that out with PHP. I wish I could find a copy of the old query, it was about a page and a half (36 lines) long. It sure makes the PHP code simple! Note: managers can have more than one Employer they control, and Employers can have more than one position on the site, and they can create and close them at any time they want. Query() is a little function I wrote that wraps mysql_query() and error handling code so I don't have to look at it whem I'm writing programs. Rick Rick Widmer Internet Marketing Specialists http://www.developersdesk.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
At 12:53 PM + 1/7/02, George Pitcher wrote: One of the features of the FMPro solution is that when a user is looking at a list of resources (in fact, university course packs) each row will display the number of associated records from the 'books' database. Now which is the best method to do this (speed/efficiency)? I have tried performing a row-level query on the related db but its very slow and times out before the second row can be displayed. Alternately, I could set the main db to increase/decrease a number field when adding or deleting books from the list. Which is recommended? Not to interrupt with the current discussion, because some of the issues brought up, will eventually be needed by you. But for the issue at hand, what you want is SQL. I, too, am an avid FileMaker Developer, who uses PHP/MySQL or FileMaker/Lasso, FileMaker/PHP for web-based solutions. I very much miss my FileMaker calculations, but SQL can handle many of them. I actually find Filemaker's handling of string functions (Filemaker calls them text functions) more flexible, and full-featured then SQL. But with a lot of practice in SQL, you can replicated (recreate FileMaker level functionality) by using SQL. For your situation, it sounds like a calculation like the one below will work. Not knowing what you considered resources, I've basically mentally associated to categories. Lets state that you have a library table that stores books, articles, etc. A category table that stores categories. And a join table called media_cat that stores all relations between library and category. That being true every library_item can have one or more categories, and you wish to create a count of the number of items in each category. The following query will suffice, and should be adaptable for your situation: SELECT library.type, media_cat.category, categories.description, COUNT(library.type) AS count FROM categories, media_cat LEFT JOIN library ON library.libraryID=media_cat.fileID WHERE categories.category=media_cat.category GROUP BY media_cat.category ORDER BY media_cat.category The results from this could be formatted to display like so: Communications (with 9 documents) Donor Relations (with 3 documents) or like so: * databases (with 2 items) applications, query languages, theory, and implementation * networking (with 1 items) networks, telecommunications, protocols, routers, etc. The above explains the more complicated query between three tables, but you can also do this with a single table, or two tables. Alnisa -- . Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . transforming nonprofits through technology . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
Hi Rick, many people have a very shallow understanding of SQL - particularly [he says generalising like crazy] people who 'fall into it' from (say) PHP programming... 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. WOW! I was beginning to think I was the only one around here who thought that way. Here is a sample query that does most of the work on generating invoices for a job search site. Lines in the code that don't have around them are comments I just added... =Thank you for the kind words. It has been an interesting day, and Brian's problem has certainly caused me to revisit the above claim. I'm hoping I've got a solution for him (dependent upon assumptions about 'business rules'), but he will be the judge of that. Thereafter, knowing the db/tbl is so badly set up, it would be quite interesting to run a comparison between 'problem fixed using PHP' and 'problem fixed using MySQL' to see what the relative efficiencies/speed really is. Of course we would then have to 'correct' the db structure and re-try the tests, too. If you're interested perhaps we could cook up some ideas between us? =I have read through the impressive query below. It is an excellent example of code that (I suspect) many would have implemented partly/substantially in PHP. Well done! =I think that it causes me to suggest that my earlier comment (per top of this msg) was only partly thought through, in that having clearly thought out/documented 'business rules' coming out of your design phase has enabled you to collect together the query. In other words, it is likely to be all-but impossible to conceive of such a query unless one uses a top-down design approach. What would be your comment? =I particularly liked the way that the 'business rules' have been embodied into the query. You say comments I just added, do you mean that these are not actually present in the PHP code? Many people don't realise that you can embed comments in MySQL queries, both the /*...*/ and the #-from-here-to-the-end-of-the-line conventions work. Accordingly all of those comments could be permanently built-in as in-line documentation. =Well done! =dn PS there's a small offering at the end of this msg $R = query( SELECT Managers.ManagerID, NameFirst, NameLast, . Managers.Email, Managers.Phone, Managers.Fax, . PayDesc, PayApproved, . Employers.EmployerID, Name, Motif, . Positions.PositionID, PONumber, Memo, . IF( Title = '', 'No Title', Title ) AS Title, . if the title field is blank, replace it with 'No Title' IF( ''=Managers.Mail, Managers.Phys, Managers.Mail ) . AS Address, . if the manager has a mailing address use it, else use the physical address. DATE_FORMAT( DateActive, '%b-%e-%y' ) AS DateActive, . IF( DateClosed, . DATE_FORMAT( DateClosed, '%b-%e-%y' ), . '--Cont--' ) AS DateClosed, . If DateClosed is blank, say the position is continuing in the closed field. TO_DAYS( DateActive ) AS Active, . TO_DAYS( DateClosed ) AS Closed . COUNT(*) AS Count . Count how many job seekers have responded to the ad so we can brag about it on the bill. FROM PaymentMethods . LEFT JOIN Managers USING( PayMethod ) . LEFT JOIN Employers USING( ManagerID ) . LEFT JOIN Positions USING( EmployerID ) . LEFT JOIN Links USING( PositionID ) . WHERE PayMethod = 1 . Don't bill credit card custmers. (Bill Me only) AND (( TO_DAYS( Positions.DateCreate ) = $EOM . AND TO_DAYS( DateClosed ) = $BOM ) . Only bill for positions that were visible this month. BOM = TO_DAYS() of the first of this month, EOM = end of month. I do a query before this one just to get these values as I didn't want to try to reverse engineer the TO_DAYS() function in MySQL. OR ( PositionID IS NULL ) . Ignore entries which have no positions at all OR ( Employers.EmployerID IS NULL )) ; Ignore entries with no employer record GROUP BY NameLast, NameFirst, Name, Title . ORDER BY NameLast, NameFirst, Name, Title . '' ); I like the way this is coded into PHP because it allows me to look at the SQL and ignore the PHP code around it very easily. After this query I run thru the data with control breaks on Manager (NameLast, NameFirst), the employers they manage (Name) and the name of the position I am billing on. The data is formatted as an IIF file for import into QuickBooks which prints fancy invoices for each manager. Earlier when we billed by the days the ad was up and calculated the billing amount in the query, but we changed to billing with a two
Re: [PHP-DB] Concept help required
David, =Prevailing wisdom in system design suggests that the data should be 'designed' first, and 'code'/processing only later (relational or structured design philosophy, even object-oriented design). Accordingly I recommend considering which parts of your current files should be converted into MySQL tables, and what might need to be added/subtracted to ensure that the relationships between tables is adequately expressed/because that makes other 'old data' unnecessary. As you would seem to have identified your data, and grouped/categorised it into tables, you might be able to go straight into the process of 'normalising' your data - a series of steps/techniques which enable you to analyse the data and structure it into a 'relational' form. (if you are not familiar with this term: it's back to the books) Actually the Filemaker system is fairly 'normalised' having been developed over a couple of years, our previous web developer (commercial partner) pulled out of our service an I stepped in to deliver the goods and in doing so, went through the analysis and re-design process. It may well be that there could be some marginal improvement in doing it again. I'll have a look anyway - its part of the learning process. =Have I misunderstood? It seems to me that you are not offering this data to the web, ie I can't get to it; you are only offering it to the copyright fee-paying clients. Hence the publishers' argument seems illogical/ignorant... Yes, exactly, but they control what happens and we are not mature enough as a service to hit them over the heads yet. I run a dual site with a main 'Live' service and a Training service allowing users to play with the processes before they get near the real thing. =and now a third environment: for development, and a fourth: for system testing... I did omit to say that my server hosts a development solution (Filemaker/Lasso) and that as well as the dev databases being on my laptop, so is the MySQL/PHP solution. New laptop expected within a couple of weeks and this one will then be switched to Linux. Regards George --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.307 / Virus Database: 168 - Release Date: 11/12/01 _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
George, =I was pleased to hear from Miles. There are likely to be as many suggestions for how things should be done, as there are developers. I'll be interested to hear from others - personally (cf on the list) if the comments might 'interfere'/argue a case (and thus risk muddying your waters) or 'in public'. =Prevailing wisdom in system design suggests that the data should be 'designed' first, and 'code'/processing only later (relational or structured design philosophy, even object-oriented design). Accordingly I recommend considering which parts of your current files should be converted into MySQL tables, and what might need to be added/subtracted to ensure that the relationships between tables is adequately expressed/because that makes other 'old data' unnecessary. As you would seem to have identified your data, and grouped/categorised it into tables, you might be able to go straight into the process of 'normalising' your data - a series of steps/techniques which enable you to analyse the data and structure it into a 'relational' form. (if you are not familiar with this term: it's back to the books) Actually the Filemaker system is fairly 'normalised' having been developed over a couple of years, our previous web developer (commercial partner) pulled out of our service an I stepped in to deliver the goods and in doing so, went through the analysis and re-design process. It may well be that there could be some marginal improvement in doing it again. I'll have a look anyway - its part of the learning process. =I'm feeling a little 'at sea' here, because we had established that you want to learn more about joining, yet you recognise the word normalisation straight off. I don't want to insult you by 'talking down'/teaching grandma to suck eggs... Please be sure it is not another one of those words that has a particular definition in the relational world, but was subjugated by wiley marketing people at FM to mean something 'just a little different' (alternatively that my ignorance of the product is the issue). Feel free to realign my approach... =Continuing on, in such a situation I am always inclined to try to 'cut corners' (against the 'standard' structured analysis/book approach) - bet this comment gets some of our list colleagues gagging, already thinking I'm long-winded in my methods. Because, as an outsider, I would not have such an intimate understanding of the data items and their inter-relationships as you do, I would probably attempt to take the existing data structures (the tables, and the list of fields/columns in each) and restate/extend these into something called a ELH diagram (entity life history) - the theory of which you will find in any competent structured analysis and design or SSADM text (the latter relevant to your location, SSADM being a British Government initiative, if you'll pardon the oxymoron). =The purpose of an ELH diagram is to take a piece of data (in your case, because we are assuming/checking normalisation, I'd 'cheat' and work at the table level - rather than something more atomic like the data-item level). Once again we draw boxes (I have some wonderful s/w for doing these tasks, but it is M$). A single label/box at the top, featuring the name of the data-unit, and I would guess a minimum of three (must be my favorite number!) boxes in the next row, representing the arrival/creation of the data, its use within the system, and the removal of the data from the system once its usefulness has subsided, respectively. The third row of boxes represents the actual, individual events in the life-cycle/daily operation of the system, and how they alter/update/use the data. Lines drawn between the boxes show how these events relate and where there may be some iteration. =This is an analysis/checking tool. As such it bores the socks off most techies. However it is most useful to ensure that data is being used properly, and for a consistent purpose. In theory it can also be used to check the data structure because by following the uses to which a piece of data is put during its life, you can ensure that it is being represented using the 'best' data type for the purpose. It can certainly be used to ensure that you have the best design of relationships between multiple data items - both in the single table/normalisation sense, but most especially in the relationships between tables. As I go, my diagrams accumulate a bunch of notes around the margins with lead-out lines heading back into/from a point in the diagram - reminding me to check things as the design (or in your case, verification) proceeds. Methodically iterating across dozens of diagrams, the notes are removed - and the system data coalesces. =One of the great things about doing this, is that in examining where the data is coming from, going to, and how it is being used; you are also making a list of all of the db queries that you will need to contain within
RE: [PHP-DB] Concept help required
You wouldn't happen to have a spare ELH diagram (or two) lying around that one could take a look at, would you? Thanks. TIM -When you save for a long time to buy something, then you find that you can't afford it --- that's inflation. -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 7:23 AM To: George Pitcher; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Concept help required =The purpose of an ELH diagram is to take a piece of data (in your case, because we are assuming/checking normalisation, I'd 'cheat' and work at the table level - rather than something more atomic like the data-item level). Once again we draw boxes (I have some wonderful s/w for doing these tasks, but it is M$). A single label/box at the top, featuring the name of the data-unit, and I would guess a minimum of three (must be my favorite number!) boxes in the next row, representing the arrival/creation of the data, its use within the system, and the removal of the data from the system once its usefulness has subsided, respectively. The third row of boxes represents the actual, individual events in the life-cycle/daily operation of the system, and how they alter/update/use the data. Lines drawn between the boxes show how these events relate and where there may be some iteration. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Concept help required
Haven't been following and not sure if this is what you're after, but these are some great data models: http://www.databaseanswers.com/data_models/index.htm HTH olinux --- Tim Foster [EMAIL PROTECTED] wrote: You wouldn't happen to have a spare ELH diagram (or two) lying around that one could take a look at, would you? Thanks. TIM -When you save for a long time to buy something, then you find that you can't afford it --- that's inflation. -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 7:23 AM To: George Pitcher; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Concept help required =The purpose of an ELH diagram is to take a piece of data (in your case, because we are assuming/checking normalisation, I'd 'cheat' and work at the table level - rather than something more atomic like the data-item level). Once again we draw boxes (I have some wonderful s/w for doing these tasks, but it is M$). A single label/box at the top, featuring the name of the data-unit, and I would guess a minimum of three (must be my favorite number!) boxes in the next row, representing the arrival/creation of the data, its use within the system, and the removal of the data from the system once its usefulness has subsided, respectively. The third row of boxes represents the actual, individual events in the life-cycle/daily operation of the system, and how they alter/update/use the data. Lines drawn between the boxes show how these events relate and where there may be some iteration. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
George, =As a general comment, it is always dangerous to replicate when shifting platforms, better to reverse engineer and then implement anew and taking advantages of the strengths of the new tools. This particularly when moving into the relational field... Perhaps the use of the word 'replicate' was wrong. I am in fact re-engineering based on my knowledge of how the whole operation is performed (as I designed and built the original Filemaker/Lasso system) and trying to preserve the look and feel of the web pages. =makes perfect sense =you will need to describe the 'internal calculations' before this question can be easily/sufficiently answered. However many people fail to appreciate that the (My)SQL language offers a lot of power/functionality. In your case you are going for the PHP combination so I will be quite surprised if you 'run out' of functionality! The original Filemaker (FM) databases use calculations stored internally. You define a field to store the result of a calculation. This could be something quite complex or a static number (or string) or data from a related database. Filemaker requires a separate database to represent the equivalent of a table in standard SQL databases. Some calculation fields can be indexed and some cannot (esp those containing related data). I expect that I can replace these calculations with functions which I define. =it sounds as if you are still getting to grips with the advantages and power that SQL and relational databases bring to 'filing' tasks. There also is a terrible possibility of terminology pollution/confusion. When I last looked at FM (many, many moons ago), I consigned it to file 13 as being too much of a 'shoebox' style 'database', and my being more interested in something PC-ish that would run something more like a table-relational model (if not SQL), eg Paradox or even Access (make signs for protection and mutter incantations to ward off the evil eye...) The problem with 'shoe box' packages was that they prefered single-file solutions - it was difficult/impossible to 'relate' two files, except by producing procedural code in the package's language. Thus the data itself did not define the linkage, as it does in the relational view of the world (which I was more comfortable with coming from a mainframe-view of the world). Relational databases are made up of multiple related tables (for table you can read file, in MySQL). Shoe box databases are files. Thus there is no multiple and without the code no 'relating'. Does this make sense? Is it a fair portrayal? That being the case, you need to jettison your current understandings of some terms and FM concepts, to be able to take on board MySQL and relational technology and terminology... =let's try drawing a picture to 'see' the model you are contemplating using to upgrade your system: imagine three boxes in a row across the page. Label the left-hand one MySQL, the center one PHP, and the one on the right-hand side HTML or browser. We can then add the following functional descriptions: 'holds the data', 'handles the processing/calculations', 'displays output and collects user input'. Working at this level it should be really easy to now add a diagram for the existing FM setup; and further to draw correspondences between 'existing' and 'proposed'. However this is really simplistic, mainly because you can shift a lot of 'calculation' that FM requires/embodies, out of the central box and over to MySQL. For example, the table relationships, eg a link between the name of a 'package' in a list/table of packages, and the names of the (multiple) books within the package in a list/table of books. The 'functionality' can be implemented using the power of the SQL language (see talk of dates etc, below), but much will be 'hidden' within the standard functionality of the RDBMS engine and/or implicit in the relational model. (yes, back to the 'more reading' theme). =If you want to persue this discussion a bit further, could you list the current FM 'databases'/files and (briefly) describe how they 'fit' together? =as you can see, without giving a little more information, it is very difficult to give a satisfactory answer. How about listing your table definitions/schema. Almost any retrieval operation that does not select all of the records in a table will speed up when indexes are employed. If speed is a concern then that argues against the earlier suggestion of PostGres. I think that the table definition list would be far too long for this list. Speed is an issue as at the moment I am doing all this under my own steam in my own time (partly to extend my skills) and hope to be able to persuade my bosses that this would be a beneficial move (they are very conservative). Performance improvements would help. =you are talking as if there are numerous queries. What's wrong with performing a join, or am I missing some significance? No but I
Re: [PHP-DB] Concept help required
DL Neil (I presume you have a first name tucked away inside there), Your comments are appreciated. I am becoming more and more comfortable with what I am doing with MySQL/PHP. With reference to the 3-box trick, I thought that the bulk of my work would be inthe centre box but as you point out that would be inefficient if the work can be pre-processed in the RDBMS box. A simple explanation of my Filemaker system follows. But first, a description of what the service provides might help. Our members (50+ uk universities) can request material (usually book chapters or journal articles) to be delivered electronically (or rarely, by paper) to their students. We handle copyright clearance through the UK rights agency (CLA) and through publishers/authors. We pass the prices back to the clients via the web interface and the client accepts/declines. We then source originals from the British Library which are digitised by a bureau and put into PDF before a front page is attached (currently automated using Applescript but hope to use PDFlib in future) and delivered to the university. We invoice monthly for items completed, not by course. We have a success rate of 60% mainly due to the reluctance of publishers having their material mounted on the web. Now the system: (number of current records in parentheses) Transactions (15000+) [contains a record for each requested extract with workflow and cost information] Course (900+) [Holds data for courses such as student numbers, dates etc] Bib_source (6000+) [Book or journal data held here at title level] Bib_extract (9000+) [Chapter/article level data held] Publishers (18000+) [Rightsholder details data bought in] Customers (50+) [Client details] Buyers (200+) [Individuals who can make purchasing decisions at clients] Illustrations (150+) [Illustrations require special handling and there can be several per extract] Invoices (300+) [data taken from Transactions and Customers to produce PDF invoices] Userlog (4000++) [log of users accessing main system] Weblog (500+) [covers whole site and started in December] Staff (10+) [Staff names, addresses, emails etc] Scanrates [CLA-provided table to store pre-priced material - covers about 40% of requests] Helpdesk-general [General helpdesk alloowing LAN-wide staff access and direct responses to users] Helpdesk-transactional [As above but specifically set up to handle problems about individual transactions] I run a dual site with a main 'Live' service and a Training service allowing users to play with the processes before they get near the real thing. I'll need to leave now to pick the kids up from school as the wife is ill. I'll see your comments tomorrow when I get back in. Regards George - Original Message - From: DL Neil [EMAIL PROTECTED] To: George Pitcher [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 08, 2002 1:07 PM Subject: Re: [PHP-DB] Concept help required George, =As a general comment, it is always dangerous to replicate when shifting platforms, better to reverse engineer and then implement anew and taking advantages of the strengths of the new tools. This particularly when moving into the relational field... Perhaps the use of the word 'replicate' was wrong. I am in fact re-engineering based on my knowledge of how the whole operation is performed (as I designed and built the original Filemaker/Lasso system) and trying to preserve the look and feel of the web pages. =makes perfect sense =you will need to describe the 'internal calculations' before this question can be easily/sufficiently answered. However many people fail to appreciate that the (My)SQL language offers a lot of power/functionality. In your case you are going for the PHP combination so I will be quite surprised if you 'run out' of functionality! The original Filemaker (FM) databases use calculations stored internally. You define a field to store the result of a calculation. This could be something quite complex or a static number (or string) or data from a related database. Filemaker requires a separate database to represent the equivalent of a table in standard SQL databases. Some calculation fields can be indexed and some cannot (esp those containing related data). I expect that I can replace these calculations with functions which I define. =it sounds as if you are still getting to grips with the advantages and power that SQL and relational databases bring to 'filing' tasks. There also is a terrible possibility of terminology pollution/confusion. When I last looked at FM (many, many moons ago), I consigned it to file 13 as being too much of a 'shoebox' style 'database', and my being more interested in something PC-ish that would run something more like a table-relational model (if not SQL), eg Paradox or even Access (make signs for protection and mutter incantations to ward off the evil eye...) The problem with 'shoe box' packages
Re: [PHP-DB] Concept help required
- From: DL Neil [EMAIL PROTECTED] To: George Pitcher [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 08, 2002 1:07 PM Subject: Re: [PHP-DB] Concept help required George, =As a general comment, it is always dangerous to replicate when shifting platforms, better to reverse engineer and then implement anew and taking advantages of the strengths of the new tools. This particularly when moving into the relational field... Perhaps the use of the word 'replicate' was wrong. I am in fact re-engineering based on my knowledge of how the whole operation is performed (as I designed and built the original Filemaker/Lasso system) and trying to preserve the look and feel of the web pages. =makes perfect sense =you will need to describe the 'internal calculations' before this question can be easily/sufficiently answered. However many people fail to appreciate that the (My)SQL language offers a lot of power/functionality. In your case you are going for the PHP combination so I will be quite surprised if you 'run out' of functionality! The original Filemaker (FM) databases use calculations stored internally. You define a field to store the result of a calculation. This could be something quite complex or a static number (or string) or data from a related database. Filemaker requires a separate database to represent the equivalent of a table in standard SQL databases. Some calculation fields can be indexed and some cannot (esp those containing related data). I expect that I can replace these calculations with functions which I define. =it sounds as if you are still getting to grips with the advantages and power that SQL and relational databases bring to 'filing' tasks. There also is a terrible possibility of terminology pollution/confusion. When I last looked at FM (many, many moons ago), I consigned it to file 13 as being too much of a 'shoebox' style 'database', and my being more interested in something PC-ish that would run something more like a table-relational model (if not SQL), eg Paradox or even Access (make signs for protection and mutter incantations to ward off the evil eye...) The problem with 'shoe box' packages was that they prefered single-file solutions - it was difficult/impossible to 'relate' two files, except by producing procedural code in the package's language. Thus the data itself did not define the linkage, as it does in the relational view of the world (which I was more comfortable with coming from a mainframe-view of the world). Relational databases are made up of multiple related tables (for table you can read file, in MySQL). Shoe box databases are files. Thus there is no multiple and without the code no 'relating'. Does this make sense? Is it a fair portrayal? That being the case, you need to jettison your current understandings of some terms and FM concepts, to be able to take on board MySQL and relational technology and terminology... =let's try drawing a picture to 'see' the model you are contemplating using to upgrade your system: imagine three boxes in a row across the page. Label the left-hand one MySQL, the center one PHP, and the one on the right-hand side HTML or browser. We can then add the following functional descriptions: 'holds the data', 'handles the processing/calculations', 'displays output and collects user input'. Working at this level it should be really easy to now add a diagram for the existing FM setup; and further to draw correspondences between 'existing' and 'proposed'. However this is really simplistic, mainly because you can shift a lot of 'calculation' that FM requires/embodies, out of the central box and over to MySQL. For example, the table relationships, eg a link between the name of a 'package' in a list/table of packages, and the names of the (multiple) books within the package in a list/table of books. The 'functionality' can be implemented using the power of the SQL language (see talk of dates etc, below), but much will be 'hidden' within the standard functionality of the RDBMS engine and/or implicit in the relational model. (yes, back to the 'more reading' theme). =If you want to persue this discussion a bit further, could you list the current FM 'databases'/files and (briefly) describe how they 'fit' together? =as you can see, without giving a little more information, it is very difficult to give a satisfactory answer. How about listing your table definitions/schema. Almost any retrieval operation that does not select all of the records in a table will speed up when indexes are employed. If speed is a concern then that argues against the earlier suggestion of PostGres. I think that the table definition list would be far too long for this list. Speed is an issue as at the moment I am doing all this under my own steam in my own time
Re: [PHP-DB] Concept help required
For DL Neill: Like the 3 box approach. A very strong image for separating the functionality. Why don't you write it up as an article for DevShed or WebMonkey? If you look at the traffic on the list there are lots of people who just see PHP pages as happening and have no clear understanding of what happens and where it happens. For George: You have a very interesting system there. DL is right, don't try to reverse engineer, start with a clean sheet and go from there. You have the advantage of knowing what results you want and the nature of the processing steps. With a relational database you will be freed from the procedural code necessary to fetch data. Use this link for a quick look at Codd's 12 rules which define a relational database: http://www.databaseanswers.com/codds_rules.htm Nice pictures! Take some comfort that there is no RDBMS today which fulfills all these rules, just as none fully comply with the SQL1 and SQL2 standards. Regards - Miles At 01:52 PM 1/8/2002 +, George Pitcher wrote: DL Neil (I presume you have a first name tucked away inside there), Your comments are appreciated. I am becoming more and more comfortable with what I am doing with MySQL/PHP. With reference to the 3-box trick, I thought that the bulk of my work would be inthe centre box but as you point out that would be inefficient if the work can be pre-processed in the RDBMS box. stuff snipped as I'm just passing out bouquets ... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Concept help required
Hi all, In trying to learn PHP (and MySQL), I am attempting to replicate a database-web solution built previously using Filemaker Pro and Lasso (on NT). I am currently working with PHP/MySQL on NT and will be moving this to Linux before the end of January. Filemaker is able to perform calculations internally and therefore I guess I will need to write functions to mimic this externally. Am I right here? One of the features of the FMPro solution is that when a user is looking at a list of resources (in fact, university course packs) each row will display the number of associated records from the 'books' database. Now which is the best method to do this (speed/efficiency)? I have tried performing a row-level query on the related db but its very slow and times out before the second row can be displayed. Alternately, I could set the main db to increase/decrease a number field when adding or deleting books from the list. Which is recommended? Dates I played around with my learning site over the holidays and found that I was not able to easily handle dates between the format required by users (dd/mm/) and that used by MySQL (-mm-dd) and therefore I wrote functions to parse the data both ways. All the example I could find on Dates used 'today' as the example. I want to be able to play around with stored dates. Is my function method the correct way or is there another way? MTIA George in Edinburgh --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.307 / Virus Database: 168 - Release Date: 11/12/01 _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
George, Suggestions interspersed below Miles At 12:53 PM 1/7/2002 +, George Pitcher wrote: Hi all, In trying to learn PHP (and MySQL), I am attempting to replicate a database-web solution built previously using Filemaker Pro and Lasso (on NT). I am currently working with PHP/MySQL on NT and will be moving this to Linux before the end of January. Filemaker is able to perform calculations internally and therefore I guess I will need to write functions to mimic this externally. Am I right here? MySQL doesn't have stored procedures, if that's what you mean. If they are really important you might want to consider PostgreSQL. Double-check the MySQL docs, and a newer version might have this. One of the features of the FMPro solution is that when a user is looking at a list of resources (in fact, university course packs) each row will display the number of associated records from the 'books' database. Now which is the best method to do this (speed/efficiency)? I have tried performing a row-level query on the related db but its very slow and times out before the second row can be displayed. Alternately, I could set the main db to increase/decrease a number field when adding or deleting books from the list. Which is recommended? Don't do the second. You're creating a maintenance headache. I'm no SQL wizard. Are you fetching information from a courses table, and displaying the number of associated books for each course to get results like this, w/o formatting ... The Athenian Galley U. Stroke 2 Grecian Roots and Folk Clothing C. Me. Bare 4 where the number at the end is the number of books for the course? select course_name, instructor_name, count(books) from courses, books where books.course_id = courses.course_id and (whatever other criteria you are using to select the course) But something tells me that won't work and I'm no SQL wizard. Something tells me that we're venturing into GROUP BY or HAVING territory. Dates I played around with my learning site over the holidays and found that I was not able to easily handle dates between the format required by users (dd/mm/) and that used by MySQL (-mm-dd) and therefore I wrote functions to parse the data both ways. All the example I could find on Dates used 'today' as the example. I want to be able to play around with stored dates. Is my function method the correct way or is there another way? Yes - but why can't we convert the world to that oh-so-simple date format of year,month,day which sorts and indexes so beautifully and is completely unambiguous. Cheers - Miles Thompson MTIA George in Edinburgh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
Miles, Thanks for the response. My description of the Filemaker solution was very simple. In fact it is 13 databases with some containing up to 18000 records. At the centre is a Transaction database containing 15000 'book' records. Each transaction record contains a pack ID field and this is what is queried to produce the 'count' but its painfully slow. The MySQL version of this table contains some 160 fields. In practice, this db table will grow by approx 300 records per week as new transactions are added by users (all UK universities). I also have other queries which need to be carried out to produce the information needed to provide good feedback to users. For instance, each transaction has a 'state': unsubmitted, submitted and complete. I want to be able to show the state of the pack as 'unsubmitted' when all transactions are 'unsubmitted', 'partially submitted' when some have been submitted and others not, 'submitted' when all transactions have been submitted and 'complete' when all transactions are at the 'complete' stage. So with the overall count, that would be 5 queries and I can think of at least one other query which would be needed to cover another process. If a single query is taking so long, what will 6 do to my database's overall performance? Would indexing help? George - Original Message - From: Miles Thompson [EMAIL PROTECTED] To: George Pitcher [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, January 07, 2002 1:29 PM Subject: Re: [PHP-DB] Concept help required George, Suggestions interspersed below Miles At 12:53 PM 1/7/2002 +, George Pitcher wrote: Hi all, In trying to learn PHP (and MySQL), I am attempting to replicate a database-web solution built previously using Filemaker Pro and Lasso (on NT). I am currently working with PHP/MySQL on NT and will be moving this to Linux before the end of January. Filemaker is able to perform calculations internally and therefore I guess I will need to write functions to mimic this externally. Am I right here? MySQL doesn't have stored procedures, if that's what you mean. If they are really important you might want to consider PostgreSQL. Double-check the MySQL docs, and a newer version might have this. One of the features of the FMPro solution is that when a user is looking at a list of resources (in fact, university course packs) each row will display the number of associated records from the 'books' database. Now which is the best method to do this (speed/efficiency)? I have tried performing a row-level query on the related db but its very slow and times out before the second row can be displayed. Alternately, I could set the main db to increase/decrease a number field when adding or deleting books from the list. Which is recommended? Don't do the second. You're creating a maintenance headache. I'm no SQL wizard. Are you fetching information from a courses table, and displaying the number of associated books for each course to get results like this, w/o formatting ... The Athenian Galley U. Stroke 2 Grecian Roots and Folk Clothing C. Me. Bare 4 where the number at the end is the number of books for the course? select course_name, instructor_name, count(books) from courses, books where books.course_id = courses.course_id and (whatever other criteria you are using to select the course) But something tells me that won't work and I'm no SQL wizard. Something tells me that we're venturing into GROUP BY or HAVING territory. Dates I played around with my learning site over the holidays and found that I was not able to easily handle dates between the format required by users (dd/mm/) and that used by MySQL (-mm-dd) and therefore I wrote functions to parse the data both ways. All the example I could find on Dates used 'today' as the example. I want to be able to play around with stored dates. Is my function method the correct way or is there another way? Yes - but why can't we convert the world to that oh-so-simple date format of year,month,day which sorts and indexes so beautifully and is completely unambiguous. Cheers - Miles Thompson MTIA George in Edinburgh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.307 / Virus Database: 168 - Release Date: 11/12/01 _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
George, In trying to learn PHP (and MySQL), I am attempting to replicate a database-web solution built previously using Filemaker Pro and Lasso (on NT). I am currently working with PHP/MySQL on NT and will be moving this to Linux before the end of January. =As a general comment, it is always dangerous to replicate when shifting platforms, better to reverse engineer and then implement anew and taking advantages of the strengths of the new tools. This particularly when moving into the relational field... Filemaker is able to perform calculations internally and therefore I guess I will need to write functions to mimic this externally. Am I right here? MySQL doesn't have stored procedures, if that's what you mean. If they are really important you might want to consider PostgreSQL. Double-check the MySQL docs, and a newer version might have this. =you will need to describe the 'internal calculations' before this question can be easily/sufficiently answered. However many people fail to appreciate that the (My)SQL language offers a lot of power/functionality. In your case you are going for the PHP combination so I will be quite surprised if you 'run out' of functionality! One of the features of the FMPro solution is that when a user is looking at a list of resources (in fact, university course packs) each row will display the number of associated records from the 'books' database. Now which is the best method to do this (speed/efficiency)? I have tried performing a row-level query on the related db but its very slow and times out before the second row can be displayed. Alternately, I could set the main db to increase/decrease a number field when adding or deleting books from the list. Which is recommended? Don't do the second. You're creating a maintenance headache. I'm no SQL wizard. Are you fetching information from a courses table, and displaying the number of associated books for each course to get results like this, w/o formatting ... The Athenian Galley U. Stroke 2 Grecian Roots and Folk Clothing C. Me. Bare 4 where the number at the end is the number of books for the course? select course_name, instructor_name, count(books) from courses, books where books.course_id = courses.course_id and (whatever other criteria you are using to select the course) But something tells me that won't work and I'm no SQL wizard. Something tells me that we're venturing into GROUP BY or HAVING territory. =as you can see, without giving a little more information, it is very difficult to give a satisfactory answer. How about listing your table definitions/schema. Almost any retrieval operation that does not select all of the records in a table will speed up when indexes are employed. If speed is a concern then that argues against the earlier suggestion of PostGres. =you are talking as if there are numerous queries. What's wrong with performing a join, or am I missing some significance? Dates I played around with my learning site over the holidays and found that I was not able to easily handle dates between the format required by users (dd/mm/) and that used by MySQL (-mm-dd) and therefore I wrote functions to parse the data both ways. All the example I could find on Dates used 'today' as the example. I want to be able to play around with stored dates. Is my function method the correct way or is there another way? Yes - but why can't we convert the world to that oh-so-simple date format of year,month,day which sorts and indexes so beautifully and is completely unambiguous. =Are you talking about functions implemented in PHP? Refer above, my earlier comments on the power of SQL, check out: SELECT DATE_FORMAT( colNm, format ) FROM tblNm in the manual at 6.3.4 Date and Time Functions. I'll be surprised if the long list of 'formats' doesn't give you more than enough options to keep (even Uni students) quiet! =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Concept help required
DL Neil,
Thanks for your response.
=As a general comment, it is always dangerous to replicate when shifting
platforms, better to reverse engineer
and then implement anew and taking advantages of the strengths of the new
tools. This particularly when moving
into the relational field...
Perhaps the use of the word 'replicate' was wrong. I am in fact
re-engineering based on my knowledge of how the whole operation is performed
(as I designed and built the original Filemaker/Lasso system) and trying to
preserve the look and feel of the web pages.
=you will need to describe the 'internal calculations' before this
question can be easily/sufficiently answered.
However many people fail to appreciate that the (My)SQL language offers a
lot of power/functionality. In your
case you are going for the PHP combination so I will be quite surprised if
you 'run out' of functionality!
The original Filemaker (FM) databases use calculations stored internally.
You define a field to store the result of a calculation. This could be
something quite complex or a static number (or string) or data from a
related database. Filemaker requires a separate database to represent the
equivalent of a table in standard SQL databases. Some calculation fields can
be indexed and some cannot (esp those containing related data). I expect
that I can replace these calculations with functions which I define.
=as you can see, without giving a little more information, it is very
difficult to give a satisfactory answer.
How about listing your table definitions/schema. Almost any retrieval
operation that does not select all of the
records in a table will speed up when indexes are employed. If speed is a
concern then that argues against the
earlier suggestion of PostGres.
I think that the table definition list would be far too long for this list.
Speed is an issue as at the moment I am doing all this under my own steam in
my own time (partly to extend my skills) and hope to be able to persuade my
bosses that this would be a beneficial move (they are very conservative).
Performance improvements would help.
=you are talking as if there are numerous queries. What's wrong with
performing a join, or am I missing some
significance?
No but I probably am. I have been working almost exclusively with Filemaker
for the past 7 years moving from Mac to Win NT in the process, with a short
flirtation with MS Access and ASP. I'm really a SQL newbie and am gradually
getting to grips with what is possible. JOINs are alien to me and I'll ned
to read up and experiment with them to see how they work and how I can best
use them.
Dates
I played around with my learning site over the holidays and found that
I was
not able to easily handle dates between the format required by users
(dd/mm/) and that used by MySQL (-mm-dd) and therefore I wrote
functions to parse the data both ways. All the example I could find on
Dates
used 'today' as the example. I want to be able to play around with
stored
dates. Is my function method the correct way or is there another way?
Yes - but why can't we convert the world to that oh-so-simple date
format
of year,month,day which sorts and indexes so beautifully and is
completely
unambiguous.
=Are you talking about functions implemented in PHP? Refer above, my
earlier comments on the power of SQL, check
out:
SELECT DATE_FORMAT( colNm, format ) FROM tblNm
in the manual at 6.3.4 Date and Time Functions. I'll be surprised if the
long list of 'formats' doesn't give
you more than enough options to keep (even Uni students) quiet!
I wrote my own functions to handle dates in the way I am comfortable with.
In dbdate() $input is the date pulled from MySQL and in revdate() $input is
the dd/mm/ date being grabbed from the users form data.
function dbdate($input)
{
$today = explode(-,$input);
$month = $today[1];
$day = $today[2];
$year = $today[0];
$p_date = $day . / . $month . / . $year ;
return $p_date;
}
function revdate($input)
{
$ztoday = explode(/,$input);
$day = $ztoday[0];
$month = $ztoday[1];
$year = $ztoday[2];
$revdate = $year . - . str_pad($month, 2, 0, STR_PAD_LEFT) . - .
str_pad($day, 2, 0, STR_PAD_LEFT);
return $revdate;
}
By the way, my site is for university staff not sudents.
I welcome any comments and hope to learn by implementation.
Regards
George
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.307 / Virus Database: 168 - Release Date: 11/12/01
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
