----- 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
