shmideo Posted October 20, 2014 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/291955-code-to-find-string-within-a-field/ Share on other sites More sharing options...
Barand Posted October 20, 2014 Share Posted October 20, 2014 SELECT COUNT(*) as total FROM asterisk_cdr WHERE tablefield = 'given string' Quote Link to comment https://forums.phpfreaks.com/topic/291955-code-to-find-string-within-a-field/#findComment-1494282 Share on other sites More sharing options...
shmideo Posted October 21, 2014 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/291955-code-to-find-string-within-a-field/#findComment-1494301 Share on other sites More sharing options...
Barand Posted October 21, 2014 Share Posted October 21, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/291955-code-to-find-string-within-a-field/#findComment-1494309 Share on other sites More sharing options...
shmideo Posted October 21, 2014 Author Share Posted October 21, 2014 (edited) Awesome!! Thank you Barand I'll give this a try. shmideo Edited October 21, 2014 by shmideo Quote Link to comment https://forums.phpfreaks.com/topic/291955-code-to-find-string-within-a-field/#findComment-1494318 Share on other sites More sharing options...
shmideo Posted October 21, 2014 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/291955-code-to-find-string-within-a-field/#findComment-1494325 Share on other sites More sharing options...
Barand Posted October 21, 2014 Share Posted October 21, 2014 Then where I used "tablefield", use "channel" in the query Quote Link to comment https://forums.phpfreaks.com/topic/291955-code-to-find-string-within-a-field/#findComment-1494328 Share on other sites More sharing options...
shmideo Posted October 24, 2014 Author Share Posted October 24, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/291955-code-to-find-string-within-a-field/#findComment-1494572 Share on other sites More sharing options...
Barand Posted October 24, 2014 Share Posted October 24, 2014 Then where I used "tablefield", use "channel" in the query Quote Link to comment https://forums.phpfreaks.com/topic/291955-code-to-find-string-within-a-field/#findComment-1494583 Share on other sites More sharing options...
shmideo Posted October 24, 2014 Author Share Posted October 24, 2014 I used 'dst', 'calldate' in the query but not sure if what I've done in the above code correct Quote Link to comment https://forums.phpfreaks.com/topic/291955-code-to-find-string-within-a-field/#findComment-1494588 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.