Page 1 of 1

Searching my database...

Posted: Mon Sep 04, 2006 5:21 pm
by Anarchy
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>
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]

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'>&nbsp;</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>";
}
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]

Posted: Mon Sep 04, 2006 7:08 pm
by Ollie Saunders

Posted: Mon Sep 04, 2006 7:18 pm
by RobertGonzalez

Code: Select all

SELECT * FROM `mytable` WHERE `mycolumn` LIKE '%thispartofaword%';

Posted: Tue Sep 05, 2006 12:41 am
by Chris Corbyn
Technically (though it would require a fulltext index set in your database) this is better:

Code: Select all

SELECT * FROM `mytable` WHERE 1 AND MATCH(`mycolumn`) AGAINST('thispartofawor');

Posted: Thu Sep 07, 2006 7:46 am
by Anarchy
A million thanks guys!!
I knew it was a simple thing to do, but searching for it just gave me too many too complicated results to weed out the part i was looking for...
Again, THANK YOU!!!

Cheerz!
/A.