Creating customized searches

Hexxx

I have alot of data in a table and have to do a website which would allow a user to create customized seaches to view some or all the pieces of data in the table. the problem is there are at least 20 something attributes for that table, which means there could be quite ALOT of different combinations of searches possible and I won't be able to code them all. Is there anyway to do this? Create customized searches (and their queries) without actually typing out every (im sure at least a hundred) different combinations?

Roger_Ramjet

Yes, you have to design a sensible form that will help to build the query conditions by naming the input fields the same as the table column names. If the user wants to search on a column they add input into the associated text box.

Here is an example - I have left out all data validation for clarity but you must validate the input and protect against sql injection attacks when you use it live.

<form .....
<input type="text" name="col1" />
<input type="text" name="col2" />
<input type="text" name="col3" />
</form>

// then on the target script

$wary = array();

foreach ($_POST as $key=>$val) {
   if (!empty($val) and ($key != 'submit')) {
      $wary[$key] = $val;
   }
}
// base query
$sql = "SELECT * FROM table WHERE ";
// then add all submitted query params
foreach ($wary as $key=>$val) {
   $sql .= "$key = '$val' AND ";
}
// then remove the final AND
$sql = substr($sql, 0, (strlen($sql) - 4));

Hexxx

So I have to manually code using PHP about a couple hundred different queries and then figure out how to format them in presentable tables? I am planning to use checkboxes for letting the user select the columns he wants to see from the fact table.

I am using Dreamweaver and DreamWeaver Unleashed is talking about installing a ODBC and using it from DreamWeaver, would that help me/ would I need that?

Roger_Ramjet

Did you read my post?? I showed you how to get the user input and create a query from the fields selected and the input. If you want to do it with checkboxes then fine, the process is the same, you go through the post vars and find out which ones the user has selected and add where clause for that field. In my example if the user had input search terms fopr col1 and col3 then the query would be built as

"SELECT * FROM table WHERE col1 = 'foo' AND col3 = 'bar'"

No you do not use ODBC with mysql, you use the php mysql connection and functions. In fact you only ever use ODBC when nothing else is available.

If you want to use PHP then you'd be best to stop using Dreamweaver for it.

Hexxx

ok thanks, I'll try it and see. I now read it over better, thought it was going to be for just one query in sections, but it's for a more general query where the select statement consists of the user selection boxes.

Hexxx

I got some of the dynamic searches where, and I had to use the same suggestions you made as I had to select columns dynamically like

SELECT [B]col1[/B], [B]col3[/B], [B]col22 [/B]FROM [B]tableA[/B], [B]tableB[/B] where ....

Where the items in bold are also dynamically inserted to make the select statment. I now have to be able to display the output of this query in an HTML table form on the screen but I don't know how to create the HTML table to display it in because I don't the name of the colums that the user would be selecting so I can't say row['col1'] and such. Is there a command that would get me the names of the fields that I queried? The closed thing I found was mysql_list_fields but that only works for a table and not the result of the query.

Hexxx

Ok, I found one called mysql_field_name that works with the result. I'll try using that. I need to align each column properly and diffrentiate columns (every 2 a different column say), so I would have to try using a big of for loops.

Roger_Ramjet

mysql_fetch_array will return an indexed-associative array so you can use the index to find out the key of each field returned.

$row = mysql_fetch_array($result);

$row is now an array so you can use all array functions
array_keys will return all the keys so you've got the field names

or just do something like this

foreach ($row as $key=>$val ) {

$key give you the column name

You could also compare array keys of $row with an array that lists all the possible columns, you would then have an exception list of columns NOT returned which would be very usefull for conditional display where format and data type is an issue.

Hexxx

Thanks, I didn't think of using the associative array from mysql_fetch_array to get the key, your advice is most useful and appreciated.