trying to code simple search

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
mdrs
Forum Newbie
Posts: 5
Joined: Fri Aug 28, 2009 4:54 pm

trying to code simple search

Post by mdrs »

first off, great site. i've answered a lot of questions without even having to register by reading old posts.

okay, my problem is simple. i'm trying to code a simple search of a database called "users". i can connect just fine but when the code gets to the select statement, it seems to run into a wall. i can't figure out what's going on and was hoping someone could give me a hint.

Code: Select all

 
<?php
 
$first_name = $_POST['firstname'];
$last_name = $_POST['lastname'];
 
$dbc = mysqli_connect('OMITTED', 'a5753424_test', 'OMITTED','a5753424_test')
    or die('error connecting to db');
    
$query = "SELECT * FROM users WHERE first_name LIKE %$first_name% OR last_name LIKE %$last_name%";
 
$result = mysqli_query($dbc, $query)
        or die('error querying db');
 
while($row = mysql_fetch_array($result)){ 
    $search_first = $row['first_name']; 
    $search_last = $row['last_name']; 
    
    echo '$search_first' . '$search_last'. "<br>";
 
    } 
        
mysqli_close($dbc);
 
?>
 
i don't want this done for me, but i am beyond what i can solve by banging my head off the keyboard. near as i can figure, it has to be with my select statement because i know that the connection info is good for the db. but i can't for the life of me catch what i'm missing here. any help would be appreciated.
sousousou
Forum Commoner
Posts: 29
Joined: Fri Aug 28, 2009 1:10 pm

Re: trying to code simple search

Post by sousousou »

The % are jokers. So isnt the query searching for lastname with something in front of it? Same goes for last name. This way nothing will come up.
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: trying to code simple search

Post by mikemike »

You need to put single quotes around your values, otherwise MySQL assumes you mean column names. So:

Code: Select all

SELECT * FROM users WHERE first_name LIKE %$first_name% OR last_name LIKE %$last_name%
Should be:

Code: Select all

$query = "SELECT * FROM users WHERE first_name LIKE '%".$first_name."%' OR last_name LIKE '%".$last_name."%'";
Notice how I've escaped the string too, always good practice.

Also, depending on your needs, you might prefer a MATCH AGAINST (FULLTEXT) search rather than a LIKE search, it's much faster, more accurate and let's you order by relevancy.

Plenty on FULLTEXT searches here: http://dev.mysql.com/doc/refman/4.1/en/ ... earch.html
mdrs
Forum Newbie
Posts: 5
Joined: Fri Aug 28, 2009 4:54 pm

Re: trying to code simple search

Post by mdrs »

thanks for the replies, guys.

upon using that statement, i get the error stating that "mysql_fetch_array(): supplied argument is not a valid MySQL result resource", which leads me again back to my SELECT statement, does it not? is the problem with my SELECT statement?

also, i did check out the MATCH AGAINST statement, but it says that you have to use a literal string, not a variable name, so how would you use this without hard coding what you're searching for which i KNOW isn't how this is supposed to be used. i tried to find a decent tutorial about that but most seem to be written for people who wouldn't need tutorials.
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: trying to code simple search

Post by susrisha »

Code: Select all

 
$result = mysqli_query($dbc, $query)
        or die('error querying db');
 
while($row = mysql_fetch_array($result)){ //check this once may be mysqli_fetch_array()
    $search_first = $row['first_name'];
    $search_last = $row['last_name'];
   
    echo '$search_first' . '$search_last'. "<br>";
 
    }
       
mysqli_close($dbc);
 
 
you are querying through mysqli and fetching with mysql.
mdrs
Forum Newbie
Posts: 5
Joined: Fri Aug 28, 2009 4:54 pm

Re: trying to code simple search

Post by mdrs »

susrisha wrote:

Code: Select all

 
$result = mysqli_query($dbc, $query)
        or die('error querying db');
 
while($row = mysql_fetch_array($result)){ //check this once may be mysqli_fetch_array()
    $search_first = $row['first_name'];
    $search_last = $row['last_name'];
   
    echo '$search_first' . '$search_last'. "<br>";
 
    }
       
mysqli_close($dbc);
 
 
you are querying through mysqli and fetching with mysql.

oh my GOD, you're right susrisha. that was creatively stupid of me. thanks for your help, everyone.
mdrs
Forum Newbie
Posts: 5
Joined: Fri Aug 28, 2009 4:54 pm

Re: trying to code simple search

Post by mdrs »

morbid wrote:try to find it here.they got good resources.

http://www.phpscriptor.com
very cool site. thanks a lot.
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: trying to code simple search

Post by mikemike »

mdrs wrote:also, i did check out the MATCH AGAINST statement, but it says that you have to use a literal string, not a variable name, so how would you use this without hard coding what you're searching for which i KNOW isn't how this is supposed to be used. i tried to find a decent tutorial about that but most seem to be written for people who wouldn't need tutorials.
MySQL will see it as a literal string if you use a PHP variable, as the variable is placed into the string (as a string itself) before it's sent to MySQL, so if you have:

Code: Select all

 
$fields = 'fielda,fieldb,fieldc';
$user_id = 8;
$query = "SELECT ".$fields" FROM tables WHERE user_id = ".$user_id;
What is actually being sent to MySQL is:

Code: Select all

SELECT fielda,fieldb,fieldc FROM TABLES WHERE user_id = 8
mdrs
Forum Newbie
Posts: 5
Joined: Fri Aug 28, 2009 4:54 pm

Re: trying to code simple search

Post by mdrs »

mikemike wrote:
mdrs wrote:also, i did check out the MATCH AGAINST statement, but it says that you have to use a literal string, not a variable name, so how would you use this without hard coding what you're searching for which i KNOW isn't how this is supposed to be used. i tried to find a decent tutorial about that but most seem to be written for people who wouldn't need tutorials.
MySQL will see it as a literal string if you use a PHP variable, as the variable is placed into the string (as a string itself) before it's sent to MySQL, so if you have:

Code: Select all

 
$fields = 'fielda,fieldb,fieldc';
$user_id = 8;
$query = "SELECT ".$fields" FROM tables WHERE user_id = ".$user_id;
What is actually being sent to MySQL is:

Code: Select all

SELECT fielda,fieldb,fieldc FROM TABLES WHERE user_id = 8
that looks like you could use variables, then. at worst, you'd have to have one variable in the function at at time, right?
Post Reply