problem using array in WHERE clause

[bpgillett]

after the user submits multiple selections from a list item within an html form containing music composer's names, i use the WHERE clause with an array and a 'foreach' statement to perform a boolean search on a table of music compositions. the idea is to query the table multiple times--each time supplying a different value for the 'user_name' field. the script then prints the results. if no rows are returned, i display, "No records found! Please modify your search" and exit.

everything works well until a user_name in the middle of the array happens to return no results. i can't figure out how to get the script to move on to the next user_name in the array and continue printing results until all items in the array have been used. instead, the script just prints "No records found! Please modify your search" and exits. any ideas? thanks.

--brian

// if all composers is selected then populate $composer[] with all usernames
if ($all_composers){
$result = mysql_query("SELECT DISTINCT user_name FROM compositions");
if ($myrow = mysql_fetch_array($result)) {
do {
$composer[] = $myrow['user_name'];
} while ($myrow = mysql_fetch_array($result));
}}

if ($composer == "") {
	die('<font color="#333333" size="2" face="Arial, Helvetica, sans-serif">You specify individual composers or select \'All Composers\'.<br><br><A HREF="javascript:history.back()">Return to form</A></font>');
}


//search database for compositions
foreach ($composer as $user_array) {

$result = mysql_query("SELECT * FROM compositions WHERE user_name='$user_array' $instrument_search_string year BETWEEN '$y_min' AND '$y_max' AND length BETWEEN '$l_min' AND '$l_max' ORDER BY last_name",$db) or die("Query Error: ".mysql_error());

$num=mysql_numrows($result);
if ($num==0) {

echo "<font color=\"#333333\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">No records found!  Please modify your search.</font>";	
echo "<br><p><font size=\"2\" face=\"Arial, Helvetica, sans-serif\"><a href=\"catalogue.php\">Return to search page</a></font></p>";

exit;

 } else {

if ($myrow = mysql_fetch_array($result)) {

printf("<tr><td><font color=\"#333333\" size=\"3\" face=\"Arial, Helvetica, sans-serif\"><br>%s, %s<br></font></td>", $myrow["last_name"], $myrow["first_name"]);

  echo "<table border=0 bgcolor=\"#CCCCCC\">\n";

  do {

printf("<tr><td><font color=\"#333333\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">'%s' for %s %s %s </font></td>", $myrow["title"], strp_char($myrow["instrumentation"]), sec2hms($myrow["length"]), $myrow["year"]);

  } while ($myrow = mysql_fetch_array($result));

echo "</table>\n";


}
}
}

[bpat1434]

It's funny. You answer your own question in your own post. HOW ABOUT THAT?!?!

i can't figure out how to get the script to move on to the next user_name in the array and continue printing results until all items in the array have been used.

PHP has the "function" continue which will continue the loop.

Oh, and [php][/php] work wonders for code. Easier on the eyes.

~Brett

[bpgillett]

thanks. 'continue' works great. any idea how to get the "No records found! Please modify your search." message to display only if $result returns false for all items in my $composer array. presently, if the last item in the $composer array happens to return false then the message is still printed (at the end of the printed output). sorry, i'm just hung up here. thanks.

--brian

foreach ($composer as $user_name) {

$result = mysql_query("SELECT * FROM compositions WHERE user_name='$user_name' $instrument_search_string year BETWEEN '$y_min' AND '$y_max' AND length BETWEEN '$l_min' AND '$l_max' ORDER BY last_name",$db) or die("Query Error: ".mysql_error());

$num=mysql_numrows($result);
if ($num==0) {

continue;

 } else {

if ($myrow = mysql_fetch_array($result)) {

printf("<tr><td><font color=\"#333333\" size=\"3\" face=\"Arial, Helvetica, sans-serif\"><br>%s, %s<br></font></td>", $myrow["last_name"], $myrow["first_name"]);

  echo "<table border=0 bgcolor=\"#CCCCCC\">\n";

  do {

printf("<tr><td><font color=\"#333333\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">'%s' for %s %s %s </font></td>", $myrow["title"], strp_char($myrow["instrumentation"]), sec2hms($myrow["length"]), $myrow["year"]);

  } while ($myrow = mysql_fetch_array($result));

echo "</table>\n";


}
}
}

$num=mysql_numrows($result);
if ($num==0) {

echo "<font color=\"#333333\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">No records found!  Please modify your search.</font>";	
echo "<br><p><font size=\"2\" face=\"Arial, Helvetica, sans-serif\"><a href=\"catalogue.php\">Return to search page</a></font></p>";
exit;
}

[bpat1434]

I've run into some problems with people who code and use:
"mysql_numrows()"

I do know that mysql_num_rows() works 100%. Perhaps that is where you're getting mixed up, but I'm pretty sure it's not.

Some comments in your code would not only help me/others, but you as well. But to take a short look, let's do this:

foreach ($composer as $user_name) {

You start a loop for each composer as user_name.

    $result = mysql_query("SELECT * FROM compositions WHERE user_name='$user_name' $instrument_search_string year BETWEEN '$y_min' AND '$y_max' AND length BETWEEN '$l_min' AND '$l_max' ORDER BY last_name",$db) or die("Query Error: ".mysql_error()); 

You set the query. Fine.

    $num=mysql_num_rows($result);

You define the number of rows that the query returns.

    if ($num==0)
    {
        continue;
    }

If mysql doesn't return rows, DISPLAY ERROR and move to next user_name.

    else
    {
        if ($myrow = mysql_fetch_array($result)) {

Otherwise, if $myrow can fetch the array, let's do something.

            printf("<tr><td><font color=\"#333333\" size=\"3\" face=\"Arial, Helvetica, sans-serif\"><br>%s, %s<br></font></td>", $myrow["last_name"], $myrow["first_name"]);

        echo "<table border=0 bgcolor=\"#CCCCCC\">\n";

Print out the name of the composer.

            do {
                printf("<tr><td><font color=\"#333333\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">'%s' for %s %s %s </font></td>", $myrow["title"], strp_char($myrow["instrumentation"]), sec2hms($myrow["length"]), $myrow["year"]);
            }

Print out the song info. . .

            while ($myrow = mysql_fetch_array($result));

. . .while $myrow is fetching an array for the current result

            echo "</table>\n";
        }
    }
}

Finish the table definition, and close ALL brackets (including the loop bracket).

$num=mysql_numrows($result);
if ($num==0) {
    echo "<font color=\"#333333\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">No records found!  Please modify your search.</font>";    

    echo "<br><p><font size=\"2\" face=\"Arial, Helvetica, sans-serif\"><a href=\"catalogue.php\">Return to search page</a></font></p>";
    exit;
}

Now, outside the foreach() loop you specify an error for a variable that will get checked once, the last time you go through it. This probably should replace your first if($num == 0) definition, and the exit; should be replaced with continue;

You could also do this another way. You could write a function that prints out all the information you want. Then, IF the number is NOT zero, call the function, if so, then show the error.

~Brett