SQL DISTINCT COUNT

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");
   }

  ?>

Select allOpen in new window

you can also use like this

  $txt= $row['Herred'];

check the sample code here
http://www.tizag.com/mysqlTutorial/mysqlcount.php

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());

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 :)

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

still any errors?

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 :)

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.

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);
          }); 
                  
    });    
};

Select allOpen in new window

The file we are looking at is ajaxHerredHtml.php

Datatbase structure:

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?

The Value of myQuery2 is the name of the County (Amt)

The column Amt holds the names of the Counties (pure text).

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.

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());

Select allOpen in new window

Example:

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".

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());

Select allOpen in new window

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"

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>

Select allOpen 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.

Just for the better understanding;

Amt = County
Herred = Shire
Sogn = Parish

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']);

Peter,
I was only pointing out that IF the data was comma separated, then the count technique suggested needs +1

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 .....

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.

did you check like this?
$row_count = count($result);
echo $row_count;

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

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:



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);

 }

Select allOpen 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?

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.

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]

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; ?>

Select allOpen in new window

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 :)

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");
}

?>

Select allOpen 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

I have moved on a bit, and I have managed to fix the line 19 (line 5) error. I will be back later :)

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'));

Yep. I have now got it working :)

But I have two problems:

1.  It is showing Herreder and Sogne twice - like this:

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>"); 
    ?>

Select allOpen in new window

But it seems that I can't sue that method here. How to do :)

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");
}

?>

Select allOpen in new window

I have fixed the double displaying.

Working on the links now - hop that I can fix it :)

Nope - have to give in to the links in this setup.

Can you help, Chris?

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'";

Select allOpen 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'];
}

Select allOpen 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; ?>

Select allOpen in new window

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

Select allOpen in new window

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'];
}

Select allOpen 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'));

Select allOpen in new window

That helped. Thanks a lot. It is working beautifully now :)

Cool :)