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, Apr 15 '13 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, Apr 15 '13 at 17:29

score 2 · Answer 1 · answered Apr 15 '13 at 16:43

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 '13 at 16:43

alwaysLearn

6,882
7
39
67

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 Apr 15 '13 at 17:21
This approach is also most helpfull if you later want to print results using fwrite since the implode function will convert arrays to strings. – Khul Apr 15 '13 at 19:26

Fabio · Accepted Answer · 2013-04-15T16:42:28.573

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 '13 at 16:42

answered Apr 15 '13 at 16:36

Fabio

23,183
12
55
64

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 Apr 15 '13 at 16:41
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! – Khul Apr 15 '13 at 17:11

score 0 · Answer 3 · answered Apr 15 '13 at 16:38

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 '13 at 16:38

TheMethod

2,893
9
41
72

score 0 · Answer 4 · answered Apr 15 '13 at 16:40

0

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

implode (",",$array)

answered Apr 15 '13 at 16:40

exussum

18,275
8
32
65

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. – Khul Apr 15 '13 at 17:18

PHP + convert array for use with IN statement

4 Answers4