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 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' 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 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 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> 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 Awesome!! Thank you Barand I'll give this a try. shmideo 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 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 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 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> 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 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 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
Archived
This topic is now archived and is closed to further replies.