Lil Puppy Posted July 2, 2008 Share Posted July 2, 2008 I have 2 different php files that technically accomplish the same output but both fail. I'm quite new to php and mysql, I'm usually just modifying existing code but I need to make this and can't find any examples to work from.What I'm trying to do is make a table that will hold numerical data for the prices of various dyes (for a video game fansite) and the output goes into a table on a webpage(once I get it working right) for the average of the column for each dye that isn't null. I already have the input form to put the data in the table, but I can't seem to get php to output anything correctly and I can't see any errors(though I'm sure there are plenty).I only need to get one of the files to work right so I know what I'm doing wrong.Hope someone can help point out some errors ThanksFile 1: Using more complex script <?phpinclude("config.inc.php");$link = mysql_connect($db_host,$db_user,$db_pass);if(!$link) die ('Could not connect to database: '.mysql_error());mysql_select_db($db_name,$link);//Arrays for color and table field names$color = array("Black","White","Silver","Red","Blue","Green","Yellow","Orange","Purple","Brown");$entry = array("field_1","field_2","field_3","field_4","field_5","field_6","field_7","field_8","field_9","field_10");echo "<html><body>"; //I think I need this//Loop through the table field names and get averages of numerical data in columnsfor ($i=0; $i<=9; $i++){ $xx = $color[$i]; //For Switch function $yy = $entry[$i]; //For Select Avg below $price = mysql_query("SELECT AVG(' . $yy . ') FROM ' . $db_table . '"); echo $xx . " " . $yy . " " . $i; //testing to see if it actually loops switch ($xx) { case "Black": echo $xx . ":" . $price . "k<br/>"; break; case "White": echo $xx . ":" . $price . "k<br/>"; break; case "Brown": echo $xx . ":" . $price . "g"; break; default: echo $xx . ":" . $price . "g<br/>"; }}echo "</body></html>";mysql_close($link);?> File 2: Using only DB calls <html><body><?phpinclude("config.inc.php");$link = mysql_connect($db_host,$db_user,$db_pass);if(!$link) die ('Could not connect to database: '.mysql_error());mysql_select_db($db_name,$link);echo "Black: " . mysql_query("SELECT avg(`field_1`) FROM `pre_dye_prices` WHERE field_1>0") . "<br>";echo "White: " . mysql_query("SELECT avg(`field_2`) FROM `pre_dye_prices` WHERE field_2>0") . "<br>";echo "Silver: " . mysql_query("SELECT avg(`field_3`) FROM `pre_dye_prices` WHERE field_3>0") . "<br>";echo "Red: " . mysql_query("SELECT avg(`field_4`) FROM `pre_dye_prices` WHERE field_4>0") . "<br>";echo "Blue: " . mysql_query("SELECT avg(`field_5`) FROM `pre_dye_prices` WHERE field_5>0") . "<br>";echo "Green: " . mysql_query("SELECT avg(`field_6`) FROM `pre_dye_prices` WHERE field_6>0") . "<br>";echo "Yellow: " . mysql_query("SELECT avg(`field_7`) FROM `pre_dye_prices` WHERE field_7>0") . "<br>";echo "Orange: " . mysql_query("SELECT avg(`field_8`) FROM `pre_dye_prices` WHERE field_8>0") . "<br>";echo "Purple: " . mysql_query("SELECT avg(`field_9`) FROM `pre_dye_prices` WHERE field_9>0") . "<br>";echo "Brown: " . mysql_query("SELECT avg(`field_10`) FROM `pre_dye_prices` WHERE field_10>0");mysql_close($link);?></body></html> And the output I get from each one of those is: File1:"; //I think I need this� ⼀⼀䰀漀漀瀀 琀栀爀漀甀最栀 琀栀攀 琀愀戀氀攀 昀椀攀氀搀 渀愀洀攀猀 愀渀搀 最攀琀 愀瘀攀爀愀最攀猀 漀昀 渀甀洀攀爀椀挀愀氀 搀愀琀愀 椀渀 挀漀氀甀洀渀猀� for ($i=0; $i<=9; $i++){ऀ␀砀砀 㴀 ␀挀漀氀漀爀嬀␀椀崀㬀 ⼀⼀䘀漀爀 匀眀椀琀挀栀 昀甀渀挀琀椀漀渀� $yy = $entry[$i]; //For Select Avg belowऀ␀瀀爀椀挀攀 㴀 洀礀猀焀氀开焀甀攀爀礀⠀∀匀䔀䰀䔀䌀吀 䄀嘀䜀⠀✀ ⸀ ␀礀礀 ⸀ ✀⤀ 䘀刀伀䴀 ✀ ⸀ ␀搀戀开琀愀戀氀攀 ⸀ ✀∀⤀㬀� echo $xx . " " . $yy . " " . $i; //testing to see if it actually loopsऀ猀眀椀琀挀栀 ⠀␀砀砀⤀� {ऀ挀愀猀攀 ∀䈀氀愀挀欀∀㨀� echo $xx . ":" . $price . "k";ऀ戀爀攀愀欀㬀� case "White":ऀऀ攀挀栀漀 ␀砀砀 ⸀ ∀㨀∀ ⸀ ␀瀀爀椀挀攀 ⸀ ∀欀㰀戀爀⼀㸀∀㬀� break;ऀ挀愀猀攀 ∀䈀爀漀眀渀∀㨀� echo $xx . ":" . $price . "g";ऀ戀爀攀愀欀㬀� default:ऀऀ攀挀栀漀 ␀砀砀 ⸀ ∀㨀∀ ⸀ ␀瀀爀椀挀攀 ⸀ ∀最㰀戀爀⼀㸀∀㬀� }紀� 攀挀栀漀 ∀㰀⼀戀漀搀礀㸀㰀⼀栀琀洀氀㸀∀㬀� � mysql_close($link);㼀㸀--------------------------------File2:㰀戀漀搀礀㸀� 0") . "";攀挀栀漀 ∀匀椀氀瘀攀爀㨀 ∀ ⸀ 洀礀猀焀氀开焀甀攀爀礀⠀∀匀䔀䰀䔀䌀吀 愀瘀最⠀怀昀椀攀氀搀开㌀怀⤀ 䘀刀伀䴀 怀瀀爀攀开搀礀攀开瀀爀椀挀攀猀怀 圀䠀䔀刀䔀 昀椀攀氀搀开㌀㸀 ∀⤀ ⸀ ∀㰀戀爀㸀∀㬀� echo "Red: " . mysql_query("SELECT avg(`field_4`) FROM `pre_dye_prices` WHERE field_4>0") . "";攀挀栀漀 ∀䈀氀甀攀㨀 ∀ ⸀ 洀礀猀焀氀开焀甀攀爀礀⠀∀匀䔀䰀䔀䌀吀 愀瘀最⠀怀昀椀攀氀搀开㔀怀⤀ 䘀刀伀䴀 怀瀀爀攀开搀礀攀开瀀爀椀挀攀猀怀 圀䠀䔀刀䔀 昀椀攀氀搀开㔀㸀 ∀⤀ ⸀ ∀㰀戀爀㸀∀㬀� echo "Green: " . mysql_query("SELECT avg(`field_6`) FROM `pre_dye_prices` WHERE field_6>0") . "";攀挀栀漀 ∀夀攀氀氀漀眀㨀 ∀ ⸀ 洀礀猀焀氀开焀甀攀爀礀⠀∀匀䔀䰀䔀䌀吀 愀瘀最⠀怀昀椀攀氀搀开㜀怀⤀ 䘀刀伀䴀 怀瀀爀攀开搀礀攀开瀀爀椀挀攀猀怀 圀䠀䔀刀䔀 昀椀攀氀搀开㜀㸀 ∀⤀ ⸀ ∀㰀戀爀㸀∀㬀� echo "Orange: " . mysql_query("SELECT avg(`field_8`) FROM `pre_dye_prices` WHERE field_8>0") . "";攀挀栀漀 ∀倀甀爀瀀氀攀㨀 ∀ ⸀ 洀礀猀焀氀开焀甀攀爀礀⠀∀匀䔀䰀䔀䌀吀 愀瘀最⠀怀昀椀攀氀搀开㤀怀⤀ 䘀刀伀䴀 怀瀀爀攀开搀礀攀开瀀爀椀挀攀猀怀 圀䠀䔀刀䔀 昀椀攀氀搀开㤀㸀 ∀⤀ ⸀ ∀㰀戀爀㸀∀㬀� echo "Brown: " . mysql_query("SELECT avg(`field_10`) FROM `pre_dye_prices` WHERE field_10>0");� mysql_close($link);㼀㸀� 㰀⼀栀琀洀氀㸀 Link to comment Share on other sites More sharing options...
Synook Posted July 2, 2008 Share Posted July 2, 2008 Are you naming the files with a .php at the end, and is your server set up to parse PHP files? Are you even running these from a server?There are two problems: 1) the files aren't being parsed, and 2) the encoding is wrong. Try fixing the first one first. Link to comment Share on other sites More sharing options...
Lil Puppy Posted July 2, 2008 Author Share Posted July 2, 2008 Yes, Yes, Yes. I run joomla and SMF with a mysql server.They are .php, I just tried another encoding type and it parses now thank you. The SQL command works on the mysql db exactly like it's supposed to but through php it doesn't.This: echo "Black: " . mysql_query("SELECT avg(`field_1`) FROM `pre_dye_prices` WHERE field_1>0") . "<br>"; Produces: Black: Resource id #3 I can debug the rest of my code if I can just get that output to work right. Link to comment Share on other sites More sharing options...
Synook Posted July 2, 2008 Share Posted July 2, 2008 You need to retrieve the resultset. You could do echo "Black: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_1`) FROM `pre_dye_prices` WHERE field_1>0"))) . "<br>"; Link to comment Share on other sites More sharing options...
Lil Puppy Posted September 22, 2008 Author Share Posted September 22, 2008 Sorry for bringing this back up, but I haven't been able to get around to testing and continuing this project since my last reply.Thank you for the information, it works great. The only problem I have is that I need to make this more efficient for the mysql server (during initial testing I caused some severe problems with my shared hosting provider that took nearly a week to resolve )The code below works just fine, just wondering if there's any tips I can get to make this more efficient or better for the mysql server.TIA. <html><body><?phpinclude("config.inc.php");$link = mysql_connect($db_host,$db_user,$db_pass);if(!$link) die ('Could not connect to database: '.mysql_error());mysql_select_db($db_name,$link);echo "Black: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_1`) FROM `pre_dye_prices` WHERE field_1>0"))) . "k <br>";echo "White: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_2`) FROM `pre_dye_prices` WHERE field_2>0"))) . "k <br>";echo "Silver: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_3`) FROM `pre_dye_prices` WHERE field_3>0"))) . "g <br>";echo "Red: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_4`) FROM `pre_dye_prices` WHERE field_4>0"))) . "g <br>";echo "Blue: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_5`) FROM `pre_dye_prices` WHERE field_5>0"))) . "g <br>";echo "Green: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_6`) FROM `pre_dye_prices` WHERE field_6>0"))) . "g <br>";echo "Yellow: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_7`) FROM `pre_dye_prices` WHERE field_7>0"))) . "g <br>";echo "Orange: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_8`) FROM `pre_dye_prices` WHERE field_8>0"))) . "g <br>";echo "Purple: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_9`) FROM `pre_dye_prices` WHERE field_9>0"))) . "g <br>";echo "Brown: " . implode(mysql_fetch_assoc(mysql_query("SELECT avg(`field_10`) FROM `pre_dye_prices` WHERE field_10>0"))) . "g <br>";mysql_close($link);?></body></html> Link to comment Share on other sites More sharing options...
justsomeguy Posted September 22, 2008 Share Posted September 22, 2008 You can send 1 query instead of 10 and loop through the results to echo your different values. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.