1

this code read the data from database where i have 3 tables i want trying to print the title which is course and its details in module table but i got an error saying Unknown column 'SCQF07' in 'where clause' can give me advice how to solve this problem thanks in advance 

<?php
    $m = new mysqli("localhost","user","pwd","courses");
    if ($m->connect_errno)
    {
        die("Database connection failed");
    }
    $m->set_charset('utf8');
    $i = $_REQUEST['ide'];

    $sql = "SELECT module.level, module.credits, cm.course, module.school, course.title
              from cm
              join module on (module = module.id)
              join course on (course = course.id)
              where  module = $i";

    $res = $m->query($sql) or die($m->error);
    $CastArray = Array();
    $row = $res->fetch_assoc();

?>

<html>
  <head>
  </head>
  <body>
    <h1><?php  echo "$row[tile] - <a href=movies3.php?ide=$row[course]>$row[title]</a>"; ?></h1>
  </body>
</html>

table course 

 id primary key      
 title     
 href     
level   
 award   
summary  
 dept     
 subject  
overview 
 wyl      
careers

table cm

course foreign key
module  foreign key
num

table module

id      primary key
title       
level 
credits       
school
Brad Larson
  • 170,088
  • 45
  • 397
  • 571
32113
  • 39
  • 6
  • where are you setting "$i" and what it the query you have before the H1 to allow the setting of the variables in the heading? - also is it a typo in the heading line "$row[tile]" should be "$row[title]"? – gavgrif Mar 22 '16 at 22:23
  • thanks - and the typo? in the heading? or is it supposed the be "tile"? the the $i is wrapped in I presume? and there is a form somewhere that yields the request of "ide"? – gavgrif Mar 22 '16 at 22:26
  • 1
    The code shown is incomplete - how is $sql executed? How is $row generated? – Don't Panic Mar 22 '16 at 22:33
  • your join conditions are incomplete – Mihai Mar 22 '16 at 23:55
  • Why are you vandalizing your own post? – Darwin von Corax Mar 23 '16 at 01:05
  • 2
    With respect, Stack Overflow questions and the contributed and upvoted answers aren't just for you; they help a whole community. It's best if you refrain from deleting them: someone else may have a similar problem and learn from your experience. – O. Jones Mar 23 '16 at 01:16

1 Answers1

2

Your value $_REQUEST['ide'] contains non-numeric characters. Thus, the where clause

WHERE module = $i

expands to something like

WHERE module = SCQF07

and MySQL interprets the unquoted string SCQF07 as a column name. You need to either

  1. change your query to

    $sql = "SELECT module.level, module.credits, cm.course, module.school, course.title
      from cm
      join module on (module = module.id)
      join course on (course = course.id)
      where  module = '$i'"

(note the single quotes around $i), or

  1. switch to using prepared statements, which automagically handle quoting, escaping and type-matching and also provide a measure of protection against SQL injection attacks.

To retrieve all rows of the result you need to call fetch repeatedly. This is usually done within a while loop, like so:

<?php
    $res = $m->query($sql) or die($m->error);
    $CastArray = Array();

    while ($row = $res->fetch_assoc())
    {
?>
    <h1><?php  echo "{$row['title']} - <a href=movies3.php?ide={$row['course']}>{$row['title']}</a>"; ?></h1>
<?php
    }
?>
Community
  • 1
  • 1
Darwin von Corax
  • 5,201
  • 3
  • 17
  • 28