Searching my database...
Posted: Mon Sep 04, 2006 5:21 pm
feyd | Please use
The database connection is already taken care of with an include, so this code works fine, except for that the way it works now, the 'company' search will only look for exact matches and I want to make it match substrings and then output the whole row where these substrings are found...[/syntax]
Is there anyone out there that knows of a good way to do this?
Please advice!
Thanks in advance!
/A.
Edit: Sorry about that... I'll better myself! Thanx for fixing it!
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
[b]First of all,[/b] I'm very new to all of this (PHP & MySQL) so please bare with me if this is a lame question or if my code looks funny/ineffective...
I have a very basic database table with not many entries, it will grow, but not by much I think, maybe 10 rows a month or so.
What I want to fix is a search function... I have a form text field where the user types in a search criteria, then i have 3 radiobuttons that work together with the input textbox, so if the user selects the 'User Name' radiobutton the script will output all rows in the table where the 'user_name' matches (also if no input has been made in the textbox, it will search for the user that is logged in by getting his user name from a cookie). This works fine, I have no problem with this part. Also I have a radiobutton for searching for a specific date 'YYYY-MM-DD' format which works like a charm aswell... But then we come to the tricky bit...
The last search function is to search for a company name... The user inputs, for instance, "Soft", then he selects the 'Company' radiobutton and clicks the 'Search' submit-button, now I want the script to fetch all rows where the word "Soft" matches the 'company' field. BUT I want it to match all strings that contains the word "Soft", so all the following companies would be a match: "Softwarehouse", "Microsoft", "The Soft Company", etc... You get the picture...
It's a MySQL database, and the 'company' field type is 'tinytext'...
[b]The Form: [/b]
[i](Nevermind the coding here, the form is printed in the script with the echo function, but i cut the unneccessary code out, you prolly only need to know the name and value info anyway... This part works fine so...)[/i]
[syntax="html"]<form action='$php_self' method='post'>
<input type='text' name='search_string'>
<input type='radio' name='criteria' value='booking_date' checked='checked'>
<input type='radio' name='criteria' value='company'>
<input type='radio' name='criteria' value='booked_by'>
<input type='submit' name='submit' value='Search'>
</form>Code: Select all
// Check if the form has been submitted
if ($_POST['submit'] == 'Search') {
// Set a variable to hold the string from the textbox
$searchfor = $_POST['search_string'];
// Check what the user wants to search for
// If he wants to search for a company...
if ($_POST['criteria'] == 'company') {
$query = "SELECT event_id, booking_date, start_time, end_time, company, user_name, date_created
FROM calendar
WHERE company = '$searchfor' ORDER BY booking_date ASC";
// If he wants to search for a specific user...
} else if ($_POST['criteria'] == 'booked_by') {
// ...then check if the textbox was empty and if it was get the active user's username from a cookie
if ($_POST['search_string'] == '') {
$user_name = strtolower($_COOKIE['user_name']);
$query = "SELECT event_id, booking_date, start_time, end_time, company, user_name, date_created
FROM calendar
WHERE user_name = '$user_name' ORDER BY booking_date ASC";
// ...otherwise make a search for the username entered into the textbox
} else {
$query = "SELECT event_id, booking_date, start_time, end_time, company, user_name, date_created
FROM calendar
WHERE user_name = '$searchfor' ORDER BY booking_date ASC";
}
// If none of the above we guess he wants to search for a specific date, which is the default setting...
} else {
$query = "SELECT event_id, booking_date, start_time, end_time, company, user_name, date_created
FROM calendar
WHERE booking_date = '$searchfor' ORDER BY booking_date ASC";
}
// Finally take the result and print it with a while loop...
$result = mysql_query($query);
echo "<table bgcolor='#222222' border='0' width='500' cellspacing='1' cellpadding='2'>";
echo "<tr><th>Date</th><th>Time</th><th>Company</th><th>User Name</th><th>Created</th><th>Edit</th><th>Delete</th></tr><tr><td colspan='7'> </td></tr>";
while($rad=mysql_fetch_array($result)) {
echo "<tr><td>";
echo $rad['booking_date'];
echo "</td><td>";
echo $rad['start_time']."-".$rad['end_time'];
echo "</td><td>";
echo $rad['company'];
echo "</td><td>";
echo $rad['user_name'];
echo "</td><td>";
echo $rad['date_created'];
echo "</td><td>";
echo "<a href='editevent.php?event_id=".$rad['event_id']."'><img src='images/edit.gif'></a>";
echo "</td><td>";
echo "<a href='deleteevent.php?event_id=".$rad['event_id']."'><img src='images/trash.gif'></a></td></tr>";
}
echo "</table>";
}Please advice!
Thanks in advance!
/A.
Edit: Sorry about that... I'll better myself! Thanx for fixing it!
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]