#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    3
    Rep Power
    0

    Help needed with indexes and queries


    Hi All,
    My db was built through the generosity of others. Meaning: I know nothing about this stuff I recently deleted some indexes thinking they were unnecessary and guess what happened? That's right, "she no work no more".

    I don't think my db is complex, all it's supposed to do is search for a matching zip code and display the contact info for anyone who covers it. Or display a message if there are no matches.

    The error message I'm getting is:
    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /hermes/bosweb26b/b2458/ipg.paramedicalexamcom/NASCPE/results.php on line 41

    Line 41 code in the php is:
    mysql_select_db("nascpe_directory1"); $query = "SELECT * FROM examiners WHERE MATCH(coverage) AGAINST('$zip')"; $result = mysql_query($query); if (mysql_num_rows($result)==0) { echo 'There is currently nobody serving this Zip Code'; } else { echo '<table id="zip_table"><thead>'; echo "<tr><th>First Name</th><th>Last Name</th><th>City</th><th>State</th><th>Zip</th><th>Tel</th><th>Fax</th><th>Website</th><th>Email</th></tr></thead><tbody>"; while ($row = mysql_fetch_array($result)) { echo "<tr><td>".$row['fname']."</td><td>".$row['lname']."</td><td>".$row['city']."</td><td>".$row['state']."</td><td>".$row['zip']."</td><td>".$row['tel']."</td><td>".$row['fax']."</td><td>".$row['website']."</td><td>".$row['email']."</td></tr>"; } echo '</tbody></table>'; } ?></td>

    I'm hoping someone can help me rebuild the index, or query, or whatever.

    Thanks in advance

    PS - The only thing I can't find anywhere on the MySQL page(?) is <table id="zip_table"> if it matters.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    please try this --

    copy out the SELECT statement, replace $zip with an actual value, and submit the query directly to mysql (i.e. not via php)

    you'll have a better idea of what went wrong
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    3
    Rep Power
    0
    Thanks Rudy. Your mention about the index needing to be FullText did the trick. Sorry again for disturbing you.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    Originally Posted by TonyBlair
    Sorry again for disturbing you.
    no problem, i slept like a babe...
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo