LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 03-30-2005, 10:07 PM   #1
DropHit
Member
 
Registered: Oct 2003
Posts: 95

Rep: Reputation: 15
Need mysql query help


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
 
Old 03-31-2005, 06:23 AM   #2
david_ross
Moderator
 
Registered: Mar 2003
Location: Scotland
Distribution: Slackware, RedHat, Debian
Posts: 12,047

Rep: Reputation: 64
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.
 
Old 03-31-2005, 11:34 AM   #3
DropHit
Member
 
Registered: Oct 2003
Posts: 95

Original Poster
Rep: Reputation: 15
Getting wrong count

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
 
Old 03-31-2005, 12:15 PM   #4
david_ross
Moderator
 
Registered: Mar 2003
Location: Scotland
Distribution: Slackware, RedHat, Debian
Posts: 12,047

Rep: Reputation: 64
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?
 
Old 03-31-2005, 12:37 PM   #5
DropHit
Member
 
Registered: Oct 2003
Posts: 95

Original Poster
Rep: Reputation: 15
Thats IT!

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

Last edited by DropHit; 03-31-2005 at 12:42 PM.
 
Old 03-31-2005, 12:48 PM   #6
david_ross
Moderator
 
Registered: Mar 2003
Location: Scotland
Distribution: Slackware, RedHat, Debian
Posts: 12,047

Rep: Reputation: 64
Is there any way you could make the tabe data available.

Is that the version of MySQL you are using?
 
Old 03-31-2005, 01:28 PM   #7
DropHit
Member
 
Registered: Oct 2003
Posts: 95

Original Poster
Rep: Reputation: 15
Sure thing

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
 
Old 04-01-2005, 12:32 PM   #8
DropHit
Member
 
Registered: Oct 2003
Posts: 95

Original Poster
Rep: Reputation: 15
Anyone else?

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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL++ Query jimbob8483 Programming 1 12-15-2004 07:47 AM
MySQL Query Help Gerardoj Programming 1 06-10-2004 10:54 PM
Mysql Error: Lost Connection to Mysql during query ramnath Programming 5 11-18-2003 01:27 PM
mysql query ????? shaahul Linux - Newbie 1 09-06-2003 04:28 AM
mysql query !!! hitesh_linux Linux - General 1 02-03-2003 04:36 AM


All times are GMT -5. The time now is 12:33 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration