Re: Search function not working...

Jason Pruim wrote:

So I said in another thread that I would be asking another question about functions... So here it goes, I am attempting to write a function to search the database, which used to work just fine when I wrote it without using a function (Would that be considered static?) Now that I am attempting to rewrite my stuff so I can reuse the code, now it's not working... Here is what I used to do and it worked just fine: $qstring = "SELECT * FROM ".$table." WHERE FName like '%$search%' or LName like '%$search%' or Add1 like '%$search%' or Add2 like '%$search%' or City like '%$search%' or State like '%$search%' or Zip like '%$search%' or XCode like '%$search%'"; if ($_SESSION['search'] != NULL){

    echo "The search string is: <strong>$search</strong>.<BR>";
    $qrow[]= mysql_query($qstring) or die(mysql_error());
    $qresult = $qrow[0];
    $num_rows = mysql_num_rows($qresult);
    //display search form
        echo "
            <form action='search.php' method='GET'>
            <label>Search:
            <input type='text' name='search' id='search' />
            </label>
            <input type='submit' value='Go!' />
        </form>";

        echo <<<HTML
    <a href='index.php'>Return to database</A>
    <P>Total Records found: {$num_rows}</P>
    <A href='excelexport.php'>Export selection to excel</A>
    <form method='GET' action='edit.php'>
    <table border='1'>
        <tr>
        <th><a href='?order=a'>First Name</A></th>
        <th><A href='?order=b'>Last Name</A></th>
        <th><A href='?order=c'>Address Line 1</A></th>
        <TH><A href='?order=d'>Address Line 2</A></th>
        <TH><A href='?order=e'>City</A></th>
        <th><A href='?order=f'>State</A></th>
        <th><A href='?order=g'>Zip</A></th>
        <TH><A href='?order=h'>Code</A></th>
        <th><A href='?order=i'>ID #</A></th>
        <TH>Edit</th>
        <th>Delete</th>
    </tr>
       HTML;
    echo "Just testing: ".$_SESSION['search'];

while($qrow = mysql_fetch_assoc($qresult)) {

    //Display the search results using heredoc syntax

echo <<<HTML

        <tr>
               <td>{$qrow['FName']}</td>
        <td>{$qrow['LName']}</td>
        <td>{$qrow['Add1']}</td>
        <td>{$qrow['Add2']}</td>
        <td>{$qrow['City']}</td>
        <td>{$qrow['State']}</td>
        <td>{$qrow['Zip']}</td>
        <td>{$qrow['XCode']}</td>
        <td>{$qrow['Record']}</td>
        <td><a href='edit.php?Record={$qrow['Record']}'>Edit</a></td>
        <td><a href='delete.php?Record={$qrow['Record']}'>Delete</a></td>
    </tr>
        </form>

HTML; Now, here is what I have as a function and is not working: <?PHP $FName =""; $LName =""; $Add1 = ""; $Add2 = "";

//        $_SESSION['search'] = $_GET['search'];
    function search($searchvar, $table, $num_rows, $FName, $LName, $Add1, $Add2) {
    $qstring = "SELECT * FROM ".$table." WHERE FName like '%$searchvar%' or LName like '%$searchvar%' or Add1 like '%$searchvar%' or Add2 like '%$searchvar%' or City like '%$searchvar%' or State like '%$searchvar%' or Zip like '%$searchvar%' or XCode like '%$searchvar%'";
        $qrow[]= mysql_query($qstring) or die(mysql_error());
        $qresult = $qrow[0];
        $num_rows = mysql_num_rows($qresult);
    //while($qrow = mysql_fetch_assoc($qresult)) {              $FName = $qrow['FName'];
    $LName = $qrow['LName'];
    $Add1 = $qrow['Add1'];
    $Add2 = $qrow['Add2'];
    
        return;
        }
    ?>

And what happens, is first of all it displays the entire database on the search page, which I'm kind of okay with... But when you search, it updates the variables, and echo's out the right search term, but it doesn't update the database to only show the search results... I think it might be tied to it displaying the entire database at page load... But I'm not sure.. Anyone have an idea of what I did wrong other then everything? :) Oh, and as far as calling the function I do this: search($searchvar, $table, $num_rows, $FName, $LName, $Add1, $Add2); -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com japruim@raoset.com

Ok, here would be my rendition of this function. <?php function search($searchvar, $table) { // Since we want to ensure that we have good data before we run our // query, we want to clear our search data before we use it $clean_searchvar = mysql_real_escape_string($searchvar); // Build our SQL statement $SQL = "SELECT * FROM {$table} WHERE FName LIKE '%{$clean_searchvar}%' OR LName LIKE '%{$clean_searchvar}%' OR Add1 LIKE '%{$clean_searchvar}%' OR Add2 LIKE '%{$clean_searchvar}%' OR City LIKE '%{$clean_searchvar}%' OR State LIKE '%{$clean_searchvar}%' OR Zip LIKE '%{$clean_searchvar}%' OR XCode LIKE '%{$clean_searchvar}%'"; // Process SQL statement, continue on success or display error $res = mysql_query($SQL) or die(mysql_error()); // Initialize array to contain data set from DB $results = array(); // Check to make sure their is a result set if ( mysql_num_rows($res) > 0 ) { // Loop through result set while ( $row = mysql_fetch_assoc($res) ) { // Place returned row of data into our results array

       			$results[] = $row;

} } // Return result set of data, or blank array return $results; } // $_SESSION['search'] = $_GET['search']; $searchvar = 'something you want to search for'; // Call function $dataSet = search($searchvar, 'myTable'); // Get the number of results returned $num_rows = count($dataSet); ?> if the above code get messed up by the email, check out this link. http://www.cmsws.com/examples/php/testscripts/japruim@raoset.com/Search_function_not_working.php This should return to you all the information you need. -- Jim Lucas "Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them." Twelfth Night, Act II, Scene V by William Shakespeare