www.webdeveloper.com
Results 1 to 9 of 9

Thread: [RESOLVED] select criteria not working

  1. #1
    Join Date
    May 2009
    Posts
    248

    resolved [RESOLVED] select criteria not working

    I am selecting data from a table and sorting it by school for 2 cities. One person is in the directory twice for both Toronto and Ottawa. But I dont want the person to show up twice. This is what I have but it is not working. John Smith is showing twice even though I have expressed that the entry listed as 'Smith, John (Toronto)' to not show.

    PHP Code:
    $sql1="SELECT * FROM directory WHERE school='$school' AND (city='toronto' OR city='ottawa') AND (title='Partner' OR title='Associate') AND name!='Smith, John (Toronto)' ORDER BY name"

  2. #2
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    1,031
    I'm pretty terrible at SQL but I'll take a wild guess and go with:
    PHP Code:
    $sql1="SELECT * FROM directory WHERE school='$school' AND (city='toronto' OR city='ottawa') AND (title='Partner' OR title='Associate') GROUP BY name ORDER BY name"

  3. #3
    Join Date
    May 2009
    Posts
    248
    the names are different though. One is 'Smith, John' and the second is 'Smith, John (Toronto)' .

    I think the problem is that both entries has one of the criteria ie their title is 'Associate". So it meets the title criteria but not the name criteria.

    How do I make it follow both criteria?

  4. #4
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    1,031
    As far as I know using the 'AND' operator should prevent it from pulling records based on only 1 of a set of criteria. Perhaps the reason your initial query didn't filter out that record could be due to the value of the field not being exactly what you think it is (it happens).

    Perhaps
    PHP Code:
    $sql1="SELECT * FROM directory WHERE school='$school' AND (city='toronto' OR city='ottawa') AND (title='Partner' OR title='Associate') AND name NOT LIKE '%Smith, John (Toronto)%' ORDER BY name"
    That's just a guess though. Or even throw in a UCASE(name) when checking the field (and use all caps in your string) just to get around any case issues.

    Ultimately though I feel in all situations you should never be trying to fix/correct your data by trying to alter how it gets output from a data source. You seem to have two different records in all data regard but as a human analyzing it you are able to see they are the same in a way. So as far as the database goes (and any queries to pull said data), you don't have any duplicates. Adding in a specific case fix just throws a band-aid on your system and over time these things create more problems. I feel like the real solution is changing how the data is structured in that regard so you can have a John Smith in two cities and even as two records but you define some key that allows you to connect the two pieces of data.

  5. #5
    Join Date
    May 2009
    Posts
    248
    100% agree with what you have posted. Firstly, I copied the name directly from the database. Secondly, The person spends time in 2 offices so needs different criteria for each office. I did not know a different way to represent each entry.

    Any help will be greatly appreciated.

  6. #6
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    1,031
    I guess I have a couple of questions (and suggestions). Again, bare in mind that I am far from an SQL expert.

    Is it not possible to have the name be exactly the same for both records? If so, how about setting the primary (not unique) key to the name field? This would allow you to link the two records based on the name and each record would differ in the other fields (such as city). Though I'll admit using a name as a key isn't the best way to go about it (unless the data entering that field is strictly controlled).

    I suppose you could also add another field to use as a key/identifier, but I don't know how troublesome that would be given you already have records that would then need to be updated.

  7. #7
    Join Date
    May 2009
    Posts
    248
    Many people fill in the records. The reason that there are 2 records (even though I suggested not) is that the person is in Toronto and Ottawa and together they represent Ontario. So when Ontario is shown the record needs to show if from Toronto or Ottawa but the city cannot be shown (ugg too many rules). So you see I needed to show what office info it was when both offices where shown.

    Also I cant use the name as the unique key bec it turns out we have a few people with the exact same name.

  8. #8
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    1,031
    Well then I guess this goes back to my very last suggestion about adding a new field to serve as a key/identifier for situations where you have multiple records representing one thing. So essentially each person (regardless of how many records exist in the database for them) gets an ID. We'll say that for John Smith the ID is 12345. For both of his records this field will show 12345. Now as far as the data is concerned you know that both records are in fact the same person and you can alter the query to only show on record.

    Remember that this also affects how you enter the data. You say there are others in the database with matching names, however they are indeed separate people and thus they would need separate IDs. How you assign the IDs and how they are set each time a record is added depends on how the data gets entered into this database to begin with.

  9. #9
    Join Date
    May 2009
    Posts
    248
    I changed the WHERE to read AND id!='####' and that worked, thanks

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles