-
Posts
253 -
Joined
Everything posted by alexandre
-
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
does this means that for example , i want to make a load more button using thise query if i have to set a limit to a super high number, will this make me unable to make this load more button for which i guess i have to separate the number of rows pulled so with a high limit , will be impossible no ? -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
that is good to know thank you, those are things that normal people would break their head to normally try to understand what create a certain behavior, after that people still wonder why i prefer asking questions on forums than searching on google, this is a pretty good example why. lets say i would never have guessed this by myself. -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
all in all , now this is working fine, i thank you a lot for your help. -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
yes this worked for bringing back the order as it was but somehow the order is only based on the total received, with the mariadb line commented i think it was closer to take in count the total_donated. -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
but i think finally the order seem fine based on what they received and donated , except for the first place in the rank you can see they donated the same amount and the one who received the most is still in second place .. i think it is missing a tiny bit of accurracy still but is very close. -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
on phpmyadmin it is saying server type mariadb: Server version: 10.4.24-MariaDB - mariadb.org binary distribution -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
so as i am trying it , it is telling me that the array key are not defined for total_donated and total_received. i past it this way: $stmt = 'SELECT users_name , @seq := @seq + 1 as seq , @rank := CASE WHEN total_received = @prevr THEN CASE WHEN total_donated = @prevd THEN @rank ELSE @seq END ELSE @seq END as rank , @prevr := total_received as received , @prevd := total_donated as donated FROM ( SELECT users_name , total_donated , total_received FROM random_donation_clash WHERE total_participant = 1 ORDER BY total_received DESC, total_donated ASC -- LIMIT 18446744073709551615 --(required by mariaDB users only - bug workaraound) ) ordered JOIN ( SELECT @rank := 0, @seq := 0, @prevd := 0, @prevr := 0 ) init'; $result = mysqli_query($con, $stmt); if (mysqli_num_rows($result) > 0) { while ($result_row = mysqli_fetch_assoc($result)) { $seq[] = $result_row['seq']; echo"<div class='rankingtrue2'> <div class='ranking_wrapper'> <table class='rankingtable3'> <tr> <td>rank</td><td>username</td><td>total donated</td><td>total_received</td> </tr> <tr> <td class='remain1'>" . $result_row['seq']. "</td> <td class='remain1'>" . $result_row['users_name']. "</td> <td class='remain3'>" . $result_row['total_donated']. "</td> <td class='remain4'>" . $result_row['total_received']. "</td> </tr></table></div></div><br><br>"; } } i changed the array key to donated and received and here is the output: you can see the seq row is fine but the order is all messed up, i feel like i am doing something wrong somewhere. -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
i see thank you for the explanations. if iwanted to use this query with mysqli, would i have to replace the prefixes and sql assignement operator by something else, or this work as it is in a normal query? as i am asking i didnt tried it yet , i am more trying to understand it first. -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
i would just need to know is that a pdo query? because the "@" like @seq ,@rank, @prevr and @prevd i dont really know what they represent or this ":=" i would need some explanations if you can ? -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
im sorry i already marked your other example as the solution but this one would deserve a markup on it. this is exactly the output that i was thinking about. would you mind if i use this code? -
yes i agree with you . i wonder how openclassroom did. it started small and got bigger with time , i remember when i was using le site du zero when i was younger and now its a website deserving diploma. but i understand that this is not the same purpose for every website, what you have here is already great with a majoritary nice community.
-
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
something i am wondering is if its possible to use conditional in a query like if total received is equal and totldonated < then go up in rank , something like that because the only problem is caused by the equal records who are being counted. $stmt = 'SELECT users_name , total_donated , total_received , ( SELECT COUNT(*)+1 FROM random_donation_clash rd2 WHERE rd2.total_received > rd1.total_received AND total_participant = 1 ) AS rank FROM random_donation_clash rd1 WHERE total_participant = 1 ORDER BY total_received DESC'; -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
this almost does it just that the rank end up not a continuous rank. it jumps to another number. and keep going up, i think it is counting the duplicates that are not displayed. thats why i am using the total_participant = 1 as WHERE clause, every participants have one record with total_participant= 1 all their other records this collumn equal zero. if i add the total_participant WHERE clause to the first part of alias it increases the accuracy of the rank , now the only problem is that this is jumping the position in the rank after two or more have an equal total_received, probably there is a way that i could compare the total_donated and if it is lower then the other participant it go higher in rank than the one having higher difference between received and donated. -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
i really tried to like pdo but the synthax of the parameter felt repelling and i couldnt keep up with developping my things. on the other hand i have a pdo connection file at hand. only if i can find a solution with mysqli first i will always look for it. i really like mysqli extension and the synthax as well thats mostly why. also for the query , can i ask what is the rd1 and rd2, are they collumns that you added? -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
my query right now looks like this : $stmt = 'SELECT users_name, total_donated, total_received FROM random_donation_clash WHERE total_participant = 1 ORDER BY total_received DESC'; $result = mysqli_query($con, $stmt); if (mysqli_num_rows($result) > 0) { while ($result_row = mysqli_fetch_assoc($result)) { $received[] = $result_row['total_received']; it makes me a ranking well ordered but still quite simple , if i wanted to push it a bit farther i would calculate the rate between the loss and gains and order the ranking differently. but as you can see i am a bit struggling with basic stuff so i will keep it simple and just look to add what you just shown above. here is my output. -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
oh ok , i was just wondering . you put so much effort into this , i hope i will succeed so i can pay it forward to you 😄 -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
is this the output of a query you did or you managed arrays ? -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
yes this looks like it, only mine is ordered by the total received by participant but thats basicly the same. i was trying to display the rank of the participants in the ranking panel -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
my problem is that i need to define that offset row by the name and total_received , since they are pulled in desc order to create a ranking of the donations it wont show in the order they have submitted to the table. by that i mean that i would not be able to use a auto-incrementing collumn to aim at the row i need .. -
how to count the number of rows preceding a given row?
alexandre replied to alexandre's topic in PHP Coding Help
kind of i didnt think about searching if i could use a variable with the limit clause. -
i have been looking around and could not find a solution to count the number of rows present between 0 and a given row. my given row needs to represent the offset of the total number of pulled rows from the query. can it be done by a simple query? or i would need to pull the whole table and then manipulate the data to define that offset myself by counting the arrays in the array until it meets a condition or so?
-
yes but everything doesnt have to be easy. i was just suggesting this because i saw tons of tutorials that are leading to deprecated stuff or even a lot who are teaching people to write code in a risky way. i am not sure who could write those , but a lot of platform are offering this and it seems to work. in practice all you would need to do is to get people who have tutorials written to send a request to post them and then would be left to approve them or not. but like i said it is a simple suggestion maybe even personal at this point since i really appreciate this website and would like to be able to learn the best stuff i need in a single place.
-
looking for an easy way to implement pagination
alexandre replied to alexandre's topic in PHP Coding Help
<?php // DB credentials. define('DB_HOST','localhost'); define('DB_USER','root'); define('DB_PASS',''); define('DB_NAME','); // Establish database connection. try { $pdo = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4'")); } catch (PDOException $e) { exit("Error: " . $e->getMessage()); } this is the connection file i am using to connect but it doesnt work maybe it is where i placed it earlier in my code but , tomorrow i will try to redo it. gotta stop for tonight , my right eye is now blurry for some reason ..