shmideo

Thanks. I don't want to limit results, just the ability to jump right to the end if needed.

Is it possible to have a link at the top of a page that has been displayed from a database? Just that some results show thousands of rows. Thanks.

Yes I approached it the wrong way but I have learnt a lot here on PHP freaks. Great forum, thanks.

Sorry Barand, ignore that, mistake on my part. Works perfctly! Thank you

Wow, thanks, this works great and gives a running total. Is there a way to supress all but the last row? Meaning just give a total for all rows found. I did try playing around with the $tot but maybe it is somethig different.

Well I'm still inexperienced with PHP. Yes every row in that column has a number > 0 which is what I wanted. I don't have a clue how to accumulate the duration, that's why I posted.

The query works, but yes I would prefer to know the proper way. This is the code I've been working on (with help from the guys on this forum) for for the past few days! error_reporting(E_ALL | E_NOTICE); ini_set('display_errors', '1'); if (isset($_POST['submitted'])) { include('connect1.php'); if (!empty($_POST['calldate'])) { $calldate = mysqli_real_escape_string ($dbcon, $_POST['calldate']); } else { $calldate = FALSE; echo '<p class="error">ha ha, you have not entered a date!</p>'; } $channel = $_POST['channel']; $submitted = $_POST['submitted']; $duration = 'duration'; If($calldate){ $query = "SELECT * FROM asterisk_cdr WHERE calldate LIKE '%$calldate%' AND channel LIKE '%$channel%' AND duration"; $result = mysqli_query($dbcon, $query) or die('Error getting data'); $num_rows = mysqli_num_rows($result); echo " '$num_rows' calls found for '$calldate'"; echo "<br/>"; echo "<table width='300px'>"; echo "<tr> <th>Time of Call: Call Duration</th>"; while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { echo "<table width='400px'>"; echo "<tr><td>"; echo $row['calldate']. " (". $row['duration'] . " sec)" ; echo "</td></table>"; echo "</td>"; }

"duration" is one of the columns in the table which contains number of seconds and it displays each row found. I just want to to get a total count for that column and echo the result.

I get all the results displayed but how can I echo the total value for for all rows found for under column 'duration' which only contains numbers (seconds). $query = "SELECT * FROM asterisk_cdr WHERE calldate LIKE '%$calldate%' AND channel LIKE '%$channel%' AND duration"; $result = mysqli_query($dbcon, $query) or die('Error getting data'); $num_rows = mysqli_num_rows($result);

Perfect tryingtolearn, thank you! Can't believe I didn't spot the $dbc

The code: <?php error_reporting(E_ALL | E_NOTICE); ini_set('display_errors', '1'); if (isset($_POST['submitted'])) { // connect to the database include('connect1.php'); $calldate = $_POST['calldate']; $channel = $_POST['channel']; $submitted = $_POST['submitted']; $duration = 'duration'; // Validate the input calldate: if (!empty($_POST['calldate'])) { $calldate = mysqli_real_escape_string ($dbc, $_POST['calldate']); } else { $calldate = FALSE; echo '<p class="error">You forgot to enter a date!</p>'; } $query = "SELECT * FROM asterisk_cdr WHERE calldate LIKE '%$calldate%' AND channel LIKE '%$channel%'"; $result = mysqli_query($dbcon, $query) or die('Error getting data'); $num_rows = mysqli_num_rows($result); echo "$num_rows calls found for $calldate"; echo "<table>"; echo "<tr> <th>Call Details: </th>"; while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { echo "<tr><td>"; echo $row['calldate']; echo "</td>"; } echo "</table>"; } ?> </th> </tr> </table> </body> </html>

Thanks, tried that but seems to be ignoring. Have insert after the declaring the variables.

The code works great now but how can I validate for blank fields in the form? Have tried as below but it only checks when first run then ignores. if(!isset($calldate) || trim($channel) == '') { echo "You did not fill out the required fields."; }

Doh! Thank you so much guys. All makes so much more sense now! Truly appreciated.

Thanks, I kinda know what you are saying but have not been able to work it out. Would appreciate some more guidance, if possible.

I tried that and now getting the error; "error getting data" (from line 43 mysqli_query...). Nothing in the php error logs. The two table columns I'm trying to read are 'calldate' and 'channel'. Search criteria used: 2014-11-01 (calldate) and SIP/4546975289 (channel) which exist within asterisk:cdr table.

I've done some reading up and a couple of tutorials and have come up with this. It's the same goal except I put it into a 2 field form "Date" and "Channel". The connect to database is in a separate .php which the main code includes. The connect to db code does not get errors and when I run the main code and fill in the form I get blank white screen. I have error reporting on within the code but still blank results. Have checked php error log within WAMP and nothing. It seems the html is being executed but not the PHP code within? <html> <head> <title>Call Statistics Test</title> <style type="text/css"> table { background-color:#FCF; } th { width: 150px; text-align: left; } </style> </head> <body> <h1>Calls Search</h1> <form method="post" action="search1.php"> <table>Search Call Date: <input type="text" name="calldate" /></table> <table>Search Channel: <input type="text" name="channel" /></table> <input type="submit" /> </form> <?php ini_set('display_errors', 1); error_reporting(E_ALL); if (isset($_POST['submitted'])) { // connect to the database include('connect1.php'); $calldate = $_POST['calldate']; $channel = $_POST['channel']; $query = "SELECT * FROM asterisk_cdr WHERE $calldate LIKE '%$calldate%' AND $channel LIKE '%$channel%'"; $result = mysqli_query($dbcon, $query) or die('error getting data'); $num_rows = mysqli_num_rows($result); echo "$num_rows results found"; echo "<table>"; echo "<tr> <th>Channel</th>"; while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { echo "<tr><td>"; echo $row['channel']."<br />"; echo "</td>"; } echo "</table>"; } ?> </body> </html>

Thanks guys. I am fairly new to PHP and MySql and have been paying around with this code for a few days now!. Although I did borrow a snippt " while ($row = mysql_fetch_array", the original posted code I did. I think I've progressed, now getting "Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in". Code: <?php define("DB_HOST", "localhost"); define("DB_USER", "root"); define("DB_PASSWORD", ""); define("DB_DATA", "jm_db"); $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATA); if($mysqli->connect_errno) { printf("Connect failed: %s\n", $mysqli->connect_error()); exit(); } $query = mysqli_query($mysqli,"SELECT COUNT(*) FROM asterisk_cdr WHERE calldate LIKE '%2014-10-11%' AND channel LIKE '%SIP/4546975289%'"); while ($row = mysqli_fetch_array($query, MYSQL_ASSOC)) { $result = $row[0] ; echo ($row["channel"]); } ?>

Yes I did switch to using 'mysqli' as 'mysql_connect' it gave an error. Regarding the undefined function, I don't use this anywhere else, but it seems to expect a parameter in this line "$mysqli = chn("localhost", "root", ""). Thanks

Hi, could I have some help with this code please? Getting: "Fatal error: Call to undefined function chn() in ......" Thanks <?php $mysqli = chn("localhost", "root", "") or die("Connect failed : " . mysql_error()); mysql_select_db("jm_db"); $result = mysql_query("SELECT COUNT(*) FROM asterisk_cdr WHERE calldate LIKE '%2014-10-11%' AND channel LIKE '%SIP/4546975289%'"); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo ($row["channel"]); } mysql_free_result($result); ?>

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

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>

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

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

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