RE: Not getting matching records
I have a TBL of users and I have created a search screen where you can type in first or last name and it will retrieve the appropriate records. Here is the statement: Select * from STUDENTS WHERE FName LIKE '% .$_REQUEST['searchit']. %' OR LName LIKE '% . $_REQUEST['searchit']. %' OR idStudent LIKE '% .$_REQUEST['searchit']. %' I'm assuming you're using PHP with MySQL because of the reference to $_REQUEST and using . To concatenate. It could be a case, whitespace or data type issue. Try using: SELECT * FROM students WHERE lower( trim( FName ) ) LIKE '% . strtolower( trim( $_REQUEST['searchit'] ) ) . %' OR lower( trim( LName ) ) LIKE '% . strtolower( trim( $_REQUEST['searchit'] ) ) . %' OR lower( trim( idStudent ) ) LIKE '%. strtolower( trim( $_REQUEST['searchit'] ) ) . %' If your idStudent field is a numeric data type then you need to do an equality comparision rather than a like... SELECT * FROM students WHERE lower( trim( FName ) ) LIKE '% . strtolower( trim( $_REQUEST['searchit'] ) ) . %' OR lower( trim( LName ) ) LIKE '% . strtolower( trim( $_REQUEST['searchit'] ) ) . %' OR idStudent = . $_REQUEST['searchit'] If this doesn't work please post the table structure (describe tablename) and your PHP script. Cheers Dean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Not getting matching records
Thanks - but did not work...here is the information you
requested. In looking at this please remember that, for
example, there are lots of smith records in the data, but
only one with the first name allenand there are lots of
sarah records in the data but only one with the last name
ellis...it will find sarah ellis, but not allen smith. But if
I run that join statement listed in my original post, it will
find both
Anyway following is the info...
TABLE STRUCTURE:
idStudent int PRIMARYKEY AUTO INC
FName varchar(50)
LName varchar(50)
Address varchar(50)
City varchar(50)
State char(2)
Zip varchar(10)
Phone varchar(20)
Email varchar(255)
DOB datetime
Gender int
Deleted int
PHP SCRIPT:
/* Performing SQL query */
$GETSTUDENT_query = Select * from STUDENTS WHERE
lower(Trim(FName)) LIKE '%
.strtolower(trim($_REQUEST['searchit'])). %' OR lower(trim(LName)) LIKE
'%
.strtolower(trim($_REQUEST['searchit'])). %' OR idStudent LIKE '%
.$_REQUEST['searchit']. %';
$GETSTUDENT_result = mysql_query($GETSTUDENT_query) or
die(Query failed :
. mysql_error());
$GETSTUDENT_num_results = mysql_num_rows($GETSTUDENT_result);
if ($GETSTUDENT_num_results == 0)
{
echo table class='manage_retro';
echo tr;
echo td;
echo No results found;
echo /td;
echo /tr;
} else {
echo WRITE THE DATA TO THE SCREEN
}
OK first off your query shouldn't do a LIKE on the idStudent column because
it's a numeric column
So you need to try:
$GETSTUDENT_query =
Select * from STUDENTS
WHERE
lower(Trim(FName)) LIKE '%.strtolower(trim($_REQUEST['searchit'])).%'
OR
lower(trim(LName)) LIKE '%.strtolower(trim($_REQUEST['searchit'])).%'
OR
idStudent = .$_REQUEST['searchit']
If you want to be a little more advanced you can distinguish between a name
and ID search...
if( is_numeric( trim($_REQUEST['searchit']) ) ) {
$GETSTUDENT_query =
Select * from STUDENTS
WHERE
idStudent = .$_REQUEST['searchit'] ;
} else {
$GETSTUDENT_query =
Select * from STUDENTS
WHERE
lower(Trim(FName)) LIKE
'%.strtolower(trim($_REQUEST['searchit'])).%'
OR
lower(trim(LName)) LIKE '%.strtolower(trim($_REQUEST['searchit'])).%
;
} // end if
Can you post the html of your form...
Does MySQL report an error?
What version of MySQL and PHP are you using.
If you are convinced that the data are correct you could do a check to see
if there are any invisible non whitespace chars in the field values...
SELECT Fname, length(fname), Lname, length(fname) from students ;
Also a shot in the dark here but the table 'may' be corrupt so try an
'optimize table'
http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.html
or
Myisamchk
http://dev.mysql.com/doc/mysql/en/myisamchk_syntax.html
Cheers
Dean
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Not getting matching records
OK getting somewhere now...I have modified the code to the
more advanced option that you suggested...I am now getting a
return when I search by the ID but still not when I search by
the FName or LName:
Here is the html:
snip
* No, no error reported
* What command do I type to find out the version of PHP and
MYSQL? I had my network guy install that stuff and he is not
around anymore :)
* Tried the length thing and each field returns the proper length
* Am looking at the myisamchk and optimize commands now...
OK, this is a PHP problem not a MySQL problem
If you type 'smith' in your form and press enter then you'll not get any
results where as if you type 'smith' in your form and ***CLICK*** the Submit
button you'll get the results!
It may be a good idea to turn on all PHP error reporting
error_reporting( E_ALL ) ;
And to check for a value that has been POSTed, do something similar to
if( $_SERVER['REQUEST_METHOD'] == POST AND isset( $POST['searchit'] ) ) }
$HITBUTTON = 'YES' ;
}
There are plenty of tutorials out there for handling forms and the logic
required for form processing in PHP, Google is your friend but you can also
try www.zend.com and www.phpbuilder.com
As a general rule of thumb, if you're not getting the expected results
then...
1. echo out the completed SQL statement and paste it in to the command line
mysql utility (or use MySQLcc http://dev.mysql.com/downloads/mysqlcc.html).
If you get the expected results then the problem is in your script logic.
2. Sprinkle your PHP scripts with DEBUG/echo statements
?php
define( '_DEBUG_', TRUE ) ;
.
.
.
Code
.
.
if( _DEBUG ) {
echo Value of the variable MY_SQL_QUERY is .$MY_SQL_QUERY ;
}
3. Get something like the ZEND Studio IDE so you can watch variable values
and step through a script a line at a time.
Hope this helps
Dean
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Not getting matching records
For the benefit of the list... Marty and I corresponded off list to make sure his PHP wasn't at fault, it wasn't! Turns out the problem was a corrupt table or index that 'optimize table tablename' fixed up. Cheers Dean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not getting matching records
Has anyone seen a post on this issue? If not, can anyone offer any advice? I have a TBL of users and I have created a search screen where you can type in first or last name and it will retrieve the appropriate records. Here is the statement: Select * from STUDENTS WHERE FName LIKE '% .$_REQUEST['searchit']. %' OR LName LIKE '% .$_REQUEST['searchit']. %' OR idStudent LIKE '% .$_REQUEST['searchit']. %' The statement works great for the most part. However, it is a bit sporadic. For example, I type in my name (because I know I am in the DB and it will NOT pull back any results. I even ran this command from the UNIX box directly and it will not work. So I have another page which pulls ALL records from another TBL and joins the USER TBL and I AM LISTED!! Here is the statement for that page: Select * FROM CAMPREG INNER JOIN STUDENTS on STUDENTS.idStudent = CAMPREG.idStudent ORDER BY LName; Again, the first one will NOT retrieve my name..the second one will list me in the master list. Help :-)
