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
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?