Report

PHP - Linking MySQL tables and counting results [Solved]

Ask a question BrianGreen 908Posts Saturday January 17, 2015Registration date ModeratorStatus April 24, 2017 Last seen - Last answered on Apr 24, 2017 at 03:14 PM by BrianGreen
Ok I give up ... again. Once again I call out for your help and offer 2 virtual beers for the solver of this really easy sounding problem.

Ok I have a collection of MySQL tables in a database called 'tickets'. one is called 'raw_data' and one called 'support_type'. The raw_data table holds all the data that a user enters via a web interface and includes a few fields that have repeating data. I have attempted to normalise this table by introducing another table that I want to link to the raw_data table - in this case the support_data table. Here is the structure of these 2 tables:

raw_data

id - auto increment - key - int (11)
Support_Type - int (2)
some other columns


support_type

id - auto increment - key - int (2)
Support_Type - varchar (15)


What I want is similar to what I can produce with the following PHP code ...
<?php
include 'header_logged_in.php';
include 'includes/navigation.inc.php';
include 'database_handler.php';
?>

<main>
<h1>
Ticket breakdown by type
</h1>

<?php
$start = mysqli_real_escape_string($conn, $_POST['start']);
if (empty($start)) {
$start = date("d/m/y");
}

$end = mysqli_real_escape_string($conn, $_POST['end']);
if (empty($end)) {
$end = date("d/m/y");
}

$query = "SELECT Support_Type, COUNT(Support_Type) AS qty FROM raw_data WHERE Date BETWEEN '$start' AND '$end' GROUP BY Support_Type ORDER BY qty DESC";

$result1 = mysqli_query($conn, $query);
while($row = mysqli_fetch_array($result1))
{
echo $row['Support_Type']."(".$row['qty'].")<br />";
}


What this does is to count the amount of occurrences of each Support_Type between 2 dates entered by the user on the web page ... which at this point is a number because of the normalisation.

What I want is the name of the Support_Type which is held in the support_type table. So instead of having this:

2(7)
3(4)
1(1)

I want this:

Subs(7)
Password(4)
Other(1)

One of the things I have tried in the SQL query is this


$query = "SELECT support_type.Support_Type, COUNT(support_type.Support_Type) AS qty
FROM support_type
WHERE Date BETWEEN '$start' AND '$end'
AND raw_data.Support_Type=support_type.Support_ID
GROUP BY raw_data.Support_Type
ORDER BY qty DESC";


But this fails dismally

I hope this all makes sense and I hope I have given all of the information needed. I appreciate any help that you can give.
See more 
Helpful
+0
plus moins
Found the solution. It needed an inner join aparently.

SELECT support_type.Support_Type, COUNT(*) AS qty 
FROM support_type, raw_data
WHERE support_type.Support_ID=raw_data.Support_Type AND raw_data.Date BETWEEN '$start' AND '$end'
GROUP BY support_type.Support_Type
ORDER BY qty DESC


2 beers for me:)

Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!