select numbers !?

Codes here !

Moderators: egami, macek, gesf

Post Reply
unisabilly
New php-forum User
New php-forum User
Posts: 19
Joined: Wed Aug 17, 2005 6:00 am

Fri May 05, 2006 2:18 pm

is that possible to select numbers from 1 to number of results, i don't know how to express my question ... let me give an example ~
i want to select the name and surname of the students
i can simply use : "select name,surname from students" and the result will be:
name surname
---------------------------
Mark Robert
Billy Jones
Susan Lee
..... .....
..... ......
..... ...
Michael Geer
----------------------------

but i want the result to be as follow (with using SQL only and there is no 'num' colum in the table, which means SQL must generate it):

num name surname
----------------------------------------
1 Mark Robert
2 Billy Jones
3 Susan Lee
.... .... ....
.... .... ....
.... .... ....
103 Michael Geer
---------------------------------------

thanx ~

User avatar
gesf
Moderator
Moderator
Posts: 1716
Joined: Sun Dec 29, 2002 5:03 am
Location: Portugal / Sweden
Contact:

Fri May 05, 2006 5:45 pm

I think there's a way, i just can't remember now.
Or perhaps you can create an auto_increment field, let's say ID.
Anyway, you can make it with PHP too using that same query. I'm changing the query a bit :)
Example:

Code: Select all

<?php

$result = mysql_query("SELECT CONCAT('name', ' ', 'surname') AS fullname FROM students");

$i = 1;

while($row = mysql_fetch_array($result)){
   print $i . ' ' . $row['fullname'];
   $i++;
}


?>
Sincerely,
Gonçalo "gesf" Fontoura

gesf.org | sessionstart.com | urlms.com

unisabilly
New php-forum User
New php-forum User
Posts: 19
Joined: Wed Aug 17, 2005 6:00 am

Sat May 06, 2006 10:39 am

gesf thank you for your reply ~
there are two questions ~
1. if i use auto increment, the id will be generated while i enter the data, whick may cause some problem if i select certain rows with conditions, the row number (id) might not be in continue series.

2.can only use SQL to solve the question ~ :(

Coditor
New php-forum User
New php-forum User
Posts: 243
Joined: Wed Feb 01, 2006 9:18 am
Location: Netherlands
Contact:

Mon May 08, 2006 12:02 am

1. Doesn't matter in Gesf's code, because he's using an internal counter (i$).

2. Afaik that's not possible. MySql doesn't have a ROWID or something similar - at least to my knowledge

Coditor

User avatar
gesf
Moderator
Moderator
Posts: 1716
Joined: Sun Dec 29, 2002 5:03 am
Location: Portugal / Sweden
Contact:

Tue May 09, 2006 1:30 am

I don't remember if it is or not possibe with just SQL, but i balieve in Coditor.
You better make it other way. E.g: with PHP.
Sincerely,
Gonçalo "gesf" Fontoura

gesf.org | sessionstart.com | urlms.com

User avatar
Alexej Kubarev
Site Admin
Site Admin
Posts: 2213
Joined: Fri Mar 05, 2004 7:15 am
Location: Täby, Stockholms län
Contact:

Sat May 13, 2006 4:29 am

Oh well it might be possible if you use a stored procedure (haha, you never saw that one comming, gesf, didn't you? :D **joke** )
Anyways..
There you can make a full selection then using a flow control structure create a new selection WITH your numbers. npt so hard actually.. however this feature is supported only in MySQL 5.0 and higher

The easiest way there would actually be using a temporary table that will be killed after your procedure is done.
Best Regards,
Alexej Kubarev
-------------------------------
Zend Certified Engineer
Image Image

User avatar
gesf
Moderator
Moderator
Posts: 1716
Joined: Sun Dec 29, 2002 5:03 am
Location: Portugal / Sweden
Contact:

Mon May 22, 2006 6:07 am

Ahahah maybe. :D That's why i said I knew there was a way.
Well... this all because i read too many articles some of those without even trying... in this case of MySQL 5 cause... i don't have it :)
Sincerely,
Gonçalo "gesf" Fontoura

gesf.org | sessionstart.com | urlms.com

Post Reply