PHP query to db

[odisey]

I am not sure if this is the right forum:

 

My question is about running a db parse query.  Using PhpMyAdmin I would run something like:

 

SELECT student.studentname, characteristics.char_desc, SUM(student_score.score) as total

FROM student_score

    INNER JOIN question  ON student_score.q_id = question.q_id

    INNER JOIN area ON question.area_id = area.area_id

    INNER JOIN characteristics ON area.char_id = characteristics.char_id

    INNER JOIN student ON student_score.student_id = student.student_id

WHERE student_score.mid_final = 'M'

    AND student_score.student_id = '7001234'

    AND student_score.year = '2006'

GROUP BY student.studentname, characteristics.char_desc

 

 

Notice I have accessed columns in two tables, student and characteristics.  Is it possable to run such a query in Php to parse a MySQL db?

 

Something like

 

    $query = "SELECT column FROM table1 AND column FROM table2, SUM SUM (student_score.score) as total

FROM student_score

    INNER JOIN question  ON student_score.q_id = question.q_id

    INNER JOIN area ON question.area_id = area.area_id

    INNER JOIN characteristics ON area.char_id = characteristics.char_id

    INNER JOIN student ON student_score.student_id = student.student_id

WHERE student_score.mid_final = 'M'

    AND student_score.student_id = '7001234'

    AND student_score.year = '2006'

GROUP BY student.studentname, characteristics.char_desc";

 

 

$result = @mysql_query($query);

   

  if ($result) {

 

 

  while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

 

 

PRINT HERE

 

[aschk]

Is it possable to run such a query in Php to parse a MySQL db?

 

Yes...

[odisey]

Id is possable to get an example of the query and output?

 

 

[aschk]

You've done all the hard work yourself already.

 

The finishing touch (to see what you are getting) would be to do something like :

while($row ....) {

echo "<pre>";
print_r($row);
echo "</pre>";

}

Please note this is for debugging really, but it'll show you what you need to know.

[odisey]

Actually Barand helped me construct the tables and joins...expert.  I have the site built now...And I am adding the last page...the print results pages(s). 

 

Thank you,

Marc

[odisey]

I am trying this simple snippet to get a print to build from -- I get nothing...  Any ideas why?

 

 $query = "SELECT student.last_name, characteristics.char_desc

WHERE student_score.mid_final = 'M'
    AND student_score.student_id = '700900127'
    AND student_score.year = '2006'";

/*   

, SUM (student_score.score) as total
FROM student_score 

INNER JOIN question ON student_score.q_id = question.q_id
    INNER JOIN area ON question.area_id = area.area_id
    INNER JOIN characteristics ON area.char_id = characteristics.char_id
    INNER JOIN student ON student_score.student_id = student.student_id

GROUP BY student.last_name, characteristics.char_desc";    */


$result = @mysql_query($query);
          
         if ($result) {
                             echo 'result';
        
      while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
   

	/*	  	  
	   echo '<p align="left">' . $row['total'] . '</p>'; 
	   
	//  ' . $row['char_desc']'</p>'; 	
	   
	   echo '<br /><br />';     

		*/


		echo "<pre>";
print_r($row);
echo "</pre>";
		   } 
		   
	}   

 

 

 

Thank you for all your help,

Marc

[revraz]

Is student id and year INTs or CHARS in your DB?

 

If INTs, remove the ' '

 

AND student_score.student_id = 700900127

    AND student_score.year = 2006";

 

[odisey]

I had tried that - and again after your post...  Nothing prints...?

[odisey]

I've narrowed it down to the SELECT statement dot syntax.  Why would dot syntax execute in the WHERE statement and not the SELECT statement?

 

 

This will not execute:

 

	 $query = "SELECT student_score.description_id, student.last_name WHERE student_score.mid_final = 'M' AND student_score.student_id = 700900127 AND student_score.year = 2006 AND student.student_id = 700900127";

 

This will

 

	 $query = "SELECT description_id FROM student_score WHERE student_score.mid_final = 'M' AND student_score.student_id = 700900127 AND student_score.year = 2006";

 

 

Marc