put the sql results into an array and use natsort()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

put the sql results into an array and use natsort()

Post by illmapu »

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>");
?>
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

If you don't need other data, just username and rcount, change your SQL statement to:

Code: Select all

$sql = "SELECT username, rcount FROM users WHERE rcount&gt;0 ORDER BY rcount DESC";
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:

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>
Cheers,
Scorphus.
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Post by illmapu »

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!
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

illmapu wrote:rcount | varchar(15)
Hi,


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.
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Post by illmapu »

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!
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

illmapu wrote:...will I lose any data or will it mess anything up by switching it to an INT midstream?
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: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?
It is recommended that you change the type to INT, since rcount is a numeric type.

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 DESC
Use it while you won't make the changes.

Hope that helps, please let me know if it doesn't.

Cheers,
Scorphus.

references: Mathematical Functions
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

You can also use cast() or convert() in the where-clause:

Code: Select all

$sql = "SELECT username, rcount FROM users WHERE cast(rcount as unsigned)>0 ORDER BY cast(rcount as unsigned) DESC";
...or similiar.
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Post by illmapu »

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)

Code: Select all

SELECT username, ABS(rcount) AS rcount FROM users where rcount>0 ORDER BY rcount DESC
Any ideas? It is only showing the username, nothing else.

Thanks again in advance! :)
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

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.
Post Reply