Aero77

one table is default from wordpress, the other one I made myself. Didnt pay much attention to what kind of tables there were as long as I got my data in it. First one is the wp_toplist and the second is wp_links (from wordpress)

id mediumint(9) link_id text visitor_ip varchar(15) click_at date site_votes text on_page varchar(50) link_id bigint(20) No link_url varchar(255) No link_name varchar(255) No link_image varchar(255) No link_target varchar(25) No link_description varchar(255) No link_visible varchar(20) No Y link_owner bigint(20) No 1 link_rating int(11) No 0 link_updated datetime No 0000-00-00 00:00:00 link_rel varchar(255) No link_notes mediumtext No link_rss varchar(255) First one is wp_toplist and 2nd is wp_links

I tried that now, but it returns the same results

Im making a wordpress toplist plugin. Each vote is a new entry in the database table called wp_toplist where I store the IP address for 12 hours or so. So to find out how many votes each site has on the list I count the records and group them by link id From what I can see it seems to be working cause they sites are listed one by one with their total number of votes, unless I'm missing something

nevermind I figured it out. SELECT *, count(wp_toplist.link_id) AS totalVotes FROM wp_toplist INNER JOIN wp_links ON wp_toplist.link_id=wp_links.link_id GROUP BY wp_toplist.link_id ORDER BY totalVotes DESC is that okay you think or is there a better way ?

I have two tables, where I need data from both, but I also need to count or sum the votes in one of the tables and make a variable like totalVotes. This is the current sql query I got which works, but it don't count SELECT * FROM wp_links INNER JOIN wp_toplist ON wp_links.link_id=wp_toplist.link_id Hope someone can help. Thanks for reading

I know, but they close the browser and opens it again, but its not really that important. I want to reset the toplist every month or so. I just wanna find out how they count the clicks in the and out and such

would they not still be able just to access the link over again ? I will collect the votes from a link/code they put on their own websites

sounds better, but Im afraid there wont be too much votes if you have to login or register. I dont really care if the same user votes over again, but I dont want them to sit there with the F5 key and gather 60 votes per min

What would be the best way to collect votes for my toplist ? Im not so good with php, but gonna try anyway. I just want to collect votes and temporary restrict the IP from voting over again for a limited time. (like a day or so) Should I have one record in the database for each vote ?

I'm trying to make an sql query to calculate the average fuel usage and make a high score list. To calculate correctly I need to do some calculations. Like this LITERS - "the first tanking" / KM * 10 = L/10km Here is my non-working query. The problem is to find the first tanking for each user and subtract that from the total amount of liters in a given date range. Like all tankings in June - the first tanking. Hope you understand what I mean The query //START foreach($oDB->query("SELECT d.vehicle_id, d.liter FROM diesel AS d LEFT OUTER JOIN members AS m ON m.id = d.userid WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id GROUP BY d.userid") as $row) { $FirstFill = $row['liter']; global $FirstFill; } //END $query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel FROM members AS m LEFT OUTER JOIN diesel AS d ON d.userid = m.id WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id GROUP BY m.id ORDER BY AvgFuel ASC"; $sql=$oDB->prepare($query); $sql->execute(); $row = $sql->fetchAll(); return $row; This one kind of works, but the $FirstFill variable gets wrong cause it will subtract the same amount of gas on each user which is wrong. I would need some help to get this right, and possible all in one query. I was messing around with UNION without luck Thanks for reading!

Im doing a mistake here. Its PDO I wanna use. Here is the complete code that works <?php include 'template/header.php'; echo "<div class=\"bluebox\">"; function createMonths($id='month_select', $selected=null) { /*** array of months ***/ $months = array( 1=>'January', 2=>'February', 3=>'March', 4=>'April', 5=>'May', 6=>'June', 7=>'July', 8=>'August', 9=>'September', 10=>'October', 11=>'November', 12=>'December'); /*** current month ***/ $selected = is_null($selected) ? date('m') : $selected; $select = '<select name="'.$id.'" id="'.$id.'">'."\n"; foreach($months as $key=>$mon) { $select .= "<option value=\"$key\""; $select .= ($key==$selected) ? ' selected="selected"' : ''; $select .= ">$mon</option>\n"; } $select .= '</select>'; return $select; } //start the blue background and the user bar on top of the page echo "<div class=\"mtab\"><table width=\"100%\" cellspacing=\"0\" cellpadding=\"5\" border=\"0\">"; echo "<tbody>"; function getContent() { include 'connection.php'; $dx = date('m', strtotime('today - 30 days')); if (!isset($_POST["month_select"])) { $month = $dx; $year = "2014"; } else { $month = $_POST["month_select"]; $year = $_POST["year_select"]; } //START >> "last fuel fill value" $dieselinfo = mysqli_query($con,"SELECT d.vehicle_id, d.liter FROM diesel AS d LEFT OUTER JOIN members AS m ON m.id = d.userid WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id GROUP BY d.vehicle_id ORDER BY d.id DESC"); while($data = mysqli_fetch_array($dieselinfo)) { $FirstFill = $data['liter']; } //END >> "last fuel fill value" $query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel FROM members AS m LEFT OUTER JOIN diesel AS d ON d.userid = m.id WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id GROUP BY m.id ORDER BY AvgFuel ASC"; $sql=$oDB->prepare($query); $sql->execute(); $row = $sql->fetchAll(); return $row; } // error_reporting(E_ALL); ini_set('display_errors', 1); //Row Colors setting $color1 = "#E1EEf4"; $color2 = "#FFFFFF"; $row_count = 0; $data = getContent(); foreach($data as $row) { $nid = $row['id']; $username = $row['username']; $AvgFuel = $row['AvgFuel']; $row_color = ($row_count % 2) ? $color1 : $color2; print "<tr bgcolor=\"$row_color\"><td><a rel=external href=\"user.php?profile=".$nid."\"><div class=\"buttonlink\"><img src=\"icons/user.png\" /> ".$username."</div></a></td><td>".round($AvgFuel, 2)."</td></tr>"; $row_count++; } ?> </tbody></table> <br /> <?php echo "<form method=\"post\" action=\"dscore.php\">"; echo createMonths(); echo "<select style=\"margin-top:5px;\" name=\"year_select\"> <option value=\"2014\">2014</option> <option value=\"2013\">2013</option> <option value=\"2012\">2012</option> </select>"; echo "<input type=\"submit\" value=\"Send\" /></form>"; ?> </div> </div> </body> </html> My problem with this code is that I would like to subtract the first amount of fuel for each user on each average calculation to make the average correct. The only way I could think of was to have two queries as you can see. In my first post I was trying to remove the function part, but thats not really what I need help with. Its more about making the queries and calculations right.

I'm trying to make a while loop with an odbc sql query, but not sure how to do it. Hopefully someone here can help me I've got this code $query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel FROM members AS m LEFT OUTER JOIN diesel AS d ON d.userid = m.id WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id GROUP BY m.id ORDER BY AvgFuel ASC"; $sql=$oDB->prepare($query); $sql->execute(); $row = $sql->fetchAll(); return $row; while($row = odbc_fetch_array($sql) ) { $nid = $row['id']; } but it just gives me a white page, nothing more. Thanks for reading!

I need two queries cause I don't know how to put them into one. Problem here is I need to know the field Liters from the database in the first query to subtract it from the second query to be able to calculate the average fuel consumption of the chosen range. This is my code, hope you can help function getContent() { include 'connection.php'; $dx = date('m', strtotime('today - 30 days')); if (!isset($_POST["month_select"])) { $month = $dx; $year = "2014"; } else { $month = $_POST["month_select"]; $year = $_POST["year_select"]; } //last fuel fill value $query2 = "SELECT d.liter as FirstFill FROM members AS m LEFT OUTER JOIN diesel AS d ON d.userid = m.id WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 ORDER BY d.id ASC LIMIT 1"; $sql2=$oDB->prepare($query2); $sql2->execute(); $row2 = $sql2->fetchAll(); return $row2; $FirstFill = $row2['FirstFill']; $query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel FROM members AS m LEFT OUTER JOIN diesel AS d ON d.userid = m.id WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 GROUP BY m.id ORDER BY AvgFuel ASC"; $sql=$oDB->prepare($query); $sql->execute(); $row = $sql->fetchAll(); return $row; }

Yes they are stored as varchar. Is it possible to change that without loosing data ?