0

i have got 5 queries as given below , i need to perform a union on all 

  if($dv_state_ids!="")//check if no state is entered
    $state_sql="SELECT pkg_id FROM package_state where state_id in ($dv_state_ids)  ";
  if($dv_city_ids!="")//check if no city is entered
    $city_sql="SELECT pkg_id FROM package_city where  city_id in ($dv_city_ids)";
  if($dv_category_ids!="")//check if nocategory is entered
    $category_sql="SELECT pkg_id FROM package_category where category_id in ($dv_category_ids) ";
  if($dv_sub_category_ids!="")//check if no subcategory is found
    $sub_category_sql="SELECT pkg_id FROM package_category where subcategory_id in ($dv_sub_category_ids) ";
  if($dv_category_ids!="")
    $package_sql="SELECT pkg_id FROM package where id in ($dv_category_ids) ";

i can run a union on the above queries by the following statement

$sql_get_any_packages=$state_sql." union ".$city_sql." 
union 
".$category_sql." union ".$sub_category_sql." union ".$package_sql ;

but if any query is null then this would generate an error in mysql. one way is that i frame 32 conditions (which would be formed by these 5 querys to check for different conditions)

can any body suggest me another way out

also i need to perform an intersection over the resultant of these 5 queries and sql does not support INTERSECT

Please help me with this

fancyPants
  • 50,732
  • 33
  • 89
  • 96
prashant kumar
  • 9
  • 1
  • It's working even if any of that query returns null – Toretto Nov 30 '12 at 14:17
  • its not about query returning null its if a union is in between 2 queries suppose query1 and query2 then if query1 union query2 returns a result ......... now if query2 if null then its an sql error and in my case there are about 32 such combinations to check with – prashant kumar Nov 30 '12 at 14:36

1 Answers1

1

By a “query is null” you apparently mean the PHP variable being null. This is in no way specific to SQL or SQL unions.

Repeated ifs

So what you can do is this:

$sql_get_any_packages = "";
if (!is_null($state_sql))
 $sql_get_any_packages .= " union " . $state_sql;
if (!is_null($city_sql))
 $sql_get_any_packages .= " union " . $city_sql;
if (!is_null($category_sql))
 $sql_get_any_packages .= " union " . $category_sql;
if (!is_null($sub_category_sql))
 $sql_get_any_packages .= " union " . $sub_category_sql;
if (!is_null($package_sql))
 $sql_get_any_packages .= " union " . $package_sql;
$sql_get_any_packages = substr($sql_get_any_packages, strlen(" union "));

This simply collects all non-null parts, including a leading " union " for each, and removes the initial " union " at the end. If all queries might be null simultaneously, then the end result will be FALSE.

Using an array

As an alternative, you could drop your parts into an array, use array_filter to drop the null elements, and then implode the result using " union " as glue. The corresponding code might look like this (see also an example on ideone):

$sql_get_any_packages = array(
    $state_sql,
    $city_sql,
    $category_sql,
    $sub_category_sql,
    $package_sql);
$sql_get_any_packages = array_filter($sql_get_any_packages);
$sql_get_any_packages = implode(" union ", $sql_get_any_packages);

About intersecting

If you want to intersect the results, you don't want to comoute their union. Once you do a union, you no longer have access to its individual parts, so you can't compare them against one another. To achieve intersections, you use joins in SQL. A full example could look like this:

SELECT package_state.pkg_id
FROM package_state, package_city, package_category, package_category, package
WHERE package_state.state_id IN ($dv_state_ids)
  AND package_city.city_id IN ($dv_city_ids)
  AND package_category.category_id IN ($dv_category_ids)
  AND package_category.subcategory_id IN ($dv_sub_category_ids)
  AND package.id in ($dv_category_ids)
  AND package_state.pkg_id == package_city.pkg_id
  AND package_state.pkg_id == package_category.pkg_id
  AND package_state.pkg_id == package.pkg_id

You can build a query like this using solutions like the ones I described above, but they will become slightly more complicated. In particular, the table package_state in the above query plays a somewhat different role than all the other tables: that's the table form which you SELECT your output value, and thats also the table which occurs on the left hand side of all thepkg_idcomparisons, where the other tables appear on the right hand side. So you might want to collect several arrays, one of table names and one offoo IN (bar)` conditions. Then you can use the first element of the array of table names to play that special role.

Beware the SQL injection

Be careful about pasting strings from user requests into your query. They might contain ugly stuff which will corrupt your data. Make sure the input is sane, e.g. by checking that it is only a comma-separated list of integer values. Or use prepared statements, which can isolate you from most of these problems.

Community
  • 1
  • 1
MvG
  • 57,380
  • 22
  • 148
  • 276