Jump to content

iwato

Members
  • Content count

    826
  • Joined

  • Last visited

  • Days Won

    1

iwato last won the day on June 10

iwato had the most liked content!

Community Reputation

7 Neutral

1 Follower

About iwato

  • Rank
    Invested Member

Contact Methods

  • AIM
    moogoonghwa
  • Website URL
    http://www.grammarcaptive.com
  • Yahoo
    iddor
  • Skype
    kiusau

Profile Information

  • Gender
    Male
  • Location
    Seattle, Washington USA 98104

Previous Fields

  • Languages
    HTML, CSS, Javascript, PHP, MySQL and Spoken Language

Recent Profile Visitors

10,219 profile views
  1. Two Forms, Two Actions, Same Page

    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
  2. What does this code do?

    $_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']
  3. SELECT JOIN - A Failed Query

    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
  4. SELECT JOIN - A Failed Query

    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
  5. SELECT JOIN - A Failed Query

    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
  6. SELECT JOIN - A Failed Query

    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
  7. SELECT JOIN - A Failed Query

    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.
  8. SELECT JOIN - A Failed Query

    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
  9. SELECT JOIN - A Failed Query

    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.
  10. SELECT JOIN - A Failed Query

    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
  11. SELECT JOIN - A Failed Query

    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
  12. SELECT JOIN - A Failed Query

    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
  13. SELECT JOIN - A Failed Query

    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.
  14. SELECT JOIN - A Failed Query

    Yes, of course. This, however, is not the issue. Roddy
  15. SELECT JOIN - A Failed Query

    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
×