question about combining results

[Ansel_Tk1]

Hi All - please excuse if this is not the correct place to post this question.

 

I am trying to create a form that passes to a php page that generates the results. The form fields are 'servicetypes', 'producttypes', 'city' and 'provstate'.

 

My SQL statement on the results is creating a recordset based on this statement:

 

SELECT *

FROM smm_public_directory_listing

WHERE directory_listing_servicetypes = colname OR directory_listing_producttypes = colname2 OR directory_listing_city = colname3 OR directory_listing_provstate = colname4

 

(where colnameX = $_GET['searchfield']

 

problem is - I want the results to return like this:

 

If just one field is entered, get all records that contain that one field.

If more than one field is entered, get all records that have the combination of those fields.

 

Can someone point me in the right direction? Is this done on the PHP side instead of the SQL statement?

 

Thank you for any help you could give!

[Wildbug]

You can do this in either PHP or MySQL depending on your preference.  I'd probably assemble a query dynamically in PHP.  You also need to decide whether you want to use AND or OR -- return results that match all or any condition.

 

If you want to do this totally in MySQL, you'll need to use something like an IF() function to account for times when nothing is entered into the search field.  If you use OR to connect the conditions, you'll want to use a zero (0) as the true condition; if you use AND, use one (1).  You may need to adjust the comparison to suit your needs, especially if not using strings.

 

$query_string = sprintf("SELECT * FROM smm_public_directory_listing WHERE
IF('%s' <=> '',0,directory_listing_servicetypes = '%s') OR
IF('%s' <=> '',0,directory_listing_producttypes = '%s') OR
IF('%s' <=> '',0,directory_listing_city = '%s') OR
IF('%s' <=> '',0,directory_listing_provstate = '%s')",
mysql_real_escape_string($_GET['colname1']),
mysql_real_escape_string($_GET['colname1']),
mysql_real_escape_string($_GET['colname2']),
mysql_real_escape_string($_GET['colname2']),
mysql_real_escape_string($_GET['colname3']),
mysql_real_escape_string($_GET['colname3']),
mysql_real_escape_string($_GET['colname4']),
mysql_real_escape_string($_GET['colname4'])
);

 

To do this in PHP, you can dynamically "glue" together options with values using your OR (or AND) with standard concatenation methods.

[Ansel_Tk1]

Hi - thank you so much for your help.

 

I have tried your script and got two errors (please forgive my newbieness!):

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /mnt/w0202/d11/s45/b02a14e1/www/securitymattersmag.com/index-results.php on line 209

 

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /mnt/w0202/d11/s45/b02a14e1/www/securitymattersmag.com/index-results.php on line 243

 

I have used the script:

$query_security_directory_results = sprintf("SELECT * FROM smm_public_directory_listing WHERE

IF('%s' <=> '',0,directory_listing_servicetypes = '%s') OR

IF('%s' <=> '',0,directory_listing_producttypes = '%s') OR

IF('%s' <=> '',0,directory_listing_city = '%s') OR

IF('%s' <=> '',0,directory_listing_provstate = '%s')",

mysql_real_escape_string($_GET['colname']),

mysql_real_escape_string($_GET['colname']),

mysql_real_escape_string($_GET['colname2']),

mysql_real_escape_string($_GET['colname2']),

mysql_real_escape_string($_GET['colname3']),

mysql_real_escape_string($_GET['colname3']),

mysql_real_escape_string($_GET['colname4']),

mysql_real_escape_string($_GET['colname4'])

);

 

Line 209:

<?php } while ($row_security_directory_results = mysql_fetch_assoc($security_directory_results)); ?>

 

Line 243:

mysql_free_result($security_directory_results);

 

 

Also, why are the $_GET'['colname'] lines referred to twice each?

 

Thank you again!

Dan

 

 

 

[fenway]

Ugh, that's ugly... just build up each field by pushing onto an array, and then join then with ORs at the end.

[Wildbug]

Ugh, that's ugly... just build up each field by pushing onto an array, and then join then with ORs at the end.

 

Yeah, that's the way I'd do it in PHP (as I noted in my post), but I wanted to show it was possible to do completely in MySQL.  You have to do this type of thing sometimes when using prepared queries with Perl's DBD, for example.

 

Ansel, I didn't necessarily post that code for you to copy and paste directly into your script.  It was more an exercise in possibility.  Try to understand what it means before you copy it blindly, please.  It looks like you haven't actually queried the database, just handed the string to mysql_fetch_assoc().  Read the man page on sprintf() to understand why the column names are listed twice (the %s are replaced with the following arguments).

 

Also, if there's no good reason to do it all in MySQL, go with fenway's advice and stitch together a query string entirely in PHP.  It's better programming style.