Simple form - How to get multiple selections

[sgtpepper]

I have a simple form to select towns. The town selected becomes the variable selecttown. My form is like this:

<select size="7" name="selecttown" multiple>
<option value="Ascutney VT">Ascutney, VT</option>
<option value="Barnard VT">Barnard, VT</option>
</select>

The form is submitted to code the queries the database based on the variables of the form.

I have the query setup like this:

WHERE town = '$selecttown'

Notice that I want the form to give the user the ability to select more than one town.

If more than one town is selected....I only get results for one town. What could I be doing wrong?

[Hamsterpants]

Instead of useing "=" rather try something like

select * from WHATEVER
WHERE town like %'$selecttown'%

[sgtpepper]

hmmmm....got the error
Warning: Supplied argument is not a valid MySQL result resource in

When I replaced

WHERE town = '$selecttown'
with
WHERE town like %'$selecttown'%

[Hamsterpants]

try '%selecttown%'

[sgtpepper]

nope. This produces no results and take a long time to process.

[stolzyboy]

name your select box selecttown[]

then on your processing page before you do your select statement, have this

$selecttown = @implode(", ", $selecttown);

then do your select statement like

select * from table where town like '%$selecttown%';

[Hamsterpants]

Hi,

Can you explain what adding the [] and then what the @implode does please.

Thanks
Dill

[sgtpepper]

I still get the
Warning: Supplied argument is not a valid MySQL result resource in

error...

[stolzyboy]

the [] makes an array out of the multiple selections, and the implode creates a string out of those so you can check with the like statement

show us your connection string, query, result, fetch method, etc...

[echox]

<select size="7" name="selecttown[]" multiple>
<option value="Ascutney VT">Ascutney, VT</option>
<option value="Barnard VT">Barnard, VT</option>
</select>
.........

$selecttown = $POST["selecttown"];
// $selecttown is array then the sql is

select * from table where town like '$selecttown[0]' and town like '$selecttown[1]' and ...

[sgtpepper]

Doing it exactly like stated above produces an error that says no match for my query. My debug code shows that I have no value for selecttown[1] etc..

hmmm.....

[stolzyboy]

Originally posted by stolzyboy
the [] makes an array out of the multiple selections, and the implode creates a string out of those so you can check with the like statement

show us your connection string, query, result, fetch method, etc...

once again...

[sgtpepper]

ok. On my form page I have:

<form action="newcode.php" method="POST">
<select size="7" name="selecttown[]" multiple>
<option value="Acworth NH">Acworth, NH</option>
<option value="Charlestown NH">Charlestown, NH</option>

My newcode.php page is this

<?php
session_start();
$this_document = $_SERVER['newcode.php']; 
$db_addr = 'XXX';        // address of MySQL server.
$db_user = 'XXX';        // Username to access server.
$db_pass = 'XXX';        // Password access server.
$db_name = 'XXX';    // Name of database to connect to.
$connect = @mysql_connect("$db_addr", "$db_user", "$db_pass");
$page = $_GET['page'];
$limit = $_GET['limit'];
$selecttown = @implode(", ", $selecttown);

if (!($connect)) // If no connect, error and exit().
{
     echo("<p>Unable to connect to the database server.</p>");
     exit();
}

if (!(@mysql_select_db($db_name))) // If can't connect to database, error and exit().
{
     echo("<p>Unable to locate the $db_name database.</p>");
     exit();
}

if (!($limit)){
     $limit = 10;} // Default results per-page.

if ( !$page or $page < 0 ) { $page = 0; } // Default page value.

$numresults = mysql_query("SELECT town, list_price, bedrooms, baths, rooms, Id ,IF (lister_office_name like 'Realty', 1, 0)
AS featured
FROM residential
WHERE town like '%$selecttown%';
AND list_price BETWEEN '$lowprice' AND '$highprice'
AND bedrooms like '%$bedroomsel'
AND baths like '%$bathselect'
ORDER BY featured DESC, list_price DESC

"); // the query.

$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.

if ($numrows == 0){
     echo("No results found matching your query"); // modify the "Not Found" error for your needs.
echo $selecttown;
echo $bedroomsel;
echo $bathselect;
     exit();}



$pages = intval($numrows/$limit); // Number of results pages.
// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows % $limit) {
$pages++;} // has remainder so add one page

$current = intval($page/$limit) + 1; // Current page number.

if (($pages < 1)) {
$total = 1;} // If $pages is less than one, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.


//escape from PHP mode.
?>



<html>
<head>
<title>XXX</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>

<?php
include("head.html");
?>



<table width="100%" border="0" cellpadding="2">
  <!--DWLayoutTable-->
  <tr> 
    <td width="323" align="left"><font size="1" face="Arial, Helvetica, sans-serif">Results 
      per-page: <a href="<?=$this_document?>?query=<?=$query?>&page=<?=$page?>&limit=5">5</a> 
      | <a href="<?=$this_document?>?query=<?=$query?>&page=<?=$page?>&limit=10">10</a> 
      | <a href="<?=$this_document?>?query=<?=$query?>&page=<?=$page?>&limit=20">20</a> 
      | <a href="<?=$this_document?>?query=<?=$query?>&page=<?=$page?>&limit=50">50</a></font><font face="Arial, Helvetica, sans-serif">&nbsp; 
      </font>
<br>
</td>
    <td colspan="2" align="left"><font size="2" face="Arial, Helvetica, sans-serif">&nbsp;</font></td>
  </tr>
  <tr>
    <td align="left"><font size="2" face="Arial, Helvetica, sans-serif">Showing
      listings <b>
      <?=$first?>
      </b> - <b>
      <?=$last?>
      </b> of <b>
      <?=$numrows?>
      </b></font>for <b><i><?php print $selecttown?></b></i><br><a href="newsearch.php">Click Here</a> to change your search criteria.</td>

</td>
    <td align="left"></td>
    <td width="324" align="left">
      <p align="right"><font size="2" face="Arial, Helvetica, sans-serif">&nbsp;</font><font size="2" face="Arial, Helvetica, sans-serif">Page
      <b>
      <?=$current?>
      </b> of <b>
      <?=$total?>
      </b></font></p>
    </td>
  </tr>
  <tr> 
    <td colspan="3" align="left" valign="top"><font size="2" face="Arial, Helvetica, sans-serif">&nbsp;</font> 
      <div align="left"><font size="2" face="Arial, Helvetica, sans-serif"></font></div>
      <font size="3" face="Arial, Helvetica, sans-serif"><center>&nbsp;
      <? 
//$limitnum = 15;
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$this_document?query=$query&page=$back_page&limit=$limit\">Back</a>\n");}

for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
 $ppage = $limit*($i - 1);
 if ($ppage == $page){
 echo("<b>$i</b>\n");} // If current page don't give link, just text.
//elseif ($ppage < $page - $limitnum || $ppage > $page + $limitnum){} 
else{
 echo("<a href=\"$this_document?query=$query&page=$ppage&limit=$limit\">$i</a>\n");}
}

if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo("    <a href=\"$this_document?query=$query&page=$next_page&limit=$limit\">Next</a>");}
?>
      </font></center></td>
  </tr>
  <tr> 
    <td colspan="3" align="left"><!--DWLayoutEmptyCell-->&nbsp; </td>
  </tr>
  <tr> 
    <td colspan="3" align="left"> 
      <?
//Go back into PHP mode.
// Now we can display results

$results = mysql_query("SELECT * ,IF (lister_office_name like 'Realty', 1, 0)
AS featured
FROM residential
where town like '%$selecttown%';
AND list_price BETWEEN '$lowprice' AND '$highprice'
AND bedrooms like '%$bedroomsel'
AND baths like '%$bathselect'
ORDER BY featured DESC, list_price DESC

LIMIT $page, $limit");

for($i = 0; $i < $limit; $i++){
for ($count = 1; $row = mysql_fetch_assoc ($results);++$count)
        {
if ($count & 1) {
$color = "#FFFFFF";
$font = "white";
}
else {
$color = "#FFFFCC";
$font = "black";
}
$content=substr($row[remark1],0,90);
$format_price = number_format($row['list_price']);
$printthis = "

<table border=0 bgcolor=$color width=100% cellspacing=0 cellpadding=0>
  <tr>
    <td width=50%>
      <table border=0 width=100% cellspacing=0 cellpadding=0>
        <tr>
          <td width=50%><b><font size=2 color=#990000 face=Times New Roman, Times, serif>$$format_price</font></b></td>
          <td width=50%><font size=2 face=Times New Roman, Times, serif>$row[town]</font></td>
        </tr>
        <tr>
          <td width=50%><font size=2 face=Times New Roman, Times, serif>Bedrooms: 
            $row[bedrooms]</font></td>
          <td width=50%><font size=2 face=Times New Roman, Times, serif>Bathrooms: 
            $row[baths]</font></td>
        </tr>
      </table>
    </td>
    <td width=50%><font size=2 face=Times New Roman, Times, serif>$content ... <a href=displayprop.php?Id=$row[Id]>Click Here for more info</a></td>
</font>  

</tr>
</table>

";
?>


<table border=0 width=100% cellspacing=0 cellpadding=0>
<tr>
<td>

<a href=displayprop.php?Id=<? echo $row[Id]; ?>>

<img border="0" src="http://www.bscdata.net/propimages/<? echo $row[photo_file]; ?>" width="100" height="65">

</a>
<?
$displaystatus = "$row[status]";

if(($displaystatus) == 'C') {
$displaystatusvar = "";
}
if(($displaystatus) == 'P') {
$displaystatusvar = "
<table border=0 cellspacing=1 width=100%>
  <tr>
    <td width=100% bgcolor=#FFFF00>
      <p align=center><font size=2><b>Sale Pending</b></font></td>
  </tr>
</table>
";
}
echo $displaystatusvar;
?>

</td>
<td>
&nbsp;&nbsp;
</td>
<td width="100%">
<?
print $printthis;
?>
</td>
</tr>
</table>
<hr color=#D7DC0C noshade>
<?
}

}
?>
    </td>
  </tr>
  <tr> 
    <td colspan="3" align="left" valign="top"><font size="2" face="Arial, Helvetica, sans-serif">&nbsp;</font> 
      <div align="left"><font size="2" face="Arial, Helvetica, sans-serif"></font></div>
      <font size="3" face="Arial, Helvetica, sans-serif"><center>
      <? 
//$limitnum = 15;
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$this_document?query=$query&page=$back_page&limit=$limit\">Back</a>\n");}

for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
 $ppage = $limit*($i - 1);
 if ($ppage == $page){
 echo("<b>$i</b>\n");} // If current page don't give link, just text.
//elseif ($ppage < $page - $limitnum || $ppage > $page + $limitnum){} 
else{
 echo("<a href=\"$this_document?query=$query&page=$ppage&limit=$limit\">$i</a>\n");}
}

if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo("    <a href=\"$this_document?query=$query&page=$next_page&limit=$limit\">Next</a>");}
?>
      </font></center></td>
  </tr>
</table>

<?php
include("foot.html");
?>

</body>
</html>

Latest error says :

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in newcode.php on line 41
No results found matching your queryCroydon NH, Newport NH

[stolzyboy]

change this

$numresults = mysql_query("SELECT town, list_price, bedrooms, baths, rooms, Id ,IF (lister_office_name like 'Realty', 1, 0)
AS featured
FROM residential
WHERE town like '%$selecttown%';
AND list_price BETWEEN '$lowprice' AND '$highprice'
AND bedrooms like '%$bedroomsel'
AND baths like '%$bathselect'
ORDER BY featured DESC, list_price DESC

"); // the query.

to this

$numresults = mysql_query("SELECT town, list_price, bedrooms, baths, rooms, Id ,IF (lister_office_name like 'Realty', 1, 0)
AS featured
FROM residential
WHERE town like '%$selecttown%';
AND list_price BETWEEN '$lowprice' AND '$highprice'
AND bedrooms like '%$bedroomsel'
AND baths like '%$bathselect'
ORDER BY featured DESC, list_price DESC

") or die(mysql_error()); // the query.

that will give you the exact error mysql is being picky about

and try echoing out $selecttown after you do an implode to see if you are getting it from the previous page

you may have to replace this line

$selecttown = @implode(", ", $selecttown);

with these lines

$selecttown = $_GET['selecttown'];
$selecttown = @implode(", ", $selecttown);

[sgtpepper]

It shows this error:

You have an error in your SQL syntax near '; AND list_price BETWEEN '0' AND '300000' AND bedrooms like '%%' AND baths like ' at line 4

[stolzyboy]

change this line

where town like '%$selecttown%';

to this

where town like '%$selecttown%' //notice the semi-colon is gone

[sgtpepper]

Thank you for putting up with me. Here is the latest.

If I select 1 town....everything works fine.

If I select 2 towns I get
No results found matching your queryClaremont NH, Lempster NH

(I placed an echo so I can see the results of $selecttown which shows Claremont, NH Lempster NH)

So, I thought I should change:
$selecttown = @implode(", ", $selecttown);

to

$selecttown = $_GET['selecttown'];
$selecttown = @implode(", ", $selecttown);

This is displaying many many towns....more than just the towns selected.

[stolzyboy]

to narrow this down, just comment out your current query and do this one

select * from tablename where town like '%$selecttown%';

it could be other parts of your query that are messing up

[sgtpepper]

I have my query now as simple as possible

SELECT * from table WHERE town like '%$selecttown%'

Same kind of issue....if I select one town...it works but more than one town and:

No results found matching your queryAcworth NH, Claremont NH

[stolzyboy]

do this one thing for me, i just want to make sure you have it correct, replace all your query result stuff with this

$query = "select * from whatevertable where town like '%$selecttown%';";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
echo $row['town'] . "<br>";
}

make sure and replace the whatevertable with correct table name and i hope it really isn't table