By the way.. YOu must use LIKE for it to work.. GEOGRAPHIC= won't work it has to be GEOGRAPHIC LIKE ..
Thanks, Gurhan -----Original Message----- From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] Sent: Friday, March 15, 2002 7:37 PM To: [EMAIL PROTECTED] Subject: RE: Fetching an optional array from an optional column Hi :) Rewrite your query statement as: $sql = "SELECT * FROM links WHERE (SUBJECT1='$formatted_subject' OR SUBJECT2='$formatted_subject') AND GEOGRAPHIC='".$geographic."%' ORDER BY ORGANIZATION ASC"; I think it looks like a $geographic% variable:) Let me know if it does or does not work.. Sincerely, Gurhan -----Original Message----- From: phplist [mailto:[EMAIL PROTECTED]] Sent: Friday, March 15, 2002 7:37 PM To: [EMAIL PROTECTED] Subject: FW: Fetching an optional array from an optional column Thank you for the hint. I've tried further, and still came up short. This time, if I use the % - it returns nothing. Here's the sample code I'm using: <?php if (!subject) { header("Location: nowhere.html"); exit; } // Connects to database include("cnx_db.inc"); // First addslashes to text: $formatted_subject = addslashes($subject); // Formats Query $sql = "SELECT * FROM links WHERE (SUBJECT1='$formatted_subject' OR SUBJECT2='$formatted_subject') AND GEOGRAPHIC='$geographic%' ORDER BY ORGANIZATION ASC"; print($sql); $sql_result = mysql_query($sql); if (!$sql_result) { echo "Can't execute $sql " . mysql_error(); exit; } // organizes data in an orderly manner (ie bulleted area) while ($row = mysql_fetch_array($sql_result)) { $esc_organization = $row["ORGANIZATION"]; $esc_desc = $row["DESCRIPTION"]; $esc_url = $row["URL"]; $esc_subject1 = $row["SUBJECT1"]; $esc_subject2 = $row["SUBJECT2"]; $esc_geographic = $row["GEOGRAPHIC"]; $organization = stripslashes($esc_organization); $description = stripslashes($esc_desc); $url = stripslashes($esc_url); $subject1 = stripslashes($esc_subject1); $subject2 = stripslashes($esc_subject2); $geographic = stripslashes($esc_geographic); $option_block .= "<li><b>Organization:</b> <a href=\"http://$url\";>$organization</a><br><b>Geographic Region:</b> $geographic<br><b>Description:</b> $description<br><b>URL:</b> <a href=\"http://$url\";>$url</a></li><br><b>Subject1:</b> $subject1<br><b>Subject2:</b> $subject2\n"; } include("byebye.inc"); ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Test Result</title> </head> <body> <h1><?php echo $subject; ?></h1> You have chosen <?php echo $subject; ?>. Here are the results: <Ul> <?php echo $option_block; ?> </UL> </body> </html> The printed SQL comes out like this: SELECT * FROM links WHERE (SUBJECT1='Legislation' OR SUBJECT2='Legislation') AND GEOGRAPHIC='%' ORDER BY ORGANIZATION ASC Any ideas? Again, thanks for your help > -----Original Message----- > From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] > Sent: Friday, March 15, 2002 4:18 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: Fetching an optional array from an optional column > > > Hi, > > Since you are using a form to query the table i assume the > subject1, subject and geographic will be variables. So, you > could do something like: > > SELECT * FROM links WHERE (subject1='$sub1' OR > subject2='$sub2') AND GEOGRAPHIC like '$geo%' > > In this way, if the user enters no value for the variable > $geo then the query statement will query with (WHERE > geographic like '%' ) clause which will mean any country.. On > the other hand, if the user enters a country name for the > $geo , say Canada, then the query statement will query with > (WHERE geopgrahic like 'Canada%') clause which will hit the > rows with Canada. > > Gurhan > > > -----Original Message----- > From: phplist [mailto:[EMAIL PROTECTED]] > Sent: Friday, March 15, 2002 7:01 PM > To: [EMAIL PROTECTED] > Subject: Fetching an optional array from an optional column > > > I'm working on a form that queries by selection in PHP, where > a user selects a subject and an optional selection of > geographic region to get information. Each database entry > will have 2 subject fields, Subject 1 being the main subject > and Subject 2 being the cross-subject, and a geographic > field, which is optional. A table is set up like this: > > +------+--------------+------+----------+----------+-------------+ > | ID | Organization | URL | SUBJECT1 | SUBJECT2 | Geographic | > +------+--------------+------+----------+----------+-------------+ > | 1 | Acme | www | Math | English | Canada | > | 2 | Loony Toons | www | Comedy | Math | Brazil | > > ... > > > The idea is that the query will check the database to see if > $Subject has a match in either Subject1 or Subject2. The > geographic is an optional selection. If I select a country, > then that's fairly simple. However, if I select Math as a > subject, and left the Geographic option unselected, I want it > to select all records that has a match in either Subject1 and > Subject2, irregardless of the Geographic option. > > I tried the following query: > > SELECT * FROM links WHERE (SUBJECT1='Legislation' OR > SUBJECT2='Legislation') AND GEOGRAPHIC = '' > > But it won't find the above records because the geographic is > not a blank field. > > I could drop the ".. AND GEOGRAPHIC" part, but then if > someone does select a country, it won't take country into > consideration. How do I get it to select all records based on > the subject, irregardless of the country? > > Thanks in advance, > > Lmlweb > > > > --------------------------------------------------------------------- > 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 > > --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
