Problem with query

[matthewdingley]

Hi everyone,

I've been trying to get this work for the last 2 hours but no fiddling will sort it out. This is a basic addition to EZBoo Webstats so that each results page will generate the amount of unique IP addresses during that period. I added the if/else statements to test out the query and I have changed the username, password and database values so I can put it on this forum. It is the following part that isn't working:

if($getIPCount=mysql_query($countIPSQL, $db))
{
echo "Could do it";
}
else
{
echo "Couldnt do it";
}

I just get couldnt do it printed. The full code for this section is below.

<?php
$today = getdate($reqdate); 
$day = $today['mday']; 
$month = $today['mon'];
if($month!="10"||$month!="11"||$month!="12")
{
$month="0" . $month;
}
$year = $today['year']; 
$searchForDate = $year.'-'.$month.'-'.$day;
$c = mysql_connect ("localhost", "my_username", "my_password");
  $db = mysql_select_db ("my_database");
$countIPSQL = "SELECT count(DISTINCT address) AS theIPCount FROM ez_logezboo WHERE date LIKE '$searchForDate%'";
if($getIPCount=mysql_query($countIPSQL, $db))
{
echo "Could do it";
}
else
{
echo "Couldnt do it";
}
mysql_close ($c);  

$IPCount=mysql_fetch_array($getIPCount);
echo "There have been ";
printf($IPCount["theIPCount"]);
echo " unique IP addresses in this period.";
?>

Any ideas would be greatly appreciated

Best regards,

Matthew Dingley

[tasistro]

Try...

if($getIPCount===mysql_query($countIPSQL, $db))

with 3 equal signs.

[matthewdingley]

That has worked to get rid of the "couldnt do it" messages but it hasn't actually made anything output from the query. I also had the same problem with the mysql_fetch_array function so I did the same, but it is still not doing anything.

Thanks,

Matthew

[dbchip2000]

Try the following query, I'm not sure if you can put DISTINCT where you had it

SELECT COUNT(address) AS theIPCount FROM ez_logezboo WHERE date LIKE '$searchForDate%' GROUP BY address

if this doesn't work you can always echo(mysql_error()) to see what went wrong instead of saying it couldn't be done

[tasistro]

Given...

if($getIPCount=mysql_query($countIPSQL, $db))
{
echo "Could do it";
}
else
{
echo "Couldnt do it";
}
mysql_close ($c);

$IPCount=mysql_fetch_array($getIPCount);
echo "There have been ";
printf($IPCount["theIPCount"]);
echo " unique IP addresses in this period.";

Uh, well the fact that you close the connection to the dbase before you get the data is very important here. Trye

$IPCount=mysql_fetch_array($getIPCount);
echo "There have been ";
printf($IPCount["theIPCount"]);
echo " unique IP addresses in this period.";
mysql_close ($c);

Even better insert all that in the could do it
if($getIPCount=mysql_query($countIPSQL, $db))
{
echo "Could do it";
$IPCount=mysql_fetch_array($getIPCount);
echo "There have been ";
printf($IPCount["theIPCount"]);
echo " unique IP addresses in this period.";
}
else
{
echo "Couldnt do it";
}
mysql_close ($c);

[matthewdingley]

I've already tried moving the mysql_close function to the end of the code (where it is now) and it still doesn't work. Also, I have tested out the query in phpMyAdmin and it works fine, so there must be something wrong with the PHP behind it.

Thanks,

Matthew

[tasistro]

try changing the echo command to

echo "There have been ".$IPCount["theIPCount"]." unique IP addresses in this period.";

[matthewdingley]

That's not worked either I'm afraid.

Thanks,

Matthew Dingley

[HalfaBee]

You can make the date using
$searchForDate = date( "Y-m-d",$reqdate );
Just a bit easier than your method.

Is the date the very first char of the string you are trying to match?

Try echoing your scripts SQL to see what it is actually trying to execute..

Halfabee

[matthewdingley]

Sorry, that hasn't worked either. The code I am now using is:

<?php
$c=mysql_connect("localhost", "my_username", "my_password");
$db=mysql_select_db("my_database");
$searchForDate = date( "Y-m-d",$reqdate );
$theSQL="SELECT COUNT( DISTINCT address ) AS theIPCount FROM ez_logezboo WHERE date LIKE '$searchForDate%'";
$getIPCount=mysql_query($theSQL, $db);
$IPCount=mysql_fetch_array($getIPCount);
echo "There have been ". $IPCount["theIPCount"] ." unique visitors in this period";
mysql_close ($c);  

?>

I try putting an or die command after the mysql_query function like this:

$getIPCount=mysql_query($theSQL, $db) or die("Invalid query: " . mysql_error());

But the mysql_error returns nothing.

I have moved all the code into it's own page to make sure that nothing within the main page is affecting it. I also tried echoing the SQL and that worked fine and the SQL returned the right value when I tested it in phpMyAdmin.

Within the database, the string I am trying to match follows the same pattern that I have tested, and it works fine.

Thanks,

Matthew