No PHP output for mailscanner msql

waelaltaqi · 09-30-2007, 04:40 PM

Hello all,
first off, i'm just starting to learn PHP so excuse my question if it didn't meet other's standards.

I'm trying to write a PHP script that will query Mailscanner mysql db for all mail received for a certain domain within the month period:

Code:

<?php
// Building mysql connection
$con = mysql_connect("localhost","root","mypass");
if (!$con)
  {
  die ('Could not connect:' . mysql_error());
  }
// Selecting Database
mysql_select_db("mailscanner", $con);

$allmail = mysql_query("SELECT COUNT(*) FROM maillog WHERE to_domain="mydomain.com" AND MONTH(date) = MONTH(CURRENT_DATE)");

//Printing output
echo $allmail;

mysql_close($con);
?>

i'm not quit sure why the script is not giving any ouput. I tried to output couple of simpler mysql queries and they worked fine. I also tested the sql query above within the mysql shell and it worked fine.After i pass this point i'm going to try writting similar scripts for spam and virus mail. I'm kind of stuck here and i need your guys help.

I will appreciate any help...

graemef · 09-30-2007, 08:07 PM

The mysql_query function returns a "resource" to the result set and not the result itself. So you can't actually print it directly. Here is an example from the PHP manual on how you can get the actual results of your query.

PHP Code:



<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');

// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    foreach ($line as $col_value) {
        echo "\t\t<td>$col_value</td>\n";
    }
    echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>

waelaltaqi · 10-01-2007, 10:33 AM

it worked for a simpler mysql script but not the one i'm intending to use. there is something wrong with the sql query or the way i wrote in the php script. the script right now is:

Code:

<?php
$con = mysql_connect("localhost","root","iwfcti4743");
if (!$con)
  {
    die('Could not connect:' . mysql_error());
  }

mysql_select_db("mailscanner", $con);

$result = mysql_query("select COUNT(*) as 'AllMSG' from maillog where to_domain="ctiemail.com" AND MONTH(date) = MONTH(CURRENT_D)");

while($row = mysql_fetch_array($result, MYSQL_ACCOS))
    {
    echo $row['ALLMSG'];
   }

mysql_close($con);
?>

when i look at /var/www/httpd/error_log, it comes back with this:

Code:

[client 127.0.0.1] PHP Parse error:  parse error, unexpected T_STRING in /var/www/html/test.php on line 10

i tried what you mentioned with much simpler queries and it worked. my problem now is obviously on the $result line...
i would appreciate further help and thanks in advance

graemef · 10-01-2007, 05:26 PM

You need to escape the double quotes in your string. That is:

PHP Code:



$result = mysql_query("select COUNT(*) as 'AllMSG' from maillog where to_domain=\"ctiemail.com\" AND MONTH(date) = MONTH(CURRENT_D)");

Otherwise when php comes to the double quote before ctiemail.com it treats that as a close quote to the whole string, and then it gets confused when it sees the label ctiemail.com and doesn't know how to proceed.