Need mysql query help

DropHit · 03-30-2005, 09:07 PM

I have this

$sql2 = "SELECT ImportedPaidLeads.SubmitDate, count(*) as groupcount from ImportedPaidLeads, temp_table WHERE ImportedPaidLeads.ssn = temp_table.ssn AND ImportedPaidLeads.SubmitDate BETWEEN '".$_POST['export_date']."' AND '".$_POST['export_date2']."' group by SubmitDate";

And it is working but it is not selecting DISTINCT records, menaing it is showing duplicates. I have tried the DISTINCT in there but then it only shows one record per group, need tomse gurus to help me make this select only distnct records.

Z

david_ross · 03-31-2005, 05:23 AM

Can you post an example of what you get - it should only produce distinct records but if you have a one to many relationship then the one will be returned in an array for each of the many.

DropHit · 03-31-2005, 10:34 AM

OK well results look like this:

For the period 2005-3-20 to 2005-3-26

There have been 167 apps
There have been 75 leads

Submit Date Leads
2005-03-20 4
2005-03-21 15
2005-03-22 22
2005-03-23 27
2005-03-24 17
2005-03-25 10
2005-03-26 3

Basically the counts are wrong, due to dup records on the one side of the join, tha actual distinct matching count is obtained with this:

Code:

$sql3 = "SELECT DISTINCT ImportedPaidLeads.ssn from ImportedPaidLeads, temp_table WHERE ImportedPaidLeads.ssn = temp_table.ssn AND ImportedPaidLeads.SubmitDate BETWEEN '".$_POST['export_date']."' AND '".$_POST['export_date2']."'";  
mysql_query ($sql3) or die(mysql_error());
$res3 = mysql_query($sql3);

$total_leads3 = mysql_num_rows($res3);

That returns the 75 but if you add the count up on each row above you get a higher number becuase of duplicate rows, should i use another type of JOIN in the first code to allow for distinct?

Z

david_ross · 03-31-2005, 11:15 AM

I'm still not sure what you are trying to get as a result. Do you not want the number of leads associated with a specific date? What count would you like beside each date?

DropHit · 03-31-2005, 11:37 AM

Yes, i want the CORRECT count for each date, the DISTINCT count for each date as shown above the Count for each date is not Distinct, rather it is including duplicates in the count for each date.

Thats why the actual no dup count is 75 and the sum of the date group counts is higher, i just found this:

http://bugs.mysql.com/bug.php?id=8821

But dunno if it applies.

Z

this is what i am using to count the groups:

Code:

$sql2 = "SELECT ImportedPaidLeads.SubmitDate, count(DISTINCT ImportedPaidLeads.ssn) as groupcount from ImportedPaidLeads, temp_table WHERE ImportedPaidLeads.ssn = temp_table.ssn AND ImportedPaidLeads.SubmitDate BETWEEN '".$_POST['export_date']."' AND '".$_POST['export_date2']."' group by SubmitDate";

david_ross · 03-31-2005, 11:48 AM

Is there any way you could make the tabe data available.

Is that the version of MySQL you are using?

DropHit · 03-31-2005, 12:28 PM

MySQL version 3.23.58

Table ImportedPaidLeads looks like:

Lender | SubmitDate | ssn
LenderOne 2005-03-13 550-55-5555
LenderOne 2005-03-13 550-55-5005
LenderOne 2005-03-15 550-55-5215
LenderOne 2005-03-15 550-55-5134 ---Dups
LenderOne 2005-03-15 550-55-5134 ---Dups
LenderOne 2005-03-17 550-55-5005
LenderOne 2005-03-17 550-55-5215 ---Dups

Table temp_table looks like

ssn
550-55-5555
550-55-5005
550-55-5215
550-55-5214
550-55-5212
550-55-5211

Should be no dups in table_temp as i use distinct to grab it from one dbase and put it into my temp_table see full code below:

Code:

<?php
session_start();

$MyArray = "";


$export_date = $_POST['export_date'];
session_register ("export_date");

$export_date2 = $_POST['export_date2'];
session_register ("export_date2");

define('db_host','localhost');
  define('db_user','****');
  define('db_pass','****');
  define(db_link, mysql_connect(db_host,db_user,db_pass));
  define('db_name',''.$_SESSION['db_name'].'');
  mysql_select_db(db_name);
  
//grab ssn numbers from long_form table no dups  
$sql = "SELECT DISTINCT ssn_part_1, ssn_part_2, ssn_part_3 FROM long_form WHERE signup_date BETWEEN '".$_POST['export_date']."' AND '".$_POST['export_date2']."'";                 
$res = mysql_query($sql);
$total_rows = mysql_num_rows($res);


// loop the recordset create ssn array
while ($article = mysql_fetch_assoc($res)) { 
$MyArray[] .= $article['ssn_part_1'] . "-" . $article['ssn_part_2'] . "-" . $article['ssn_part_3'];

}

//connect and make temp table

$hostname_main = "localhost";
$database_main = "******";
$username_main = "****";
$password_main = "*****";
$main = mysql_pconnect($hostname_main, $username_main, $password_main) or die(mysql_error());

mysql_select_db($database_main, $main);

$temp_table = "CREATE TEMPORARY TABLE `temp_table` (`ssn` varchar(15) default NULL) TYPE=MyISAM";
mysql_query($temp_table);


//insert ssn array into temp table
foreach ($MyArray AS $value) { 
$sql2 = "INSERT INTO temp_table (ssn) VALUES ('".$value."')";
mysql_query ($sql2) or die(mysql_error()); 


}
 
//compare temp_table and paidleads table and select matching data from paidleads table to get leads

$sql2 = "SELECT ImportedPaidLeads.SubmitDate, count(DISTINCT temp_table.ssn) as groupcount from ImportedPaidLeads, temp_table WHERE ImportedPaidLeads.ssn = temp_table.ssn AND ImportedPaidLeads.SubmitDate BETWEEN '".$_POST['export_date']."' AND '".$_POST['export_date2']."' group by SubmitDate";  
mysql_query ($sql2) or die(mysql_error());
$res2 = mysql_query($sql2);

$sql3 = "SELECT count(DISTINCT temp_table.ssn) as allleads from ImportedPaidLeads, temp_table WHERE ImportedPaidLeads.ssn = temp_table.ssn AND ImportedPaidLeads.SubmitDate BETWEEN '".$_POST['export_date']."' AND '".$_POST['export_date2']."'";  
mysql_query ($sql3) or die(mysql_error());
$res3 = mysql_query($sql3);
$showleads = mysql_fetch_assoc ($res3);



$sql4 = "SELECT count(DISTINCT ssn) as allapps from temp_table"; //get the ammount of products in the db
mysql_query ($sql4) or die(mysql_error());
$res4 = mysql_query($sql4);

$showapps = mysql_fetch_assoc ($res4)



?>

The i use this to display the result:

Code:

<p align="center" class="Content">For the period <?php echo $export_date ?> to <?php echo $export_date2 ?></p>

<p align="center" class="Content">There have been <?php echo $showapps['allapps']; ?> apps <br>

<?php while ($showleads = mysql_fetch_assoc ($res2)) 
{ ?>
   

                  <?php echo $showleads['SubmitDate']; ?>
                    
                  <?php echo $showleads['groupcount']; ?>
                    
                  <? php }  
				
				$dtemp_table = "DROP TABLE temp_table ";
				mysql_query($dtemp_table);
				mysql_free_result($res4);
				mysql_free_result($res);?>

Also, this seems slow, and sometime i get doubled results meaning, it will count everything twice.

Thx for the help i owe ya

Z

DropHit · 04-01-2005, 11:32 AM

It seems this may be a bug in mysql, or possibly my version. I have tried 3-4 diff ways to do this and have had now luck!

Anyone else wanna help out?

Z