[SOLVED] Problem with search queary and AVG

[cs1h]

Hi,

 

I have a script that gathers data from several databases and compiles it all together. Most of it works fine but there is a section that is meant to a bunch of results from a table and find out the average. The script is below and I have highlighted the problem area.

 

<?php $db=mysql_connect("localhost","xxxx","xxxx");
mysql_select_db("xxxx") or die("Unable to select database");
$result=mysql_query("SELECT name,country,Title,Abstract,added,version,code1,type FROM `master` WHERE (Email = '$username_b') ORDER BY id DESC LIMIT 6") or die(mysql_error());
if(!$result){$sw=0;$error="You have not written any articles yet";}
else
{
  $sw=1;
  $name=array();
  $country=array();
  $Title=array();
  $Abstract=array();
  $added=array();
  $version=array();
  $code1=array();
  $type=array();
  while($row=mysql_fetch_array($result))
  {
   $name[]=$row["name"];
   $country[]=$row["country"];
   $Title[]=$row["Title"];
   $Abstract[]=$row["Abstract"];
   $added[]=$row["added"];
   $version[]=$row["version"];
   $code1=$row["code1"];
   $type[]=$row["type"];
   echo $row["name"];
   }
  mysql_close($db);
  $db=mysql_connect("localhost","yyyy","yyy");
  mysql_select_db("yyyym") or die("Unable to select database");
  for($i=0;$i<count($Title);$i++)
  {  
//this next bit is the issue
   $result=mysql_query("SELECT AVG(rating_num) FROM master WHERE code1='".$code1[$i]."'")or die(mysql_error());
   if(!$result){$sw=0;$error="No results please try a different <a href=reports.php>search 2</a>.";}
   else
   {
    $sw=1;
$rating=array();
while($row=mysql_fetch_array($result))
  {
   $rating[]=$row["AVG(rating_num)"];
   echo $row["AVG(rating_num)"];
   }
//down to hear
mysql_close($db);
  $db=mysql_connect("localhost","wwww","wwww");
  mysql_select_db("wwww") or die("Unable to select database");
  for($i=0;$i<count($Title);$i++)
  {
   $result=mysql_query("SELECT country,alias FROM country_list WHERE country='".$country[$i]."'")or die(mysql_error());
   if(!$result){$sw=0;$error="No results please try a different <a href=reports.php>search 3</a>.";}
   else
   {
    $sw=1;
    $row=mysql_fetch_array($result);
     print "<div><div id=\"publish_edit_delete\"><div id=\"pagebreak_left\"><span class=\"black_small\">Not Published - Publish Now</span></div><div id=\"pagebreak_right\"><span class=\"black_small\"><a href=\"work.php\">Edit - Delete</a></span></div>
</div><div id=\"abstract_edit\" \"margin-bottom:0;\"><a href=more2.php?id=$More><div id=abstract_image class=".$row["country"].">".$row["alias"]."<br />
{$type[$i]}</div></a><div class=\"abstract_header\"><span class=\"abstract_title\"><a href=more2.php?id=$More>{$Title[$i]}</a></span><span class=\"comment_stars\"><img src=\"3_star_w.gif\"/></span></div><div class=\"abstract_text\"><span class=\"black_abstract_small\">{$Abstract[$i]}</span></div><div class=\"abstract_town_link\"><div class=\"abstract_town\"><span class=\"grey_abstract_capitals\">{$name[$i]}</span></div><div class=\"abstract_link\"><span class=\"grey_small\">08/08/08</span></div></div></div>
<div id=\"pagespace\"></div></div>";
    }
   }
  }
}
If($sw==0){echo $error;}
if($db){mysql_close($db);}
} ?>

 

It doesn't come up with the error, but neither does it produce results.

 

Can anyone help?

 

Thanks,

Colin

[akitchin]

have you tried looking at what's in $row?  is it putting anything into that variable?

[.josh]

dunno, but maybe this has something to do with it:

 

Earlier, you have

 

$code1=$row["code1"];

 

but all of your other assignments would suggest it should be

 

$code1[]=$row["code1"];

 

Because later on in your loop you attempt to use it as if it were an array, when you aren't actually making an array out of it. 

[cs1h]

Hi,

 

You were right about the array so I changed it but its still not working.

 

Can anyone help further?

 

All help is very much appreciated.

 

Thanks,

Colin

[.josh]

Still no errors or anything?

 

Change

 

$result=mysql_query("SELECT AVG(rating_num) FROM master WHERE code1='".$code1[$i]."'")or die(mysql_error());

 

to

 

$sql = "SELECT AVG(rating_num) FROM master WHERE code1='".$code1[$i]."'";
echo $sql;
$result=mysql_query($sql)or die(mysql_error());

 

Does $sql echo out what you expect it to echo out? Does entering in the echoed out query string directly into your db yield desired results?

[cs1h]

I made that change and played about with it for a bit, it's coming out with the expected sql but it's still not working out the averages.

 

The code is now

 

<?php $db=mysql_connect("localhost","x","x");
mysql_select_db("x") or die("Unable to select database");
$result=mysql_query("SELECT name,country,Title,Abstract,added,version,code1,type FROM `master` WHERE (Email = '$username_b') ORDER BY id DESC LIMIT 8") or die(mysql_error());
if(!$result){$sw=0;$error="You have not written any articles yet";}
else
{
  $sw=1;
  $name=array();
  $country=array();
  $Title=array();
  $Abstract=array();
  $added=array();
  $version=array();
  $code1v2=array();
  $type=array();
  while($row=mysql_fetch_array($result))
  {
   $name[]=$row["name"];
   $country[]=$row["country"];
   $Title[]=$row["Title"];
   $Abstract[]=$row["Abstract"];
   $added[]=$row["added"];
   $version[]=$row["version"];
   $code1v2[]=$row["code1"];
   $type[]=$row["type"];
   }
  mysql_close($db);
  $db=mysql_connect("localhost","s","s");
  mysql_select_db("s") or die("Unable to select database");
  for($i=0;$i<count($Title);$i++)
  {
   $sql = "SELECT AVG(rating_num) FROM master WHERE code1='".$code1v2[$i]."'";
echo $sql;
$result=mysql_query($sql)or die(mysql_error());
   if(!$result){$sw=0;$error="No results please try a different <a href=reports.php>search 2</a>.";}
   else
   {
    $sw=1;
$rating=array();
while($row=mysql_fetch_array($result))
  {
   $rating[]=$row["AVG(rating_num)"];
   }
   }
}
mysql_close($db);
  $db=mysql_connect("localhost","d","d");
  mysql_select_db("d") or die("Unable to select database");
  for($i=0;$i<count($Title);$i++)
  {
   $result=mysql_query("SELECT country,alias FROM country_list WHERE country='".$country[$i]."'")or die(mysql_error());
   if(!$result){$sw=0;$error="No results please try a different <a href=reports.php>search 3</a>.";}
   else
   {
    $sw=1;
    $row=mysql_fetch_array($result);
     print "<div><div id=\"publish_edit_delete\"><div id=\"pagebreak_left\"><span class=\"black_small\">Not Published - Publish Now</span></div><div id=\"pagebreak_right\"><span class=\"black_small\"><a href=\"work.php\">Edit - Delete</a></span></div>
</div><div id=\"abstract_edit\" \"margin-bottom:0;\"><a href=more2.php?id=$More><div id=abstract_image class=".$row["country"].">".$row["alias"]."<br />
{$type[$i]}</div></a><div class=\"abstract_header\"><span class=\"abstract_title\"><a href=more2.php?id=$More>{$Title[$i]}</a></span><span class=\"comment_stars\">{$rating[$i]}</span></div><div class=\"abstract_text\"><span class=\"black_abstract_small\">{$Abstract[$i]}</span></div><div class=\"abstract_town_link\"><div class=\"abstract_town\"><span class=\"grey_abstract_capitals\">{$name[$i]}</span></div><div class=\"abstract_link\"><span class=\"grey_small\">08/08/08</span></div></div></div>
<div id=\"pagespace\"></div></div>";
    }
   }
  
If($sw==0){echo $error;}
if($db){mysql_close($db);}
}?>

 

The output from the recommended alteration looks like this

 

SELECT AVG(rating_num) FROM xx WHERE code1='1249957745'SELECT AVG(rating_num) FROM xx WHERE code1='1167940224'SELECT AVG(rating_num) FROM xx WHERE code1='32732'SELECT AVG(rating_num) FROM xx WHERE code1='78432'SELECT AVG(rating_num) FROM xx WHERE code1='3131'SELECT AVG(rating_num) FROM xx WHERE code1='16945'SELECT AVG(rating_num) FROM xx WHERE code1='21705'

 

Can anyone help me anymore?

 

I appreciate all help.

 

Thanks,

Colin

[.josh]

Okay did you try entering one of those query strings directly into your database to see if it returns expected results?

[cs1h]

Yes, I put it into sql and got some results.

 

Some of the queries come up with Null because they have not been rated yet, but the ones with entries come up with the right results. Could this be affecting it?

 

Thanks,

Colin

[.josh]

Nope, shouldn't affect it.  Okay next, change this:

 

  while($row=mysql_fetch_array($result))
  {
   $rating[]=$row["AVG(rating_num)"];
   }
   }

 

to this:

 

  while($row=mysql_fetch_array($result))
  {
   $rating[]=$row["AVG(rating_num)"];
   }
   print_r($rating);
   }

 

does it echo out the expected results from the query?

[cs1h]

It gives the results

 

Array ( [0] => ) Array ( [0] => ) Array ( [0] => ) Array ( [0] => ) Array ( [0] => 4.0000 ) Array ( [0] => ) Array ( [0] => )

 

but I don't really understand it, the 0 results are where there is no matching rows in the table and the 4.000 is the one result where there was matching rows in the table.

 

Can you see anything wrong with it?

 

Thanks for the help so far,

Colin

[.josh]

Okay first off, get rid of this line:

 

   $rating=array();

 

It's not necessary to declare an array but more importantly, you have it inside your for(..count($Title)) loop, which is causing it to overwrite the array. 

 

That might not be the entire problem, but it's a step closer.

 

[cs1h]

Hi, made that change and it came up with this,

 

Array ( [0] => ) Array ( [0] => [1] => ) Array ( [0] => [1] => [2] => ) Array ( [0] => [1] => [2] => [3] => ) Array ( [0] => [1] => [2] => [3] => [4] => 4.0000 ) Array ( [0] => [1] => [2] => [3] => [4] => 4.0000 [5] => ) Array ( [0] => [1] => [2] => [3] => [4] => 4.0000 [5] => [6] => )

 

I don't really understand what this means.

 

It's also now showing the figure 4.0000 in the correct result in the right place.

 

Thanks for the help,

Colin

[.josh]

Okay the print_r($rating); is echoing out all the elements of your array each time your for loop iterates.  So we can see from that, that on the last iteration, you have an array with 6 elements with no results except for one, which is what you said there should be.  So you can now remove the print_r($rating); from your code, because you are now getting an array of expected results from your loops. 

 

So...is it still not displaying it in here?

 

  for($i=0;$i<count($Title);$i++)
  {
   $result=mysql_query("SELECT country,alias FROM country_list WHERE country='".$country[$i]."'")or die(mysql_error());
   if(!$result){$sw=0;$error="No results please try a different <a href=reports.php>search 3</a>.";}
   else
   {
    $sw=1;
    $row=mysql_fetch_array($result);
     print "<div><div id=\"publish_edit_delete\"><div id=\"pagebreak_left\"><span class=\"black_small\">Not Published - Publish Now</span></div><div id=\"pagebreak_right\"><span class=\"black_small\"><a href=\"work.php\">Edit - Delete</a></span></div>
</div><div id=\"abstract_edit\" \"margin-bottom:0;\"><a href=more2.php?id=$More><div id=abstract_image class=".$row["country"].">".$row["alias"]."<br />
{$type[$i]}</div></a><div class=\"abstract_header\"><span class=\"abstract_title\"><a href=more2.php?id=$More>{$Title[$i]}</a></span><span class=\"comment_stars\">{$rating[$i]}</span></div><div class=\"abstract_text\"><span class=\"black_abstract_small\">{$Abstract[$i]}</span></div><div class=\"abstract_town_link\"><div class=\"abstract_town\"><span class=\"grey_abstract_capitals\">{$name[$i]}</span></div><div class=\"abstract_link\"><span class=\"grey_small\">08/08/08</span></div></div></div>
<div id=\"pagespace\"></div></div>";
    }
   }

 

If not, then next problem will probably be working on that horrible mess of a print "..."

[cs1h]

Its displaying it in one of the results (where the average was 4.0000) but its not displaying the results where the query result is 0. Is that just because in the

print_r($rating);

it doesn't show any thing (if its 0).

 

Thanks for all the help,

 

Colin

[.josh]

Right.  It's not displaying anything for the other ones, because there was no average to calculate from the query. As you said yourself:

 

Some of the queries come up with Null because they have not been rated yet, but the ones with entries come up with the right results.

 

If you want it to print some kind of default or 0 or something, you can add a condition in the previous loop we were working on, to assign something to that element if nothing was returned.