SitePoint Sponsor

User Tag List

Page 2 of 4 FirstFirst 1234 LastLast
Results 26 to 50 of 80
  1. #26
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    dr john, don't confuse the guy by optimizing everything right away plz

    he's trying to learn many-to-many relationships

    curtis, the query in post #22 is fine

    i'm willing to bet the cities table isn't actually what you've shown us

    please run this query and show what it produces:
    Code:
    SHOW CREATE TABLE cities
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  2. #27
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here ya go:
    SQL query: SHOW CREATE TABLE cities;
    Rows: 1
    Table Create Table
    cities CREATE TABLE `cities` (
    `city_id` smallint(4) NOT NULL auto_increment,
    `city` varchar(20) NOT NULL,
    PRIMARY KEY (`city_id`),
    UNIQUE KEY `city` (`city`)
    ) ENGINE=MyISAM AUTO_INCREMENT=48 DEFAULT CHARSET=utf8
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  3. #28
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and here's the one for its sister table, IDcity:
    SQL query: SHOW CREATE TABLE IDcity;
    Rows: 1
    Table Create Table
    IDcity CREATE TABLE `IDcity` (
    `mem_id` smallint(4) NOT NULL,
    `city_id` smallint(3) NOT NULL,
    PRIMARY KEY (`mem_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    weird

    you actually do have a column called city

    you now either have uncovered a real mysql bug in that it doesn't like the name `city` (the chance of which is vanishingly slim, seeing as how there are millions of mysql installations around da world), or else you are doing something else wrong

    do you know how to dump all your tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    enlighten me yet again...
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  6. #31
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what are you using, phpmyadmin? heidisql? mysql query browser?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #32
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use phpmyadmin
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  8. #33
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, use the Export tab, check the check box to generate both structure and data, select all 7 tables, save the export to a text file, and attach the text file to a post here

    make sure there is no sensitive data in your tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #34
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the attached .doc file.
    Attached Files Attached Files
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  10. #35
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's pretty, but it's not helpful

    i wanted you to generate the CREATE TABLE statements (structure) as well as the INSERT statements (data) so that i could set up exactly the same tables over here on my testing system

    i'm not gonna type them in off a word doc, sorry

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #36
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the issue is not going to be your sql if you can run the query directly in the sql tab in phpmyadmin and get results.

    Strip all references to city out of your script. Run the script with a value for city hard coded in place.

    Do you get an error then?

    How about if you set a variable right at the beginning of the script for $city and then put that in your query instead.

    Do you get an error?

  12. #37
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I did as you asked: phpMyAdmin; Export tab; check the box for a text doc;
    choices are
    CSV
    CSV for MS Excel
    Microsoft Excel 2000
    Microsoft Word 2000
    LaTeX
    Open Document Spreadsheet
    Open Document Text
    PDF
    SQL
    XML
    YAML

    But you knew that.

    Selecting Word 2000 let me select both Structure and Data check boxes, and the results I attached to the post. How do I specify to include the CREATE TABLE statement results?
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  13. #38
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll try your advice from post #36 this evening and post here again when I have some results to share.

    Thank you so much for your attention. I really do appreciate the ongoing support.
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  14. #39
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    choose the SQL option, i think (i;m doing this from memory, because i haven't used phpmyadmin in over 4 years)

    keep trying until the output contains CREATE TABLE and INSERT statements

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #40
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LOL - I just found the SQL radio button, and it dumps LOTS of stuff :-) Let me remove phone/email data and I'll attach it in a bit...
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  16. #41
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And here is your .sql file.
    I shortened tables considerably - there are now 20 members, and the (I hope) appropriate number of phone records, email records, etc. I checked, and all relationships between the seven tables appear to be sound. Please let me know if I need to do something additional or differently to make your helping me any easier.

    Driving soon; back online in a couple hours...
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  17. #42
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Had to change it to a .doc. I hope the formatting is OK ...
    Attached Files Attached Files
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  18. #43
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I might be wrong on this one, but

    Code:
    CREATE TABLE IF NOT EXISTS `IDcity` (
      `mem_id` smallint(4) NOT NULL,
      `city_id` smallint(3) NOT NULL,
      PRIMARY KEY  (`mem_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    and
    Code:
    CREATE TABLE IF NOT EXISTS `cities` (
      `city_id` smallint(4) NOT NULL auto_increment,
      `city` varchar(20) NOT NULL,
      PRIMARY KEY  (`city_id`),
      UNIQUE KEY `city` (`city`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=48 ;
    It's late here and I'm tired, so not sure if the different definitions for city_id can affect the join - smallint(3) vs smallint(4)

  19. #44
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i still don't understand why you had to use microsoft word ~ptui~

    your script failed in several places, because you apparently lost the semicolon at the end of a few statements

    the email and phones tables did not load because you had UNIQUE keys on the email address and phone numbers, but they were all bullpuk@spammer.org and xxx-xxx-xxxx

    attached is a "corrected" script that actually runs (you might want to create a separate testing database to try it, so as not to overwrite your actual data)

    then run this query:
    Code:
    SELECT l_name, f_name, city, num, address
    FROM names
    LEFT JOIN IDcity ON names.mem_id = IDcity.mem_id
    LEFT JOIN cities ON IDcity.city_id = cities.city_id
    LEFT JOIN IDphone ON names.mem_id = IDphone.mem_id
    LEFT JOIN phone ON IDphone.phone_id = phone.phone_id
    LEFT JOIN IDemail ON names.mem_id = IDemail.mem_id
    LEFT JOIN email ON IDemail.email_id = email.email_id
    --WHERE city = 'Richmond'
    ORDER BY l_name, f_name
    notice that the WHERE clause is commented out

    these are the results:
    Code:
    l_name	f_name	city	num	address
    Abbot	Chris	Concord	xxx-xxx-xxx2	bullpuk8@spammer.org
    Adams	J. Richard	Livermore	xxx-xxx-xxx3	bullpuk1@spammer.org
    Albertson	Suzanne	San Ramon	xxx-xxx-xxx4	bullpuk2@spammer.org
    Allen	Linda	Livermore	xxx-xxx-xxx5	bullpuk3@spammer.org
    Anderson	Bob	Livermore	xxx-xxx-xxx6	bullpuk4@spammer.org
    Appleby	Wendy	Roseville	xxx-xxx-xxx7	bullpuk5@spammer.org
    Arrambide	Juan	Hayward	xxx-xxx-xxx8	
    Aufdenkamp	Jan	Pleasanton	xxx-xxx-xxx9	bullpuk6@spammer.org
    Bacskai	Judith	Kensington	xxx-xxx-xx10	bullpuk7@spammer.org
    Bacskai	Robert	Kensington	xxx-xxx-xx10	bullpuk7@spammer.org
    Ball	Karin	San Ramon	xxx-xxx-xx12	bullpuk9@spammer.org
    Bastani	Ali	Concord	xxx-xxx-xx13	bullpuk10@spammer.org
    Benjamin	Daniel	Livermore	xxx-xxx-xx14	
    Berckmoes	Russ	Pleasanton	xxx-xxx-xx15	bullpuk11@spammer.org
    Blanco	Gail	Pleasanton	xxx-xxx-xx16	bullpuk12@spammer.org
    Boerman	Brian	Folsom	xxx-xxx-xx17	
    Boerman	Carla	Folsom	xxx-xxx-xx17	
    Bond	Richard	Dublin	xxx-xxx-xx18	bullpuk13@spammer.org
    Bousfield	Sarah	Reno	xxx-xxx-xx19	bullpuk14@spammer.org
    McKay	Deb	San Ramon	xxx-xxx-xxx4	bullpuk2@spammer.org
    notice anything? it's working!!! it does not give the "unknown column 'city'" error message



    now uncomment the WHERE clause and see if you can explain what happens
    Attached Files Attached Files
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #45
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have narrowed it down but not quite got it. Check this code:

    Code:
     if($_POST['l_name'] || $_POST['f_name']) {
          $whereClause .= " AND city = '$city'";
        } 
        else { 
          $whereClause = "city = '$city'"; 
        }
    take the AND out and echo out your sql query at this point:

    Code:
     if($_POST['l_name'] || $_POST['f_name']) {
          $whereClause .= " city = '$city'";
          echo $sql_result;
        } 
        else { 
          $whereClause = "city = '$city'"; 
        }
    you will see that you don't get the same error message. what you should get though is an error similar to:

    Code:
     right syntax to use near 'city = 'Livermore' AND gender = 'M'' at line 2
    I hard coded values for all your variables. Do you see the single quote before city? That means that somewhere else further up in your code you have a single quote somewhere it doesn't belong. When city is then appended to the query your query is actually being terminated by the single quote and thus throwing the error.

    I'll still look but someone else may find it first.

  21. #46
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 and guelphdad...Let's just agree right now that you are two tenacious dudes.

    i still don't understand why you had to use microsoft word ~ptui~
    1. I used Word because it was the first option I thought of. Notepad is far better, however...good call there.

    your script failed in several places, because you apparently lost the semicolon at the end of a few statements
    2. I didn't lose the semicolons. I manually removed the semicolon from the last statement in several of the groupings because I confused the SQL statements syntax with some programming languages syntax where you're supposed to do that. In some of my (I think) JavaScript arrays I believe that I have to omit the closing semicolon from the last member.

    the email and phones tables did not load because you had UNIQUE keys on the email address and phone numbers, but they were all bullpuk@spammer.org and xxx-xxx-xxxx
    3. I have UNIQUE keys on some of the fields in my db tables because the system will then throw an error if I accidentally try to INSERT a record with a duplicate phone number, email address, etc. I use the UNIQUE key in this way as a security device of sorts. Before I attached the file and posted it I removed for privacy's sake my club members' email addresses and phone numbers and replaced them with gibberish...then I forgot that the UNIQUE keys were there and might cause problems for you.

    So, aside from using the UNIQUE key as a security device of sorts (preventing duplicate data entry), what else is it good for? I mean, why does the key exist?

    Alright, I'm going to create a duplicate db, install the tables and data, and see what shakes out when I run your new and improved SELECT query.

    15 minutes later ***

    Well, I'm glad it's not just me.

    You went and lower-cased the IDemail, IDphone and IDcity table names to idemail, idphone and idcity, then failed to make those updates in your SELECT statement. :-) Good to know that some super-human people are only human...

    I recreated the seven tables using your new and improved script, then updated and ran your SELECT statement sans WHERE clause and....it works great. All the data is returned perfectly well.

    Now, let me uncomment that WHERE clause.......and........

    I get message saying MySQL returned an empty result set (i.e. zero rows).

    Wha??

    OK...the tone I sense in the last line of your post is rather mirthful...nearly cynical.

    Something approaching merriment.

    Well, the city of Richmond has city_id of 33, and none of our 20 members in the idcity table are assigned city_id 33. But, if I go into the idcity table and update one of the members to city_id of 33, then your query returns that member. Or, if I adjust the WHERE clause to reflect 'Livermore', or any city inhabited by any of our twenty members, then we're golden. OK, that's easy enough.

    So, I'm guessing that you are thinking that my tables need to be wiped and recreated, is that correct? And, that I have to then reINSERT a-l-l t-h-o-s-e r-e-c-o-r-d-s...... mmmmm....that sounds like fun!

    guelphdad, thank you again VERY much for hunting this thing down. I will check out your suggested path on Saturday as well by playing with that $city whereClause as you suggest. It sounds like you are on to something...
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  22. #47
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your understanding of UNIQUE keys is correct, the purpose is to prevent duplicates

    and you also correctly realized why no results came back for Richmond

    as for the upper/lower case on the table names, i'm not quite sure how that happened, except to say that it shouldn't matter:
    ... database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix.
    -- http://dev.mysql.com/doc/refman/5.0/...nsitivity.html
    most mysql developers therefore use lower case names exclusively

    anyhow, i think we have demonstrated, finally, that the issue is not with the SQL

    so, no, you don't have to recreate and reload -- but now that you have produced an export file (called a dump or backup file, this is exactly what the mysqldump utility produces), you now know how to back up your data so that you could, if you needed to, recreate your data easily

    did you think any further about the WHERE 1=1 trick i mentioned? this should make your php processes which assemble the query simpler...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #48
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, I'll use all lowercase table names from now on. Good tip.
    When you suggest that "the issue is not with the SQL", does that mean that the issue is not with my tables, columns, query, etc? Does that mean that the dump I attached is not corrupted?

    If that's the case, then the problem lies in the PHP. I'll work through guelphdad's path and see what shakes out later this afternoon.

    Thank you again for your continued guidance and patience. If Sunday comes and goes and I'm still down this rat hole, then I'm going to consider rebuilding the tables, re-inserting the data, etc, and along the way employing guelphdad's new and improved "Big Boy" set-up. I'm already going to add your "1=1" to the end of my default WHERE clause in my PHP script.

    Please do let me know if any other possible solutions cross your mind.

    Oh - and my little 20-member practice db I created last night for this little project is the perfect place to mess with multiple email addresses, phones and cities for some poor member to experiment with your "24-rows" thing we discussed earlier. I'm interested in your SQL trick to alleviate that problem...
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  24. #49
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, yes... another solution is not to use a many-to-many relationship structure if you don't actually use it

    for instance, do you really plan to relate a single member to multiple cities? multiple emails? multiple phones?

    if you do, then this is the appropriate time to ensure that your test data actually has instances of these relationships

    so that i can finally get around to showing you the awful thing called cross join effects if you try to retrieve them all in the query as it is now written

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #50
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    guelphdad, thank you very much for offering some good input.
    I examined your idea at the bottom of post #45. You display this code:
    'city = 'Livermore' AND gender = 'M''
    and suggest that the single quote before the word "city" is erroneous. Actually it's always there in error messages of this type, because the system output is wrapping single quotes around the piece of the query with which it has a problem. If you look closely at the end of the query text, you'll see two single quotes after the capital "M". The first of those closes the single quote that precedes the capital M, and the second closes the single quote that precedes the word "city". If the system wrapped asterisks around the query string with which it has a problem, it might instead look like this:
    *city = 'Livermore' AND gender = 'M'*

    So, the issue is not with quotation marks -- at least not here.
    Curtis
    Was it summer when the river ran dry, or was it just another dam?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •