put the sql results into an array and use natsort()
Moderator: General Moderators
put the sql results into an array and use natsort()
Hi,
I have another post relating to this question, but I read some of the forum questions/answers and it led me to believe that maybe I could do this? Does anyone know how I can take this code and make it work to put the sql results into an array in php and use natsort()?
I'm trying to show my members who have the most referrals to the least number of referrals, but right now it is mixing them, for example 3,10,2, when i need it 10,3,2.
Thanks in advance!
<?
require('include/config.php');
$db = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname,$db);
$sql = "SELECT * FROM users where rcount>0 ORDER BY rcount DESC";
$result = mysql_query($sql) or die(mysql_error());
$num = mysql_num_rows($result);
print("<table><tr><td>Member</td><td>Referrals</td></tr>");
if($num != 0)
{
{
while($row = mysql_fetch_array($result)) {
$username = $row[0];
$rcount = $row[10];
print("<tr><td>$username</td><td>$rcount</td></tr>");
} } }
print("</table>");
?>
I have another post relating to this question, but I read some of the forum questions/answers and it led me to believe that maybe I could do this? Does anyone know how I can take this code and make it work to put the sql results into an array in php and use natsort()?
I'm trying to show my members who have the most referrals to the least number of referrals, but right now it is mixing them, for example 3,10,2, when i need it 10,3,2.
Thanks in advance!
<?
require('include/config.php');
$db = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname,$db);
$sql = "SELECT * FROM users where rcount>0 ORDER BY rcount DESC";
$result = mysql_query($sql) or die(mysql_error());
$num = mysql_num_rows($result);
print("<table><tr><td>Member</td><td>Referrals</td></tr>");
if($num != 0)
{
{
while($row = mysql_fetch_array($result)) {
$username = $row[0];
$rcount = $row[10];
print("<tr><td>$username</td><td>$rcount</td></tr>");
} } }
print("</table>");
?>
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
If you don't need other data, just username and rcount, change your SQL statement to:
It should be working and showing things ordered, please take a moment to show us the users table design. Run this on your site and show us the output:
Cheers,
Scorphus.
Code: Select all
$sql = "SELECT username, rcount FROM users WHERE rcount>0 ORDER BY rcount DESC";Code: Select all
<pre>
<?php
$conn = mysql_connect($dbAddress, $dbUser, $dbPass);
mysql_select_db($dbName);
$result = mysql_query('desc users');
while ($row = mysql_fetch_row($result))
echo "$row[0] | $row[1]\n";
?>
</pre>Scorphus.
Hi,
Thank you for trying to help me. I tried the code you included, but it did not show the referral amounts.
Here is the result of the output you requested, I hope it helps.
username | varchar(20) realname | varchar(50) email | varchar(30) password | varchar(50) viewpopups | varchar(10) active | varchar(25) credits | varchar(25) creditsused | varchar(255) oldip | varchar(100) rip | varchar(255) rcount | varchar(15) rcredits | varchar(25) time | varchar(20) referral | varchar(15) currenturl | varchar(100) oldurl | varchar(100) suspend | varchar(5)
Thanks again in advance!
Thank you for trying to help me. I tried the code you included, but it did not show the referral amounts.
Here is the result of the output you requested, I hope it helps.
username | varchar(20) realname | varchar(50) email | varchar(30) password | varchar(50) viewpopups | varchar(10) active | varchar(25) credits | varchar(25) creditsused | varchar(255) oldip | varchar(100) rip | varchar(255) rcount | varchar(15) rcredits | varchar(25) time | varchar(20) referral | varchar(15) currenturl | varchar(100) oldurl | varchar(100) suspend | varchar(5)
Thanks again in advance!
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
Hi,illmapu wrote:rcount | varchar(15)
As you can see, the rcount's column type is varchar(15), a string type. So it is ordering things in the right way, in an alphabetical order. You'll have to change it's column type to an integer type (Numeric Types), then it will get numerically ordered.
Cheers,
Scorphus.
Hi,
Thank you very much for responding. I'm sorry if I seen stupid about this question, but will I lose any data or will it mess anything up by switching it to an INT midstream? I am afraid if I change that, that I could effect the database as it is now functioning. I am still learning, as I'm sure we all are. So, to be clear, I should change the type from a VARCHAR to an INT?
Thanks again for your help, very appreciated!
Thank you very much for responding. I'm sorry if I seen stupid about this question, but will I lose any data or will it mess anything up by switching it to an INT midstream? I am afraid if I change that, that I could effect the database as it is now functioning. I am still learning, as I'm sure we all are. So, to be clear, I should change the type from a VARCHAR to an INT?
Thanks again for your help, very appreciated!
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
It depends on some factors. The way the stored data will be converted. The way your application treats rcount (maybe you'll have to change some parts).illmapu wrote:...will I lose any data or will it mess anything up by switching it to an INT midstream?
It is recommended that you change the type to INT, since rcount is a numeric type.illmapu wrote:I am afraid if I change that, that I could effect the database as it is now functioning. I am still learning, as I'm sure we all are. So, to be clear, I should change the type from a VARCHAR to an INT?
However, you can adapt your application to make MySQL convert it to integer on the fly, look:
Code: Select all
SELECT username, ABS(rcount) AS rcount FROM users ORDER BY rcount DESCHope that helps, please let me know if it doesn't.
Cheers,
Scorphus.
references: Mathematical Functions
You can also use cast() or convert() in the where-clause:
...or similiar.
Code: Select all
$sql = "SELECT username, rcount FROM users WHERE cast(rcount as unsigned)>0 ORDER BY cast(rcount as unsigned) DESC";Hi,
scorphus, I used this and I'm getting half of what I was hoping for. It is showing the members in the order that I need(talk about close...lol), but it is not showing the number of referrals for them now. (tease....LOL)
Any ideas? It is only showing the username, nothing else.
Thanks again in advance!
scorphus, I used this and I'm getting half of what I was hoping for. It is showing the members in the order that I need(talk about close...lol), but it is not showing the number of referrals for them now. (tease....LOL)
Code: Select all
SELECT username, ABS(rcount) AS rcount FROM users where rcount>0 ORDER BY rcount DESCThanks again in advance!
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
Just to be sure: are you still using $rcount = $row[10];? With the new query, rcount is at index 1 of $row. So it should be $rcount = $row[1];
Also: I think that JAM's solution is much more reliable than mine, since it's using explicit cast functions, and not forcing MySQL to do a cast by using ABS(VARCHAR).
Cheers,
Scorphus.
Also: I think that JAM's solution is much more reliable than mine, since it's using explicit cast functions, and not forcing MySQL to do a cast by using ABS(VARCHAR).
Cheers,
Scorphus.