[SOLVED] php mysql problem

[phplearner2008]

Hi,

I have a web page that has a drop down list with multiple select. Based on the user's selections, mysql database has to be queried for each option selected by user.

 

The name of drop down list is EDUCATION. $lines counts the number of options selected. I am storing value of each option in $mystore[$i]

 

I am using loop as I need to query database for each option the user selects from the list. The code does not work. Any suggestions?

 

<?php

$lines = 0;

$i=0;

foreach ($_POST['EDUCATION'] as $EDUCATION => $value)

{

echo "Education: $EDUCATION; Value: $value<br>";

$store[$EDUCATION] = $value;

$mystore[$i] = $store[$EDUCATION];

$lines++;

}

$eduresult = SELECT * FROM profile;

for($i=0;$i<$lines;$i++)

{

$edu = "SELECT * FROM profile WHERE EDUCATION = '$mystore[$i]'";

 

$eduresult =mysql_query(".$eduresult.UNION.$edu.")or die(mysql_error());

}

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

 

echo $row['NAME'];

echo $row['EMAIL'];

 

}

 

?>

[fenway]

Why select everything then merge it with a subset????

[hitman6003]

Why are you creating two arrays with the inverted keys/values?  You don't even use the $store array...

 

$store[$EDUCATION] = $value;
$mystore[$i] = $store[$EDUCATION];

 

As fenway said, why are you unioning a "SELECT *" with a subset of that same table?  Also, why are you looping through each $mystore when you could simply use an IN clause?

 

$eduresult = "SELECT * FROM profile";

for($i = 0; $i < $lines; $i++) {
$edu = "SELECT * FROM profile WHERE EDUCATION = '$mystore[$i]'";

$eduresult = mysql_query(".$eduresult.UNION.$edu.")or die(mysql_error());
}

 

Better way...?

 

<?php

// apply mysql_real_escape_string to all the elements...
array_walk($_POST['EDUCATION'], 'mysql_real_escape_string');

// generate the query...
$query = "SELECT * FROM profile WHERE `EDUCATION` IN('" . implode("', '", $_POST['EDUCATION']) . "')";

$result = mysql_query($query) or die("Query: " . $query . "\n\n" . mysql_error());

while ($row = mysql_fetch_assoc($result)) {
echo $row['NAME'] . " " . $row['EMAIL'];
}

[phplearner2008]

Thanks a lot hitman6003, the code works just fine! My problem is solved.