How can I fetch database field as variable?

[robot]

Hi All,

There are two different field in database table.
average here the number for e.g. 50 60 30 30
post_id here the id is integer 100, 159 89  89 and so on

post_id is not primary key so the value can be repeated
average field has also same values

 

For static value it functions. for. e.g. If I write post id 89 than the result is 60.
SELECT SUM(average) FROM example WHERE post_id = 89

The result = 60

 

I want to select the sum of average of each post_id.

SELECT SUM(average) FROM example WHERE post_id = distinct post_id
How can I write the  post_id as variable?

 

Thanks a lot

 

 

 

[kicken]

Use a GROUP BY clause in your query.

[Psycho]

@robot,

 

You need to show HOW you are creating your query today. You will want to use a prepared statement for your query if you want to include a variable value. "How" you do that will depend on what functions you are using, which should likely be MySQLi or PDO.

 

PDO is definitely preferred. Here is a tutorial: https://phpdelusions.net/pdo

[robot]

Many thanks.

It works but for every post the average is same. I want to show according to post id. If postid is 89 than it should show the average of the postid 89 only.

At present It shows on every post the same value.

$_sql = "SELECT SUM(average) FROM Example GROUP BY post_id";

$_daten = mysql_fetch_assoc($_result);
$_datenavg = $_daten["SUM(average)"];
$_totalst = $_datenavg * 0.1;

echo "Total:" .' ' .$_totalst ?>

Thanks a lot

[Jacques1]

The code you've shown makes no sense, because you're only fetching a single row. You'll need a loop to go through all rows.

 

How exactly this works depends on the context. We're not psychic. If you already have a query for all posts, then you should add the SUM(average) to that query. Show your full code, not just random fragments.

[Psycho]

robot,

 

You need to stop using the mysql_ functions and change to mysqli_ or, better yet, PDO. The mysql_ drivers have been deprecated for quite some time and don't even work in current versions of PHP.

 

Then, you would create a prepared statement to pass variables, as needed, into your query. You don't say where the value for the single post ID will come from, the code below assumes it will be passed on the query string (i.e. a $_GET value).

<?php
 
//Connect to DB. See the link I gave above for the values to use
$pdo = new PDO($dsn, $user, $pass, $opt);
 
//Create a prepared statement
$sql = "SELECT SUM(average) FROM Example GROUP BY post_id WHERE post_id = :post_id";
$stmt = $pdo->prepare($sql);
//Execute the query, passign the parameter(s) as an array
$stmt->execute(['post_id' => $_GET['post_id']);
//Since there is a single valule - just fetch the column instead of the whole row
$sumOfAvgForPostId = $sth->fetchColumn();
 
?>

[robot]

many thanks. There is some mistake in code.

$stmt->execute(['post_id' => $_GET['post_id']);

host = 'localhost';
$db   = 'dbname';
$user = 'root';
$pass = '';
$charset = 'utf8';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);
 
//Create a prepared statement
$sql = "SELECT SUM(average) FROM Example GROUP BY post_id WHERE post_id = :post_id";
$stmt = $pdo->prepare($sql);
//Execute the query, passign the parameter(s) as an array
$stmt->execute(array('post_id' => $_GET['post_id']));
//Since there is a single valule - just fetch the column instead of the whole row
$sumOfAvgForPostId = $sth->fetchColumn();

@Guru

In database table there are many columns but two (post_id and average) are needed to calculate the average. The table for example has post_id where the id of the post has been saved. If somebody created a post the post id will be generated automatically.  The column average contains the value if somebody rate the post. This will be written as integer value in the column average.

For example 4 people created four different post. so there are 4 differnt post_ids. lets say 100, 200, 89, 59. now the column average - here will be the rating value saved by different poeple.

The two columns in the table will be shown as:

post_id average

100       90

100      80

100       80

100       90

68      70

68      70

68     70

89     100

59     100

59     50

59     50

 

Result what I want to show.

If I will be on post with id 100 than 340*0.1 = 34

If I will be on post with id 70 than 210*0.1 = 21

If I will be on post with id 89 than 100*0.1 = 10

If I will be on post with id 59 than 200*0.1 = 20

 

At present I shows 21 on all of the post.

this was the code for that

$_sql = "SELECT SUM(average) FROM Example GROUP BY post_id";

$_daten = mysql_fetch_assoc($_result);
$_datenavg = $_daten["SUM(average)"];
$_totalst = $_datenavg * 0.1;

echo "Total:" .' ' .$_totalst ?>

Now as Psycho recommended to write all in pdo and wrote a code.

It doesnot function.

$sql = "SELECT SUM(average) FROM Example GROUP BY post_id WHERE post_id = :post_id";

Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE post_id = ?'

 

I am trying to do it without PDO.

 

Thanks

[Psycho]

I am trying to do it without PDO.

 

Well, I'm not going to do it with mysql_ functions. Either move to mysqli_ or PDO.

 

As for the query, I put the WHERE clause before the GROUP BY clause. They need to be swapped

 

SELECT SUM(average) FROM Example WHERE post_id = :post_id GROUP BY post_id

 

But, if you want the average to be multiplied by .1, then do it in the query instead of querying the data and then doing additional math on it

 

SELECT (SUM(average) * 0.1) as totalst FROM Example WHERE post_id = :post_id GROUP BY post_id

 

As to how you will insert the variable for the post_id in the query, I gave you a solution using PDO. If you want to change your code to use mysqli_ (which is not as intuitive) I will show you how to do it with that.

[Jacques1]

The GROUP BY is useless when you've already restricted the data to one particular post_id. kicken suggested the grouping only because the OP initially wanted the sum for each post. Appearently the OP wasn't very clear or has changed his mind.