Jump to content

iwato

Members
 View Profile  See their activity

  • Posts

    1,506

  • Joined

  • Last visited

  • Days Won

    4

 Content Type 

Everything posted by iwato

  1. iwato
    It is in the host page into which the <div> tag from the HTML template is displayed. Roddy
  2. iwato
    As a simultaneous learner and doer who has been subject to large interruptions (the largest lasting for as much as six years) I make an effort to document everything that I study and create. To this end my HTML template is merely a <div> tag within an explanatory HTML page. As a result, the only very useful <head> tag is that of the dynamically filled host page. In the past, I have used the CSS scoped attribute to style specific sections or divisions that I have included into the hostage. What is your opinion of this practice?. Roddy
  3. iwato
    GREETING: I hope that everyone who celebrated the American holiday of Thanksgiving this past week spent a wonderful holiday full of cheer, good food and drink, and restored family ties and friendships. My celebration was short, alone, but very well spent. And, in the quiet of the office, abandoned by nearly everyone for the holiday break, I was able to finish nearly three weeks of study, investigation, and experimentation with the grammar of the MySQL database. You may discover the results by clicking on any direct reference to a specific podcast on the Grammar Captive Weekly Podcast webpage that is itself not a referenced podcast. This includes requests for a specific podcast coming from remote third parties. Not only do the referenced podcasts appear, where before they did not, but only that number of podcasts actually referenced fills the page. Important in your regard is that I have W3Schools to thank for helping me to realize what for me has been a major feat. Many thanks! BACKGROUND: In the same spirit of good coding practice I have another question related to coding strategy. All of the Click and Listen panels are created from a subsection of the same HTML template that is included into a PHP template generator before being dumped into the host HTML webpage as a dynamically filled <div> of the Grammar Captive Weekly Podcast page. QUESTION: Where, how, and, of course, when is the best place and time to enter the CSS styling? On the HTML template, the final hosting page, or dynamically via PHP before the page is dumped into the host page. In answering these question please explain your logic for recommending the strategy that you do. Roddy
  4. iwato
    Yes, this was the question. And, what a great answer! I wish you and everyone else at W3Schools a fabulous Happy Thanksgiving! With a little good fortune it could prove to be one very productive holiday. Roddy
  5. iwato
    BACKGROUND: Good ideas are sometimes like luck, they come in streaks. Thanks to the experiential wisdom of this forum and those around me in the Galvanize collective workspace, I have decided to tarry a little longer with the reconstruction of my data base. Indeed, I have learned a new term -- normalization. What before I intended to achieve with row duplication I hope now to achieve with simple updates. In order to achieve this important structural change and still retain the benefits of the old structure, I must disengage one of my form's <fieldset>s and create from it a new form that is submitted manually and only occasionally. It would appear that the small changes to the separate table that this fieldset previously replenished with each new form submission can now be achieved with MySQL TRIGGER and UPDATE statements. This strategy has led to the following two questions: QUESTION ONE: Under the assumption that one can have only one $_POST variable per page how does one typically go about using the same $_POST variable for multiple forms on the same page? If I were to implement this task on my own, without the advice of others, I would create a bivariate if-. else if-, else- statement that tests for the present of one of two variables and then handles only that data associated with the form submitted. Is there another, perhaps more thorough, separation that does not include the creation of a separate form page? QUESTION TWO: When executing a MySQL TRIGGER that updates a single row value of a single column of a table different from the one that executes the trigger, will the DEFAULT setting for the UPDATE statement trigger a timestamp with the ON UPDATE CURRENT_TIMESTAMP? As always your wisdom would be well-appreciated. Roddy
  6. iwato

    What does this code do?

    iwato replied to Snorkel's topic in PHP

    $_GET is a called a superglobal in PHP. It is a wrapper for one to many variables sent via an HTTP request. HTTP REQUEST: http://www.grammarcaptive.com?hash=30a6836a3f7c5fc57751a61098e5c221&podcast_no=21 URL Without a Query String: http://www.grammarcaptive.com Query String: ? + hash=30a6836a3f7c5fc57751a61098e5c221 + & + podcast_no=21 The string literal '30a6836a3f7c5fc57751a61098e5c221' becomes the value of $_GET['hash'] The number 21 becomes the value of $_GET['podcast_no']
  7. iwato
    It turns out that the aliases must be repeated for each SELECT and JOIN statement, but it makes no difference whether they are the same or different for each SELECT and JOIN statement. Roddy
  8. iwato
    ALMOST THERE? There is an improvement, but i am still not there as the procedure returns NULL for the field userbio. (SELECT p.id, p.usertype, p.username, c.userbio FROM parent_table AS p LEFT OUTER JOIN child_table AS c ON c.id = p.id WHERE p.id = 10) UNION (SELECT pu.id, pu.usertype, pu.username, cu.userbio FROM parent_table AS pu LEFT OUTER JOIN child_table AS cu ON cu.id = pu.id WHERE pu.id IN (SELECT ref FROM ref_table WHERE ref_table.id = 10) ) Output id usertype username userbio 10 3 david NULL 4 3 tim NULL 6 1 liz NULL Never mind. It works. For some reason the listed data was never entered into the child_table. Alas, I am a happy dummy! Roddy
  9. iwato
    Also, provided that the row data contained within a table column is compatible with the new definition is it possible to redefine a MySQL column without destroying the data? Roddy
  10. iwato
    Before i implement the suggested code I have several questions. Firstly, I have noticed that the number of columns and their respective definitions must match when combining SELECT statements with the UNION operator. Does this mean that I must repeat the phrase "p.id, p.usertype, p.username, c.userbio" for each SELECT statement similarly combined. Secondly, the aliases in the proposed complementary statement are declared after they are used. This suggests that there is such a thing as scope in their application. Does the scope of the alias extend beyond the SELECT statement in which it is employed? Does it, for example, apply to the entire SQL statement? Thirdly, is there a pecking order in the use of SELECT statements. Does the first SELECT statement determine what is returned for all subsequent SELECT statement? Or, does each SELECT statement determine for itself what will be returned. Finally, although I can understand how designating specific columns would reduce the redundancy in the result set, I cannot understand why it would cause values to appear that have not already appeared with the use of the * wildcard operator. Can you explain this? Although i can well appreciate the need for the speedy and orderly storage and retrieval of information, my mind is not built like a symmetric associative array, and I am having trouble dealing with the matrix-like nature of SQL and its Chinese-like grammatical syntax. Roddy
  11. iwato
    A closer look suggests confusion in what is being read. Notice the difference in the number of ID values between the previous entry and this latter one. In the previous case were received values for three IDs. In this case only two IDs are reported. What is more, the reported values are completely different! Roddy p.s. Please do not interpret my reluctance to rebuild my database structure. I need to move ahead quickly. Already a year has past, and I have yet to publish a single podcast! Once my project has been tested, and I know that it is has been worth the investment, I can return and refurbish. i have kept good record of what I have done and still have much to do, before i can launch.
  12. iwato
    Some progress has been achieved. Now all matched rows are reporting, but still there is no data coming from child_table. Also, myPhpAdmin and MySQLi are reporting different results. (SELECT * FROM parent_table LEFT OUTER JOIN child_table ON child_table.id = parent_table.id WHERE parent_table.id = 9 ) UNION (SELECT * FROM parent_table LEFT OUTER JOIN child_table ON child_table.id = parent_table.id WHERE parent_table.id IN (SELECT ref FROM ref_table WHERE ref_table.id = 9 ) ) MySQLI id: usertype: username: jason userbio: id: usertype: username: david userbio: myPhpAdmin id usertype username id usertype userbio 9 3 jason NULL NULL NULL 10 3 david NULL NULL NULL
  13. iwato
    Insofar as the query is not rejected, your grouping is better than all of those that I tried. Unfortunately, the results are nearly an empty set and do not provide any information for the original query -- only those to which the original query referred. id: usertype: username: tim userbio: id: usertype: username: liz userbio: Compare the following results with those above id: 10 usertype: 3 username: david id: 4 usertype: 3 username: tim id: 6 usertype: 1 username: liz The desired, but not forthcoming result is id: 10 usertype: 3 username: david userbio: I am David. id: 4 usertype: 3 username: tim userbio: I am Tim. id: 6 usertype: 1 username: liz userbio: I am Liz. Roddy ps. Depending on whether I run the statement with MySQLi or myPhpAdmin the results are different, but still incomplete.
  14. iwato
    jSG: What exactly did you have in mind. I have now tried just about every associative relation possible. The result is, without exception, a failed query. i believe a new approach is required. Roddy
  15. iwato
    Yes, that is correct. It now returns the information contained in parent_table for the originally queried row as well as the each of the rows to which the originally queried row refers in ref_table. id: 10 usertype: 3 username: david id: 4 usertype: 3 username: tim id: 6 usertype: 1 username: liz The next step is to do the same, but include the data from the chile_table. Unfortunately, the following SQL statement does not return any data from child_table, and duplicate like variables: SELECT * FROM parent_table LEFT OUTER JOIN child_table ON child_table.id = parent_table.id WHERE parent_table.id IN(SELECT ref FROM ref_table WHERE ref_table.id = 10) UNION SELECT * FROM parent_table LEFT OUTER JOIN child_table ON child_table.id = parent_table.id WHERE parent_table.id IN(SELECT ref FROM ref_table WHERE ref_table.id = 10) RETURN VALUES id usertype username id usertype userbio 4 3 tim NULL NULL NULL 6 1 liz NULL NULL NULL I can do UNION or JOIN, but i cannot do both together. Roddy
  16. iwato
    Yes, that is correct. It now returns the information contained in parent_table for the originally queried row as well as the each of the rows to which the originally queried row refers in ref_table. Now, in order to add the corresponding information from the child table would it be sufficient to simply add child_table after parent_table as shown below, SELECT * FROM parent_table WHERE id = 10 UNION SELECT * FROM parent_table, child_table WHERE id IN( SELECT ref FROM ref_table WHERE id = 10 ) or must I add another UNION statement such as what follows? SELECT * FROM parent_table WHERE id = 10 UNION SELECT * FROM parent_table WHERE id IN( SELECT ref FROM ref_table WHERE id = 10 ) UNION SELECT * FROM child_table WHERE id IN( SELECT ref FROM ref_table WHERE id = 10 ) Roddy
  17. iwato
    SELECT * parent_table WHERE id = 10 UNION SELECT * FROM parent_table WHERE id IN( SELECT ref FROM ref_table WHERE id = 10 ) This was copied from the SQL module window of the myPhpAdmin application. As JSG's reply assumed that I had combined the parent_table with the child_table, I ran his response only for the parent_table. I also changed the value of id in the belief that the NULL value entered into row 10 might affect the result. The error message was identical.
  18. iwato
    Yes, of course. This, however, is not the issue. Roddy
  19. iwato
    Is there perhaps a missing alias in your proposed statement? The phpMyAdmin SQL console is reporting the following error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near table. WHERE id = 10 UNION SELECT * FROM table WHERE Roddy
  20. iwato
    Having received all of the very useful information about table structure, column constraints, variable type, variable choice, and character set -- much of which I will certainly implement in my final production I feel no closer to the solution of my original problem -- the failed grammar of my SQL statement . So as to renew focus I would like to draw your attention to the following entry This entry provides a SQL statement with two sets of output. The first set of output is what is realized by the provided SQL statement. The second set of output is what is actually desired -- namely, full information for not only the selected row, but also each of the rows to which the selected row refers. The question, then, is how to write the SQL statement to achieve not the realized result, but the desired result. Once again, as I am still relatively new to MySQL, please accompany your solution with an explanation. Reading the online MySQL manual is simultaneously instructive and confusing, and I feel very fortunate to have got anything returned at all. Roddy
  21. iwato
    RESPONSE TO JSG's QUESTIONS QUESTION: Why are you using a composite primary key? It doesn't look like you have more than one row for any one ID, so why isn't the ID the primary key? RESPONSE: The above prototype will be applied (if I can get it to work) to podcast episodes. As it is possible, even likely that some podcasts will require revision, there may be multiple entries for a single podcast -- in effect, multiple rows with the same podcast number. --- QUESTION: Why is the username char and not varchar? And why uft8_bin for it? RESPONSE: The primary difference between the CHAR and VARCHAR type is one of length. As this is a heuristic prototype and the data of the variables (fields/columns) associated with a particular podcast must fit into a template, I must be able to control their length. Assigning more characters than is necessary simply clutters the database with unused volume. --- QUESTION: And why uft8_bin for it? RESPONSE: Yes, if this were not a multipurpose prototype, then I would agree: the username would be restricted to an ASCII format. This said, I am catering to an international audience and other fields cannot be restricted to the ASCII format. --- QUESTION: What's the purpose of the child table at all? There's only one additional field there - will each ID/usertype combo have multiple descriptions? If not, then I don't see a reason for it. RESPONSE: This prototype will eventually be applied for the production of RSS feeds. Apple Computer uses its own namespace that is different from the generic RSS2 channel and item elements. There will likely be other podcast hosts that will require custom namespaces. I have three tables for creating RSS2 feeds: channel, item, and iTunes. Placing everything into one table is confusing and partitioning a table adds unwanted complexity. --- QUESTION: What is the meaning of each of the four fields in the ref table? RESPONSE: ref_id is the ref_table row number. id corresponds to a particular row in the parent_table. ref corresponds to a row that is referenced by the selected row in the parent_table. One parent_table row can refer to multiple other rows within the parent_table. ref_count is an ordinal counter that both counts and ranks the relative importance of the referenced rows listed in the ref column for a particular parent_table row.
  22. iwato
    INSERT INTO `parent_table` (`id`, `usertype`, `username`) VALUES ('1', '1', 'roddy.'), ('2', '2', 'beth'), ('3', '3', 'matt'), ('4', '3', 'tim'), ('5', '2', 'tyler'), ('6', '1', 'liz'), ('7', '1', 'aldo'), ('8', '1', 'adjit'), ('9', '3', 'jason'), ('10', '3', 'david') The parent_table Data Set
  23. iwato
    UPDATE: Since I last posted I have acquired new information and am now well positioned to pose a more precise question. BACKGROUND: The following query on the above tables yields the data shown below the query. SELECT * FROM parent_table LEFT OUTER JOIN child_table ON parent_table.id = child_table.id LEFT OUTER JOIN ref_table ON parent_table.id = ref_table.id WHERE parent_table.id = '2' id: 2 usertype: 2 username: beth userbio: I am Beth. ref_id: 4 ref: 6 count_ref: 1 id: 2 usertype: 2 username: beth userbio: I am Beth. ref_id: 5 ref: 9 count_ref: 2 The GOAL: Obtain the above information as well as that corresponding to each of the rows identified in the ref field by the values 6 and 9. In particular, id: 2 usertype: 2 username: beth userbio: I am Beth. id: 6 usertype: 1 username: liz userbio: I am Liz. id: 9 usertype: 3 username: jason userbio: I am Jason. QUESTION: How to I write the SQL statement to obtain the above information?
  24. iwato
    PROBLEM: Turn a two-step SELECT procedure into a one-step procedure. The two-step procedure is 1) Discover with a query to a parent table what other rows in the parent table are related to the queried row. 2) Obtain selected data for the queried row and other related rows from the parent table and a child table that are connected by a FOREIGN KEY. BACKGROUND: I have two tables -- a parent (parent_table) and a child (child_table) table - connected by a valid FOREIGN KEY and an additional table (ref_table) that contains information about the relationship among the rows of both the parent and child tables. Please find below the results of three SHOW CREATE TABLE statements to help you in your understanding of the table structure. In addition, I have included the INSERT statements for the child_table and ref-table. I accidentally destroyed the INSERT statement for the parent_table. This said, the parent and child tables are very similar in structure. DISCLAIMER: Please understand that the problem that I have created is heuristic in nature and is being used to create a prototype for subsequent, more practical use. The PARENT Table parent_table CREATE TABLE `parent_table` ( `id` int(3) NOT NULL DEFAULT '0', `usertype` enum('1','2','3') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '2' COMMENT '1=good, 2=neutral, 3=bad', `username` char(150) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`,`usertype`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 The CHILD Table child_table CREATE TABLE `child_table` ( `id` int(3) NOT NULL DEFAULT '0', `usertype` enum('1','2','3') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '2' COMMENT '1=good, 2=neutral, 3=bad', `userbio` varchar(500) DEFAULT NULL, KEY `parent` (`id`,`usertype`), CONSTRAINT `child_table_ibfk_1` FOREIGN KEY (`id`, `usertype`) REFERENCES `parent_table` (`id`, `usertype`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO `child_table` (`id`, `usertype`, `userbio`) VALUES ('1', '1', 'I am Roddy.'), ('2', '2', 'I am Beth.'), ('3', '3', 'I am Matt.'), ('4', '3', 'I am Tim.'), ('5', '2', 'I am Tylor.'), ('6', '1', 'I am Liz.'), ('7', '1', 'I am Aldo.'), ('8', '1', 'I am Adzit.'), ('9', '3', 'I am Jason.'), ('10', '3', 'I am David.') The REFERENCE Table ref_table CREATE TABLE `ref_table` ( `ref_id` int(3) NOT NULL DEFAULT '0', `id` int(3) DEFAULT NULL, `ref` int(3) DEFAULT NULL, `count_ref` int(1) DEFAULT NULL, KEY `par_ref` (`ref_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO ref_table (ref_id,id,ref,count_ref) VALUES ('1','1','2','1'), ('2','1','6','2'), ('3','1','7','3'), ('4','2','6','1'), ('5','2','9','2'), ('6','4','1','1'), ('7','4','2','2'), ('8','4','6','3'), ('9','4','10','4'), ('10','5','6','1'), ('11','6','1','1'), ('12','6','2','2'), ('13','6','9','3'), ('14','7','5','1'), ('15','7','6','2'), ('16','8','1','1'), ('17','9','10','1'), ('18','10','4','1'), ('19','10','6','2'), ('20',10, NULL,'1'); EXPLANATION BY EXAMPLE: Say a user is interested in row 4 of the parent_table. When the database is queried a SELECT JOIN statement looks in the id field of the ref_table and finds four corresponding rows identified by ref_id 6, 7, 8, and 9. With each of these latter rows is associated a different row -- namely, 1, 2, 6, 10. Without further selection is returned all of the information contained in the parent_table and child_table associated with rows 1, 2, 4, 6, and 10. This is what is supposed to happen, but does not. The REJECTED SQL STATEMENT SELECT * FROM parent_table OUTER JOIN child_table ON parent_table.id = child_table.id OUTER JOIN ref_table ON parent_table.id = ref_table.id QUESTION ONE: Does the table structure make sense? Are the constraints properly set? QUESTION TWO: Why is my SQL statement rejected as poorly formatted? This is my first attempt to use the JOIN clause. So, please be as thorough with your answer as possible. I must believe that I have a long road ahead with MySQL and should prepare for it as i move forward. Roddy
  25. iwato
    Although we are closer, I am afraid to say that we are almost there, because I have learned that , unless I know for sure, the smallest matter can take an indefinite amount of time to repair. Please click on the image after the following page opens http://www.grammarcaptive.com/single_payment_acknowledgment.php. It would appear that the first Colorbox has been suppressed, but not the second. if (navtype == 1) { $.colorbox({open:false}); } else if I have now tried to fix the problem by doubling the content of the if-statement. concatenating the same line of jQuery as $.colorbox({open:false}).colorbox({open:false}); adding the following second line of code: $("iframe.splash").colorbox({open:false}); adding an onclose callback function to the first call to Colorbox using the same code in item 3. All of the these produce the same half-positive result. Any additional ideas? Roddy
×
×
  • Create New...