Jump to content

code to find string within a field


shmideo
 Share

Recommended Posts

Hi

I have a MySql database and wondered if there is a code to search a table field with a given string and display the number of unique occurrences.

The table is asterisk_cdr and would like to know how many calls received daily on a perticular channel.

 

Thanks

shmideo

 

Link to comment
Share on other sites

After re-reading your post title I am thinking you may want something like this instead, to search for text within the field

SELECT 
    tablefield
    , COUNT(*) as total
FROM asterisk_cdr
WHERE tablefield LIKE '%given string%'
GROUP BY tablefield

This should show you how to use it in a PHP script

 

 

<?php
//
// CONNECT TO THE DATABASE SERVER (use your credentials)
//
$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

//
// GET THE VALUE TO SEARCH FROM FROM SUBMITTED FORM DATA
//
$search = isset($_GET['search']) ? $_GET['search'] : '';
//
// ADD THE WILDCARD CHARACTERS
//
$sqlSearch = '%'.$search.'%';

//
// PREPARE AND EXECUTE THE QUERY
//
$sql = "SELECT 
            tablefield
            , COUNT(*) as total
        FROM asterisk_cdr
        WHERE tablefield LIKE '%given string%'
        GROUP BY tablefield";
$stmt = $db->prepare($sql);
$stmt->bind_param('s', $sqlSearch);
$stmt->execute();
$res = $stmt->get_result();

//
// LOOP THROUGH THE RESULTS AND BUILD THE HTML OUTPUT
//
$output = "";
while ($row = $res->fetch_row()) {
    $output .=  "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
}
?>
<!DOCTYPE HTML >
<html lang="en">
<head>
    <title>Sample</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" >
    <style type='text/css'>
    table {
        border-collapse: collapse;
    }
    th {
        background-color: #369;
        color: white;
    }
    td {
        background-color: #eee;
    }
    </style>
</head>
<body>
<form>
    Search for : <input type="text" name="search" value="<?=$search?>" size="10">
    <input type="submit" name="btnSubmit" value="Search">
</form>
<hr/>
<table border='1'>
    <tr><th>Table Field</th><th>Total</th></tr>
    <?=$output?>
</table>
</body>
</html>

 

 

Edited by Barand
Link to comment
Share on other sites

Thanks Barand

 

Hope you can help again! I'm trying to add a date parameter so that it can filter on 'dst' and 'date'.

This is so that I can check how many calls received for a destination on a given date, but I cannot solve it.

 

Thanks

shmideo
 

<?php
//
// CONNECT TO THE DATABASE SERVER (use your credentials)
//
$db = new mysqli(localhost,****,****,****);

//
// GET THE VALUE TO SEARCH FROM FROM SUBMITTED FORM DATA
//
$search = isset($_GET['search']) ? $_GET['search'] : '';
//
// ADD THE WILDCARD CHARACTERS
//
$sqlSearch = '%'.$search.'%';

//
// PREPARE AND EXECUTE THE QUERY
//
$sql = "SELECT
            dst, calldate
            , COUNT(*) as total
        FROM asterisk_cdr
        WHERE tablefield LIKE '%given string%'
        GROUP BY dst";
        
$stmt = $db->prepare($sql);
$stmt->bind_param('s', $sqlSearch);
$stmt->execute();
$res = $stmt->get_result();

//
// LOOP THROUGH THE RESULTS AND BUILD THE HTML OUTPUT
//
$output = "";
while ($row = $res->fetch_row()) {
    $output .=  "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
}
?>
<!DOCTYPE HTML >
<html lang="en">
<head>
    <title>Sample</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" >
    <style type='text/css'>
    table {
        border-collapse: collapse;
    }
    th {
        background-color: #369;
        color: white;
    }
    td {
        background-color: #eee;
    }
    </style>
</head>
<body>
<form>
    Channel Number:
    <input type="text" name="search" value="<?=$search?>" size="10">
    <input type="submit" name="btnSubmit" value="Search">
</form>

<form>
    Date Ie, 2014/05/19:
      <input type="text" name="search" value="<?=$search?>" size="10">
    <input type="submit" name="btnSubmit" value="Search">
</form>
<hr/>
<table border='1'>
    <tr><th>Table Field</th><th>Total</th></tr>
    <?=$output?>
</table>
<p> </p>
</body>
</html>
Edited by Ch0cu3r
code tags when posting code please
Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.