array based query php

Question

instead of searching a mysql DB for one variable at a time, i want to search for a number of variables, and fit it into the initial query string

so instead of

$query="SELECT * FROM table1 WHERE state = 'CA' OR state = 'CO' OR state = 'TX'";

i want

$states = ("CA,CO,TX");
$query="SELECT * FROM table1 WHERE state = $states";

I have tried

<?
include("connect.php"); // file to connect to db
$states = array(CA,TX);
$states_str = implode(",", $states);


$query="SELECT * FROM table1 WHERE state IN ($states_str)";

$result=mysql_query ($query);
while($row = mysql_fetch_array($result)) {
echo $row['state'];
}

?>

but i get this error message

 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in  ... on line 11

this is line 11

while($row = mysql_fetch_array($result)) {

however, if i use this code, i get a result, only to show that when i switch back to the old conventional query it works

<?
include("connect.php"); // file to connect to db
$states = array(CA,TX);
$states_str = implode(",", $states);


$query="SELECT * FROM table1 WHERE state = 'CA'";

$result=mysql_query ($query);
while($row = mysql_fetch_array($result)) {
echo $row['state'];
}

?>

but then i am bypassing the implode() thing, which i found somewhere else but it is not working for me

Please, DO NOT use mysql_query in new applications. It's deprecated, dangerous if used incorrectly, and is being removed from future versions of PHP. A modern replacement like PDO is not hard to learn. A guide like PHP The Right Way will help you avoid making mistakes like this. — tadman
– tadman, Commented Nov 7, 2013 at 18:15
hmmph ... imagine the work involved when 'somebody' decides they dont like the syntax ... to dive into a zillion lines of code and re-write everything when it is all working fine in the first place is one of the big negatives for me in re PHP. what works one day is gone the next, you have no idea why, and then when u find out u gotta rewrite a lotta code. truly annoying. — user2835653
– user2835653, Commented Nov 7, 2013 at 20:19
mysql_query has been deprecated for a while now, and widely regarded as obsolete for at least five years. Remember "working fine" is a subjective thing. If your code is riddled with SQL injection bugs, it is really not fine. — tadman
– tadman, Commented Nov 7, 2013 at 22:00
possible duplicate of mysql_fetch_array() expects parameter 1 to be resource, boolean given in select — John Conde
– John Conde, Commented Nov 7, 2013 at 23:31
possible duplicate of Reference - What does this error mean in PHP? — Glavić
– Glavić, Commented Nov 14, 2013 at 10:57

geomagas · Accepted Answer · 2013-11-07 17:19:15Z

2

Apparently, the query fails because the string values are not quoted.

Try:

$states = array('CA','TX');
$states_str = implode("','", $states);
$query="SELECT * FROM table1 WHERE state IN ('$states_str')";

answered Nov 7, 2013 at 17:19

geomagas

3,2801 gold badge19 silver badges28 bronze badges

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

Comments

Strawberry · Accepted Answer · 2013-11-07 17:23:00Z

0

 <?php
 include('../testi.inc');

 $states = array('CA','TX');
 $states_str = implode("','", $states);

 $query = "SELECT * FROM states WHERE state IN ('$states_str')";

 $result=mysqli_query ($db,$query) or die(mysql_error());
 while($row = mysqli_fetch_assoc($result)) {
 echo $row['state'];
 }

 ?>

or something like that

answered Nov 7, 2013 at 17:23

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Comments

Prince Singh · Accepted Answer · 2013-11-07 17:26:34Z

0

<?
include("connect.php"); // file to connect to db
$states = array('CA','TX');
$states_str = implode("','", $states);
$states_str = rtrim($states_str,",'");


$query="SELECT * FROM table1 WHERE state IN ('$states_str')";

$result=mysql_query ($query);
if($result){
while($row = mysql_fetch_array($result)) {
echo $row['state'];
}
}
?>

Thats because your query is failing so first check for it

edited Nov 7, 2013 at 17:26

answered Nov 7, 2013 at 17:20

Prince Singh

5,2035 gold badges30 silver badges33 bronze badges

Collectives™ on Stack Overflow

array based query php

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related