2

I Have A Table Called skill in Which i Have Different Fields Like

skillPer1 SkillPer2 SkillPer3 ..... skillPer8. (skillPer=skillPercentage)

and another table called user which contains userImage and etc.. so i want to combine this two tables for skill result as well as user profile in same div

So When User Stores His Skill in % Out Of 100 Then I want To display The highest K From Those 8 skillPer fields

For Example User Has Store skillPer1 in PHP 80% skillPer2 in C++ 90% so That i want to display highest % from those table fields that is 90% So Basically I want This Two Statements two be combine together and get the user Profile as well as his skill Highest % but i cant figured it out how to do that

 <?php 
 $userID=$_REQUEST['userID'];
  $sql1= "(SELECT *, @maxVal:= GREATEST(skillPer1,skillPer2,skillPer3,skillPer4,skillPer5,skillPer6,skillPer7,skillPer8) as higherPercentage,
CASE @maxVal 
WHEN skillPer1 THEN 'skillPer1'
WHEN skillPer2 THEN 'skillPer2'
WHEN skillPer3 THEN 'skillPer3'
WHEN skillPer4 THEN 'skillPer4'
WHEN skillPer5 THEN 'skillPer5'
WHEN skillPer6 THEN 'skillPer6'
WHEN skillPer7 THEN 'skillPer7'
WHEN skillPer8 THEN 'skillPer8'
       END AS max_value_skill_name
FROM `skill`) ";         
 $res1 = mysql_query($sql1) or die(mysql_error());
while ($row1 = mysql_fetch_assoc($res1)) {

?>

<?php 
$userID = intval($_SESSION['userid']);
$sql = "(SELECT * FROM user ORDER BY signup_date DESC)"; 
$res = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($res))
{
?>

 <?php echo '../uploads/'.$row['userImage'];?>
<?php echo '../uploads/'.$row1['skillPercentage'];?>

<?php }} ?>
Abhi Burk
  • 2,003
  • 1
  • 14
  • 21

2 Answers2

0

You can write sql query like below....

SELECT id, GREATEST(a,b,c,d) as higherPercentage FROM `your_table`;

in above example a,b,c and d are the columns... hope this will help...

to get max value percentage with its skill column try below...

SELECT *, @maxVal:= GREATEST(a,b,c,d) as higherPercentage,
CASE @maxVal WHEN a THEN 'a'
WHEN b THEN 'b'
WHEN c THEN 'c'
WHEN d THEN 'd'
       END AS max_value_skill_name
FROM `your_table`

it will return result like.. resultset

Skalbhile
  • 156
  • 13
  • you will get max value of percentage in "higherPercentage " this column – Skalbhile Jul 13 '16 at 10:42
  • is it essential to create higherPercentage column or i shud echo it directly – Abhi Burk Jul 13 '16 at 10:45
  • you can directly echo it, but I think your problem will be remain for which skill you are getting this max value? for that you can refer this link http://stackoverflow.com/questions/12195558/greatest-value-of-multiple-columns-with-column-name – Skalbhile Jul 13 '16 at 10:47
  • Any One here for my help – Abhi Burk Jul 13 '16 at 17:14
  • Hi, as i can see your modified questions and code. so you can use join to combine both the tables, you can add join on users table with skills table, of course you need common user_id field in both the tables. so basically you don't need to fire separate queries, you can do this like `SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;` also please add where clause in your query to filter record for single user. as i can see you have user id in your session. – Skalbhile Jul 14 '16 at 07:02
0

MySQL GREATEST() function returns the greatest of the given arguments.

$result = mysqli_query($connection ,"SELECT id, GREATEST(Skill1,Skill2,Skill3,........) as highestskillPercentage FROM table") or die(mysqli_error($connection));

while($row = mysqli_fetch_assoc($result)){

$id = $row['id'];
$percentahe = $row['highestskillPercentage'];

if you will not use AS highestskillPercentage you will get output like

GREATEST(Skill1,Skill2,skill3,skill4…………………...)
90
48
78
23
85

but if you use highestskillPercentage you will get

 highestskillPercentage
    90
    48
    78
    23
    85
Passionate Coder
  • 7,154
  • 2
  • 19
  • 44