Jump to content

How to count records from two columns


shmideo

Recommended Posts

Hi all

 

PHP newbie and first post!

 

I hope someone can help. I have looked at some of the tutorials but still can't get my head around how to get the data from within a PHP script.

 

Trying to create a form with two fields "Date" and "Channel" to look at the 'calldate' and 'channel' columns and then give a count result, so just want to find whatever specified on the form and give the number of ocurrences found grouped by date.

 

Ie, how many ocurrences for specified 'chanel' %CLI/1234567% for a given date.

 

Thanks

 

 

 

Link to comment
Share on other sites

This is what I have so far, but stuck on the date part.

 

<?php//// CONNECT TO THE DATABASE SERVER (use your credentials)//$db = new mysqli(localhost,root,"",jm_db);//// 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 calldate, channel , COUNT(*) as total FROM asterisk_cdr WHERE tablefield LIKE '%given string%' GROUP BY calldate"; $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> Call Date: <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
Share on other sites

This is what I have so far, but stuck on the date part.

What do you mean? What's the date part?You have an issue with your query. You're trying to bind a parameter, but there isn't a placeholder for it in the query string. When you're using prepared statements you need to have placeholders in the query for each value. You're not doing any error checking either, you should check for errors when you prepare and then execute the query. Each class has properties to check if an error occurred, you would check the statement class after calling execute, for example:http://php.net/manual/en/mysqli-stmt.error.phpIn general, it's not the best idea to copy and paste code. It doesn't help you understand how the code works. You have a prepared statement there, for example, but do you understand how it works and why you should use it?
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...