Page 1 of 1

Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 1:47 pm
by simonmlewis

Code: Select all

include "dbconn.php";
$town = $_POST['town'];
$area = $_POST['area'];
$result = mysql_query ("SELECT * FROM lincssolicitor WHERE town = '$town' AND area LIKE '%$area%'");
if (mysql_num_rows($result)==0) { echo "Sorry, we have no registered solicitors meeting your criteria.  Please try a different area of law, or a different town."; }
else {
while ($row = mysql_fetch_object($result)){
        echo "<div class='librarybox' style='padding-left: 10px'>
        <div class='faq_title'>$row->company</div>
<table width='670' border='0' cellspacing='0' cellpadding='0' class='bodytext'>
  <tr>
    <td valign='top' width='95'>Name:</td>
    <td>$row->name</td>
    <td>Address:</td>
    <td rowspan='3' valign='top' width='275'>$row->address</td>
  </tr>
  <tr>
    <td valign='top'>Email:</td>
    <td><a href='mailto:$row->email'>$row->email</a></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td valign='top'>Telephone:</td>
    <td>$row->telephone</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td valign='top'>Website:</td>
    <td><a href='http://$row->website'>$row->website</a></td>
    <td>Town:</td>
    <td>$row->town</td>
  </tr>
  <tr>
    <td valign='top'>Area of Work </td>
    <td>$row->area</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  </table>
</div>";
        }}
    mysql_free_result($result);
    mysql_close($sqlconn);
This is the query.
I am posting $town to the query (there's always content), but $area is something empty, as they often just want to find the results based on a TOWN only.

Problem is, when they leave $area empty, it produces nothing and just shows everything in the databse.

What am I doing wrong?

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 2:03 pm
by McInfo
When $area is an empty string, the clause in your query looks like

Code: Select all

area = '%%'
, meaning "where area is anything".

If you want to run the query only when $area is not an empty string, use a condition like

Code: Select all

if ($area != '')
Also, sanitize user input. At least use mysql_real_escape_string().
simonmlewis wrote:it produces nothing and just shows everything in the databse
Those two statements are contradictory.

Edit: This post was recovered from search engine cache.

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 2:08 pm
by simonmlewis
But won't that mean running two queries?

One if it is empty, and one if it is NOT empty?

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 2:17 pm
by McInfo
Sorry, I edited my last post before I saw your reply.

To answer your question ambiguously: maybe.

Are all of the solicitors in your table from the same town? The town clause in your query should limit the results even if the area clause matches all of the records because the query uses "AND" instead of "OR". The only way I can see that all of the records would be returned is if all of the solicitors are from the same town.

Edit: This post was recovered from search engine cache.

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 2:20 pm
by simonmlewis
No - there are multiple towns - and multiple "areas".

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 2:23 pm
by simonmlewis
The page has two queries.
The first checks if $area has been given any content. If it hasn't, it produces EVERYTHING in the table.

When I run the search, if I enter "employment" and "London", for instance, it loads solicitors in London that deal with Employment.

If I select just London and do not enter anything in the $area field, it then produces EVERYTHING in the table.

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 2:27 pm
by McInfo
It might help if you post the whole script.

Edit: This post was recovered from search engine cache.

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 2:30 pm
by simonmlewis

Code: Select all

<?php
include "dbconn.php";
echo "
If you wish to search a particular area of law, along with a town of a registered solicitor, please use the option below.<br/><br/>
<div class='faq_title'>Search</div>
<form method='post' action='index.php?page=findsol&title=find a solicitor'>
Enter area of law:<br/>
<input type='text' name='area'>&nbsp;&nbsp;
<select name='town'>";
            
            $resultarea = mysql_query ("SELECT DISTINCT town FROM solicitor ORDER BY town ASC");
            while ($rowarea = mysql_fetch_object($resultarea))
      {
            echo "<option value='$rowarea->town'>$rowarea->town</option>";
            }
            mysql_free_result($resultarea);
            
            echo "</select>&nbsp;&nbsp;<input type='submit' value='Search'></form><br/>";
            
$town = $_POST['town'];
$area = $_POST['area'];
 
if ($area == NULL) {
$result = mysql_query ("SELECT * FROM solicitor");
while ($row = mysql_fetch_object($result)){
        echo "<div class='librarybox' style='padding-left: 10px'>
        <div class='faq_title'>$row->company</div>
<table width='670' border='0' cellspacing='0' cellpadding='0' class='bodytext'>
  <tr>
    <td valign='top' width='95'>Name:</td>
    <td>$row->name</td>
    <td>Address:</td>
    <td rowspan='3' valign='top' width='275'>$row->address</td>
  </tr>
  <tr>
    <td valign='top'>Email:</td>
    <td><a href='mailto:$row->email'>$row->email</a></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td valign='top'>Telephone:</td>
    <td>$row->telephone</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td valign='top'>Website:</td>
    <td><a href='http://$row->website'>$row->website</a></td>
    <td>Town:</td>
    <td>$row->town</td>
  </tr>
  <tr>
    <td valign='top'>Area of Work </td>
    <td>$row->area</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  </table>
</div>";
        }
    mysql_free_result($result);
    }
 
elseif ($area <> NULL) {
$result = mysql_query ("SELECT * FROM solicitor WHERE town = '$town' AND area LIKE '%$area%'");
if (mysql_num_rows($result)==0) { echo "Sorry, we have no registered solicitors meeting your criteria.  Please try a different area of law, or a different town."; }
else {
while ($row = mysql_fetch_object($result)){
        echo "<div class='librarybox' style='padding-left: 10px'>
        <div class='faq_title'>$row->company</div>
<table width='670' border='0' cellspacing='0' cellpadding='0' class='bodytext'>
  <tr>
    <td valign='top' width='95'>Name:</td>
    <td>$row->name</td>
    <td>Address:</td>
    <td rowspan='3' valign='top' width='275'>$row->address</td>
  </tr>
  <tr>
    <td valign='top'>Email:</td>
    <td><a href='mailto:$row->email'>$row->email</a></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td valign='top'>Telephone:</td>
    <td>$row->telephone</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td valign='top'>Website:</td>
    <td><a href='http://$row->website'>$row->website</a></td>
    <td>Town:</td>
    <td>$row->town</td>
  </tr>
  <tr>
    <td valign='top'>Area of Work </td>
    <td>$row->area</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  </table>
</div>";
        }}
    mysql_free_result($result);
    }
 
    mysql_close($sqlconn);
    ?>

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 2:52 pm
by McInfo
simonmlewis wrote:If I select just London and do not enter anything in the $area field, it then produces EVERYTHING in the table.
It's doing what it was told to do (Lines 23-24):

Code: Select all

if ($area == NULL) {
    $result = mysql_query ("SELECT * FROM solicitor");
If you don't want it to return everything, you need to add a WHERE clause to the query. Also add a PHP condition to make sure $town is not an empty string. And remember to sanitize user input.

Edit: This post was recovered from search engine cache.

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 2:54 pm
by simonmlewis
If you would kindly read back to what I said before this statement, you will see why that particular area of code is written.

Part one is to produce 'the lot'.
Part two is the search query.

The search query is the part in question here.

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 3:11 pm
by McInfo
The last script you posted says, "If area is an empty string, show all records from the table; otherwise, show records that match town and area." What do you want it to say?

Edit: This post was recovered from search engine cache.

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 3:16 pm
by simonmlewis
No...

Code: Select all

elseif ($area <> NULL) {
In other word, if someone has decided they want to search, and $area has something in it...then....

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 3:17 pm
by simonmlewis
Oh - I see your point....actually. Let me try something then respond again.

Re: Why won't this SQL query work? It's a simple one.

Posted: Mon Oct 12, 2009 3:19 pm
by simonmlewis
Sorted. It was daft - as you say, my last statement (although you said it asks if it is empty) asked if Area was not empty.

I altered it to 'town' and it is fine.

Thanks. Often the simplest problems are just unseen!!!