?
Solved

Sorting parameter arrays and filtering mysql query

Posted on 2013-11-14
13
1 solution
Medium Priority
?
393 Views
Last Modified: 2013-11-14
Hi,

I have an mysql query which need to be filtered by the values of an url parameter that looks like this: <url>?featuresId=18, 2, 50

I have the array below to sort my values:

------------------

$temp=explode(',', $HTTP_GET_VARS['featuresId']);
foreach($temp as $index=>$value)
{
      if(is_int($value))
      {
            $temp[$index]=(int)$value;
      }
      else
      {
            unset($temp[$index]);
      }
}
asort($temp);
$get_features_query = "select * from features where feature_name like '%|" . implode("|%|", $temp) . "|%'";

-------------------------------------

The thing is i need the query to be filtered by feature_name like this:

$get_features_query = "select * from features where feature_name like %|2|%|18|%|50|%;

Any ideas how can i get to the bottom of this?

Thanks
0
Comment
Question by:b0byan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 8
  • 4
13 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39647794
You need to get something of a foundation in how to use PHP.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

I'll look at the problem specifics in a moment. ~Ray
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39647826
You would want to start with something like this:
http://www.laprbass.com/RAY_temp_b0byan.php

<?php // RAY_temp_b0byan.php
error_reporting(E_ALL);

/**
 * ?featuresId=18, 2, 50
 * SEE http://www.experts-exchange.com/Database/MySQL/Q_28293954.html
 */

// IF THERE IS ANYTHING IN THE REQUEST
if (!empty($_GET['featuresId']))
{
    var_dump($_GET);
}

// CREATE THE FORM FOR THE INPUT VALUES
$form = <<<ENDFORM
<form>
<input name="featuresId" />
<input type="submit" />
</form>
ENDFORM;

echo $form;

Open in new window

This code will enable you to see the relationship between the URL variables and the data that arrives in the PHP script via the $_GET array.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39647860
Next, we would move on to something like this, which filters the input.  Note that it uses is_numeric() and does not use is_int().  The reason for this choice is clear when you read the online man page for is_int().

<?php // RAY_temp_b0byan.php
error_reporting(E_ALL);

/**
 * ?featuresId=18, 2, 50
 * SEE http://www.experts-exchange.com/Database/MySQL/Q_28293954.html
 */

// IF THERE IS ANYTHING IN THE REQUEST
if (!empty($_GET['featuresId']))
{
    // ACTIVATE THIS LINE TO SEE THE REQUEST VARIABLES
    // var_dump($_GET);

    // BREAK THE REQUEST VARIABLES INTO AN ARRAY, USING THE COMMA
    $arr = explode(',', $_GET['featuresId']);

    // ELIMINATE ANYTHING THAT IS NOT A NUMBER
    foreach ($arr as $key => $val)
    {
        $arr[$key] = trim($val);
        if (is_numeric($arr[$key])) continue;
        unset($arr[$key]);
    }

    // IF THERE ARE ANY NUMBERS LEFT
    if (!empty($arr)) var_dump($arr);
}

// CREATE THE FORM FOR THE INPUT VALUES
$form = <<<ENDFORM
<form>
<input name="featuresId" value="18, 2, 50" />
<input type="submit" />
</form>
ENDFORM;

echo $form;

Open in new window

0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Download the list
 

Author Comment

by:b0byan
ID: 39647872
Hi,

I don't know if you understood me right. Maybe my english is not too good and i'm sorry for this.

To make it more clearer why i need the query filtered like this is because my table column looks something like this:

products_id featuresId
10107 |1|2|3|5|6|22|27|29|53|56|86|96|100|103|104|117|13...
16536 |3|6|9|13|14|21|22|39|40|51|53|56|57|58|65|66|136|...
121169 |3|6|7|9|11|12|15|17|18|21|32|34|39|41|42|50|51|56...

If you look at the featuresId column you'll understand why i need those things in the begining. I don't see any other solution to find and narrow my results searching i nthis column but this

Thanks
Hope you help me solve this
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39647893
The next step would be to construct the query string.  You would never want to use SELECT * (antipractice #26) but instead your query would name the columns that you wanted to select.  

Without seeing the CREATE TABLE statement, we are guessing about the nature of the feature_name column.  I am going to guess that it is not a "name" but is really a numeric id, probably of type INT.  This next step would enable us to create the WHERE clause for the query.

<?php // RAY_temp_b0byan.php
error_reporting(E_ALL);

/**
 * ?featuresId=18, 2, 50
 * SEE http://www.experts-exchange.com/Database/MySQL/Q_28293954.html
 */

// IF THERE IS ANYTHING IN THE REQUEST
if (!empty($_GET['featuresId']))
{
    // ACTIVATE THIS LINE TO SEE THE REQUEST VARIABLES
    // var_dump($_GET);

    // BREAK THE REQUEST VARIABLES INTO AN ARRAY, USING THE COMMA
    $arr = explode(',', $_GET['featuresId']);

    // ELIMINATE ANYTHING THAT IS NOT A NUMBER
    foreach ($arr as $key => $val)
    {
        $arr[$key] = trim($val);
        if (is_numeric($arr[$key])) continue;
        unset($arr[$key]);
    }

    // IF THERE ARE ANY NUMBERS LEFT
    if (!empty($arr))
    {
        $where = 'WHERE feature_name = ' . implode(' OR feature_name = ', $arr);
        echo $where;
    }
}

// CREATE THE FORM FOR THE INPUT VALUES
$form = <<<ENDFORM
<form>
<input name="featuresId" value="18, 2, 50" />
<input type="submit" />
</form>
ENDFORM;

echo $form;

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39647901
products_id featuresId
10107 |1|2|3|5|6|22|27|29|53|56|86|96|100|103|104|117|13...
16536 |3|6|9|13|14|21|22|39|40|51|53|56|57|58|65|66|136|...
121169 |3|6|7|9|11|12|15|17|18|21|32|34|39|41|42|50|51|56...
Oh, NO!  Your data base is misdesigned.  Please make a Google search for the exact terms "Should I Normalize My Database" and read the very thoughtful discussion on the issue.  You should never have more than one atomic value in any column of a data base table.  A correct design would link the products_id to the featuresId via a junction table.  This would establish the relationship that is inherent in relational data base.
0
 

Author Comment

by:b0byan
ID: 39647907
Yeaa sorry for the misspeling. I've modified the query for this example only and missed some stuff. In fact feature_name is featuresId
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39647930
If you're stuck with the existing design (and I sincerely hope you're not) you may be able to construct the WHERE clause with something like this.

<?php // RAY_temp_b0byan.php
error_reporting(E_ALL);

/**
 * ?featuresId=18, 2, 50
 * SEE http://www.experts-exchange.com/Database/MySQL/Q_28293954.html
 */

// IF THERE IS ANYTHING IN THE REQUEST
if (!empty($_GET['featuresId']))
{
    // ACTIVATE THIS LINE TO SEE THE REQUEST VARIABLES
    // var_dump($_GET);

    // BREAK THE REQUEST VARIABLES INTO AN ARRAY, USING THE COMMA
    $arr = explode(',', $_GET['featuresId']);

    // ELIMINATE ANYTHING THAT IS NOT A NUMBER
    foreach ($arr as $key => $val)
    {
        $arr[$key] = trim($val);
        if (is_numeric($arr[$key])) continue;
        unset($arr[$key]);
    }

    // IF THERE ARE ANY NUMBERS LEFT
    if (!empty($arr))
    {
        $where = "WHERE featuresId LIKE '%|" . implode("|%' OR featuresId LIKE '%|", $arr) . "|%'";
        echo $where;
    }
}

// CREATE THE FORM FOR THE INPUT VALUES
$form = <<<ENDFORM
<form>
<input name="featuresId" value="18, 2, 50" />
<input type="submit" />
</form>
ENDFORM;

echo $form;

Open in new window

Edited to use "featuresId" instead of "feature_name"
0
 

Author Comment

by:b0byan
ID: 39647954
Yea i know the table is not a good design. In fact is very troublesome. But this is how i received my data from my employers and can't argue with that.
I have to go around some problems. And this is the only solution that i found to filter and narrow products by facility.
I cannot modify the table.

My question is can you help me in this case?

The way i constructed the url and the filter worked so far for me. The only issue that i have in the query below is i have to have the parameter values ordered ascending in order to display the results correctly.

This is what i need. To order the values in an array and then put them in the query in this form  '%|2|%|18|%|50|%'

$get_features_query = "select * from features where featuresId like '%|" . str_replace(",", "|%|", $HTTP_GET_VARS['featuresId']) . "|%'

Thanks
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39647972
Please see this link.
http://www.experts-exchange.com/Database/MySQL/Q_28293954.html#a39647930

You can use PHP sort() to change the order of array elements.

When you post a question here at EE, you will get quick and accurate help if you prepare the SSCCE.  In the case of database questions this would include the CREATE TABLE statement.

Do not use HTTP_GET_VARS -- it is deprecated.
http://php.net/manual/en/reserved.variables.get.php
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39647983
This shows how to sort the numbers.  Please see line 30.  If your database is really set up as described, I'm fairly certain that the sort will not be needed.  If you want to post the CREATE TABLE statement and some sample data, I'll be glad to show you what you can do with the WHERE clause.

<?php // RAY_temp_b0byan.php
error_reporting(E_ALL);

/**
 * ?featuresId=18, 2, 50
 * SEE http://www.experts-exchange.com/Database/MySQL/Q_28293954.html
 */

// IF THERE IS ANYTHING IN THE REQUEST
if (!empty($_GET['featuresId']))
{
    // ACTIVATE THIS LINE TO SEE THE REQUEST VARIABLES
    // var_dump($_GET);

    // BREAK THE REQUEST VARIABLES INTO AN ARRAY, USING THE COMMA
    $arr = explode(',', $_GET['featuresId']);

    // ELIMINATE ANYTHING THAT IS NOT A NUMBER
    foreach ($arr as $key => $val)
    {
        $arr[$key] = trim($val);
        if (is_numeric($arr[$key])) continue;
        unset($arr[$key]);
    }

    // IF THERE ARE ANY NUMBERS LEFT
    if (!empty($arr))
    {
        // SORT THE NUMBERS
        sort($arr);

        $where = "WHERE featuresId LIKE '%|" . implode("|%' OR featuresId LIKE '%|", $arr) . "|%'";
        echo $where;
    }
}

// CREATE THE FORM FOR THE INPUT VALUES
$form = <<<ENDFORM
<form>
<input name="featuresId" value="18, 2, 50" />
<input type="submit" />
</form>
ENDFORM;

echo $form;

Open in new window

0
 

Author Closing Comment

by:b0byan
ID: 39648132
Thanks for your help. I'll go from here on my own
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

30 Day Free Trial
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Display your dates as Today, Yesterday etc in PHP
Article by: Gary
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
PHP Error Handling: Never Say die() Again
Article by: Ray
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Using PHP to Count Item Occurrences in Arrays
Video by: Marco
The viewer will learn how to count occurrences of each item in an array.
How to Add a Watermark to an Image Using PHP (Part 1)
Video by: Marco
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Advertise Here
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here