Need Help Regarding A Sql Join Query

heshan · October 8, 2012

Hi All,

I have a basic HTML form which accepts admission number and term values. Thereafter user should select 'Generate' button to view the results. I want these columns to be get displayed as results columns.

Admission Number, Name with initials, Subject, Term and Mark.

These data should be generated from following tables.

exam (exam_id, exam_name,__)

mark (mark_id, admission_no, subject_id, exam_id, mark)

student_info (name_with_initial,____)

subject (subject_id, name,___)

I have created following query to output the above results. But the page does not generate any output..

//Pass the form data
$admission_no=$_POST['admission_no'];
$term = $_POST['term'];

//query
$this->result = mysql_query("SELECT mark.admission_no, student_info.name_with_initial,subject.name, exam.exam_name, mark.mark FROM mark INNER JOIN subject ON  mark.subject_id=subject.subject_id INNER JOIN exam ON mark.exam_id=exam.exam_id INNER JOIN student_info ON mark.admission_no=student_info.admission_no WHERE mark.admission_no='$admission_no' AND exam.exam_name='$term'")   or die (mysql_error());

Barand · October 8, 2012

There is no code there that would produce any output

BTW I lost the will to live while I was scrolling through that query (has your return key broken?)

For the benefit of others

SELECT mark.admission_no, student_info.name_with_initial,
   subject.name, exam.exam_name, mark.mark
FROM mark
   INNER JOIN subject ON  mark.subject_id=subject.subject_id
   INNER JOIN exam ON mark.exam_id=exam.exam_id
   INNER JOIN student_info ON mark.admission_no=student_info.admission_no
WHERE mark.admission_no='$admission_no' AND exam.exam_name='$term'

Edited October 8, 2012 by Barand

heshan · October 9, 2012

Hi Barand,

I am generating reports in PHP by using TCPDF tool. There the report automatically comes provided that my query is accurate. We only need to change the SQL query in order to get the desired results. Here it displays columns in a atable format without any data. It means there is something wrong in my query.. :confused:

DarkerAngel · October 9, 2012

No I believe what Barand is saying is that the code that you have provided has no indication on how you are trying to display your SQL Query you are simply showing the area of the query that is run and nothing more.

Have you tried passing class::result ($this->result) to:

$array = mysql_fetch_assoc($this->result); // ??? Gather Result into an array (though this is demonstrative and would need to be looped for multiple rows)

Maybe for debug/development to make sure you are properly gathering data to use:

print_r($array);// Would display the array structure if data is available

Would output something like: (where "data" should be replaced by what is stored in the SQL tables)

Array
(
  [mark.admission_no] => data
  [student_info.name_with_initial] => data
  [subject.name] => data
  [exam.exam_name] => data
  [mark.mark] => data
)

Also as far as I'm aware TCPDF's purpose is only to format/output PDF files so I'm not sure what is going on exactly with the $this->result, but all it should be bringing back is a Resource ID for the query. Where it goes from there I can't tell from what you have shared.

But that's just my two cents for what I have interpreted from the thread so far. Hope this helps in getting you to what you're looking for.

heshan · October 9, 2012

Hi DarkerAngel,

Thanks for your points..I have tried this print_r($array) thing also..But there is not any output.It means i think the data should not be retrieved properly..Here is my full page.

<?php
require_once('tcpdf/config/lang/eng.php');
require_once('tcpdf/tcpdf.php');
class MYPDF extends TCPDF {

public function myconnection(){
$this->con = mysql_connect("localhost","root","");

mysql_select_db("student_management", $this->con);

$admission_no=$_POST['admission_no'];
$term = $_POST['term'];


$this->result = mysql_query("SELECT mark.admission_no, student_info.name_with_initial,subject.name, exam.exam_name, mark.mark FROM mark
INNER JOIN subject ON mark.subject_id=subject.subject_id INNER JOIN exam ON mark.exam_id=exam.exam_id
INNER JOIN student_info ON mark.admission_no=student_info.admission_no
WHERE mark.admission_no='$admission_no' AND exam.exam_name='$term'") or die (mysql_error());

$array=mysql_fetch_assoc($this->result);
print_r($array);
}

// Colored table
public function ColoredTable($header,$data) {
 // Colors, line width and bold font
 $this->SetFillColor(255, 128, 0);
 $this->SetTextColor(255);
 $this->SetDrawColor(128, 0, 0);
 $this->SetLineWidth(0.3);
 $this->SetFont('', 'B');
 // Header
 $w = array(25, 40, 20, 20, 20);
 $num_headers = count($header);
 for($i = 0; $i < $num_headers; ++$i) {
  $this->Cell($w[$i], 4, $header[$i], 1, 0, 'C', 1);
 }
 $this->Ln();
 // Color and font restoration
 $this->SetFillColor(255, 255, 255);
 $this->SetTextColor(0);
 $this->SetFont('');
 // Data
 $fill = 0;

 while($row2 = mysql_fetch_array($this->result))
  {

$this->Cell($w[0], 6, $row2['admission_no'], 'LR', 0, 'L', $fill);
$this->Cell($w[1], 6, $row2['name_with_initial'], 'LR', 0, 'L', $fill);
$this->Cell($w[2], 6, $row2['name'], 'LR', 0, 'L', $fill);
$this->Cell($w[3], 6, $row2['exam_name'], 'LR', 0, 'L', $fill);
$this->Cell($w[4], 6, $row2['mark'], 'LR', 0, 'L', $fill);

$this->Ln();
$fill=!$fill;

}

$this->Cell(array_sum($w), 0, '', 'T');
}
}
// create new PDF document
$pdf = new MYPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Maliyadeva Balika Vidyalaya');
$pdf->SetTitle('Student Mark Sheet');
$pdf->SetSubject('');
$pdf->SetKeywords('');
// set default header data
//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 011', PDF_HEADER_STRING);
// set header and footer fonts
//$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
//$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
//set margins
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
//set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
//set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
//set some language-dependent strings
$pdf->setLanguageArray($l);
// ---------------------------------------------------------

// set font
$pdf->SetFont('times', 'BI', 7);
// add a page
$pdf->AddPage();
// set some text to print
$txt = <<<EOD
Maliyadeva Balika Vidyalaya,
Colombo Road,
Kurunegala.
Tel:037 2222372


Student Mark Sheet
EOD;
// print a block of text using Write()
$pdf->Write($h=0, $txt, $link='', $fill=0, $align='C', $ln=true, $stretch=0, $firstline=false, $firstblock=false, $maxh=0);
//Column titles
$header = array('Admission Number', 'Name With Initials', 'Subject', 'Term', 'Mark' );
//Data loading
//$data = $pdf->LoadData($data2);
$pdf->myconnection();
// print colored table
$pdf->ColoredTable($header, "");
// ---------------------------------------------------------
//Close and output PDF document
$pdf->Output('Customer Information.pdf', 'I');
mysql_close($con);
?>

Barand · October 9, 2012

Heshan, always do your queries this way so it's easy to print the query when you have a problem

$sql = "SELECT mark.admission_no, student_info.name_with_initial,subject.name, exam.exam_name, mark.mark FROM mark INNER JOIN subject ON mark.subject_id=subject.subject_id INNER JOIN exam ON mark.exam_id=exam.exam_id INNER JOIN student_info ON mark.admission_no=student_info.admission_no WHERE mark.admission_no='$admission_no' AND exam.exam_name='$term'";
$this->result = mysql_query($sql) or die (mysql_error());

Then if problems you can easily print the actual query that was submitted to check it's structure and completeness

echo $sql;

heshan · October 9, 2012

Thanks Barand..I think it is ok..But why data is not coming...Is it due to a problem in my table structure??

SELECT mark.admission_no, student_info.name_with_initial,subject.name, exam.exam_name, mark.mark FROM mark INNER JOIN subject ON mark.subject_id=subject.subject_id INNER JOIN exam ON mark.exam_id=exam.exam_id INNER JOIN student_info ON mark.admission_no=student_info.admission_no WHERE mark.admission_no='2012/1' AND exam.exam_name='Term 2'TCPDF ERROR: Some data has already been output, can't send PDF file

Jessica · October 9, 2012

It means there is an error being printed to your screen. You can switch to logging errors or what I would usually do for a quick debug is comment out the line that generates the PDF to the screen, ($pdf->Output('Customer Information.pdf', 'I') and put a die() there, then just read the errors.

heshan · October 9, 2012

Hi Jessica, It resulted an empty page..It means data is not coming to the page...

Jessica · October 9, 2012

Look at the source of the page. Is there anything? Any whitespace? Do you still have that print_r($array); in there?

Did you add the echo $sql part?

Post the updated code.

Barand · October 9, 2012

SELECT mark.admission_no, student_info.name_with_initial,subject.name, exam.exam_name, mark.mark
FROM mark
INNER JOIN subject ON mark.subject_id=subject.subject_id
INNER JOIN exam ON mark.exam_id=exam.exam_id
INNER JOIN student_info ON mark.admission_no=student_info.admission_no
WHERE mark.admission_no='2012/1' AND exam.exam_name='Term 2';

Have you tried running the query directly on the database (phpmyadmin or similar) to see if it returns expected results?

Edited October 9, 2012 by Barand

heshan · October 10, 2012

Yeah..I have run it in my PHP myAdmin..An empty result set was found...

@ Jessica;Here is the coding..

<?php
require_once('tcpdf/config/lang/eng.php');
require_once('tcpdf/tcpdf.php');


class MYPDF extends TCPDF {

public function myconnection(){
$this->con = mysql_connect("localhost","root","");

mysql_select_db("student_management", $this->con);

$admission_no=$_POST['admission_no'];
$term = $_POST['term'];

$sql="SELECT mark.admission_no, student_info.name_with_initial,subject.name, exam.exam_name, mark.mark FROM mark
INNER JOIN subject ON  mark.subject_id=subject.subject_id
INNER JOIN exam ON mark.exam_id=exam.exam_id
INNER JOIN student_info ON  mark.admission_no=student_info.admission_no
WHERE mark.admission_no='$admission_no' AND exam.exam_name='$term'";							

$this->result=mysql_query($sql) or die (mysql_error());
}

// Colored table
public function ColoredTable($header,$data) {
// Colors, line width and bold font
$this->SetFillColor(255, 128, 0);
$this->SetTextColor(255);
$this->SetDrawColor(128, 0, 0);
$this->SetLineWidth(0.3);
$this->SetFont('', 'B');
// Header
$w = array(25, 40, 20, 20, 20);
$num_headers = count($header);
for($i = 0; $i < $num_headers; ++$i) {
$this->Cell($w[$i], 4, $header[$i], 1, 0, 'C', 1);
}
$this->Ln();
// Color and font restoration
$this->SetFillColor(255, 255, 255);
$this->SetTextColor(0);
$this->SetFont('');
// Data
$fill = 0;

while($row2 = mysql_fetch_array($this->result))
{

$this->Cell($w[0], 6, $row2['admission_no'], 'LR', 0, 'L', $fill);
$this->Cell($w[1], 6, $row2['name_with_initial'], 'LR', 0, 'L', $fill);
$this->Cell($w[2], 6, $row2['name'], 'LR', 0, 'L', $fill);
$this->Cell($w[3], 6, $row2['exam_name'], 'LR', 0, 'L', $fill);
$this->Cell($w[4], 6, $row2['mark'], 'LR', 0, 'L', $fill);


$this->Ln();
$fill=!$fill;

}

$this->Cell(array_sum($w), 0, '', 'T');
}
}
// create new PDF document
$pdf = new MYPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Maliyadeva Balika Vidyalaya');
$pdf->SetTitle('Student Mark Sheet');
$pdf->SetSubject('');
$pdf->SetKeywords('');
// set default header data
//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 011', PDF_HEADER_STRING);
// set header and footer fonts
//$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
//$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
//set margins
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
//set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
//set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
//set some language-dependent strings
$pdf->setLanguageArray($l);
// ---------------------------------------------------------

// set font
$pdf->SetFont('times', 'BI', 7);
// add a page
$pdf->AddPage();
// set some text to print
$txt = <<<EOD
Maliyadeva Balika Vidyalaya,
Colombo Road,
Kurunegala.
Tel:037 2222372


Student Mark Sheet
EOD;
// print a block of text using Write()
$pdf->Write($h=0, $txt, $link='', $fill=0, $align='C', $ln=true, $stretch=0, $firstline=false, $firstblock=false, $maxh=0);
//Column titles
$header = array('Admission Number', 'Name With Initials', 'Subject', 'Term', 'Mark' );
//Data loading
//$data = $pdf->LoadData($data2);
$pdf->myconnection();
// print colored table
$pdf->ColoredTable($header, "");
// ---------------------------------------------------------
//Close and output PDF document
//$pdf->Output('Customer Information.pdf', 'I');
die();
mysql_close($con);
?>

Edited October 10, 2012 by heshan

DarkerAngel · October 10, 2012

**facepalm**

If I wanted to upset the integrity of this site I would flood with some internet memes of there's your problem or facepalm, you should be testing with some relevant data, something that can produce a result.

You're not getting an error because apparently your SQL is valid your not getting a return because from what you said:

...An empty result set was found...

And as far as FCPDF; maybe we can forward that to a more relevant board when we cross that bridge.

heshan · October 10, 2012

In my JOIN query i removed following part in the last line..Thereafter When i select an admission number and term (term1, 2 or 3) relevant results were displayed. But it does not take account the 'term'..It means irrespective of the term (1, 2 or 3) marks were displayed relevant to that particular admission number.

AND exam.exam_name='$term'

Barand · October 10, 2012

When you get results for an admission number, what values are in the column "exam_name"?

heshan · October 10, 2012

Hi all,

There was a white space in my Term drop down list. That's the problem..Now i fixed it and works properly...... :happy-04:

Thanks all of you guyz who helped me out..thanks again..

Sign In

Need Help Regarding A Sql Join Query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information