cs1h Posted November 4, 2008 Share Posted November 4, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/ Share on other sites More sharing options...
akitchin Posted November 4, 2008 Share Posted November 4, 2008 have you tried looking at what's in $row? is it putting anything into that variable? Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-682479 Share on other sites More sharing options...
.josh Posted November 4, 2008 Share Posted November 4, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-682534 Share on other sites More sharing options...
cs1h Posted November 5, 2008 Author Share Posted November 5, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-682754 Share on other sites More sharing options...
.josh Posted November 5, 2008 Share Posted November 5, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-682836 Share on other sites More sharing options...
cs1h Posted November 5, 2008 Author Share Posted November 5, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-683000 Share on other sites More sharing options...
.josh Posted November 5, 2008 Share Posted November 5, 2008 Okay did you try entering one of those query strings directly into your database to see if it returns expected results? Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-683004 Share on other sites More sharing options...
cs1h Posted November 5, 2008 Author Share Posted November 5, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-683014 Share on other sites More sharing options...
.josh Posted November 5, 2008 Share Posted November 5, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-683019 Share on other sites More sharing options...
cs1h Posted November 5, 2008 Author Share Posted November 5, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-683034 Share on other sites More sharing options...
.josh Posted November 5, 2008 Share Posted November 5, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-683063 Share on other sites More sharing options...
cs1h Posted November 5, 2008 Author Share Posted November 5, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-683073 Share on other sites More sharing options...
.josh Posted November 5, 2008 Share Posted November 5, 2008 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 "..." Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-683077 Share on other sites More sharing options...
cs1h Posted November 5, 2008 Author Share Posted November 5, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-683088 Share on other sites More sharing options...
.josh Posted November 5, 2008 Share Posted November 5, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/131388-solved-problem-with-search-queary-and-avg/#findComment-683094 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.