Problems with MySQL query

I’m having a few problems with this query and am not sure how to fix it. It used to work with the $sql_where showing as many times as it’s needed but now it will only show the latest one and I don’t know how to change that.

This is the code I’m using but if somebody selects male and type 1 then it will only add ‘)’ to the query but is should be added ‘OR gender=1 OR type_1 )’ This used to work but the only thing that’s changed is the site has been moved to a new server.

$sql_select = "SELECT * "; 
$sql_from = " FROM clothes, product_categories_map, type, source, sweeteners, flavour, ingredients, dietary, shipping, merchant WHERE clothes.enabled=1 and clothes.stock=1 and clothes.deleted=0 and clothes.label=shipping.merchant and shipping.country_id=".$_POST['country']." AND ( product_feed_id != 1 ";
$sql_group = " GROUP BY clothes.product_name";
$sql_limit = " LIMIT 3";
$order=$_POST['order'];
if ($_POST['order']<>""){ 
$sql_orderby = " ORDER BY clothes.$order ASC"; 
} else {
$sql_orderby = " ORDER BY clothes.product_name DESC";
}
//$sql_orderby = " ORDER BY clothes.price ASC";
if ($_POST['male']<>""){ 
$sql_where .= " OR gender=1 "; 
} 
if ($_POST['female']<>""){ 
$sql_where .= " OR gender=2 "; 
} 
if ($_POST['type_1']<>""){ 
$sql_where .= " OR type_id=1 "; 
} 
if ($_POST['type_2']<>""){ 
$sql_where .= " OR type_id=2 "; 
} 
if ($_POST['type_3']<>""){ 
$sql_where .= " OR type_id=3 "; 
} 
if ($_POST['type_4']<>""){ 
$sql_where .= " OR type_id=4 "; 
} 
$sql_where = ") ";
	$query=$sql_select . $sql_from . $sql_where . $sql_end . $sql_group . $sql_orderby . $sql_limit;
    $results=dbselect( $query,"dbLinkInt" );

Check the content of the $_POST array. If none of the IF’s is being executed, probably the array doesn’t contain what you think it does.

$sql_from = " FROM clothes, product_categories_map, type, source, sweeteners, flavour, ingredients, dietary, shipping, merchant WHERE clothes.enabled=1 and clothes.stock=1 and clothes.deleted=0 and clothes.label=shipping.merchant and shipping.country_id=".$_POST['country']." AND ( product_feed_id != 1 ";

In that section there you’re sticking the value of $_POST['country'] direct into the query without escaping it, that’s a bad idea as it leaves you open to SQL Injection attack. You should use prepared statements when dealing with user submitted data in a query. For validating the country, as there’s only a fixed selection, you could have an array with all the country names in and use in_array() with strict comparison to see if the country exists

Is there a dot missing from this line?

$sql_where = ") ";

After you’ve added all the options, you wipe them all out again.

Also if this is based on the code you had in that zip file the other day, there’s a typo where you create a variable called $type_1 from $_POST[‘type1’] unless you’ve fixed it.

I don’t understand why it’s cancelling itself out when it used to work? I’ve added a dot in by the last $sql_where so it’s now $sql_where .= ") "; but it’s still cancelling everything else out. I’ve also corrected the typo in the first $type_1

Our server is down at the moment so I can’t test it but will this work instead?

$sql_where .= "( product_feed_id != 1 ";
if ($_POST['male']<>""){ " OR gender=1 "; }
if ($_POST['female']<>""){ " OR gender=2 "; }
if ($_POST['type_1']<>""){ " OR type_id=1 "; }
if ($_POST['type_2']<>""){ " OR type_id=2 "; }
if ($_POST['type_3']<>""){ " OR type_id=3 "; }
$sql_end .= ") ";

$query=$sql_select . $sql_from . $sql_where . $sql_end . $sql_group . $sql_orderby . $sql_limit;

your conditions do nothing.

You need to echo the contents of $sql_where, and your $_POST array, so you can get an idea on what’s going wrong. Either the array doesn’t contain what you expect, or there’s an issue with how you build the condition variable.

The code in post #5 is missing the "$sql_where .= " in each condition, but should work. It’s not really any different from what you’ve posted earlier, though, other than you’ve separated the closing bracket to another variable.

Am I correct in saying that this isn’t your actual code, more a re-typing of what you actually use? I understand you may feel the need to not put your actual code on a public forum, but it does stop anyone spotting stuff like typo errors if you’ve typed it out from scratch for the post.

But basically, you need to add in some echo() statements to your php code and follow the code through until you spot what’s going wrong.

I really appreciate you helping with this, I’m getting myself so stressed and am now worried about keeping my job because I can’t get this to work.

I’ve attached the entire (real) code below to help:

$sql_select = "SELECT * "; 
$sql_from = " FROM feeds, product_categories_map, type, source, sweeteners, flavour, ingredients, dietary, shipping, merchant WHERE feeds.enabled=1 and feeds.stock=1 and feeds.deleted=0 and feeds.brand_name=shipping.merchant and shipping.country_id=".dbstr( $_POST['country'] )." AND ( product_feed_id != 1 ";
$sql_group = " GROUP BY feeds.product_name";
$sql_limit = " LIMIT 1";
$order=$_POST['order'];
if ($_POST['order']<>""){ 
$sql_orderby = " ORDER BY feeds.$order ASC"; 
} else {
$sql_orderby = " ORDER BY feeds.product_name DESC";
}
//$sql_orderby = " ORDER BY feeds.price ASC";
if ($_POST['muscle']<>""){ 
$sql_where .= " OR category_id=1 "; 
} 
if ($_POST['performance']<>""){ 
$sql_where .= " OR category_id=2 "; 
} 
if ($_POST['diet']<>""){ 
$sql_where .= " OR category_id=3 "; 
} 
if ($_POST['lean']<>""){ 
$sql_where .= " OR category_id=4 "; 
} 
if ($_POST['energy']<>""){ 
$sql_where .= " OR category_id=5 "; 
} 
if ($_POST['health']<>""){ 
$sql_where .= " OR category_id=6 "; 
} 
if ($_POST['type_1']<>""){ 
$sql_where .= " OR type_id=1 "; 
} 
if ($_POST['type_2']<>""){ 
$sql_where .= " OR type_id=2 "; 
} 
if ($_POST['type_3']<>""){ 
$sql_where .= " OR type_id=3 "; 
} 
if ($_POST['type_4']<>""){ 
$sql_where .= " OR type_id=4 "; 
} 
if ($_POST['type_5']<>""){ 
$sql_where .= " OR type_id=5 "; 
} 
if ($_POST['type_6']<>""){ 
$sql_where .= " OR type_id=6 "; 
} 
if ($_POST['type_7']<>""){ 
$sql_where .= " OR type_id=7 "; 
} 
if ($_POST['type_8']<>""){ 
$sql_where .= " OR type_id=8 "; 
} 
if ($_POST['type_9']<>""){ 
$sql_where .= " OR type_id=9 "; 
} 
if ($_POST['type_10']<>""){ 
$sql_where .= " OR type_id=10 "; 
} 
if ($_POST['type_11']<>""){ 
$sql_where .= " OR type_id=11 "; 
} 
if ($_POST['type_12']<>""){ 
$sql_where .= " OR type_id=12 "; 
} 
if ($_POST['type_13']<>""){ 
$sql_where .= " OR type_id=13 "; 
} 
if ($_POST['type_14']<>""){ 
$sql_where .= " OR type_id=14 "; 
} 
if ($_POST['type_15']<>""){ 
$sql_where .= " OR type_id=15 "; 
} 
if ($_POST['type_16']<>""){ 
$sql_where .= " OR type_id=16 "; 
} 
if ($_POST['type_17']<>""){ 
$sql_where .= " OR type_id=17 "; 
} 
if ($_POST['type_18']<>""){ 
$sql_where .= " OR type_id=18 "; 
} 
if ($_POST['type_19']<>""){ 
$sql_where .= " OR type_id=19 "; 
} 
if ($_POST['type_20']<>""){ 
$sql_where .= " OR type_id=20 "; 
} 
if ($_POST['source_1']<>""){ 
$sql_where .= " OR source_id=1 "; 
} 
if ($_POST['source_2']<>""){ 
$sql_where .= " OR source_id=2 "; 
} 
if ($_POST['source_3']<>""){ 
$sql_where .= " OR source_id=3 "; 
} 
if ($_POST['source_4']<>""){ 
$sql_where .= " OR source_id=4 "; 
} 
if ($_POST['source_5']<>""){ 
$sql_where .= " OR source_id=5 "; 
} 
if ($_POST['source_6']<>""){ 
$sql_where .= " OR source_id=6 "; 
} 
if ($_POST['source_7']<>""){ 
$sql_where .= " OR source_id=7 "; 
} 
if ($_POST['source_8']<>""){ 
$sql_where .= " OR source_id=8 "; 
} 
if ($_POST['source_9']<>""){ 
$sql_where .= " OR source_id=9 "; 
} 
if ($_POST['source_10']<>""){ 
$sql_where .= " OR source_id=10 "; 
} 
if ($_POST['source_11']<>""){ 
$sql_where .= " OR source_id=11 "; 
} 
if ($_POST['source_12']<>""){ 
$sql_where .= " OR source_id=12 "; 
} 
if ($_POST['source_13']<>""){ 
$sql_where .= " OR source_id=13 "; 
} 
if ($_POST['source_14']<>""){ 
$sql_where .= " OR source_id=14 "; 
} 
if ($_POST['source_15']<>""){ 
$sql_where .= " OR source_id=15 "; 
} 
if ($_POST['source_16']<>""){ 
$sql_where .= " OR source_id=16 "; 
} 
if ($_POST['source_17']<>""){ 
$sql_where .= " OR source_id=17 "; 
} 
if ($_POST['source_18']<>""){ 
$sql_where .= " OR source_id=18 "; 
} 
if ($_POST['source_19']<>""){ 
$sql_where .= " OR source_id=19 "; 
} 
if ($_POST['source_20']<>""){ 
$sql_where .= " OR source_id=20 "; 
} 
if ($_POST['sweeteners_1']<>""){ 
$sql_where .= " OR sweeteners_id=1 "; 
} 
if ($_POST['sweeteners_2']<>""){ 
$sql_where .= " OR sweeteners_id=2 "; 
} 
if ($_POST['sweeteners_3']<>""){ 
$sql_where .= " OR sweeteners_id=3 "; 
} 
if ($_POST['sweeteners_4']<>""){ 
$sql_where .= " OR sweeteners_id=4 "; 
} 
if ($_POST['sweeteners_5']<>""){ 
$sql_where .= " OR sweeteners_id=5 "; 
} 
if ($_POST['sweeteners_6']<>""){ 
$sql_where .= " OR sweeteners_id=6 "; 
} 
if ($_POST['sweeteners_7']<>""){ 
$sql_where .= " OR sweeteners_id=7 "; 
} 
if ($_POST['sweeteners_8']<>""){ 
$sql_where .= " OR sweeteners_id=8 "; 
} 
if ($_POST['sweeteners_9']<>""){ 
$sql_where .= " OR sweeteners_id=9 "; 
} 
if ($_POST['sweeteners_10']<>""){ 
$sql_where .= " OR sweeteners_id=10 "; 
} 
if ($_POST['sweeteners_11']<>""){ 
$sql_where .= " OR sweeteners_id=11 "; 
} 
if ($_POST['sweeteners_12']<>""){ 
$sql_where .= " OR sweeteners_id=12 "; 
} 
if ($_POST['sweeteners_13']<>""){ 
$sql_where .= " OR sweeteners_id=13 "; 
} 
if ($_POST['sweeteners_14']<>""){ 
$sql_where .= " OR sweeteners_id=14 "; 
} 
if ($_POST['sweeteners_15']<>""){ 
$sql_where .= " OR sweeteners_id=15 "; 
} 
if ($_POST['sweeteners_16']<>""){ 
$sql_where .= " OR sweeteners_id=16 "; 
} 
if ($_POST['sweeteners_17']<>""){ 
$sql_where .= " OR sweeteners_id=17 "; 
} 
if ($_POST['sweeteners_18']<>""){ 
$sql_where .= " OR sweeteners_id=18 "; 
} 
if ($_POST['sweeteners_19']<>""){ 
$sql_where .= " OR sweeteners_id=19 "; 
} 
if ($_POST['sweeteners_20']<>""){ 
$sql_where .= " OR sweeteners_id=20 "; 
} 
if ($_POST['flavour_1']<>""){ 
$sql_where .= " OR flavour_id=1 "; 
} 
if ($_POST['flavour_2']<>""){ 
$sql_where .= " OR flavour_id=2 "; 
} 
if ($_POST['flavour_3']<>""){ 
$sql_where .= " OR flavour_id=3 "; 
} 
if ($_POST['flavour_4']<>""){ 
$sql_where .= " OR flavour_id=4 "; 
} 
if ($_POST['flavour_5']<>""){ 
$sql_where .= " OR flavour_id=5 "; 
} 
if ($_POST['flavour_6']<>""){ 
$sql_where .= " OR flavour_id=6 "; 
} 
if ($_POST['flavour_7']<>""){ 
$sql_where .= " OR flavour_id=7 "; 
} 
if ($_POST['flavour_8']<>""){ 
$sql_where .= " OR flavour_id=8 "; 
} 
if ($_POST['flavour_9']<>""){ 
$sql_where .= " OR flavour_id=9 "; 
} 
if ($_POST['flavour_10']<>""){ 
$sql_where .= " OR flavour_id=10 "; 
} 
if ($_POST['flavour_11']<>""){ 
$sql_where .= " OR flavour_id=11 "; 
} 
if ($_POST['flavour_12']<>""){ 
$sql_where .= " OR flavour_id=12 "; 
} 
if ($_POST['flavour_13']<>""){ 
$sql_where .= " OR flavour_id=13 "; 
} 
if ($_POST['flavour_14']<>""){ 
$sql_where .= " OR flavour_id=14 "; 
} 
if ($_POST['flavour_15']<>""){ 
$sql_where .= " OR flavour_id=15 "; 
} 
if ($_POST['flavour_16']<>""){ 
$sql_where .= " OR flavour_id=16 "; 
} 
if ($_POST['flavour_17']<>""){ 
$sql_where .= " OR flavour_id=17 "; 
} 
if ($_POST['flavour_18']<>""){ 
$sql_where .= " OR flavour_id=18 "; 
} 
if ($_POST['flavour_19']<>""){ 
$sql_where .= " OR flavour_id=19 "; 
} 
if ($_POST['flavour_20']<>""){ 
$sql_where .= " OR flavour_id=20 "; 
} 
if ($_POST['ingredients_1']<>""){ 
$sql_where .= " OR ingredients_id=1 "; 
} 
if ($_POST['ingredients_2']<>""){ 
$sql_where .= " OR ingredients_id=2 "; 
} 
if ($_POST['ingredients_3']<>""){ 
$sql_where .= " OR ingredients_id=3 "; 
} 
if ($_POST['ingredients_4']<>""){ 
$sql_where .= " OR ingredients_id=4 "; 
} 
if ($_POST['ingredients_5']<>""){ 
$sql_where .= " OR ingredients_id=5 "; 
} 
if ($_POST['ingredients_6']<>""){ 
$sql_where .= " OR ingredients_id=6 "; 
} 
if ($_POST['ingredients_7']<>""){ 
$sql_where .= " OR ingredients_id=7 "; 
} 
if ($_POST['ingredients_8']<>""){ 
$sql_where .= " OR ingredients_id=8 "; 
} 
if ($_POST['ingredients_9']<>""){ 
$sql_where .= " OR ingredients_id=9 "; 
} 
if ($_POST['ingredients_10']<>""){ 
$sql_where .= " OR ingredients_id=10 "; 
} 
if ($_POST['ingredients_11']<>""){ 
$sql_where .= " OR ingredients_id=11 "; 
} 
if ($_POST['ingredients_12']<>""){ 
$sql_where .= " OR ingredients_id=12 "; 
} 
if ($_POST['ingredients_13']<>""){ 
$sql_where .= " OR ingredients_id=13 "; 
} 
if ($_POST['ingredients_14']<>""){ 
$sql_where .= " OR ingredients_id=14 "; 
} 
if ($_POST['ingredients_15']<>""){ 
$sql_where .= " OR ingredients_id=15 "; 
} 
if ($_POST['ingredients_16']<>""){ 
$sql_where .= " OR ingredients_id=16 "; 
} 
if ($_POST['ingredients_17']<>""){ 
$sql_where .= " OR ingredients_id=17 "; 
} 
if ($_POST['ingredients_18']<>""){ 
$sql_where .= " OR ingredients_id=18 "; 
} 
if ($_POST['ingredients_19']<>""){ 
$sql_where .= " OR ingredients_id=19 "; 
} 
if ($_POST['ingredients_20']<>""){ 
$sql_where .= " OR ingredients_id=20 "; 
} 
if ($_POST['dietary_1']<>""){ 
$sql_where .= " OR dietary_id=1 "; 
} 
if ($_POST['dietary_2']<>""){ 
$sql_where .= " OR dietary_id=2 "; 
} 
if ($_POST['dietary_3']<>""){ 
$sql_where .= " OR dietary_id=3 "; 
} 
if ($_POST['dietary_4']<>""){ 
$sql_where .= " OR dietary_id=4 "; 
} 
if ($_POST['dietary_5']<>""){ 
$sql_where .= " OR dietary_id=5 "; 
} 
if ($_POST['dietary_6']<>""){ 
$sql_where .= " OR dietary_id=6 "; 
} 
if ($_POST['dietary_7']<>""){ 
$sql_where .= " OR dietary_id=7 "; 
} 
if ($_POST['dietary_8']<>""){ 
$sql_where .= " OR dietary_id=8 "; 
} 
if ($_POST['dietary_9']<>""){ 
$sql_where .= " OR dietary_id=9 "; 
} 
if ($_POST['dietary_10']<>""){ 
$sql_where .= " OR dietary_id=10 "; 
} 
if ($_POST['dietary_11']<>""){ 
$sql_where .= " OR dietary_id=11 "; 
} 
if ($_POST['dietary_12']<>""){ 
$sql_where .= " OR dietary_id=12 "; 
} 
if ($_POST['dietary_13']<>""){ 
$sql_where .= " OR dietary_id=13 "; 
} 
if ($_POST['dietary_14']<>""){ 
$sql_where .= " OR dietary_id=14 "; 
} 
if ($_POST['dietary_15']<>""){ 
$sql_where .= " OR dietary_id=15 "; 
} 
if ($_POST['dietary_16']<>""){ 
$sql_where .= " OR dietary_id=16 "; 
} 
if ($_POST['dietary_17']<>""){ 
$sql_where .= " OR dietary_id=17 "; 
} 
if ($_POST['dietary_18']<>""){ 
$sql_where .= " OR dietary_id=18 "; 
} 
if ($_POST['dietary_19']<>""){ 
$sql_where .= " OR dietary_id=19 "; 
} 
if ($_POST['dietary_20']<>""){ 
$sql_where .= " OR dietary_id=20 "; 
} 
$sql_where .= ") ";
if ($_POST['muscle']<>"" || $_POST['performance']<>"" || $_POST['diet']<>"" || $_POST['lean']<>"" || $_POST['energy']<>"" || $_POST['health']<>""){
$sql_end .= " AND feeds.product_feed_id = product_categories_map.product_id "; 
} 
if ($_POST['type_2']<>"" || $_POST['type_3']<>"" || $_POST['type_4']<>"" || $_POST['type_5']<>"" || $_POST['type_6']<>"" || $_POST['type_7']<>"" || $_POST['type_8']<>"" || $_POST['type_9']<>"" || $_POST['type_10']<>"" || $_POST['type_11']<>"" || $_POST['type_12']<>"" || $_POST['type_13']<>"" || $_POST['type_14']<>"" || $_POST['type_15']<>"" || $_POST['type_16']<>"" || $_POST['type_17']<>"" || $_POST['type_18']<>"" || $_POST['type_19']<>"" || $_POST['type_20']<>""){
$sql_end .= " AND feeds.product_feed_id = type.product_id "; 
} 
if ($_POST['source_1']<>"" || $_POST['source_2']<>"" || $_POST['source_3']<>"" || $_POST['source_4']<>"" || $_POST['source_5']<>"" || $_POST['source_6']<>"" || $_POST['source_7']<>"" || $_POST['source_8']<>"" || $_POST['source_9']<>"" || $_POST['source_10']<>"" || $_POST['source_11']<>"" || $_POST['source_12']<>"" || $_POST['source_13']<>"" || $_POST['source_14']<>"" || $_POST['source_15']<>"" || $_POST['source_16']<>"" || $_POST['source_17']<>"" || $_POST['source_18']<>"" || $_POST['source_19']<>"" || $_POST['source_20']<>""){ 
$sql_end .= " AND feeds.product_feed_id = source.product_id "; 
} 
if ($_POST['sweeteners_1']<>"" || $_POST['sweeteners_2']<>"" || $_POST['sweeteners_3']<>"" || $_POST['sweeteners_4']<>"" || $_POST['sweeteners_5']<>"" || $_POST['sweeteners_6']<>"" || $_POST['sweeteners_7']<>"" || $_POST['sweeteners_8']<>"" || $_POST['sweeteners_9']<>"" || $_POST['sweeteners_10']<>"" || $_POST['sweeteners_11']<>"" || $_POST['sweeteners_12']<>"" || $_POST['sweeteners_13']<>"" || $_POST['sweeteners_14']<>"" || $_POST['sweeteners_15']<>"" || $_POST['sweeteners_16']<>"" || $_POST['sweeteners_17']<>"" || $_POST['sweeteners_18']<>"" || $_POST['sweeteners_19']<>"" || $_POST['sweeteners_20']<>""){ 
$sql_end .= " AND feeds.product_feed_id = sweeteners.product_id "; 
} 
if ($_POST['flavour_1']<>"" || $_POST['flavour_2']<>"" || $_POST['flavour_3']<>"" || $_POST['flavour_4']<>"" || $_POST['flavour_5']<>"" || $_POST['flavour_6']<>"" || $_POST['flavour_7']<>"" || $_POST['flavour_8']<>"" || $_POST['flavour_9']<>"" || $_POST['flavour_10']<>"" || $_POST['flavour_11']<>"" || $_POST['flavour_12']<>"" || $_POST['flavour_13']<>"" || $_POST['flavour_14']<>"" || $_POST['flavour_15']<>"" || $_POST['flavour_16']<>"" || $_POST['flavour_17']<>"" || $_POST['flavour_18']<>"" || $_POST['flavour_19']<>"" || $_POST['flavour_20']<>""){ 
$sql_end .= " AND feeds.product_feed_id = flavour.product_id "; 
} 
if ($_POST['ingredients_1']<>"" || $_POST['ingredients_2']<>"" || $_POST['ingredients_3']<>"" || $_POST['ingredients_4']<>"" || $_POST['ingredients_5']<>"" || $_POST['ingredients_6']<>"" || $_POST['ingredients_7']<>"" || $_POST['ingredients_8']<>"" || $_POST['ingredients_9']<>"" || $_POST['ingredients_10']<>"" || $_POST['ingredients_11']<>"" || $_POST['ingredients_12']<>"" || $_POST['ingredients_13']<>"" || $_POST['ingredients_14']<>"" || $_POST['ingredients_15']<>"" || $_POST['ingredients_16']<>"" || $_POST['ingredients_17']<>"" || $_POST['ingredients_18']<>"" || $_POST['ingredients_19']<>"" || $_POST['ingredients_20']<>""){ 
$sql_end .= " AND feeds.product_feed_id = ingredients.product_id "; 
} 
if ($_POST['dietary_1']<>"" || $_POST['dietary_2']<>"" || $_POST['dietary_3']<>"" || $_POST['dietary_4']<>"" || $_POST['dietary_5']<>"" || $_POST['dietary_6']<>"" || $_POST['dietary_7']<>"" || $_POST['dietary_8']<>"" || $_POST['dietary_9']<>"" || $_POST['dietary_10']<>"" || $_POST['dietary_11']<>"" || $_POST['dietary_12']<>"" || $_POST['dietary_13']<>"" || $_POST['dietary_14']<>"" || $_POST['dietary_15']<>"" || $_POST['dietary_16']<>"" || $_POST['dietary_17']<>"" || $_POST['dietary_18']<>"" || $_POST['dietary_19']<>"" || $_POST['dietary_20']<>""){ 
$sql_end .= " AND feeds.product_feed_id = dietary.product_id ";
}
	$query=$sql_select . $sql_from . $sql_where . $sql_end . $sql_group . $sql_orderby . $sql_limit;
    $product_results=dbselect( $query,"dbLinkInt" );

Does it fail with any boxes ticked, or only certain ones, or only if you tick more than a certain number of boxes? Can you do a test run and post a var_dump($_POST) and echo $sql_where?

Just to make things a little smaller (once you have it working), I’d look at using flags to decide whether to add all your AND conditions instead of re-accessing all those $_POST vars at the end. For example:

// before you start processing anything
$typecheck =false;
$sourcecheck = false;
$sweetenercheck = false;
//
if ($_POST['type_2']<>""){ 
$sql_where .= " OR type_id=2 "; 
$typecheck = true;
} 
if ($_POST['type_3']<>""){ 
$sql_where .= " OR type_id=3 "; 
$typecheck = true;
} 
...
// then later on
...
if ($typecheck) {
  $sql_end .= "AND feeds.product_feed_id = type.product_id";
  }

But obviously it needs to work first.

You would be receiving more than 100 unset index errors with your current code not to mention multiple concatenate errors where you did not define the variable before adding to it. SO instead of trying to compare a POST key to an empty value when the key might not even be present in POST, use !empty() instead. Most of your keys are numbered 1 through 20 so you can use a foreach loop to simplify the bulk of the code. As you are checking for empty on both the sql_where and sql_end lines you can use the same foreach loops and set a flag as was suggested above and if the flag is true, then add the sql_end line. Anyway, I can now load this code without it choking with errors.

<?php 

$sql_select = "SELECT * "; 
$sql_from = " FROM feeds, product_categories_map, type, source, sweeteners, flavour, ingredients, dietary, shipping, merchant WHERE feeds.enabled=1 and feeds.stock=1 and feeds.deleted=0 and feeds.brand_name=shipping.merchant and shipping.country_id=".dbstr( $_POST['country'] )." AND ( product_feed_id != 1 ";
$sql_group = " GROUP BY feeds.product_name";
$sql_limit = " LIMIT 1";
$sql_where ='';                
$sql_end ='';                      

$sql_orderby = (!empty($_POST['order']) ? ' ORDER BY feeds'.$_POST['order'].' ASC' : ' ORDER BY feeds.product_name DESC');

$category_set = false;
if (!empty($_POST['muscle'])){      $sql_where .= " OR category_id=1 "; $category_set = true;} 
if (!empty($_POST['performance'])){ $sql_where .= " OR category_id=2 "; $category_set = true;} 
if (!empty($_POST['diet'])){        $sql_where .= " OR category_id=3 "; $category_set = true;} 
if (!empty($_POST['lean'])){        $sql_where .= " OR category_id=4 "; $category_set = true;} 
if (!empty($_POST['energy'])){      $sql_where .= " OR category_id=5 "; $category_set = true;} 
if (!empty($_POST['health'])){      $sql_where .= " OR category_id=6 "; $category_set = true;}
if ($category_set === true){ $sql_end .= " AND feeds.product_feed_id = product_categories_map.product_id ";} 

$type_set = false;
foreach(range(1,20) as $t):
    $type = 'type_'.$t;
    if (!empty($_POST[$type])): 
        $sql_where .= ' OR type_id='.$t.' ';
        $type_set = true; 
    endif; 
endforeach;
if ($type_set === true){ $sql_end .= " AND feeds.product_feed_id = type.product_id ";} 

 
$source_set = false;
foreach(range(1,20) as $s):
    $source = 'source_'.$s;
    if (!empty($_POST[$source])): 
        $sql_where .= ' OR source_id='.$s.' ';
        $source_set = true;  
    endif; 
endforeach;
if ($source_set === true){ $sql_end .= " AND feeds.product_feed_id = source.product_id ";} 
 

$sweeteners_set = false;
foreach(range(1,20) as $sw):
    $sweeteners = 'sweeteners_'.$sw;
    if (!empty($_POST[$sweeteners])): 
        $sql_where .= ' OR sweeteners_id='.$sw.' ';
        $sweeteners_set = true;  
    endif; 
endforeach;
if ($sweeteners_set === true){ $sql_end .= " AND feeds.product_feed_id = sweeteners.product_id ";}  


$flavour_set = false;
foreach(range(1,20) as $f):
    $flavour = 'flavour_'.$f;
    if (!empty($_POST[$flavour])): 
        $sql_where .= ' OR flavour_id='.$f.' ';
        $flavour_set = true;  
    endif; 
endforeach;
if ($flavour_set === true){ $sql_end .= " AND feeds.product_feed_id = flavour.product_id ";} 


$ingredients_set = false;
foreach(range(1,20) as $i):
    $ingredients = 'ingredients_'.$i;
    if (!empty($_POST[$ingredients])): 
        $sql_where .= ' OR ingredients_id='.$i.' ';
        $ingredients_set = true;  
    endif; 
endforeach;
if ($ingredients_set === true){ $sql_end .= " AND feeds.product_feed_id = ingredients.product_id ";} 


$dietary_set = false;
foreach(range(1,20) as $d):
    $dietary = 'dietary_'.$d;
    if (!empty($_POST[$dietary])): 
        $sql_where .= ' OR dietary_id='.$d.' ';
        $dietary_set = true;  
    endif; 
endforeach;
if ($dietary_set === true){ $sql_end .= " AND feeds.product_feed_id = dietary.product_id ";} 

$sql_where .= ") ";

$query = $sql_select . $sql_from . $sql_where . $sql_end . $sql_group . $sql_orderby . $sql_limit;
echo $query;    
//$product_results=dbselect( $query,"dbLinkInt" );
?>
1 Like

If the only thing that’s changed is moving to a new server, are you sure that the code on the new server is the correct code? That is, if you add a blatant echo “I am new code” at the top of your PHP processing page, does it display it?

Also compare any software versions or configuration differences between the two servers - could it be that error handling is different between the two and something that @Drummin touched on is causing it to fail in a way that the old server coped? I have no experience with PHP on real servers so can’t help on that.

Thank you so much that, seems to be working much better now. Is there anything I can do to speed up how long it takes to get any results though? Our hosts say that it’s often taking over an hour and that it should only take 30 minutes. I’ve been limiting it to 1 results for testing but that still seems to take ages.

How many records are being grabbed? You probably need to add a paginate to your limit code so you are only pulling 25 or 50 records at a time.

At the moment it’s getting just one result

I was referring to the query taking an hour to run. Assuming you are not running this in a loop and hopefully above output to browser and not dealing with a million records to sift through, it shouldn’t take that log. That’s why I asked.

Sorry I don’t quite understand what you’re saying? The query is getting one result but the table in the database has thousands of entries. The code above it the exact code I’m using to get the so I’m at a lose as to why it’s taking so long

What indexes are in place on the tables used?

the feed table is 130KiB and the next highest one is 9Kib. The entire database is just over 22MiB and
I tried to click to make one of the id (auto increment rows) rows index and it said MySQL returned an empty result set (i.e. zero rows). (Query took 0.7336 sec)

I can’t help but think that the query could be improved.
Seeing it SELECT everything from ten tables doesn’t look efficient to me.

That doesn’t answer

Judicial use of Indexes can speed things up. MySQL uses them as a kind of “quick map” i.e. it can go to the Index straight away without needing to look for them in the table every time.

I’ll remove some of the ‘everything’ if I can and try to limit the query more. I don’t understand what you mean about using indexes though - how would I use them?