$_GET variable Array with MySQL Query

Question

I am having trouble figuring out how to work an if statement into my PHP containing a MySQL Query. Here's what I have so far (it's unfinished):

$industry = $_GET['industry'];
$location = $_GET['location'];
$position = $_GET['position'];
$region = $_GET['region'];

if($industry=="any"){

}

if($location=="any"){

}

if($position=="any"){

}

if($region=="any"){

}

$sql_cat = "
SELECT *
FROM jobs
WHERE industry_cat='$industry' && location_cat='$location' && position_cat='$position' && region_cat='$region'
ORDER BY id DESC
";
$result_cat = $mysqli->query($sql_cat);
$num_rows_cat = mysqli_num_rows($result_cat);

Basically, what I'm trying to say is that if any of the variables are set to 'any' then I want those variables to represent all the possibilities (there's like five possibilites in the form for each variable). I figured I'd use arrays in the where clause (like WHERE industry IN ($industry)) but then it wouldn't work if someone actually did pick a variable.

I may be a little off track but am having trouble wrapping my mind around it. Any help would be greatly appreciated. Thanks!

can you please provide some more information what you really want — Muhammad Raheel
– Muhammad Raheel, Commented Dec 18, 2012 at 5:45
It's just a search form that people submit and there's an option for 'any' which I would like to represent all possibilites for that variable when I'm querying my database. Does that make sense? — MillerMedia
– MillerMedia, Commented Dec 18, 2012 at 5:48
@Sithu, for the purposes of this, we can just use "661" "662" "663" and "664" as the variables that can be chosen. — MillerMedia
– MillerMedia, Commented Dec 18, 2012 at 5:49

Sithu · Accepted Answer · 2012-12-19 06:06:08Z

4

If I did not misunderstand your question, I would do this.

$sql = "SELECT * FROM jobs WHERE 1";
if($industry !== "any"){
    $sql .= " AND industry_cat = '$industry'"
}
if($location !== "any"){
    $sql .= " AND location_cat = '$location'"
}
if($position !== "any"){
    $sql .= " AND position_cat = '$position'"
}
if($region !== "any"){
    $sql .= " AND region_cat = '$region'"
}
$sql .= " ORDER BY id DESC"
$result = $mysqli->query($sql);

You can remove single quote around the variables in the query if you are sure they are integer.

edited Dec 19, 2012 at 6:06

answered Dec 18, 2012 at 5:52

Sithu

4,88210 gold badges68 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MillerMedia Over a year ago

Now, let's say someone actually chooses something other than 'any' on one of those, is it still going to show for that variable?

Sithu Over a year ago

@MxmastaMills, if 661 chosen for industry, for example, the first condition will resolve this by adding a condition AND industry_cat = '661' to the query. Note that you can use != instead of !==. The later is exact match including data type. You could try it :)

MillerMedia Over a year ago

I am receiving an error: "Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /example/example.php on line 57" when the search results are chosen and no results come up. It's displaying things correctly (the row count as 0, etc.) but it's giving me the error message on the page as well. I'm trying to think it through but can't figure out why the result would be empty...

Sithu Over a year ago

@MxmastaMills, it is because the correct mysqli result is not given to mysqli_num_rows(). Probably, a query failed. Check your queries in your page. I recommend to wrap your query exec with if, for example, if($result = mysqli_query($sql)){ $count = mysqli_num_rows($result); }else{ echo mysqli_error($result); , i.e., if $sql failed, it won't give such error. Please also note that you use mysqli object or mysqli functions ? i.e., $mysqli->query($sql) or mysqli_query($sql);

MillerMedia Over a year ago

Ah, you're right. There was a slight typo in the code above (iregion_cat instead of region_cat) and I hadn't noticed it previously. Thanks again for your help, it's saved me a lot of time and actually taught me a bunch too!

Olaf Dietsche · Accepted Answer · 2012-12-18 05:54:04Z

0

Build your where clause gradually

$industry = $_GET['industry'];
$location = $_GET['location'];
$position = $_GET['position'];
$region = $_GET['region'];

if($industry!="any"){
    $where[] = 'industry_cat=?';
    $where_args[] = $industry;
}

if($location!="any"){
    $where[] = 'location_cat=?';
    $where_args[] = $location;
}

if($position!="any"){
    $where[] = 'position_cat=?';
    $where_args[] = $position;
}

if($region!="any"){
    $where[] = 'region_cat=?';
    $where_args[] = $region;
}

$where_clause = implode(' and ', $where);

$sql_cat = "
SELECT *
FROM jobs
where $whereclause
ORDER BY id DESC
";
$stmt = $mysqli->prepare($sql_cat);

answered Dec 18, 2012 at 5:54

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Collectives™ on Stack Overflow

$_GET variable Array with MySQL Query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related