SQL course search

[dweb]

hi all

 

wonder if someone can help me

 

i have a table called "course_selection"

id    course      spaces    difficulty
1     IT          4         220.10
2     Maths       3         154.43
3     Science     1         154.43
4     Dance       9         50.01

I have a var which defines how many spaces are needed

$spaces_needed = 2;

so i want to run a query which

 

1: returns the courses available that can accomodate the spaces needed, but spreading the spaces over the least number of courses

 

2: returns the rows in an order with the least difficult first

 

 

My table and var example, it would return

id     course      spaces        spaces_now    spaces_used     difficulty
4      Dance        9            7             2                50.01

because i need to spread results over the least number of courses, if the var was

$spaces_needed = 11;

then it would return

id       course      spaces      spaces_now       spaces_used      difficulty
4        Dance       9           0                9                50.01
2        Maths       3           1                2                154.43

if the var was

$spaces_needed = 10;

then it would return

id        course      spaces      spaces_now      spaces_used      difficulty
4         Dance       9           0               9                50.01
3         Science     1           0               1                154.43

if the var was

$spaces_needed = 12;

then it would return

id       course      spaces     spaces_now      spaces_used     difficulty
4        Dance       9          0               9               50.01
3        Science     1          0               1               154.43
2        Maths       3          1               2               154.43

any help would be great

 

 

 

 

 

 

thanks

Edited March 6, 2014 by dweb

[jairathnem]

what do you mean by least number of courses?

[dweb]

what do you mean by least number of courses?

 

Thanks

 

What I mean is;

 

If the user wanted 3 spaces and

 

* "Maths" had 3 slots

* "Science" had 4 slots

 

* They both had the same difficulty level

 

Then it would select just 1 course, rather than taking 1 slot from Maths and 2 from Science.

 

Does that explain it?

[dweb]

is it possible? or what other information do you need? thanks

[jairathnem]

<?php
require_once 'connection.php';
$res = mysqli_query($con,"SELECT * FROM `course_selection` order by `difficulty`");
while($row=mysqli_fetch_assoc($res)){
$data[] = $row;
}
mysqli_close($con);
echo "Before preocessing: <br />";
var_dump($data);
$space = 120;
$space_needed = $space;
$space_filled = 0;
$i= 0;
$count = 0;
foreach($data as $value){
$count = $value['spaces'] + $count;
}
echo "count $count <br />";


if($count < $space) {  echo "Too may space requested, less available";die; }
while($space_filled != $space)
{


if($data[$i]['spaces'] <= ($space_needed - $space_filled)){
$space_filled = $data[$i]['spaces'] + $space_filled;
$data[$i]['spaces'] = 0;
}
else{
$data[$i]['spaces'] = $data[$i]['spaces'] - ($space_needed - $space_filled);
$space_filled = ($space_needed - $space_filled) + $space_filled;
}
$i++;
}
echo "<br/>after processing: <br />";
var_dump($data);
echo "<br /> $space_needed <br /> $space_filled";
?>

Personally I like these type of challeges as a programmer

the code doesnt update the DB but the changes are done to the array, which can be used to make change to DB.

Edited March 7, 2014 by jairathnem

[jairathnem]

EDIT to prev. post : change query to "SELECT * FROM `course_selection` order by `difficulty`, `spaces` desc"

[dweb]

thank you very much, i'll check that out :-)