Hi all,
I'm struggling to write an SQL query.
I'd like to search a database for either a person or an organisation.

I have an HTML form on a homepage, and this returns two values:

  • a search term
  • a search type, either 'person' or 'organisation'

code:

<form action="dir_query.php" method="post" name="qsearch" id="qsearch">
        <table>
            <tr>
            <td>Searching for...</td>
            <td><input type="text" name="dir_search" id="dir_search" /></td>
            </tr>
            <tr>
            <td>which is...</td>
            <td><select name="dir_type" id="dir_type">
					<option value="organisation">an organisation's name</option>
					<option value="person" selected>a person's name</option>
				</select>
                <input type="submit" value="submit" /></td>
            </tr>
        </table>
        </form>

So far, so straightforward.
When processed these are converted into variables, and included in the MySQL query string.

The database, a contact list, has a number of fields, including name (a person's name) and organisation.

I'm currently trying:

$sql=(SELECT $dir_search FROM 'directory' WHERE $dir_type="person" OR $dir_type="organisation")

No dice. In fact, like this, no output at all. I've got a feeling that this will involve a table and an array, but then I start to palpitate.

Can anyone help?

First let's dissect sql string you're trying to put togther:

From what it looks like, you're passing $dir_search as the value being searched for, and $dir_type as the field to search on. Of course, with out seeing your table I'm really just guessing here. But if what I think you're trying to get at (just at looking at your html form) is correct,

SELECT * FROM directory WHERE $dir_type LIKE '%$dir_search%'

You see where the asterisk is? That's where you tell the database which fields you want to return. You can name the fields to return, or use the asterisk to tell the db to return all fields in the table.

$dir_type is the name of the field to search on (person or organization - again, I'm guessing).

$dir_search is the actual value you want to run a compare on. I use LIKE instead of = (which is common on db searches) because it will return a partial string match, and is usually case-insensitve. The percentage signs are wildcards.

And a couple more notes - in a sql query, values are enclosed by single apostrophes. You don't have to enclose the table or field names, but if you want to, use the ` character at the top-left of your keyboard. Mostly this character comes in handy for returning aliases for field names, but that's for the next lesson...

Oh wait, one more thing. Enclose the whole string we've just build in double-quotes in the php, so then our final example becomes:

$sql = "SELECT * FROM `directory` WHERE $dir_type LIKE '%$dir_search%'";
$get_data = mysql_query($sql);

and so on...

$sql=(SELECT $dir_search FROM 'directory' WHERE $dir_type="person" OR $dir_type="organisation")

the syntax you wrote is very wrong....
Data base cloumn names are not start with $ like $dir_search...or if it is a value post form a form ,then we have write like $_POST...
so modify your query as per:

$sql = "SELECT * FROM `directory` WHERE $dir_type LIKE '%$dir_search%'";
        $get_data = mysql_query($sql);

mentioned by mcd at the above post....

And that is just what I needed: a better undertsnading, and the answer.
Thanks Shanti and mcd!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.