code to find string within a field

shmideo · October 20, 2014

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

Barand · October 20, 2014



SELECT COUNT(*) as total

FROM asterisk_cdr

WHERE tablefield = 'given string'

shmideo · October 21, 2014

Thank you Barand

I would appreciate your help again. How can I run this as a php script that connects to the database and then displays the result count for the previous day on the page?

Thanks

shmideo

Barand · October 21, 2014

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 October 21, 2014 by Barand

shmideo · October 21, 2014

Awesome!! Thank you Barand I'll give this a try.

shmideo

Edited October 21, 2014 by shmideo

shmideo · October 21, 2014

Sorry Barand, my fault for not being precise. The table field will always be the same which is called 'channel'. I'd like to search for an actual string within that field.

Thanks in advance

shmideo

Barand · October 21, 2014

Then where I used "tablefield", use "channel" in the query ::)

shmideo · October 24, 2014

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 October 27, 2014 by Ch0cu3r
code tags when posting code please

Barand · October 24, 2014

Then where I used "tablefield", use "channel" in the query

shmideo · October 24, 2014

I used 'dst', 'calldate' in the query but not sure if what I've done in the above code correct

Sign In

code to find string within a field

Recommended Posts

shmideo

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

shmideo

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

shmideo

Link to comment

Share on other sites

shmideo

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

shmideo

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

shmideo

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information