PHP + convert array for use with IN statement

Question

Lets say I want to send an email to the people who like big sized candy.

When I do this:

$query1 = mysql_query("SELECT id_candy FROM `candylist` WHERE
candysize > 100") or die(mysql_error());

$result = mysql_fetch_array( $query1 );

$query2 = mysql_query("SELECT mail FROM `buyers` WHERE
candysizeinterest IN ($result)
") or die(mysql_error());

This query2 is of course the issue.

$result is not in a proper format to be used and then I get the obvious error of:

**Unknown column 'Array' in 'where clause'**

I need help to format the results of the query in a way that it looks like:

($val1, $val2, $val3....and so on)

Then I will be able to use it for an IN statement.

But I just do not know how to do so.

you should check the reference for in queries...an in query is suposed to take a comma-separated list of values, not an array — rnirnber
– rnirnber, Commented Apr 15, 2013 at 16:37
Thanks, i did. As i stated on my post that much i do know. The thing i couldt figure out by myself was converting the query result into a usable format such as you mention or as Fabio has mentioned in his helpfull answer. — Khul
– Khul, Commented Apr 15, 2013 at 17:29

alwaysLearn · Accepted Answer · 2013-04-15 16:43:52Z

2

Try

$array = array();
while($result = mysql_fetch_array( $query1 ))
{
   $array[] = $result['id_candy'];
}

Then use implode

$in_condition  = implode(',', $array);

At last

$query2 = mysql_query("SELECT mail FROM `buyers` WHERE candysizeinterest IN ($in_condition)

NOTE : please switch to PDO or mysqli_*

answered Apr 15, 2013 at 16:43

alwaysLearn

6,9507 gold badges45 silver badges69 bronze badges

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

2 Comments

Khul Over a year ago

This is exactly what my missing step was, wich i see as alternating between php and mysql querys. I will be taking note of it. Thank you. PS: I´ll follow the comment on PDO aswell as it is my stated intention =)

Khul Over a year ago

This approach is also most helpfull if you later want to print results using fwrite since the implode function will convert arrays to strings.

Fabio · Accepted Answer · 2013-04-15 16:42:28Z

1

you have a mistake in your sintax of $query2 you place array instead of actual value

change

$query2 = mysql_query("SELECT mail FROM `buyers` WHERE candysizeinterest IN ($result)

to

$query2 = mysql_query("SELECT `mail` FROM `buyers` WHERE `candysizeinterest` IN ('".$result['id_candy']."')");

I would like to also to remember you that mysql_ functions are deprecated so i would advise you to switch to mysqli or PDO for new projects.

edited Apr 15, 2013 at 16:42

answered Apr 15, 2013 at 16:36

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

2 Comments

Khul Over a year ago

Thanks for quick reply. Yet after changing it to so i get the error: Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING.

Khul Over a year ago

Thanks again! I just changed the "'.$result[idcandy].'" because i was getting "syntax error, unexpected T_CONSTANT_ENCAPSED_STRING" to ".$result['id_curso']." and it worked like a charm. I appreciate it. I will also explore the implode options and see how it goes! Thank you!

TheMethod · Accepted Answer · 2013-04-15 16:38:38Z

0

You can use implode() to turn your array into a comma separated string:

implode(',',$result);

You can use that with IN

answered Apr 15, 2013 at 16:38

TheMethod

2,99811 gold badges46 silver badges77 bronze badges

Comments

exussum · Accepted Answer · 2013-04-15 16:40:11Z

0

To convery an array to a comma sepearted string you would use

implode (",",$array)

answered Apr 15, 2013 at 16:40

exussum

18.6k8 gold badges35 silver badges67 bronze badges

1 Comment

Khul Over a year ago

Thank you. I guess this is exactly the right php function to use in this case and will be learning on it since your comment. I apreciatte it.

Collectives™ on Stack Overflow

PHP + convert array for use with IN statement

4 Answers 4

2 Comments

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related