robot Posted April 3, 2017 Share Posted April 3, 2017 Hi All, There are two different field in database table.average here the number for e.g. 50 60 30 30post_id here the id is integer 100, 159 89 89 and so onpost_id is not primary key so the value can be repeatedaverage 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_idHow can I write the post_id as variable? Thanks a lot Quote Link to comment https://forums.phpfreaks.com/topic/303612-how-can-i-fetch-database-field-as-variable/ Share on other sites More sharing options...
kicken Posted April 3, 2017 Share Posted April 3, 2017 Use a GROUP BY clause in your query. Quote Link to comment https://forums.phpfreaks.com/topic/303612-how-can-i-fetch-database-field-as-variable/#findComment-1544993 Share on other sites More sharing options...
Psycho Posted April 3, 2017 Share Posted April 3, 2017 @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 Quote Link to comment https://forums.phpfreaks.com/topic/303612-how-can-i-fetch-database-field-as-variable/#findComment-1545003 Share on other sites More sharing options...
robot Posted April 4, 2017 Author Share Posted April 4, 2017 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 Quote Link to comment https://forums.phpfreaks.com/topic/303612-how-can-i-fetch-database-field-as-variable/#findComment-1545011 Share on other sites More sharing options...
Jacques1 Posted April 4, 2017 Share Posted April 4, 2017 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. Quote Link to comment https://forums.phpfreaks.com/topic/303612-how-can-i-fetch-database-field-as-variable/#findComment-1545023 Share on other sites More sharing options...
Psycho Posted April 4, 2017 Share Posted April 4, 2017 (edited) 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(); ?> Edited April 4, 2017 by Psycho Quote Link to comment https://forums.phpfreaks.com/topic/303612-how-can-i-fetch-database-field-as-variable/#findComment-1545031 Share on other sites More sharing options...
robot Posted April 5, 2017 Author Share Posted April 5, 2017 (edited) 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 Edited April 5, 2017 by robot Quote Link to comment https://forums.phpfreaks.com/topic/303612-how-can-i-fetch-database-field-as-variable/#findComment-1545059 Share on other sites More sharing options...
Psycho Posted April 5, 2017 Share Posted April 5, 2017 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. Quote Link to comment https://forums.phpfreaks.com/topic/303612-how-can-i-fetch-database-field-as-variable/#findComment-1545076 Share on other sites More sharing options...
Jacques1 Posted April 5, 2017 Share Posted April 5, 2017 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. Quote Link to comment https://forums.phpfreaks.com/topic/303612-how-can-i-fetch-database-field-as-variable/#findComment-1545079 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.