Mysql Query.

[Person]

I need to Query a database with the following.

 

SELECT SUM(1) AS clicks, SUM(`clcpc`), SUM(`chcpc`) FROM `nuke_pnAffiliate_clicktracking` WHERE `pl` = 'rpu' AND  `date` = '20070418'AND `cl` IN (SELECT DISTINCT(cl) FROM rpu_sales WHERE salesman = 'ryan')

 

What would be the best method ?

Thanks in advanced

[Barand]

What would be the best method ?

 

Don't know.

 

I don't know your table structure or what you trying to achieve.

[corbin]

you know SUM(1)  will always be 1 right?

[Person]

i think what it is doing is tracking clicks, and each line is a click. I dont know the tables either, but my guess would be that it is just adding lines.

[Barand]

you know SUM(1)  will always be 1 right?

 

Should give same result as COUNT(*), adding 1 for each row.

[Person]

i would think so... but how would i put that all in php ?

[Person]

ok i have the following now

 

<?php
$host = "localhost";
$user = "user";
$pass = "pass";
$dbname = "user";

$con = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);

$query = "SELECT SUM(1) AS clicks, SUM(`clcpc`), SUM(`chcpc`) FROM `nuke_pnAffiliate_clicktracking` WHERE `pl` = 'rpu' AND  `date` > '20070228'AND `cl` IN (SELECT DISTINCT(cl) FROM rpu_sales WHERE salesman = 'ryan')";
echo $query

$result= mysql_query($query);
$num_results = mysql_num_rows($result);
    
$row= mysql_fetch_assoc($result);

mysql_free_result($result);

echo $result

?>

 

But this is the error i get

 

<br />

<b>Parse error</b>:  parse error, unexpected T_VARIABLE, expecting ',' or ';' in                        <b>/home/brian/clicktracking.php</b> on line <b>13</b><br />

 

[Barand]

Missing ;  at end of a line

[john010117]

<?php
$host = "localhost";
$user = "user";
$pass = "pass";
$dbname = "user";

$con = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);

$query = "SELECT SUM(1) AS clicks, SUM(`clcpc`), SUM(`chcpc`) FROM `nuke_pnAffiliate_clicktracking` WHERE `pl` = 'rpu' AND  `date` > '20070228'AND `cl` IN (SELECT DISTINCT(cl) FROM rpu_sales WHERE salesman = 'ryan')";
echo $query;

$result= mysql_query($query);
$num_results = mysql_num_rows($result);
    
$row= mysql_fetch_assoc($result);

mysql_free_result($result);

echo $result

?>

[Person]

now thats fixed ... next error

 

 

<br />

<b>Warning</b>:  mysql_connect(): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in <b>/home/brian/clicktracking.php</b> on line <b>7</b><br />

2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)<BR>[brian@first brian]$

 

[john010117]

Are you with a host? Because I think the problem is on their end. Just check to see that you have the right conection info (host, db username, db password, db name).

[Barand]

see

 

http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html