0

Tables diagrams here

her is the mySql to select the students

$institute = $_POST['institute'];
$sections = $_POST['sections'];
$division = $_POST['division'];

$getSearch = $db->prepare("SELECT
a.name, a.phase, a.setNumber, a.email, a.sudImage, a.activity, a.id AS stud_id,
b.id, b.ins_name,
c.id, c.sec_name,
d.id, d.div_name,
e.id, e.std_id, e.sub_id, e.absence,
f.id, f.sub_name, f.subHour, f.level
FROM student_basic_info AS a
CROSS JOIN institutes AS b ON (a.institute = b.id)
CROSS JOIN ins_sections AS c ON (a.section = c.id)
CROSS JOIN ins_division AS d ON (a.division = d.id)
CROSS JOIN student_absence AS e
CROSS JOIN ins_subjects AS f ON (e.sub_id = f.id)
WHERE a.institute =? AND a.section = ? AND a.division =?
GROUP BY a.id
");

$studSearch = array();
$getSearch->bind_param('iii', $institute, $sections, $division);
if ($getSearch->execute()) {
$results = $getSearch->get_result();
//var_dump($studSearch);
$row_cnt = $results->num_rows;
?>

The Problem is the results should have 2 rows effected
row one come's out right the second come's duplicated

see the var_dump row one

array (size=1)
  0 => 
    array (size=17)
      'name' => string 'Yousef' (length=6)
      'phase' => string 'اعدادي' (length=12)
      'setNumber' => int 1234
      'email' => string 'johnef' (length=6)
      'sudImage' => string '' (length=0)
      'activity' => int 0
      'stud_id' => int 2
      'id' => int 3
      'ins_name' => string 'fonon' (length=5)
      'sec_name' => string 'فنون مسرح' (length=17)
      'div_name' => string 'شعبة مرجانية 10' (length=26)
      'std_id' => int 4
      'sub_id' => int 3
      'absence' => string '1' (length=1)
      'sub_name' => string 'فنون تطبيقية' (length=23)
      'subHour' => string '2' (length=1)
      'level' => string 'المستوي الأول' (length=25)

see the var_dump row two

array (size=2)
  0 => 
    array (size=17)
      'name' => string 'Yousef' (length=6)
      'phase' => string 'اعدادي' (length=12)
      'setNumber' => int 1234
      'email' => string 'johnef' (length=6)
      'sudImage' => string '' (length=0)
      'activity' => int 0
      'stud_id' => int 2
      'id' => int 3
      'ins_name' => string 'fonon' (length=5)
      'sec_name' => string 'فنون مسرح' (length=17)
      'div_name' => string 'شعبة مرجانية 10' (length=26)
      'std_id' => int 4
      'sub_id' => int 3
      'absence' => string '1' (length=1)
      'sub_name' => string 'فنون تطبيقية' (length=23)
      'subHour' => string '2' (length=1)
      'level' => string 'المستوي الأول' (length=25)
  1 => 
    array (size=17)
      'name' => string 'Rida Ali' (length=8)
      'phase' => string 'ابتدائي' (length=14)
      'setNumber' => int 0
      'email' => string 'johnef' (length=6)
      'sudImage' => string '' (length=0)
      'activity' => int 0
      'stud_id' => int 7
      'id' => int 3
      'ins_name' => string 'fonon' (length=5)
      'sec_name' => string 'فنون مسرح' (length=17)
      'div_name' => string 'شعبة مرجانية 10' (length=26)
      'std_id' => int 4
      'sub_id' => int 3
      'absence' => string '1' (length=1)
      'sub_name' => string 'فنون تطبيقية' (length=23)
      'subHour' => string '2' (length=1)
      'level' => string 'المستوي الأول' (length=25)

I try to put this condition CROSS JOIN student_absence AS e ON (e.std_id = a.id) but it come's out with one row only

try 2 with INNER JOIN

.....<br/>
FROM student_basic_info AS a
INNER JOIN institutes AS b ON (a.institute = b.id)<br/>
.....

results
it coming out correct but it should come out with 2 rows now it's coming out with one row only.

try 2 with LEFT JOIN

    SELECT
a.name, a.phase, a.setNumber, a.email, a.sudImage, a.activity, a.id AS stud_id,
b.id, b.ins_name,
c.id, c.sec_name,
d.id, d.div_name,
e.id, e.std_id, e.sub_id, e.absence,
f.id, f.sub_name, f.subHour, f.level
FROM student_basic_info AS a
INNER JOIN institutes AS b ON (a.institute = b.id)
INNER JOIN ins_sections AS c ON (a.section = c.id)
INNER JOIN ins_division AS d ON (a.division = d.id)

LEFT JOIN student_absence AS e ON (e.std_id = a.id)
LEFT JOIN ins_subjects AS f ON (f.id = e.sub_id)

WHERE a.institute =? AND a.section = ? AND a.division =?
GROUP BY a.id

results
it coming out correct

Yousef Altaf
  • 2,631
  • 4
  • 46
  • 71
  • Are you sure that this should be `CROSS JOIN` intead of `INNER JOIN` ? – Jorge Campos Nov 04 '15 at 13:35
  • I try this too and no luck I don't think it makes any different – Yousef Altaf Nov 04 '15 at 13:38
  • 2
    There is a HUGE difference between a CROSS and an INNER join. You should read about it here on this thread: http://stackoverflow.com/questions/17759687/cross-join-vs-inner-join-in-sql-server-2008 – Jorge Campos Nov 04 '15 at 13:41
  • I think there is something do with this `CROSS JOIN student_absence AS e` – Yousef Altaf Nov 04 '15 at 13:41
  • Most probably it is this. It should be all INNER JOINs (seeing your problem) and that specific line should be something like `INNER JOIN student_absence AS e ON (e.someColumn = OneOfTheAlreadyMentionedTables.SomeColumnName)` – Jorge Campos Nov 04 '15 at 13:44
  • @JorgeCampos see my edit – Yousef Altaf Nov 04 '15 at 13:50
  • Then you have now a problem with your data, some table may have two equivalences (for your joins). Is the two rows equals? If so adding just the DISTINCT keyword will solve your problem like `select DISTINCT a.name ...... ` But only if this is just what you need. – Jorge Campos Nov 04 '15 at 14:14
  • @JorgeCampos attached my tables diagrams to the question – Yousef Altaf Nov 04 '15 at 14:53
  • a link to google drive https://drive.google.com/open?id=0B6fKNygRi_UGSHFlSktmRW9OM1E – Yousef Altaf Nov 04 '15 at 15:22
  • I am very sorry for that here is a free link http://1drv.ms/1MzSy3m it's on oneDrive – Yousef Altaf Nov 04 '15 at 18:26
  • 1
    Which two rows are you expecting to be output? Make it really clear what you expect the result to be. If it is your: `see the var_dump row two` Then make it clear that is the result you expect to see from the query. If we provide an answer then we are expected to explain how we got to it so it so you will understand the process. – Ryan Vincent Nov 05 '15 at 15:13
  • @RyanVincent See my edit and +1 for your effort and about your question, I expected the the effected rows to come up. – Yousef Altaf Nov 07 '15 at 15:13

0 Answers0