[PHP-DB] How to eliminate bad fields from a mysql query?
Dear PHP Helpers, Can you direct me to a solution for this problem: How can I eliminate bad (blank or incorrect) fields from a mysql query? In other words, if someone leaves a form field blank or enters a value that does not have a matching entry in the database, how can I eliminate that variable from the query? The query that I am using now works fine if the user enters a value in field that exists in the database. However if a field is left blank or incorrect data is entered, no results will display on the screen. Here is the code that I am using: $result = mysql_query("SELECT salutation, first_name, mid_name, user_name, cert_deg, job_title, institution, address_1, address_2, geo_loc, state, zip, country, phone, fax, e_mail FROM svt_members where first_name like '$first_name' user_name like '$user_name' institution like '$institution' state='$state' country like '$country'", $db); while ($myrow = mysql_fetch_array($result)) { printf("Name: bfont color=\"green\" size=\"5s\"%s %s %s %s\nbr/b/font", $myrow["salutation"], $myrow["first_name"], $myrow["mid_name"], $myrow["user_name"]); printf("Credentials and Degrees: bfont color=\"blue\" size=\2\"%s/font/bbrJob Title: bfont color=\"blue\" size=\2\"%s/font/bbrCompany/Institution: bfont color=\"blue\" size=\2\"%sbr/font/bAddress: bfont color=\"blue\" size=\2\"%s %sbr/font/bCity: bfont color=\"blue\" size=\2\"%s/font/b State/Province: bfont color=\"blue\" size=\2\"%sbr/font/bCountry: bfont color=\"blue\" size=\2\"%s/font/b Postal Code: bfont color=\"blue\" size=\2\"%sbr/font/bVoice: bfont color=\"blue\" size=\2\"%s/font/b Fax: bfont color=\"blue\" size=\2\"%sbr/font/bE-mail: bfont color=\"blue\" size=\2\"%s\nP/b/font", $myrow["cert_deg"], $myrow["job_title"], $myrow["institution"], $myrow["address_1"], $myrow["address_2"], $myrow["geo_loc"], $myrow["state"], $myrow["country"], $myrow["zip"], $myrow["phone"], $myrow["fax"], $myrow["e_mail"]); } So if (for instance) a user enters data for each field except institution, how can I get the query to disregard $institution? Or if there is a better approach that I should be using instead of the above, please let me know. Thank you, Bob __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] How to eliminate bad fields from a mysql query?
Bob, Don't know if this is what you're looking for, but you *could* do something like: $noData = true; $strSQL = "SELECT field1, field2, field3 FROM table "; $results = mysql_query($strSQL, $db) or $noData = false; if ($noData) { print "TABLE"; while (list($field1, $field2, $field3) = mysql_fetch_array($results)) { len(trim($field1)) 0 ? print "TRTDLABEL/TDTD$field1/TD/TR" : print ""; len(trim($field2)) 0 ? print "TRTDLABEL/TDTD$field2/TD/TR" : print ""; len(trim($field3)) 0 ? print "TRTDLABEL/TDTD$field3/TD/TR" : print ""; } print "/TABLE"; } somewhere in your code, you might consider a cascading-style sheet 1. Cleaner to read 2. Eliminates redundant code 3. Smaller file size 4. Smaller file size = faster download maybe something like... STYLE TYPE="text/css" !-- TD {color: #FF; font-size: 10pt} //-- /STYLE now about consistent values in the database you could setup an ENUM field to ensure that you're getting data consistently. mySQL won't allow certain things to be entered if it doesn't follow a convention... Ex: mysqlALTER TABLE tShirts ADD sizes ENUM('S','M','L','XL','XXL') NOT NULL DEFAULT('M'); now for institutions??? the IF-THEN/WHILE structure will prevent blank entries, incorrect entries are another thing... HTH, christohper Oson -Original Message- From: Bob Stone [mailto:[EMAIL PROTECTED]] Sent: Friday, March 30, 2001 2:56 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] How to eliminate bad fields from a mysql query? Dear PHP Helpers, Can you direct me to a solution for this problem: How can I eliminate bad (blank or incorrect) fields from a mysql query? In other words, if someone leaves a form field blank or enters a value that does not have a matching entry in the database, how can I eliminate that variable from the query? The query that I am using now works fine if the user enters a value in field that exists in the database. However if a field is left blank or incorrect data is entered, no results will display on the screen. Here is the code that I am using: $result = mysql_query("SELECT salutation, first_name, mid_name, user_name, cert_deg, job_title, institution, address_1, address_2, geo_loc, state, zip, country, phone, fax, e_mail FROM svt_members where first_name like '$first_name' user_name like '$user_name' institution like '$institution' state='$state' country like '$country'", $db); while ($myrow = mysql_fetch_array($result)) { printf("Name: bfont color=\"green\" size=\"5s\"%s %s %s %s\nbr/b/font", $myrow["salutation"], $myrow["first_name"], $myrow["mid_name"], $myrow["user_name"]); printf("Credentials and Degrees: bfont color=\"blue\" size=\2\"%s/font/bbrJob Title: bfont color=\"blue\" size=\2\"%s/font/bbrCompany/Institution: bfont color=\"blue\" size=\2\"%sbr/font/bAddress: bfont color=\"blue\" size=\2\"%s %sbr/font/bCity: bfont color=\"blue\" size=\2\"%s/font/b State/Province: bfont color=\"blue\" size=\2\"%sbr/font/bCountry: bfont color=\"blue\" size=\2\"%s/font/b Postal Code: bfont color=\"blue\" size=\2\"%sbr/font/bVoice: bfont color=\"blue\" size=\2\"%s/font/b Fax: bfont color=\"blue\" size=\2\"%sbr/font/bE-mail: bfont color=\"blue\" size=\2\"%s\nP/b/font", $myrow["cert_deg"], $myrow["job_title"], $myrow["institution"], $myrow["address_1"], $myrow["address_2"], $myrow["geo_loc"], $myrow["state"], $myrow["country"], $myrow["zip"], $myrow["phone"], $myrow["fax"], $myrow["e_mail"]); } So if (for instance) a user enters data for each field except institution, how can I get the query to disregard $institution? Or if there is a better approach that I should be using instead of the above, please let me know. Thank you, Bob __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] How to eliminate bad fields from a mysql query?
If there are some fields that you absolutley require, or must be in a valid format, I'd use javascript to edit the form first - no reason to make a round trip to the server for something you can do server-side. **Or** - say the form is for selecting an automobile - make is required but color isn't nor is model. $sql = "Select * from someDB where make =" . $make; If $model "" { $sql = $sql . " And model = " . $model; } If $color "" { $sql = $sql . " and color=" . $color } Build your sql string incrementally and conditionally - check what you can client-side.. Phil J. Bob Stone wrote: Dear PHP Helpers, Can you direct me to a solution for this problem: How can I eliminate bad (blank or incorrect) fields from a mysql query? In other words, if someone leaves a form field blank or enters a value that does not have a matching entry in the database, how can I eliminate that variable from the query? The query that I am using now works fine if the user enters a value in field that exists in the database. However if a field is left blank or incorrect data is entered, no results will display on the screen. Here is the code that I am using: $result = mysql_query("SELECT salutation, first_name, mid_name, user_name, cert_deg, job_title, institution, address_1, address_2, geo_loc, state, zip, country, phone, fax, e_mail FROM svt_members where first_name like '$first_name' user_name like '$user_name' institution like '$institution' state='$state' country like '$country'", $db); while ($myrow = mysql_fetch_array($result)) { printf("Name: bfont color=\"green\" size=\"5s\"%s %s %s %s\nbr/b/font", $myrow["salutation"], $myrow["first_name"], $myrow["mid_name"], $myrow["user_name"]); printf("Credentials and Degrees: bfont color=\"blue\" size=\2\"%s/font/bbrJob Title: bfont color=\"blue\" size=\2\"%s/font/bbrCompany/Institution: bfont color=\"blue\" size=\2\"%sbr/font/bAddress: bfont color=\"blue\" size=\2\"%s %sbr/font/bCity: bfont color=\"blue\" size=\2\"%s/font/b State/Province: bfont color=\"blue\" size=\2\"%sbr/font/bCountry: bfont color=\"blue\" size=\2\"%s/font/b Postal Code: bfont color=\"blue\" size=\2\"%sbr/font/bVoice: bfont color=\"blue\" size=\2\"%s/font/b Fax: bfont color=\"blue\" size=\2\"%sbr/font/bE-mail: bfont color=\"blue\" size=\2\"%s\nP/b/font", $myrow["cert_deg"], $myrow["job_title"], $myrow["institution"], $myrow["address_1"], $myrow["address_2"], $myrow["geo_loc"], $myrow["state"], $myrow["country"], $myrow["zip"], $myrow["phone"], $myrow["fax"], $myrow["e_mail"]); } So if (for instance) a user enters data for each field except institution, how can I get the query to disregard $institution? Or if there is a better approach that I should be using instead of the above, please let me know. Thank you, Bob __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]