...

View Full Version : Help with form to MySQL query



Richhead
01-13-2010, 10:42 AM
I have the following problem, can anyone help.

I have a search form that captures:

$track=$_POST['track'];
$category=$_POST['category'];
$type=$_POST['type'];
$artist=$_POST['artist'];
$label=$_POST['label'];
$issue=$_POST['issue'];

I need to be able to check the contents of the form and build the query accordingly, however this is further complicated by the fact that "track" searches 5 database fields (track1, track2, .. to track5), so I need that to be a bracketed (series of OR statements).

According to the inputs I may need to have:

SELECT * FROM datatable WHERE (track1 = '$track' OR track2 = '$track' OR track2 = '$track' OR track4 = '$track' OR track5 = '$track') AND catergory = $category AND issue = $issue

Or

SELECT * FROM datatable WHERE (track1 = '$track' OR track2 = '$track' OR track2 = '$track' OR track4 = '$track' OR track5 = '$track')

Or

SELECT * FROM datatable WHERE catergory = $category

Or

SELECT * FROM datatable

Etc... for all permutations

Short of working out all of the permutations and writing code to cater for each (very bloated code); I cannot work out how to do this....

Has anyone any great ideas?

Fou-Lu
01-13-2010, 12:58 PM
You don't write a query for each, you force you're queries to build:


$sQry = 'SELECT * FROM datatable';
$aCondition = array();
if (!empty($track))
{
$aCondition[] = "(track1 = '$track' OR track2 = '$track' OR track2 = '$track' OR track4 = '$track' OR track5 = '$track')";
}
if (!empty($category))
{
$aCondition[] = 'category = ' . $category;
}
if (!empty($issue))
{
$aCondition[] = 'issue = ' . $issue;
}

if (count($aCondition) > 0)
{
$sQry .= ' WHERE ' . implode(' AND ', $aCondition);
}


You'll need to look at protecting you're queries as well, so look into the mysql_real_escape_string function. Also, if you're spanning multiple columns to search a single value, you're table is un-normalized, so you'll want to look up database normalization in regards to properly splitting you're data to handle many-to-one and many-to-many relationships.

Richhead
01-13-2010, 01:24 PM
You don't write a query for each, you force you're queries to build:


$sQry = 'SELECT * FROM datatable';
$aCondition = array();
if (!empty($track))
{
$aCondition[] = "(track1 = '$track' OR track2 = '$track' OR track2 = '$track' OR track4 = '$track' OR track5 = '$track')";
}
if (!empty($category))
{
$aCondition[] = 'category = ' . $category;
}
if (!empty($issue))
{
$aCondition[] = 'issue = ' . $issue;
}

if (count($aCondition) > 0)
{
$sQry .= ' WHERE ' . implode(' AND ', $aCondition);
}


PERFECT! Many thanks, exactly what I wanted.


You'll need to look at protecting you're queries as well, so look into the mysql_real_escape_string function.

Err.. ok, but the data will need to be able to contain quotes and ampersands etc. when saved... but I shall dig.


Also, if you're spanning multiple columns to search a single value, you're table is un-normalized, so you'll want to look up database normalization in regards to properly splitting you're data to handle many-to-one and many-to-many relationships.

Yes, agreed... although I am not sure how I would even begin to construct the SQL for the query.... I guess it's an "IN" somehow included there.

REALLY appreciate you taking the time. Thank you very much :D

Rich

Fou-Lu
01-13-2010, 01:29 PM
mysql_real_escape_string preserves what you want, but escapes it so it will not step out of you're query itself. This is especially important with quotes. You would join you're tables or execute multiple queries to handle the spanning. For what you have above, you would have 2 tables in total. The resultset would include n x m, where n is the records matched in table 1 and n is the records matched in table 2.

Richhead
01-13-2010, 01:47 PM
Not sure about separating the tracks now - need to see the results as

Category, Type, Artist, Track 1, Track 2, etc... as rows in a table, hence my non-normalised view of the data in one table

Richhead
01-13-2010, 02:11 PM
All working perfectly now, thanks, have added mysql_real_escape as a function and ensure all form data is escaped.

Many, many, thanks for the help.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum