Help with an SQL query

[coffman]

Hi,

 

I am trying to do a complex sql query and wondered if anyone could help me out.

 

Here is the query I am running:

SELECT c.customers_id,c.customers_email_address,c.customers_firstname,c.customers_lastn
ame,ci.customers_info_date_of_last_logon,ci.customers_info_date_account_created, o.customers_id FROM customers AS c, customers_info AS ci, orders AS o WHERE c.customers_id = ci.customers_info_id AND c.customers_id = o.customers_id ORDER BY  ci.customers_info_date_account_created;

 

I get a listing with the info and a row for each order from each customer. What I want is to just list the count of orders for each customers. I know that I could probably create several queries to do what I want. I just wondered if there was a simpler way (am sure there is).

 

Thanks.

-Michael

[coffman]

Nevermind. I decided to hack it with perl and it ran faster than I thought....

use DBI;

$userid = 'dbuser';
$passwd = 'dbpasswd';
$db	 = 'dbname';
$host   = 'localhost';
$connectionInfo = "DBI:mysql:$db:$host:$port";

$dbh = DBI->connect($connectionInfo,$userid,$passwd);

$query = "SELECT c.customers_id,c.customers_email_address,c.customers_firstname,c.customers_lastn
ame,UNIX_TIMESTAMP(ci.customers_info_date_of_last_logon),UNIX_TIMESTAMP(ci.customers_info_date_account_created) FROM customers AS c, customers_info AS ci WHERE c.customers_id = ci.customers_info_id ORDER BY  ci.customers_info_date_account_created";

$sth = $dbh->prepare($query);
$sth->execute();
$sth->bind_columns(undef, \$cid, \$cemail, \$cfn, \$cln, \$last_login, \$created);

while($sth->fetch()) {
 $sth->bind_columns(undef, \$cid, \$cemail, \$cfn, \$cln, \$last_login, \$created);

 $sth2 = $dbh->prepare("SELECT * FROM orders where customers_id=$cid");
 $sth2->execute();

 $count = $sth2->rows;

 print "$count, $cid, $cemail, $cfn, $cln, $created, $last_login\n"
}

[germ]

Hey!

 

Another perl geek!

 

I'm impressed.

 

*High five*

 

:lol:

 

I used to do a lot with perl until I got involved with osC.

:)

[coffman]

Hey!

 

Another perl geek!

 

I'm impressed.

 

*High five*

 

:lol:

 

I used to do a lot with perl until I got involved with osC.

:)

 

:) --- Love that perl!

[axioma]

how do you "hack it with perl "?

[germ]

how do you "hack it with perl "?

He just means he wrote a program in perl that does what he wanted.

;)