Page 1 of 1

trying to code simple search

Posted: Fri Aug 28, 2009 5:02 pm
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.

Re: trying to code simple search

Posted: Fri Aug 28, 2009 5:28 pm
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.

Re: trying to code simple search

Posted: Fri Aug 28, 2009 6:16 pm
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

Re: trying to code simple search

Posted: Fri Aug 28, 2009 9:09 pm
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.

Re: trying to code simple search

Posted: Fri Aug 28, 2009 9:19 pm
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.

Re: trying to code simple search

Posted: Fri Aug 28, 2009 9:28 pm
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.

Re: trying to code simple search

Posted: Sat Aug 29, 2009 12:22 am
by mdrs
morbid wrote:try to find it here.they got good resources.

http://www.phpscriptor.com
very cool site. thanks a lot.

Re: trying to code simple search

Posted: Sat Aug 29, 2009 8:56 am
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

Re: trying to code simple search

Posted: Sat Aug 29, 2009 11:45 pm
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?