-3

Hello i have tables like this :

Employee

EmployeeID  EmployeeName 
1234        Sooyoung      
1235        Yoona     
1236        Tiffany     
1237        Hyoyeon     
1238        Taeyeon       
1239        Seohyun
1240        Sunny
1241        Yuri

i want to generate the employee id sequentially like this : 

1234 as A     
1235 as B    
1236 as C   
1237 as D 
1238 as C      
1239 as E
1240 as D
1241 as F

may you know what mysql code and php what i have to use? thank you

TARA
  • 529
  • 1
  • 6
  • 23
  • Are you trying to make it so the output of the query contains A,B,C rather than 1234,1235,1236? – TurdPile Jul 07 '15 at 02:18
  • @tara I don't think you can enact on data directly from a query. I will continue looking for you though, and if not, I will provide a PHP companion script that you would use alonside the query. – TurdPile Jul 07 '15 at 02:29

2 Answers2

1

This is how you would do it in PHP, I am assuming this is the method you want seeing as you tagged PHP.

<?php

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("yourdatabase") or die(mysql_error());

$employees = mysql_query("SELECT * FROM Employee ORDER BY EmployeeID") 
or die(mysql_error());  

$letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$position = 0;
$position2 = 0;
$toomany = '';

while($row = mysql_fetch_array( $employees )) {
    echo "<DIV>" . $toomany.substr($letters, $position, 1) . " = " . $row['EmployeeName'] . " </div>";
    $position ++;
    if($position > 25) {
        $position = 0;
        $position2 ++;
        if($position2 > 25) { echo "We need to rethink this idea."; break; }
        $toomany = substr($letters, $position2, 1);
    }
}

?>

EDITED: What if you run out of alphabets ? what did you want to happen? I took a guess, but you will have to explain more if I am not right.

EDITED: TurdPile has a simplified version of what I had. Accept his answer.

n099y
  • 414
  • 2
  • 16
1

As stated in the comments, I don't think you can work with data directly and evaluate it to the alphabet as you are wishing (of course you can still assign results to variables).

If you must have A-Z for whatever reason, I think the best solution would be to use a PHP script to parse the results of the query. If this is the case, you won't really need the Employee ID number, as you can work with the occurrence of the results, but I left the ID number in there anyways.

<?php
// DB connection info
include("mysql_connection.php");

$query = mysql_query("SELECT EmployeeID, EmployeeName FROM Employees WHERE EmployeeID >= 1234");
$results = mysql_fetch_array($query);
$iteration = "A";
echo "ID - Name";
while(list($empNumber,$empName) = $results)){
    echo $iteration." - ".$empName;
    ++$iteration;
}
?>

Note: mysql_query was used for the example, generally you should use MySQLi or PDO.

Note 2: The use of $iteration in this will automatically increment the letter output, when it reaches Z, it should go onto AA.

TurdPile
  • 976
  • 1
  • 7
  • 21