[SOLVED] Search help, not working

[timmah1]

I have a form to search my users database by service and state

<form name="search" method="POST" action="search.php">
<select name="service" id="service">
<?php
$sql="SELECT * FROM type ORDER BY name ASC";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{

echo "<option value=" . $row['name'] . ">" . $row['name'] . "</option>";
}
?>
</select>
<select name="state" id="state" >

                        <option value=\"\"></option>
                        <option value=\"None\">All</option>
                        <option value=\"AL\">Alabama</option>
                        <option value=\"AK\">Alaska</option>
                        <option value=\"AZ\">Arizona</option>
                        <option value=\"AR\">Arkansas</option>
                        <option value=\"CA\">California</option>
                        <option value=\"CO\">Colorado</option>
                        <option value=\"CT\">Connecticut</option>
                        <option value=\"DE\">Delaware</option>
                        <option value=\"DC\">District of Columbia</option>
                        <option value=\"FL\">Florida</option>
                        <option value=\"GA\">Georgia</option>
                        <option value=\"HI\">Hawaii</option>
                        <option value=\"ID\">Idaho</option>
                        <option value=\"IL\">Illinois</option>
                        <option value=\"IN\">Indiana</option>
                        <option value=\"IA\">Iowa</option>
                        <option value=\"KS\">Kansas</option>
                        <option value=\"KY\">Kentucky</option>
                        <option value=\"LA\">Louisiana</option>
                        <option value=\"ME\">Maine</option>
                        <option value=\"MD\">Maryland</option>
                        <option value=\"MA\">Massachusetts</option>
                        <option value=\"Michigan\">Michigan</option>
                        <option value=\"MN\">Minnesota</option>
                        <option value=\"MS\">Mississippi</option>
                        <option value=\"MO\">Missouri</option>
                        <option value=\"MT\">Montana</option>
                        <option value=\"NE\">Nebraska</option>
                        <option value=\"NV\">Nevada</option>
                        <option value=\"NH\">New Hampshire</option>
                        <option value=\"NJ\">New Jersey</option>
                        <option value=\"NM\">New Mexico</option>
                        <option value=\"NY\">New York</option>
                        <option value=\"NC\">North Carolina</option>
                        <option value=\"ND\">North Dakota</option>
                        <option value=\"Ohio\">Ohio</option>
                        <option value=\"OK\">Oklahoma</option>
                        <option value=\"OR\">Oregon</option>
                        <option value=\"PA\">Pennsylvania</option>
                        <option value=\"PR\">Puerto Rico</option>
                        <option value=\"RI\">Rhode Island</option>
                        <option value=\"SC\">South Carolina</option>
                        <option value=\"SD\">South Dakota</option>
                        <option value=\"TN\">Tennessee</option>
                        <option value=\"TX\">Texas</option>
                        <option value=\"UT\">Utah</option>
                        <option value=\"VT\">Vermont</option>
                        <option value=\"VI\">Virgin Islands</option>
                        <option value=\"VA\">Virginia</option>
                        <option value=\"WA\">Washington</option>
                        <option value=\"WV\">West Virginia</option>
                        <option value=\"WI\">Wisconsin</option>
                        <option value=\"WY\">Wyoming</option>
                       </select></td>
                        </tr>
                        <tr>
                          <td height="72" align="center" valign="middle" background="images/05_search.png"><input type="submit" name="button" id="button" value="Submit" /></td>
                        </tr>
                      </table>
                    </form>

 

And my search code is here:

$search = "SELECT * FROM users WHERE service LIKE '%$type%' AND state LIKE '%$state%' ORDER BY id DESC";
$query = mysql_query($search) or die(mysql_error());
$row_sql = mysql_fetch_assoc($query);
$total = mysql_num_rows($query);

if($total>0) {
while ($row_sql = mysql_fetch_assoc($query)) {//echo out the results
echo ''.$row_sql['name'].'<br />'.$row_sql['state'].'';
}
} else
{
echo "No results to display";
}

 

The problem is, no matter what I select, it shows "No results to display".

I have info in the fields, and I match it identical, but it still says "No results to display".

 

Am I missing something?

 

Any help would be greatly appreciated.

 

Thanks in advance.

[DarkWater]

Try the query in the MySQL client. So in the search page, add "echo $search;" (without the quotes...)after:

$search = "SELECT * FROM users WHERE service LIKE '%$type%' AND state LIKE '%$state%' ORDER BY id DESC";

 

And then open MySQL and run the query.  See why it's not returning anything.

[timmah1]

I'm getting this

 

SELECT * FROM users WHERE service LIKE '%Client%' AND state LIKE '%\\\"Ohio\\\"%' ORDER BY id DESCNo results to display

 

Not sure why Ohio looks like this \\\"Ohio\\\"

 

It's grabbing the correct info, but Ohio has those

[DarkWater]

Are you using mysql_real_escape_string on $state?  If so, change it to this:

if (get_magic_quotes_gpc()) {

  $state = mysql_real_escape_string(stripslashes($_POST['state']));

}

else {

  $state = mysql_real_escape_string($_POST['state']);

}

 

Make that the new $state initialization statement.

[timmah1]

I got it to search correctly, but now it won't display the results

 

if($total>0) {
while ($row_sql = mysql_fetch_assoc($query)) {//echo out the results
echo ''.$row_sql['name'].'<br />'.$row_sql['state'].'';
}

[DarkWater]

Why are you echoing blank strings before and after the variables?

[timmah1]

Not understanding what you mean?

 

How can they be blank strings?

[DarkWater]

Never mind, it doesn't matter.  I think I found the problem. =)

You're calling this line:

$row_sql = mysql_fetch_assoc($query);

 

BEFORE you call it in the while() loop....therefore skipping over the first result.  Remove that line before the $total = mysql_num_rows() line. =P

[timmah1]

Perfect!!!

 

Thank you so much.

[DarkWater]

No problem, dude. =)