DaniWeb Forum Index > Web Development > PHP

simple sql query

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jun 2008

Posts: 13

Reputation: AliHurworth is an unknown quantity at this point

Solved Threads: 0

AliHurworth AliHurworth is offline

Offline

Newbie Poster

simple sql query

Aug 5th, 2008

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:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
<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>
<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:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$sql=(SELECT $dir_search FROM 'directory' WHERE $dir_type="person" OR $dir_type="organisation")
$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?

•

Join Date: Jan 2007

Posts: 16

Reputation: mcd is an unknown quantity at this point

Solved Threads: 6

mcd

Offline

Newbie Poster

Re: simple sql query

Aug 5th, 2008

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,

Help with Code Tags

php Syntax (Toggle Plain Text)

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:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$sql = "SELECT * FROM `directory` WHERE $dir_type LIKE '%$dir_search%'";
$get_data = mysql_query($sql);
$sql = "SELECT * FROM `directory` WHERE $dir_type LIKE '%$dir_search%'";
$get_data = mysql_query($sql);

and so on...

Last edited by mcd; Aug 5th, 2008 at 10:34 pm.

•

Join Date: Jul 2008

Posts: 1,072

Reputation: Shanti Chepuru is on a distinguished road

Solved Threads: 98

Shanti Chepuru Shanti Chepuru is offline

Offline

Veteran Poster

Re: simple sql query

Aug 6th, 2008

•

Originally Posted by AliHurworth

Help with Code Tags

php Syntax (Toggle Plain Text)
$sql=(SELECT $dir_search FROM 'directory' WHERE $dir_type="person" OR $dir_type="organisation")
$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['var']...
so modify your query as per:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
        $sql = "SELECT * FROM `directory` WHERE $dir_type LIKE '%$dir_search%'";
        $get_data = mysql_query($sql);
        $sql = "SELECT * FROM `directory` WHERE $dir_type LIKE '%$dir_search%'";
        $get_data = mysql_query($sql);

mentioned by mcd at the above post....