ibanja
asked on
mySql query
PHP MySql web application:
This seems simple but I can't figure out how to do it. I need to search an indexed table on an indexed column for an "exact match". Then I need to start displaying data from that point on for n more rows.
Example:
This works because it's an "inexact match" and there is at least one more match left:
mysql> select name from testbed where name like 'br%' limit 2;
+----------+
| name |
+----------+
| Bradshaw |
| Brown |
+----------+
2 rows in set (0.00 sec)
This doesn't work because it has satisfied it's match. I want to keep listing from this point for n more rows:
mysql> select name from testbed where name like 'bradshaw' limit 2;
+----------+
| name |
+----------+
| Bradshaw |
+----------+
1 row in set (0.00 sec)
This is what I want:
mysql> select name from testbed where name like 'bradshaw' (keep listing for one more);
+----------+
| name |
+----------+
| Bradshaw |
| Collins |
+----------+
1 row in set (0.00 sec)
Thank you for you help.
This seems simple but I can't figure out how to do it. I need to search an indexed table on an indexed column for an "exact match". Then I need to start displaying data from that point on for n more rows.
Example:
This works because it's an "inexact match" and there is at least one more match left:
mysql> select name from testbed where name like 'br%' limit 2;
+----------+
| name |
+----------+
| Bradshaw |
| Brown |
+----------+
2 rows in set (0.00 sec)
This doesn't work because it has satisfied it's match. I want to keep listing from this point for n more rows:
mysql> select name from testbed where name like 'bradshaw' limit 2;
+----------+
| name |
+----------+
| Bradshaw |
+----------+
1 row in set (0.00 sec)
This is what I want:
mysql> select name from testbed where name like 'bradshaw' (keep listing for one more);
+----------+
| name |
+----------+
| Bradshaw |
| Collins |
+----------+
1 row in set (0.00 sec)
Thank you for you help.
In posting that, I found a flaw or two. But, use this instead and if the user wants to use WILDCARDS and not exact matches, inform your users to place a % before and after the keywords:)
Here it is:
$query = 'SELECT name FROM table WHERE name LIKE ' . addslashes(trim($search_ph rase)) . ' ORDER BY b.date DESC LIMIT 2;
$fresult = mysql_query($query);
$total_results = mysql_num_rows($fresult);
// $search_phrase is the search phrase variable
Here it is:
$query = 'SELECT name FROM table WHERE name LIKE ' . addslashes(trim($search_ph
$fresult = mysql_query($query);
$total_results = mysql_num_rows($fresult);
// $search_phrase is the search phrase variable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
kiew,
> select name from testbed where name >= 'bradshaw' order by name limit 2;
That's it! Exactly what I was looking for.
Thanks to jeffparis as well. Sorry I didn't respond back right away. I got sidetracked with some non-programming issues.
> select name from testbed where name >= 'bradshaw' order by name limit 2;
That's it! Exactly what I was looking for.
Thanks to jeffparis as well. Sorry I didn't respond back right away. I got sidetracked with some non-programming issues.
SELECT name FROM testbed WHERE name = 'bradshaw' limit 2;
Just for an extra info, here is what I have for one of my blogs search tools:
$query = 'SELECT u.username, u.user_id, u.category, b.blog_id, b.date, b.subject, b.comments, b.views FROM table AS u LEFT JOIN table AS b ON u.user_id = b.author_id WHERE b.body LIKE "%' . addslashes(trim($phrase)) . '%" AND u.category != 0 ORDER BY b.date DESC LIMIT ' . $start . ', ' . $end;
$fresult = mysql_query($query);
$total_results = mysql_num_rows($fresult);