Possible to use PHP arrays as MySql parameters?

dougmcc1

Is it possible to use a PHP array as a parameter in a MySql query? Like this:

$array = array(1, 2, 3, 4, 5);
mysql_query("SELECT field FROM table WHERE param='$array'");

I want to select all the rows that contain any of the values of the array.

Steveo31

Alright! My first real post 🙂

I'm not sure of how to display the query, but here's what I came up with:

<?php
//connect:
$db = mysql_connect('localhost', 'root') or die(mysql_error());

//set up values:
$arr=array(1, 2, 3);

foreach($arr as $value){
	$sql = mysql_query("SELECT field FROM table WHERE param='$value'");
	$result = mysql_query($sql, $db)
	while ($row = mysql_fetch_assoc($result)) {
		//display code here..
	}
}

?>

dougmcc1

Yep, thanks. I was testing it myself as I was waiting for a reply and managed to do it another way as well:

$array = array(1, 2, 3);
$count = count($array);
for ($i = 0; $i < $count; $i++) {
echo "SELECT field FROM table WHERE param='$array[$i]'<BR>";
}

dougmcc1

Now how would I do this? $array.=array($param);

In this context:

for ($i = 0; $i < 3; $i++) {
//ask user for $param value
$array.=array($param); //add it to the array
}

for ($i = 0; $i < $count; $i++) {
echo "SELECT field FROM table WHERE param='$array[$i]'<BR>";
}

Steveo31

How are you going to get the user to enter the value? You can use

array_push($value)

To add it to the end of the array.

nemonoman

May I suggest that what you really want to do is join the array into an 'IN' statement with commas:

Then

SELECT * FROM sometable WHERE somefield IN ('1', '2','3')

etc.

This will select records for all the values in the IN statement in one query. Case above for example might fetch records 1-3.

You will get an error if the IN string is empty. I always add the string 'NULL' to solve this problem:

ERROR:
SELECT * FROM sometable WHERE somefield IN ()

OK
SELECT * FROM sometable WHERE somefield IN ('NULL')

dougmcc1

Thanks nemonoman. I'm still trying to solve my problem and will try your approach. I think that's exactly what I needed.

Thanks again.

dougmcc1

By the way, would I able to do this?

$array=array(1, 2, 3);

SELECT field FROM category_list WHERE param1='$param1' AND param2 IN ('$array') AND param3='$param3'

nemonoman

Well, you need to turn the array into a string saying 1,2,3

However

SELECT * FROM sometable WHERE thing1 IN (1,2,3) AND thing2=4 AND thing3 NOT IN ('a', 'b', 'c')

etc. Just fine and I do these sorts of queries all the time. THey are very fast, much faster than looping via PHP

dougmcc1

Well the thing is I dont know what the parameters are. I'm creating a directory and when I remove a subcategory, it needs to remove the subcategories below it. I'm trying to avoid typing them all in manually. With each loop I store the previous subcategory and delete the subcategories below it.

It almost works. If I delete a subcategory, it deletes all the subcategories directly below it, but then it only deletes the subcategories below the last one for the next loop.

Example:
Say I want to delete subcategory Alphabet and it contains subcategories A,B,C. And A,B,C contains subcategories.

My script will delete Alphabet, A, B, C, and all the subcategories for C. So it misses the subcategories for A and B.

Somehow I want it to remember A and B so it will delete the subcategories below them like it does for C.

I dont want to post my code so I think I'll just have to figure it out myself. But I appreciate the extra tools 🙂

nemonoman

Well, then you do want to use a loop. Delete as you walk through the array.

dougmcc1

I went back to my previous method and got it to work this time.

If it helps anyone, here's the method I used:

$arraycount = count($array);
for ($i = 0; $i < $arraycount; $i++) {
$myquery="SELECT field FROM table WHERE param='$array[$i]'" . mysql_error();
$myresult=mysql_query($myquery);
while ($myrow = mysql_fetch_assoc($myresult)) {

//extract stuff from db where param equals any of the $array values

} // while
} // end for

Drakla

I've got an id, parentid (pid) menu heirarchy setup and use this function to return all the child ids under it, it returns them as an imploded string that you could either use to delete them, or find companies/items in those categories for use in a progressive refinement browsing system

function all_sub_ids($id)
{
	$offset=0;
	$results=array();

require("db-connect-inc.php");

do
{
	$q="SELECT id FROM cats WHERE pid=$id";
	$db->query($q);

	while($row=$db->fetch_row())
		$results[]=$row[0];
}
while(@$id=$results[$offset++]);

return implode(",",$results);
}

so you could do

$ids=all_sub_ids($id);

$db->query("DELETE FROM cats WHERE ids IN ($ids)");

//or for a company directory

$db->query("SELECT * from COMPANIES where categoryid IN ($ids)");

dougmcc1

Hi Drakla,

Your 'pid' is the equivelent of my 'category', and your 'id' is the equivelent of my 'subcategory'. But I would also probably need a 'subpid' because my subcategories are located in other subcategories, which are all in the same category.

Anyway, thanks for the additional input.