Search a LONGTEXT field, and display results

Question

Okay, So short and sweet. I have a field in my database that is LONGTEXT. This field usually has around 2,000 lines of text in each of these.

What I need it to do, is go through this LONGTEXT field and take out the line that has what is inputted to it. (Sorry, I am horrible at explaining things)

For example, I originally used .txt files, but decided that MySQL was a much easier way to go as I do have a little bit of experience in it. I have this code.

function searchcode($search)
{
  $count = '0';
  $lines = file('file.txt');
  foreach($lines as $line)
  {
    if(strpos($line, $search) !== false)
    {
      echo "<tr><td>" . $line . "</td></tr>";
      $count++;
    }
  }
  echo "<tr><td style='border-top: 2px solid #000;'>$search was taken a total of $count times.</td></tr>";
}


CODE FOR SEARCHING FOR: searchcode("Camera");

This will look though the .txt file I want it to and display the lines based on what $search is. I want exactly this, but with MySQL.

In this example I was searching for a camera. And it would look through the .txt file and boom, every line with the word camera in it would display. (And the count of it obviously.)

LETS MAKE THIS CRYSTAL CLEAR: I'm looking for something to search the longtext field. Then display results based on a certain word.

I already know about the LIKE syntax, but I have no actual idea on how to use it!

EXAMPLE OF WHAT I WANT

[-Database-]

ID---------Date---------Information

1 ---------3-4-2012----camera has been removed. Jacket has been removed. camera has been added.

[-MySQL Page-]

MySQL Code Here

[-Webpage-]

Camera has been removed.

Camera has been added.

Ananth · Answer 1 · 2014-03-09T10:40:21.303

0

use this Query.

SELECT ROUND((LENGTH(`column_name`) - LENGTH(REPLACE(`column_name`, 'search_keyword', '')))/LENGTH('search_keyword')) FROM table

for example if the table have column "details"

id          details
1           this is one of the best product in the india

if we want to match and find count of the text "the" in the "details" column, then use this query

SELECT ROUND((LENGTH(`details`) - LENGTH(REPLACE(`details`, 'the', '')))/LENGTH('the')) as count FROM table

Output :

count = 3

edited Mar 09 '14 at 10:40

answered Mar 09 '14 at 09:20

Ananth

1,520
3
15
28

Can you elaborate on this one a bit? All of that seems a bit...Weird to me. – ThePsychoMonkey Mar 09 '14 at 10:13
So, that seems like it's only counting something, I'm not looking to count up stuff. I'm looking to Read each line of my LONGTEXT field, and then search for a specific word and each lines that contains that word would be outputted. – ThePsychoMonkey Mar 09 '14 at 11:08

score 0 · Answer 2 · edited May 23 '17 at 12:28

The first thing you need to do is to make sure you have mysql-server, mysql-client, php-mysql and php-mysqli installed and configured on the server, I also suggest using PhpMyAdmin for a friendly mysql administration.

Create the actual table:

CREATE TABLE code (
   id int(11) not null auto_increment,
   file longtext,
   primary key(id)
)

The next step is to choose mysql access method, you can choose mysqli or pdo

The following example will show you how to do it with mysqli:

class db{
    function query($query, $show_error=true)
    {
        $time_before = $this->get_real_time();

        if(!$this->db_id) $this->connect(DBUSER, DBPASS, DBNAME, DBHOST);

        if(!($this->query_id = mysqli_query($this->db_id, $query) )) {

            $this->mysql_error = mysqli_error($this->db_id);
            $this->mysql_error_num = mysqli_errno($this->db_id);

            if($show_error) {
                $this->display_error($this->mysql_error, $this->mysql_error_num, $query);
            }
        }

        $this->MySQL_time_taken += $this->get_real_time() - $time_before;

        $this->query_num ++;

        return $this->query_id;
    }

function get_array($query_id = '')
{
    if ($query_id == '') $query_id = $this->query_id;

    return mysqli_fetch_array($query_id);
}

    function display_error($error, $error_num, $query = '')
    {
        if($query) {
            // Safify query
            $query = preg_replace("/([0-9a-f]){32}/", "********************************", $query); // Hides all hashes
            $query_str = "$query";
        }

        echo '<?xml version="1.0" encoding="iso-8859-1"?>
        <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
        <html xmlns="http://www.w3.org/1999/xhtml">
        <head>
        <title>MySQL Fatal Error</title>
        <meta http-equiv="Content-Type" content="text/html; charset=windows-1251" />
        <style type="text/css">
        <!--
        body {
            font-family: Verdana, Arial, Helvetica, sans-serif;
            font-size: 10px;
            font-style: normal;
            color: #000000;
        }
        -->
        </style>
        </head>
        <body>
            <font size="4">MySQL Error!</font> 
            <br />------------------------<br />
            <br />

            <u>The Error returned was:</u> 
            <br />
                <strong>'.$error.'</strong>

            <br /><br />
            </strong><u>Error Number:</u> 
            <br />
                <strong>'.$error_num.'</strong>
            <br />
                <br />

            <textarea name="" rows="10" cols="52" wrap="virtual">'.$query_str.'</textarea><br />

        </body>
        </html>';

        exit();
    }
}

define ("DBHOST", "localhost");
define ("DBNAME", "DBNAME");
define ("DBUSER", "DBUSER");
define ("DBPASS", "PASS");
define ("PREFIX", "PREFIX");
define ("USERPREFIX", "USERPREFIX");
define ("COLLATE", "utf8");

$db = new db;

Now you are ready to access mysql

Insert some data:

$db->query("INSERT INTO code (code) VALUES('{$text}')");

Select from code:

  $row=$db->query("SELECT * FROM code WHERE file LIKE '%{$search}%'");
  while($row=$db->get_array()){
     /* your original code */
     foreach($row['text'] as $line){
       if(strpos($line, $search) !== false){
         echo "<tr><td>" . $line . "</td></tr>";
         $count++;
       }
     }
  }

I suppose I should mention, I have never used mysqli before, so I don't understand any of that... Well, I understand a bit of it, however, some just doesnt make sense to me. — ThePsychoMonkey, Mar 09 '14 at 10:21

Search a LONGTEXT field, and display results

2 Answers2