Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Jan 2010
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question Help with form to MySQL query

    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?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You don't write a query for each, you force you're queries to build:
    PHP Code:
    $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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    Richhead (01-13-2010)

  • #3
    New to the CF scene
    Join Date
    Jan 2010
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    You don't write a query for each, you force you're queries to build:
    PHP Code:
    $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.

    Quote Originally Posted by Fou-Lu View Post
    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.

    Quote Originally Posted by Fou-Lu View Post
    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

    Rich

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    Richhead (01-13-2010)

  • #5
    New to the CF scene
    Join Date
    Jan 2010
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

  • #6
    New to the CF scene
    Join Date
    Jan 2010
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Smile

    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •