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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

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

Post 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.
Last edited by McInfo on Thu Jun 17, 2010 12:38 pm, edited 1 time in total.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

But won't that mean running two queries?

One if it is empty, and one if it is NOT empty?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

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

Post 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.
Last edited by McInfo on Thu Jun 17, 2010 12:39 pm, edited 1 time in total.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

No - there are multiple towns - and multiple "areas".
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

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

Post by McInfo »

It might help if you post the whole script.

Edit: This post was recovered from search engine cache.
Last edited by McInfo on Thu Jun 17, 2010 12:39 pm, edited 1 time in total.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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);
    ?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

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

Post 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.
Last edited by McInfo on Thu Jun 17, 2010 12:40 pm, edited 1 time in total.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

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

Post 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.
Last edited by McInfo on Thu Jun 17, 2010 12:40 pm, edited 1 time in total.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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....
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

Oh - I see your point....actually. Let me try something then respond again.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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!!!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply