PHP - Printing Average-value From Two Tables With Php/sql

If you had a function that calculated the average of a fixed quantity of numbers, what type of scope issues would you anticipate?

Hi.

Im trying to make a PHP code that will find the average number.

What I want is, a lot of input fields where you can write a number in.

Then my code should take these numbers and divide them with the amount of input fields that has been written in.

So that some fields you don't have to write a number, and the code will still give you a correct average.

My code so far:

Code: [Select]
<?php
if (isset($_POST['calc']))
{
$m=$_POST['mat'];
$e=$_POST['da'];
$s=$_POST['eng'];
$f=$_POST['fys'];
$h=$_POST['bio'];
$p=$_POST['geo'];
$ave=($m+$e+$s+$f+$h+$p)/;
}
?>

<a href="" onclick="return hs.htmlExpand(this, { headingText: 'Beregn dit Gennemsnit:' })">
<b>Beregn dit Gennemsnit:&nbsp;&nbsp; <?php echo $ave;?></b><br></br>
</a>
<div class="highslide-maincontent">
<form id="gennemsnit" name="form1" method="post" action="">
Matematik<input name="math" type="text" id="mat" size="3" maxlength="4" /><br />
Dansk<input name="eng" type="text" id="da" size="3" maxlength="4" /><br />
Engelsk<input name="sci" type="text" id="eng" size="3" maxlength="4" /><br />
Fysik<input name="fil" type="text" id="fys" size="3" maxlength="4" /><br />
Biologi<input name="he" type="text" id="bio" size="3" maxlength="4" /><br />
Geografi<input name="pe" type="text" id="geo" size="3" maxlength="4" /></td><br />
<input name="calc" type="submit" id="calc" value="Beregn"/><br /><br />
</form>
What I need is the code of how to divide with the amount of input fields that has been written in.

thanks for any help

hi all!
i have 2 tables:

PRODUCTS
id_prod, id_cat, date, prod_name

CATEGORIES
id_cat, cat_name

My goal is obtain the average (day and month) of all products inserted by users in this format:

category Hardwa avg daily 20, month 100
category Components: avg daily 2, month 15
and so on.

PS: the 'date' field is unix timestamp (int 10).

Can you help me?

Could someone please advise the best way to determine the average of a DATE field in a SELECT query?    

Hi,

I'm trying to get out the average of records stored in a mysql table

This is the code im using right now
Code: [Select]
mysql_query("SELECT DATE(dato) , COUNT( dato ) AS counted FROM dekodere GROUP BY date(dato)", $link);
$ave = mysql_num_rows($ave);
This is getting out the average, but i need to exclude weekends because there is not stored anything during the weekend so it's making the average go off..

any tips?

Nice forum here.

I know nothing about PHP and need some help.

Here is the deal. Members on a forum will be rating products. There will be 4 (this number could change) fields where they will select a number from a drop down menu to rate different characteristics of the product. I need to average those numbers for the overall rating.

The code that outputs the individual numbers selected looks like this:

{$data['record']['field_14']}

14 being one of the fields, and 15, etc.

So what I need is code that takes multiple fields and outputs the average number.

I appreciate any help! And please keep in mind I am not familiar with PHP coding, so if you could be as detailed as possible I would appreciate it.

Thanks!

Hi,   I have a blog which records the amount of views on each article. I now want to be able to work out the average number of views per hour.   How can I work out the number of hours passed from a datetime format?   From there I can just do views divided by hours passed.   Thanks in advance!    

I have a table of restaurant menu items called menu_items.  This table has a float row(3,2) called price.  I have a query that looks like this:

$query = mysql_query("SELECT * FROM menu_items WHERE restaurant = '".$restaurantid."'");

How would I find the average of the price row from that query?  Basically I want to find the average item price for a restaurants menu.

I have a "Users" table. I would like to find out the average users sign up in total. This table has a "joined date" column so I can track how many users sign up in a single day.

For e.g.

August 16 - 10 users

August 17 - 20 users

Auguest 18 - 30 users

The total average of user sign ups would be 20 users based on the above results.

So I am wondering how can I create this function?

This is my starting query.

$get_users = $db->prepare("SELECT user_id FROM users");
$get_users->execute();
$result_users = $get_users->fetchAll(PDO::FETCH_ASSOC);
if(count($result_users) > 0) {
  foreach($result_users as $row) {
    $user_id = $row['user_id'];
  }
}

 

I manage to display data using below query that will show value for each month. How to calculate average value starting july onwards(based on user selection).   Let say the value for element 'A'  are Jul=80, Aug= 80, Sep=90. If user select Aug then the average is 80 and if user select Sep then the average is 83.   Query:

$sql2 = "Select element,
	SUM(CASE WHEN Month = 'Jan' THEN total ELSE 0 END ) AS Jan,
	SUM(CASE WHEN Month = 'Feb' THEN total ELSE 0 END ) AS Feb,
	SUM(CASE WHEN Month = 'Mac' THEN total ELSE 0 END ) AS Mac,
	SUM(CASE WHEN Month = 'Apr' THEN total ELSE 0 END ) AS Apr,
	SUM(CASE WHEN Month = 'May' THEN total ELSE 0 END ) AS May,
	SUM(CASE WHEN Month = 'Jun' THEN total ELSE 0 END ) AS Jun,
	SUM(CASE WHEN Month = 'Jul' THEN total ELSE 0 END ) AS Jul,
	SUM(CASE WHEN Month = 'Aug' THEN total ELSE 0 END ) AS Aug,
	SUM(CASE WHEN Month = 'Sep' THEN total ELSE 0 END ) AS Sep,
	SUM(CASE WHEN Month = 'Oct' THEN total ELSE 0 END ) AS Oct,
	SUM(CASE WHEN Month = 'Nov' THEN total ELSE 0 END ) AS Nov,
	SUM(CASE WHEN Month = 'Dec' THEN total ELSE 0 END ) AS Dis
	FROM tbl_ma GROUP BY element";
	$rs2 = mysql_query($sql2);
	$getRec2 = mysql_fetch_assoc($rs2);

Below is how print the data:

<?php 
while ($row = mysql_fetch_assoc($rs2)) {
?>
<tr>
<td style="color:black;background-color:#f5efef"><div align="left"><?php echo $row['element']; ?></div></td>
<?php if( $year != '2014' ){ ?>
<?php if( 1 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Jan']; ?></div></td><?php endif; ?>
<?php if( 2 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Feb']; ?></div></td><?php endif; ?>
<?php if( 3 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Mac']; ?></div></td><?php endif; ?>
<?php if( 4 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Apr']; ?></div></td><?php endif; ?>
<?php if( 5 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['May']; ?></div></td><?php endif; ?>
<?php if( 6 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Jun']; ?></div></td><?php endif; ?>
<?php } ?>
<?php if( 7 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Jul']; ?></div></td><?php endif; ?>
<?php if( 8 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Aug']; ?></div></td><?php endif; ?>
<?php if( 9 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Sep']; ?></div></td><?php endif; ?>
<?php if( 10 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Oct']; ?></div></td><?php endif; ?>
<?php if( 11 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Nov']; ?></div></td><?php endif; ?>
<?php if( 12 <= $monthID ): ?><td><div align="center" style="color:black"><?php echo $row['Dis']; ?></div></td><?php endif; ?>
<td><div align="center" style="color:black"><?php //echo average; ?></div></td>
</tr>                                  
<?php } ?>

 

Hello,

I'm trying to calculate the square root of X values in an array minus the average of the array, but the first half of the values are negative, so I'm using the abs(); function to change them to positives first, but I'm still getting "NAN" as the result for the negative values that 'should' be positive.
        $x = array(7,3,4,6,4,2,7);
        $sqrt = true;
        $x = sort($x);
        $mean = 4.71; // average
        $XminA = array();
        foreach($x as $val) {
            ($sqrt == true) ? $XminA[] = round(sqrt(abs($val) - $mean),2) : $XminA[] = round($val - $mean,2);
        }
        echo "<pre>";
        print_r($XminA);
        echo "</pre>";

and my result is...
Quote

[XminA] => Array
        (
            => NAN [1] => NAN
            [2] => NAN
            [3] => NAN
            [4] => 1.14
            [5] => 1.51
            [6] => 1.51
        )

Not sure what I'm doing wrong, any help is appreciated. Thank you!

Kind Regards,
Ace

EDIT: stupid mistake, need it to the power of 2 not square root. Using the wrong function sorry:
 ($sqrt == true) ? $XminA[] = round(pow((abs($val) - $mean),2) : $XminA[] = round($val - $mean,2); // pow() not sqrt()
Quote

[XminA] => Array
        (
            => 7.34 [1] => 2.92
            [2] => 0.5
            [3] => 0.5
            [4] => 1.66
            [5] => 5.24
            [6] => 5.24
        )

This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization)

users: id / username / password / realname / access_level
users_access_level: access_level / access_name

What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries.

Example row for users: 1234 / tmac / password / tmac / 99
Example row for users_access_level: 99 / Admin

Using the examples above, I would want the output to appear as such:

Username: Access Name: Tmac Admin
I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction.

The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself.

Thanks for reading!

Im doing a search system and Im having some problems. I need to search in two tables (news and pages), I already had sucess doing my search system for just one table, but for two tables its not easy to do. I already use a select statment with two tables using UNION because I want to show number of search results, that is number of returned rows of my first sql statment. But now I need to do a select statment that allows me to acess all fields of my news table and all fields of my pages table. I need to acess in my news table this fields: id, title, content, link, date, nViews I need to acess in my pages table this fields: id, title, content, link Im trying to do this also with UNION, but in this case Im not having any row returning. Do you see what I have wrong in my code?   <?php
//first I get my $search keyword
$search = $url[1];

$pdo = connecting();
//then I want to show number of returned rows for keyword searched
$readALL = $pdo->prepare("SELECT title,content FROM news WHERE title LIKE ? OR content LIKE ?
UNION SELECT title,content FROM pages WHERE title LIKE ? OR content like ?");
$readALL->bindValue(1,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(2,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(3,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(4,"%$search%", PDO::PARAM_STR);
$readALL->execute();
//I show number of returned rows
echo '<p>Your search keyword returned <strong>'.$readALL->rowCount().'</strong> results!</p>';
//If dont return any rows I show a error message
if($readALL->rowCount() <=0){
echo 'Sorry but we didnt found any result for your keyword search.';
}
else{
//If return rows I want to show, if it is a page result I want to show title and link that I have in my page table
//if it is a news result I want to show title and link that I have in my news table and also date of news
echo '<ul class="searchlist">';
$readALL2 = $pdo->prepare("SELECT * FROM news WHERE status = ? AND title LIKE ? OR content LIKE ? LIMIT 0,4
UNION SELECT * FROM pages where title LIKE ? OR content LIKE ? LIMIT 0,4");
$readALL2->bindValue(1, '1');
$readALL2->bindValue(2, "%$search%", PDO::PARAM_STR);
$readALL2->bindValue(3, "%$search%", PDO::PARAM_STR);
$readALL2->bindValue(4, "%$search%", PDO::PARAM_STR);
$readALL2->execute();

while ($result = $readALL2->fetch(PDO::FETCH_ASSOC)){
echo '<li>';
echo '<img src="'.BASE.'/uploads/news/'.$result['thumb'].'"/>';
echo '<a href="'.BASE.'/news/'.$result['id_news'].'">'.$result['title'].'</a>';
//if it is a news result I also want to show date on my list
//echo '<span id="date">'.$result['date'].'</span>';
echo '</li>';
}
echo ' </ul>';
//but how can I do my select statement to have access to my news table fields and my page table fields??
}
?>

I am trying to write some data from multiple SQL tables to a page. In the first table is a list of places. I then have more tables that are named after the different places. For example, say my first place in the list is called Place1. I have a table named Place1 with data that corresponds to place1. The data contained in the table named Place1 is a list of things to do in this place. It has 21 columns and each one is something to do in the morning, afternoon, and at night for each day of the week in the place Place1. What I am trying to do is display a sort of weekly calendar as a table on a webpage that lists all of the places in one column and then lists seven days of the week as 7 more columns. Then in each data cell I would want to list the things to do in the morning, afternoon and at night for the certain day of the week and for the place. The problem is that I am creating a CMS to allow other users with no coding knowledge to update events for other places, so I have to display data that could have been altered.

The only solution I know of is to do a while loop that gets all of the place names and while there are still place names, write the place names to the page and set a variable equal to the place name. Inside the while loop I would create another while loop that each time the first while loop is executed uses the variable set in the first while loop to know which table to reference and then make a call to that table pulling out the 21 columns and writing them to the page. Each time the outer while loop executes, it would (hopefully) write the place name, and then set the variable as the current place name so that the inner while loop uses the variable to write the rest of the information about that place.

I don't know if that would even work and if it did, I know it cannot be the best way to do this. I am pretty stuck here and don't really have a good solution so if anyone proposes a solution that is radically different to anything I have done, I am open to everything. Thank you!

I have a directory that I would like to allow users to print to PDF format for download.
How can I do this and where do I start?