Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: PHP AVG Query

  1. #1
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PHP AVG Query

    Hi everyone

    First of all, thanks to everyone who advised me and pointed me in the direction of a MySQL tut on my last post, it has helped enormously.

    Just wondering if anyone could help me out on the below:

    I have a MySQL table that has data from surveys and is identified by an ID per review.

    I'm trying to get the PHP script to calculate the average of 10 columns across th row - is this possibe? The code I'm trying to use is:
    Code:
    $avgtocalc = mysql_real_escape_string($_GET['ad1']);
    
    $average = mysql_query("SELECT * FROM reviewdata WHERE address='$avgtocalc' AND published LIKE 'Live' AVG ('livedthere' + 'parking' + 'crime' + 'noiseneighbours' + 
    
    'noisetraffic' + 'distanceshops' + 'distanceschools' + 'conditionpavements' + 'conditionroads' + 'nightlighting')/(10) ") or die(mysql_error());
    but it keeps coming back with a Syntax error on line 1.

    Does anyone have any ideas? I've Googled this to death but can't find what I need.

    Thanks in advance
    Dave

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Using quotations are literal in SQL. That will try to add the strings, use either the accent grave (`) or preferably nothing if you don't use reserved words or spaces.

    The location itself is probably wrong. If you want to select that, you need to place it within the columns to select of the query. This is sitting somewhere in the WHERE clause. On the other hand, if you want to use it for a where clause, you would then need to use a group and a HAVING in order to do it.
    I would suggest aliasing it as well, otherwise you'll need to use a fetch_row command in order to offset it numerically, iterate the resultset with a foreach, or use the associative value of what that expression is.

    Also this has nothing to do with PHP, moving to MySQL forum.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •