Menu
Community /Pin to ProfileBookmark
@NogDogOct 27.2005 — #@NogDogOct 28.2005 — #@NogDogOct 28.2005 — #Not tested, so no guarantees:
@NogDogOct 28.2005 — #Give this a shot (said with a tone of uncertainty):
@NogDogOct 31.2005 — #Tried this test on a DB on my PC, seemed to work pretty well. Just posting here for you to see what I'm trying in a complete context.
@NogDogOct 16.2013 — #Instead of doing the foreach() loop, just pick out the elements you want to display in whatever format you want.
PS: If you wrap your PHP code in [noparse]@NogDogOct 16.2013 — #Right, instead of doing the foreach, just explicitly output those elements that you want:
@NogDogOct 16.2013 — #Depending on what you want to do and how the tables are related, you probably either want to do a JOIN or a UNION .
PS: Don't be offended by the "let me google that for you" links, I just enjoy using that from time to time. ?@NogDogOct 16.2013 — #Without knowing exactly what the functional requirement is, I have no way of knowing which is appropriate. Joins are at the heart of the "relational" part of "relational databases". Union is a convenient way to query two separate tables for the same sort of data (which can sometimes be an indicator that your database design is not well normalized). I was hoping to spur you on to reading some of the tutorials so that you understand not just what to do but also why, as opposed to just writing code for you. @NogDogOct 16.2013 — #Use [url=http://php.net/mysql_num_rows]mysql_num_rows[/url] () in an if() statement to test for a result of 0.@NogDogOct 23.2013 — #Let's just take a (very) basic example, and see if you can figure out how/where to use it in your application:
search string in mysql
Can someone provide me a sample code how I can have a “search” field search for any keyword that matches my records in my database.
I have a list of agencies we fund, they have a 25 word description column. If someone searches for any word and it matches that of the “description” column list records by ID desc.
Is this possible to do a simple search query? find word and/or and list records, otherwise return sorry, no records showing that keyword.
Thanks
Sign in
to post a comment35 Comments(s) ↴
0
@NogDogOct 27.2005 — #[code=php]
$search = $_POST['search_string'];
$query = "SELECT * FROM table_name WHERE description LIKE '%$search%'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
# echo columns of interest in desired sequence and format
}
[/code]0
@NogDogOct 28.2005 — #[code=php]
$query = "SELECT * FROM table_name WHERE description LIKE '%$search%' OR name LIKE '%$search%'";
[/code]0
@NogDogOct 28.2005 — #Not tested, so no guarantees:[code=php]
# convert to upper case, trim it, and replace spaces with "|":
$search = strtoupper(preg_replace('/s+/', '|', trim($_POST['search_string'])));
# create a MySQL REGEXP for the search:
$regexp = "REGEXP '^.*($search).*$'";
$query = "SELECT * FROM table_name WHERE UPPER(description) $regexp " .
"OR UPPER(name) $regexp";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
# echo columns of interest in desired sequence and format
}
[/code]0
@NogDogOct 28.2005 — #Give this a shot (said with a tone of uncertainty):[code=php]
$regexp = "REGEXP '^.*[[:<:]]($search)[[:>:]].*$'";
[/code]0
@NogDogOct 31.2005 — #Tried this test on a DB on my PC, seemed to work pretty well. Just posting here for you to see what I'm trying in a complete context.[code=php]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html lang='en'>
<head>
<META HTTP-EQUIV='Content-Type' CONTENT='text/html; charset=ISO-8859-1'>
<title>Page title</title>
<style type="text/css">
table {
border-collapse: collapse;
border: solid 1px black;
}
td {
padding: 2px 6px;
border: solid 1px black;
}
</style>
</head>
<body>
<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post'>
<p>Search for: <input type='text' name='search' size='20' maxlength='64'></p>
<p><input type='submit' value='Search'></p>
</form>
<?php
if(isset($_POST['search']))
{
$connx = mysql_connect('localhost', '*****', '*****') or die("connx");
$db = mysql_select_db('test') or die(mysql_error());
# convert to upper case, trim it, and replace spaces with "|":
$search = (ini_get('magic_quotes_gpc')) ? stripslashes($_POST['search']) :
$_POST['search'];
$search = mysql_real_escape_string($search);
$search = strtoupper(preg_replace('/s+/', '|', trim($_POST['search'])));
# create a MySQL REGEXP for the search:
$regexp = "REGEXP '[[:<:]]($search)[[:>:]]'";
$query = "SELECT * FROM users WHERE UPPER(description) $regexp OR ".
"name $regexp";
$result = mysql_query($query) or die($query . " - " . mysql_error());
echo "<table>n";
while($row = mysql_fetch_assoc($result))
{
echo "<tr>";
foreach($row as $key => $value)
{
echo "<td>$value</td>";
}
echo "</tr>n";
}
}
?>
</body>
</html>
[/code]0
@NogDogOct 16.2013 — #Instead of doing the foreach() loop, just pick out the elements you want to display in whatever format you want.PS: If you wrap your PHP code in [noparse]
[code=php]...[/code][/noparse] tags (instead of "quote" tags), it will be much easier for us to read.0
@NogDogOct 16.2013 — #Right, instead of doing the foreach, just explicitly output those elements that you want:[code=php]
echo "<table>n";
while($row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td>{$row['some_field']}</td>";
echo "<td>{$row['some_other_field']}</td>";
echo "</tr>n";
}
[/code]0
@NogDogOct 16.2013 — #Depending on what you want to do and how the tables are related, you probably either want to do a PS: Don't be offended by the "let me google that for you" links, I just enjoy using that from time to time. ?
0
@NogDogOct 16.2013 — #0
@NogDogOct 16.2013 — #Use 0
@NogDogOct 23.2013 — #Let's just take a (very) basic example, and see if you can figure out how/where to use it in your application:[code=php]
$result = mysql_query($sql);
if(mysql_num_rows($result) > 0) {
// do the stuff you want to do when results are found here
}
else {
// no results were found, so handle that situation here, for example:
echo "<p>Sorry, no results matched your search.</p>";
}
[/code]