I thing you are using the initial input to create a filtered dropdown of usernames or choices to help with input??
I believe the 'worse' in the reference to the SELECT * FROM Students WHERE concat(LastName, ", ", FirstName) LIKE 'Smith, J%'; is because of index usage AND user data entry error, at least to me As for impropertly parsing the form data to SQL, you should always be validating user input anyway, especially on the web, so no damaging or security risking data is submitted for queries. (in this case risk is prob low.. but you never know) So i dont think parsing out just the 2 parts should be too hard.. Depending on the language you are using, their may be functions to help do this for you. What if the user only sends Smith? using the concat approach would yeild: SELECT * FROM Students WHERE concat(LastName, ", ", FirstName) LIKE 'Smith%'; this will always be slower than just LastName LIKE "Smith%" because of the extra concat logic then the user could still submit Smith,J (NOT Smith, J) SELECT * FROM Students WHERE concat(LastName, ", ", FirstName) LIKE 'Smith,J%'; would not find any of the relavant rows .. user error is still at work (its missing the space the concat builds in) Never trust user input :) The other comment i have about the WHERE concat(LastName, ", ", FirstName) LIKE 'Smith, J%'; solution is yes, it should work.. but i dont think it will use indexes properly or it may be slower than just a LASTNAME LIKE "Smith".. You would have to test for performance. If your users are used to the autocomplete type of features.. speed probably will be a big issue to them. Your data may be too small in size right now to notice a big difference with speed, depending on your hardware.. but you never know. -- Brian Lindner Tuesday, January 7, 2003, 11:54:59 AM, you wrote: > ----- Original Message ----- > From: "Ryan Fox" <[EMAIL PROTECTED]> > To: "Lefevre, Steven" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Tuesday, January 07, 2003 11:33 AM > Subject: Re: Weird WHERE clause possible? >> >> Backing up, is the problem that you need to uniquely identify the > student's >> name after returning the results? If so, a simple, efficient way is to > add >> an auto_increment column to your students table, so each name is given a >> unique ID. Then, when results are returned, you can create a link for > each >> student that includes the student's ID. > Well, I do already have a unique ID on the table. >> >> If this isn't the case, and you really want to do a statement like that >> above, how about: >> SELECT * FROM Students WHERE LastName='Smith' and FirstName LIKE 'J%'; >> or worse, >> SELECT * FROM Students WHERE concat(LastName, ", ", FirstName) LIKE > 'Smith, >> J%'; > Do you say 'worse', because the query is slower? > I'm migrating users from a desktop FileMaker app to an Apache/Mysql/PHP > thingy. > The opening page of the FM app has a list of all the students, in > alphabetical order. It displays rather quickly. My webpage takes a while to > load all the students -- it's like 2 MBs of data over a dial-up line! > They are used to having a type ahead field on the main page of the Filemaker > app, where they type the first letter of the student. Then Filemaker shows > only, say, students begining with "G". Then, they type the next letter, and > see only "Ga". By this time, they have usually found their student. > So, in my web app, instead of sending a whole table of all the student > names, I just have a form field where they type in the last name, or the > first few letters of the last name. > Problem is with names like Smith, there are some 40 Smiths. > The user would then want to enter "Smith, Su" into the form field. That > breaks the form field because I was only searching on the last name. > I think your 'worse' query solves the problem, but why is it worse? > If I were using your first Idea, I'd worry more about improperly parsing the > user entry into the SQL, e. g. "Smith, Su" ->> "SELECT * FROM Students WHERE LastName='Smith' and > FirstName LIKE 'Su%';" > On a hunch, it seems more likely breakable b/c of user input. > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
