www.webdeveloper.com
Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 36

Thread: search string in mysql

  1. #16
    Join Date
    Oct 2013
    Posts
    28
    Hi, I have used the above code created by NogDog (and below):

    <!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>

    Add my database and its working fine. Anyway I want to change some of the options. Right now it say:
    echo "<td>$value</td>";
    }

    Is it possible to change it so it just take out the infomations from the string that I want show? Means right now it shows everything in the string, but I only want the search to show the "Name" and "Description" from the string and not all the other info that is saved in the string like "adresse","phonenumber", "staff" and so on....


    Is the above codes the right to use og should I use something else?

    Please advice - THANKS

  2. #17
    Join Date
    Oct 2013
    Posts
    28
    The above code from NogDog work more than perfect. Anyway when you do a search using the codes given by NogDog it show the complete string from the database as the result.

    Is it somehow possible to choose what part to be showed? Like If I only want to show the column “name” and “description” and want to hide the column “address”, “phonenumber”, “staff” and so one….

    Hope someone can help or come with some other suggestions for creating this seach.

    THANKS

  3. #18
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,604
    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 [php]...[/php] tags (instead of "quote" tags), it will be much easier for us to read.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #19
    Join Date
    Oct 2013
    Posts
    28
    Hi NogDog,

    Thanks for the fast reply. Im not sure what you mean. Can you help me write the code correct. Right now it say:

    PHP Code:
      echo "<table>\n"
      while(
    $row mysql_fetch_assoc($result)) 
      { 
        echo 
    "<tr>"
        foreach(
    $row as $key => $value
        { 
          echo 
    "<td>$value</td>"
        } 
        echo 
    "</tr>\n"
      } 
    I think you want me to change something here:'

    PHP Code:
        foreach($row as $key => $value

    But im not sure what to write. Can you help me?

  5. #20
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,604
    Right, instead of doing the foreach, just explicitly output those elements that you want:
    PHP Code:
    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"

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #21
    Join Date
    Oct 2013
    Posts
    28
    Hi NogDog,

    This is perfect - WORKING. Thanks you so much

    Another question that I hope you will help me with. Is it possible to seach in to different tables. Right now the script just search in one table:
    PHP Code:
      # create a MySQL REGEXP for the search: 
      
    $regexp "REGEXP '[[:<:]]($search)[[:>:]]'"
      
    $query "SELECT * FROM `users` WHERE UPPER(`description`) $regexp OR "
               
    "`name` $regexp"

    The above search in the table "users" but is it possible to search in both table "users" and a table called "staff"?

    If yes, is it then also possible to search in different column i that table?

    If possible can you tell me the codes, as you just did?

    Thanks for all your help.

  7. #22
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,604
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  8. #23
    Join Date
    Oct 2013
    Posts
    28
    I See, What is the different on the join and Union?

  9. #24
    Join Date
    Oct 2013
    Posts
    28
    And, what do you suggest?

  10. #25
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,604
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  11. #26
    Join Date
    Oct 2013
    Posts
    28
    Hi Again NogDog,

    I found out that as for now I dont need to search in 2 tables.

    Anyway I would like if it was possible to create a text saying "No search result found" when the search did not find anything. How do I add that to my codes. Can you give me the codes?

    Please give me, as last time compleat code so I can copy and past it to the codes. I really hope you can help with this part

  12. #27
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,604
    Use mysql_num_rows() in an if() statement to test for a result of 0.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  13. #28
    Join Date
    Oct 2013
    Posts
    28
    hmmm, Im not good at coding so Ihave no idea of how or where to add the code. When I do like this it dont Work:
    PHP Code:
    <?php 
    if(isset($_POST['search'])) 

      
    $connx mysql_connect('localhost''cms_main'',J4(tgM2X.(~') or die("connx"); 
      
    $db mysql_select_db('cms_main') 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 `galleries` WHERE UPPER(`keywords`) $regexp OR "
               
    "`name` $regexp"
      
    $result mysql_query($query) or die($query " - " mysql_error()); 
      
      
    mysql_num_rows() in an if() statement to test for a result of 0.
      
    echo "<table>\n"
    while(
    $row mysql_fetch_assoc($result)) 


        echo 
    "<tr>"
        echo 
    "<td><img src=../thumbs/photos/{$row['folder']}/{$row['date']}-{$row['num']}/{$row['thumbimage']} border=1></td>";
        echo 
    "<td>{$row['name']}</td>"
        echo 
    "<td>{$row['date']}</td>"
        echo 
    "<td><a href=../members/viewgallery.php?id={$row['id']} target=blank>View Photoset</a></td>"
        echo 
    "</tr>\n"
      }  

    ?>
    Can you please let me know the codes where this should be add so it will Work. THANKS

  14. #29
    Join Date
    Oct 2013
    Posts
    28
    Hi NogDog,

    Cann you please help me. You told me that I could add the code:

    PHP Code:
    mysql_num_rows() in an if() statement to test for a result of 0. 
    But I dont know how/where to add the codes

    PHP Code:
    <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''xxxxxxx''xxxxxxx') or die("connx"); 
      
    $db mysql_select_db('xxxxxxx') 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 `xxxxxxx` WHERE UPPER(`xxxxxxx`) $regexp OR "
               
    "`xxxxxxx` $regexp"
      
    $result mysql_query($query) or die($query " - " mysql_error()); 
      
    echo 
    "<table>\n"
    while(
    $row mysql_fetch_assoc($result)) 


        echo 
    "<tr>"
        echo 
    "<td><img src=../thumbs/{$row['type']}/{$row['folder']}/{$row['date']}-{$row['num']}/{$row['thumbimage']} border=1></td>";
        echo 
    "<td>{$row['name']}</td>"
        echo 
    "<td>{$row['date']}</td>"
        echo 
    "<td><a href=view.php?id={$row['id']} target=blank>View</a></td>"
        echo 
    "</tr>\n"
      }  

    ?>
    Where do I add your codes?

    Maybe you can add it for me and post it so I can see it. I really hope you will help me

  15. #30
    Join Date
    Oct 2013
    Posts
    28
    Any help on this?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles