using an array within a mysql query??

[tippytarka]

using an array within a mysql query??


i'm using a select multiple html menu ....the user has a choice of selections where any number of those selections can be contained into the array "job_title_id[]" ...which then goes to the msql query below. currently the mysql query captures only the first selection hence.... $job_title_id[0]. how do i adjust the query (or i guess the $job_title_id[0] array inside the query?) to capture all the selected content?


$sql_job_title_count = ("SELECT COUNT(*) FROM p1_list_creative
WHERE p1_list_creative.country_id = '".$country_id."'
AND p1_list_creative.job_title_id = '".$job_title_id[0]."'");
$result_job_title_count = mysql_query($sql_job_title_count);

$num_job_title_count = mysql_result($result_job_title_count,0,0);
print ($num_job_title_count);


cheers!

 

[cLFlaVA]

Using the join() function and a slight change in your query, you'll be able to accomplish this.

The join() function places each array element into a string, separated by a designated character. You'll want the comma in this case.

[tt]
$job_titles = join( ",", $job_title_id);
[/tt]

The basic SQL syntax I'd consider is the IN clause.

[tt]
SELECT *
FROM table_name
WHERE col_name IN (value list...)
[/tt]

So, once you've joined, you can make this your query:

[tt]
SELECT COUNT(*)FROM p1_list_creative
WHERE p1_list_creative.country_id = '".$country_id."'
AND p1_list_creative.job_title_id IN ($job_titles)
[/tt]

Some additional work may need to be done to surround each job title with single quotes.

Let me know if you need more help.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[tippytarka]

what i meant about the array ....is that i only have a single array....

$job_title_id[]

which contains the following (strings if you like)...

CREAT
ADMIN
EDITE
DESIG
RESEA

at present the mysql query can query only one of those strings, but i would like to query any number of those strings when contained within the array..... $job_title_id[]

 

[kenrbnsn]

In your code, change this line:

$sql_job_title_count = ("SELECT COUNT(*) FROM p1_list_creative
    WHERE p1_list_creative.country_id = '".$country_id."'
    AND p1_list_creative.job_title_id = '".$job_title_id[0]."'");
    $result_job_title_count = mysql_query($sql_job_title_count);

to something like this:

$sql_job_title_count = "SELECT COUNT(*) FROM p1_list_creative
    WHERE p1_list_creative.country_id = '".$country_id."'
    AND ";
$tmp = array();
for ($i=0;$i<count($job_title);$i++)
    $tmp[] = "p1_list_creative.job_title_id = '".$job_title[$i]."'";
$sql_job_title_count .= implode('or ',$tmp);


p1_list_creative.job_title_id = '".$job_title_id[0]."'");
    $result_job_title_count = mysql_query($sql_job_title_count);

You probably want to print out the query statement before you execute it and make sure it's in the proper format.

Since I haven't checked this code, there may be errors in it. Check it before using.

Ken

 

[kenrbnsn]

Oops,

Just noticed an extra line got into my reply, remove:

p1_list_creative.job_title_id = '".$job_title_id[0]."'");

Ken

 

[tippytarka]

Thanks guys!