SQL DISTINCT COUNT

Hi,

I have this page http://kroweb.dk/gfdev/canvas/ where you get a list of shires and parishes when clicking a county on the map. I would like to add the number of  shires and parishes to the headline where it says for example "Herreder: Bornholm"   (if you click on the island at the top right of the map.

So I need to add a query something like SELECT COUNT(DISTINCT Herred) FROM `sogne`;
But I don't know how to fit it in to my code.

I have this php/sql file for shires:
<?php
if ((isset($_POST['myQuery2']))) {
  $con=mysqli_connect("db credentials) or die("Error connecting to database: ".mysql_error());

  mysqli_select_db($con,"genealogiskforum_dk_db9");
  mysqli_set_charset($con,"utf8");
  ?> 
  <br>



  <?php
  $query = trim($_POST['myQuery2']); 
  $raw_results = mysqli_query($con, "SELECT DISTINCT`Herred`, `Amt` FROM sogne
    WHERE (`Amt` = '$query')") or die(mysql_error());
  //var_dump($query);
    ?>

   <div class="herredscont" style=""><h3>Herreder: <?php echo $query ?></h3> </div>

      <?php
      while($row = $result=mysqli_fetch_array($raw_results)) {
        $txt= sprintf ($row["Herred"]); 
        //$txt1= sprintf (", ");?>

        <span class="herredscont2" style="font-family:Serif; font-size:14px;"> <?php echo $txt?>,&nbsp  </span>
        <!--echo $txt;
        echo $txt1;
    ?> -->

    <?php

      }

      mysqli_close($con);
   }else{  print ("POST is not set");
   }

  ?>

Open in new window


The page holds besides this file an index file and file that holds the map with the appropiate scripts to get the clicking selections on the map working. If needed I can post the code to any and/or every of these files.
Peter KromanSenior Proposal SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ganesh GuruduSenior ConsultantCommented:
Try this code
<?php
if ((isset($_POST['myQuery2']))) {
  $con=mysqli_connect("db credentials) or die("Error connecting to database: ".mysql_error());

  mysqli_select_db($con,"genealogiskforum_dk_db9");
  mysqli_set_charset($con,"utf8");
  ?> 
  <br>



  <?php
  $query = trim($_POST['myQuery2']); 
  $query_count = "SELECT COUNT(DISTINCT Herred)  as Herred  FROM sogne    WHERE (Amt = '$query')"; 

  $raw_results = mysqli_query($con, query_count) or die(mysql_error());
  //var_dump($query);
    ?>

   <div class="herredscont" style=""><h3>Herreder: <?php echo $query ?></h3> </div>

      <?php
      while($row = $result=mysqli_fetch_array($raw_results)) {
        $txt= sprintf ($row['Herred']); 
        //$txt1= sprintf (", ");?>

        <span class="herredscont2" style="font-family:Serif; font-size:14px;"> <?php echo $txt?>,&nbsp  </span>
        <!--echo $txt;
        echo $txt1;
    ?> -->

    <?php

      }

      mysqli_close($con);
   }else{  print ("POST is not set");
   }

  ?>

Open in new window


you can also use like this

  $txt= $row['Herred'];

check the sample code here
http://www.tizag.com/mysqlTutorial/mysqlcount.php
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Thanks,

It is not working.

I get this error:




Fatal error: Uncaught Error: Call to undefined function mysql_error() in /var/www/kroweb.dk/public_html/gfdev/canvas/ajaxHerredHtml.php:20 Stack trace: #0 {main} thrown in /var/www/kroweb.dk/public_html/gfdev/canvas/ajaxHerredHtml.php on line 20

Where line 20 is this line:
 $raw_results = mysqli_query($con, 'query_count') or die(mysql_error());
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
I also believe that you are trying to put the count in in stead af the result there is now .

Tha is not what I am after. I am after a count to add to the result I have now :)
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Ganesh GuruduSenior ConsultantCommented:
plz re-validate these three lines again. light change done . seems you have not taken latest code. $ symbol missed in variable query_count

$query = trim($_POST['myQuery2']);
 $query_count = "SELECT COUNT(DISTINCT Herred)  as Herred  FROM sogne    WHERE (Amt = '$query')";
$raw_results = mysqli_query($con, $query_count) or die(mysql_error());

does the table sogne  contain Herred and Amt column name. validate the name
0
Ganesh GuruduSenior ConsultantCommented:
still any errors?
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
No - I dont get any errors when I add ' ' around Herred in this line  $query_count = "SELECT COUNT(DISTINCT Herred)  as Herred  FROM sogne    WHERE (Amt = '$query')";

And yes - the table contains Amt, Herred and Sogn columns

But it is not this I want. What I get here is a wrong count. It counts "1" no matter which choice is made in the map which is not correct, and the result that was there before is gone now.

From my last post
I also believe that you are trying to put the count in in stead af the result there is now .

Tha is not what I am after. I am after a count to add to the result I have now :)
0
Ganesh GuruduSenior ConsultantCommented:
it seem issue with trim($_POST['myQuery2']);  this might be the parameter getting from different page.

check the value of trim($_POST['myQuery2']);


and can you also provide the table details like column name , data types with sample data. and the scenarios of fetch details etc.
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
myQuery2 comes from a script in the map file:

for(var i=0;i<allPaths.length;i++){
    allPaths[i].addEventListener('click',function (e){
    var area=this.id;   
    /* Here I need that clicking a selection fires a query in the DB*/       
$.ajax({
       url: 'ajaxHerredHtml.php',
       data:{myQuery2:area},
       method: 'POST',
       dataType: 'html'
     }).done(function (res){             
            $('#herreds_list').html(res);
          }); 
                  
    });    
};

Open in new window


The file we are looking at is ajaxHerredHtml.php

Datatbase structure:
Sk-rmbillede-2017-12-17-16.34.06.png
0
Ganesh GuruduSenior ConsultantCommented:
can you try
echo trim($_POST['myQuery2']);
and check what value its passing for this variable.

and also what type of data contain in Amt column?
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
The Value of myQuery2 is the name of the County (Amt)

The column Amt holds the names of the Counties (pure text).
0
Ganesh GuruduSenior ConsultantCommented:
Dear Peter,

data:{myQuery2:area} # myQuery2 should be the html control ID  and area variable should hold the actual data .

However,
$query = trim($_POST['myQuery2']);
and the value of  $query should have exact matching value of  Amt column in table.

seems the value is not matching properly.
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
It is holding the exact same data :)

This code works nicely, and shows the shires in the county chosen in the map, which is exactly what it is supposed to do:
$query = trim($_POST['myQuery2']); 
  $raw_results = mysqli_query($con, "SELECT DISTINCT`Herred`, `Amt` FROM sogne
    WHERE (`Amt` = '$query')") or die(mysql_error());

Open in new window


Example:
Sk-rmbillede-2017-12-17-17.13.59.png
What I want is just the count - in this specific case it would be 4 - and I want it displayed in the top line so that it will say "4 Herreder: Bornholm" en stead of "Herreder: Bornholm".
0
Ganesh GuruduSenior ConsultantCommented:
dear Friend,

it seems you have list of county's with Comma separated in database. is a case. you have two options,

if the county's are static you can add one more column NumCounty which contain the number value. and you can fetch this value and display directly in website.

OR use below query

$query = trim($_POST['myQuery2']); 
  $raw_results = mysqli_query($con, "SELECT DISTINCT`Herred`, `Amt` , LENGTH([`Amt`]) - LENGTH(REPLACE([`Amt`], ',', '') `NumCounty` FROM sogne
    WHERE (`Amt` = '$query')") or die(mysql_error());

Open in new window

0
PortletPaulfreelancerCommented:
IF it is true that you have that data as a comma separated string (which isn't a good idea in relational databases) then you can count by comparing (the original string length - length of string after replacement of commas) + 1 unless you happen to have a comma at the end of the string

IF  your table consists of the 3 columns: Amt, Herred and Sogn whyare you using distinct

Unless you actually have real duplicates stored in that table there is no point to "select distinct", all it does it slows down the query eecution.

----
"A herred is a minor jurisdictional district in Denmark" and "sogne" something like "parish"
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
It is NOT comma separated in the database - look at the rest of the code :)
       $txt= sprintf ($row["Herred"]); 

        <span class="herredscont2" style="font-family:Serif; font-size:14px;"> <?php echo $txt?>,&nbsp  </span>

Open in new window

I am using DISTINCT because there is several shires in a county, and I only need to display the shires that belong to the County selected.
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Just for the better understanding;

Amt = County
Herred = Shire
Sogn = Parish
0
Ganesh GuruduSenior ConsultantCommented:
you are simply confusing!

Can you share the data of this table? screen short or in excel etc?
and what value your getting in  trim($_POST['myQuery2']);
1
PortletPaulfreelancerCommented:
Peter,
I was only pointing out that IF the data was comma separated, then the count technique suggested needs +1
1
Peter KromanSenior Proposal SpecialistAuthor Commented:
But PortletPaul - you are quite right.

What I want to count is the shires belonging to the actual county, and I have defined the county using the WHERE clause in the query and I have asured that the shires are only represented once (and not as may times as there are Sogne (Parishes) in the given Amt (County)) using the DISTINCT clause.
Given this it should not be necessary to use DISTINCT in the COUNT statement - but I still can't make it work .....
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
There is nothing confusing about this Ganesh. It is just a simple question which seems to be made very complex .....

As I pointed out earlier the myQuery2 variable returns  the name of the Amt (County) selected. I have var_dumped botth the $query and the trim($_POST['myQuery2'] so you can see it for your self on the page.

The data in the table is straight forward - I have allready shared the structure and here is a sample of the content - there is nothing strange about that.

Sk-rmbillede-2017-12-18-08.05.28.png
0
Ganesh GuruduSenior ConsultantCommented:
did you check like this?
$row_count = count($result);
echo $row_count;
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
I have checked your suggestion. It returns this error:

Notice: Undefined variable: result in /var/www/kroweb.dk/public_html/gfdev/canvas/ajaxHerredHtml.php on line 26
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
What I don't understand is that I can't get the COUNT statement to return a string.

When I test the queries in SequelPro I get these results:

Sk-rmbillede-2017-12-18-10.19.05.png
Sk-rmbillede-2017-12-18-10.19.52.png
Where the latter represents all the lines in the table because there is a Herred attatched to every line.

But when I test it in the code e.g like this:
 $test =  mysqli_query($con, "SELECT COUNT(`Herred`) AS Herred FROM sogne");
 while($row = $result=mysqli_fetch_object($test)) {
 $txt3 = sprintf($test);

 }

Open in new window


I get this error:
Catchable fatal error: Object of class mysqli_result could not be converted to string in /var/www/kroweb.dk/public_html/gfdev/canvas/ajaxHerredHtml.php on line 20 - where line 20 is line 3 in the above code snippet

Where am I going wring here?
0
Ganesh GuruduSenior ConsultantCommented:
// Return the number of rows in result set
  $rowcount=mysqli_num_rows($test);

OR

$test =mysqli_query($con, "SELECT COUNT(`Herred`) AS Herred FROM sogne");
$row = mysql_fetch_array($test);

$total = $row[0];
echo "Total rows: " . $total;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Use a mthod to force it into a string.

Count returns an integer.


Eg

Select cast (.... as varchar) from ....

Select concat (....) from ....

Sorry only have a phone so just a brief comment.
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Thanks Ganesh,

Now we hit the right solution. Thanks a lot.

Working code:
[code]<?php
  session_start();
  error_reporting(E_ALL);
  ini_set('display_errors', 1);

  if ((isset($_POST['myQuery2']))) {
    $con=mysqli_connect("db credentials") or die("Error connecting to database: ".mysql_error());

    mysqli_select_db($con,"genealogiskforum_dk_db9");
    mysqli_set_charset($con,"utf8");
    ?>
    <br>

    <?php

    $query = trim($_POST['myQuery2']);
    $raw_results = mysqli_query($con, "SELECT DISTINCT `Herred` FROM sogne WHERE (`Amt` = '$query')") or die(mysql_error());

    $count =  mysqli_query($con, "SELECT COUNT(DISTINCT `Herred`) AS Herred FROM sogne WHERE (`Amt` = '$query')");
    $row = mysqli_fetch_array($count);
    $total = $row[0];

    ?>

    <div class="herredscont" style=""><h3> Herreder: <?php echo $query ?>, <? echo $total ?> stk.</h3> </div>

    <?php
    while($row = $result=mysqli_fetch_array($raw_results)) {
      $txt = sprintf ($row["Herred"]);

      ?>

      <span class="herredscont2" style="font-family:Serif; font-size:14px;"> <?php echo $txt?>,&nbsp </span>


      <?php

    }


    mysqli_close($con);
  }else{  print ("POST is not set");
}

?>[/code]
0
Chris StanyonWebDevCommented:
Hey Peter,

Just looking at this when you accepted a solution. Basically, I think you're over-thinking this. There is no need to be running several queries just to get a count. A query returns an array, so just count the array. And looking at your page, it seems you're running 2 separate queries - one for the Parish and one for the Shire (so that's 4 queries in total). You can do all of this with just one query very simply:

<?php
$county = trim($_POST['county']);
$sql = "SELECT DISTINCT parish, shire FROM yourTable WHERE county = '$county'";

$result = $mysqli->query($sql);
$rows = $result->fetch_all(MYSQLI_ASSOC);

$parishes = array_unique(array_column($rows, 'parish'));
$shires = array_unique(array_column($rows, 'shire'));
?>

<div class="herredscont">
    <h3>Herreder: <?php echo $county ?>, <?php echo count($parishes) ?> stk.</h3>
</div>

<?php foreach ($parishes as $parish): ?>
    <span><?php echo $parish ?>, </span>
<?php endforeach; ?>

<div class="sognecont">
    <h3>Sogne: <?php echo $county ?>, <?php echo count($shires) ?> stk.</h3>
</div>

<?php foreach ($shires as $shire): ?>
    <span><?php echo $shire ?>, </span>
<?php endforeach; ?>

Open in new window

0
Peter KromanSenior Proposal SpecialistAuthor Commented:
You are absolutely right. I am running 4 queries as it is now.

And I am sure that the solution that you propose it the "simple" solution I was looking for.
I will work on it and get back to you.
Thanks :)
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Hi Chris,

I have reworked your suggestion like this:
<?php
$amt = trim($_POST['myQuery']);
$sql = "SELECT DISTINCT Sogn, Herred FROM sogne WHERE Amt = '$amt'";

$result = $mysqli->query($sql);
$rows = $result->fetch_all(MYSQLI_ASSOC);

$sogne = array_unique(array_column($rows, 'sogn'));
$herreder = array_unique(array_column($rows, 'amt'));
?>

<div class="herredscont">
    <h3>Herreder: <?php echo $county ?>, <?php echo count($sogne) ?> stk.</h3>
</div>

<?php foreach ($sogne as $sogn): ?>
    <span><?php echo $sogn ?>, </span>
<?php endforeach; ?>

<div class="sognecont">
    <h3>Sogne: <?php echo $query ?>, <?php echo count($herreder) ?> stk.</h3>
</div>

<?php foreach ($herreder as $herred): ?>
    <span><?php echo $Herred ?>, </span>
<?php endforeach; ?>

<?php

    mysqli_close($con);
  }else{  print ("POST is not set");
}

?>

Open in new window


But it makes trouble over line 19 (line 5 above) like this:

Notice: Undefined variable: mysqli in /var/www/kroweb.dk/public_html/gfdev/canvas/ajaxSognHerredHtml.php on line 19

Fatal error: Uncaught Error: Call to a member function query() on null in /var/www/kroweb.dk/public_html/gfdev/canvas/ajaxSognHerredHtml.php:19 Stack trace: #0 {main} thrown in /var/www/kroweb.dk/public_html/gfdev/canvas/ajaxSognHerredHtml.php on line 19
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
I have moved on a bit, and I have managed to fix the line 19 (line 5) error. I will be back later :)
0
Chris StanyonWebDevCommented:
No worries Peter,

As you've probably figured out it was because in my code I'd called the DB Connection $mysqli and you call yours $con :)

You do have another problem on line 8 and 9 though. In your query you are selecting 2 columns called Sogn and Herred, but on line 9 you are trying to read one called Amt (and on line 8 you have a lower case 's'. Those lines should read:

$sogne = array_unique(array_column($rows, 'Sogn'));
$herreder = array_unique(array_column($rows, 'Herred'));
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Yep. I have now got it working :)

But I have two problems:

1.  It is showing Herreder and Sogne twice - like this:
Sk-rmbillede-2017-12-18-15.14.52.png
2. I need to get the links in to the Sogne part. In the former files I had done it like this:
while($row = $result=mysqli_fetch_array($raw_results)) {
    $txt= sprintf ("<a href=\"".$row["URL"]."\"target=\"_blank\">" . $row["Sogn"]."</a>"); 
    ?>

Open in new window

But it seems that I can't sue that method here. How to do :)
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
And here comes the code:
<?php
  error_reporting(E_ALL);
  ini_set('display_errors', 1);

  if ((isset($_POST['myQuery']))) {
    $con=mysqli_connect("db credentials") or die("Error connecting to database: ".mysql_error());

    mysqli_select_db($con,"genealogiskforum_dk_db9");
    mysqli_set_charset($con,"utf8");
    ?> 
    <br>



<?php
$amt = trim($_POST['myQuery']);
$sql = "SELECT DISTINCT Sogn, Herred FROM sogne WHERE Amt = '$amt'";
//var_dump($amt);

$result = $con->query($sql);
$rows = $result->fetch_all(MYSQLI_ASSOC);

$sogne = array_unique(array_column($rows, 'Sogn'));
$herreder = array_unique(array_column($rows, 'Herred'));
?>

<div class="herredscont">
    <h3>Herreder: <?php echo $amt ?>, <?php echo count($herreder) ?> stk.</h3>
</div>

<?php foreach ($herreder as $herred): ?>
    <span><?php echo $herred ?>, </span>
<?php endforeach; ?>

<div class="sognecont">
    <h3>Sogne: <?php echo $amt ?>, <?php echo count($sogne) ?> stk.</h3>
</div>

<?php foreach ($sogne as $sogn): ?>
    <span><?php echo $sogn ?>, </span>
<?php endforeach; ?>

<?php

    mysqli_close($con);
  }else{  print ("POST is not set");
}

?>

Open in new window

0
Peter KromanSenior Proposal SpecialistAuthor Commented:
I have fixed the double displaying.

Working on the links now - hop that I can fix it :)
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Nope - have to give in to the links in this setup.

Can you help, Chris?
0
Chris StanyonWebDevCommented:
Sure Peter,

I think this should work but I can't test it fully as I don't have your dataset. Firstly we need to include the URL column in the query:

$sql = "SELECT DISTINCT Sogn, Herred, URL FROM sogne WHERE Amt = '$amt'";

Open in new window

And then the idea is that instead of calling array_unique() on the $herreder, we simply build a new array that also contains the $link:

// Get the unique Shires
$sogne = array_unique(array_column($rows, 'Sogn'));

// Get the Parishes along with the Links
foreach ($rows as $key => $row) {
    $herreder[$row['Herred']] = $row['URL'];
}

Open in new window

And then in your HTML:

<?php foreach ($herreder as $herred => $link): ?>
    <span><a href="<?php echo $link ?>"><?php echo $herred ?></a>, </span>
<?php endforeach; ?>

Open in new window

0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Thanks Chris,

You have switched Herreder and Sogne. It is the Sogne (Parishes) I need links for.

But it is returning a little strange result. First it returns all the parishes  as numbers and the link returns the name of the Sogn, and then it returns the parishes correctly with the right links.

The code:
<?php
$amt = trim($_POST['myQuery']);
$sql = "SELECT DISTINCT Sogn, Herred, URL FROM sogne WHERE Amt = '$amt'";
//var_dump($amt);

$result = $con->query($sql);
$rows = $result->fetch_all(MYSQLI_ASSOC);

//$sogne = array_unique(array_column($rows, 'Sogn'));
// Get the unique Shires
$sogne = array_unique(array_column($rows, 'Sogn'));

// Get the Parishes along with the Links
foreach ($rows as $key => $row) {
    $sogne[$row['Sogn']] = $row['URL'];
}

$herreder = array_unique(array_column($rows, 'Herred'));
?>

<div class="herredscont">
    <h3>Herreder: <?php echo $amt ?>, <?php echo count($herreder) ?> stk.</h3>
</div>

<?php foreach ($herreder as $herred): ?>
    <span class="countHerred"style="font-family:Serif; font-size:14px;"><?php echo $herred ?>, </span>
<?php endforeach; ?>


<div class="sognecont">
    <h3>Sogne: <?php echo $amt ?>, <?php echo count($sogne) ?> stk.</h3>
</div>

<?php foreach ($sogne as $sogn => $link): ?>
    <span><a href="<?php echo $link ?>"> <?php echo $sogn ?></a>, </span>
<?php endforeach; ?>

<?php

Open in new window

0
Chris StanyonWebDevCommented:
Hey Peter,

Looks like you're defining your $sogne array twice:

$sogne = array_unique(array_column($rows, 'Sogn'));

// Get the Parishes along with the Links
foreach ($rows as $key => $row) {
    $sogne[$row['Sogn']] = $row['URL'];
}

Open in new window

We don't need the array_unique call - just the foreach loop, so you end up with:

$result = $con->query($sql);
$rows = $result->fetch_all(MYSQLI_ASSOC);

// Get the Parishes along with the Links
foreach ($rows as $key => $row) {
    $sogne[$row['Sogn']] = $row['URL'];
}

$herreder = array_unique(array_column($rows, 'Herred'));

Open in new window

0
Peter KromanSenior Proposal SpecialistAuthor Commented:
That helped. Thanks a lot. It is working beautifully now :)
0
Chris StanyonWebDevCommented:
Cool :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.