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