Need help with db select

Report · May 01, 2011

As usual I have been stuck for a while on what is probably a very simple solution so perhaps someone can help me, thanks.

I have a portfolio page to which I post a url to filter the portfolio type (0, 1 or 2) and display the results.

It works ok except that I can't get the url right to show all records. I have tried things like

/portfolio.php?websitetype=1&2
and
/portfolio.php?websitetype=*

/portfolio.php?websitetype>0

Perhaps I need to change my select?

$query_portfolio = "SELECT * FROM portfoliodb WHERE portfoliodb.websitetype='". $_GET['websitetype'] ."' AND portfoliodb.visible = 1 ORDER BY portfoliodb.portfolio_id DESC";

Report · May 01, 2011

bikeman01 wrote:
As usual I have been stuck for a while on what is probably a very simple solution so perhaps someone can help me, thanks.
I have a portfolio page to which I post a url to filter the portfolio type (0, 1 or 2) and display the results.
It works ok except that I can't get the url right to show all records. I have tried things like
/portfolio.php?websitetype=1&2
and
/portfolio.php?websitetype=*
/portfolio.php?websitetype>0
Perhaps I need to change my select?
$query_portfolio = "SELECT * FROM portfoliodb WHERE portfoliodb.websitetype='". $_GET['websitetype'] ."' AND portfoliodb.visible = 1 ORDER BY portfoliodb.portfolio_id DESC";

First address ?websitetype=1&2 is like saying $_GET['websitetype']=1 and 2=nothing, so that address won't work. Neither will address 2 or 3. What is "*" and what is >0?. The websitetype variable should equal a number. Simple as that. If the portfoliotype is equal to 1, is the visible field also set to 1? If so, can we see the loop you are running to print all records because that is more likely where your problem lies.

Report · May 01, 2011

I don't have a loop to print all records. What you see is what I have - I can only show specific 'websitetype' 1,2 or 3

I had hoped that * would be a wildcard ie show all records

Likewise I also tried /portfolio.php?websitetype=1&websitetype=2&websitetype=3

Can I change the select statement to show all records if the url doesn't have any params ie /portfolio.php ?

Report · May 01, 2011

For the URL parameter the query is filtering the item. What you can do is tell php to query everything IF the URL parameter equals a certain value, like ALL. Or else your can tell php to filter based off the entered value like 1 or 2 or 3 or whatever.

Try something like this for your query:

// run the query if the URL parameter for websitetype is set.
// for example: portfolio.php?websitetype=1 or portfolio.php?websitetype=ALL
if (isset($_GET['websitetype'])) {
          // start condition to run query if ALL types are to be shown.
          if ($_GET['websitetype'] == 'ALL') {
                    $query_portfolio = "SELECT *
                    FROM portfoliodb
                    ORDER BY portfoliodb.portfolio_id DESC";
               // else run a query to filter only selected types
               // based off value of URL parameter
               } else {
                    $query_portfolio = "SELECT *
                    FROM portfoliodb
                    WHERE portfoliodb.websitetype='". $_GET['websitetype'] ."'
                    AND portfoliodb.visible = 1
                    ORDER BY portfoliodb.portfolio_id DESC";
          // end the condition whether the URL parameter
          // was for ALL types or a certain type.
          }
// end if the URL parameter for websitetype is set.
}

Then use /portfolio.php?websitetype=ALL to show ALL records or /portfolio.php?websitetype=1 to show records where type equals 1.

Report · May 01, 2011

Thanks chap - i can see this is the solution I need so I'll give it a go.

Report · May 02, 2011

Ok I've now got it working as suggested so that's a big improvement, thanks but it's not quite what I was after. What I want is for all records to be retrieved by default when there is no url suffix and not require ?websitetype=ALL

Report · May 02, 2011

So just change the condition so that the query filter is not included if the query parameter is empty.

Report · May 02, 2011

bikeman01 wrote:
Ok I've now got it working as suggested so that's a big improvement, thanks but it's not quite what I was after. What I want is for all records to be retrieved by default when there is no url suffix and not require ?websitetype=ALL

That is not what your OP asked for!

It works ok except that I can't get the url right to show all records. I have tried things like

/portfolio.php?websitetype=1&2
and
/portfolio.php?websitetype=*

/portfolio.php?websitetype>0

// determine if the URL parameter for websitetype is set.
if (isset($_GET['websitetype'])) {
               // run a query to filter only selected types
               // based off value of URL parameter
                    $query_portfolio = "SELECT *
                    FROM portfoliodb
                    WHERE portfoliodb.websitetype='". $_GET['websitetype'] ."'
                    AND portfoliodb.visible = 1
                    ORDER BY portfoliodb.portfolio_id DESC";
// else if the URL parameter for websitetype is not set.
} else {
                    // run query to display ALL types
                    $query_portfolio = "SELECT *
                    FROM portfoliodb
                    ORDER BY portfoliodb.portfolio_id DESC";
// end condition if the URL parameter for websitetype is set.
}