read data from database got error

Question

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

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
– gavgrif, Commented Mar 22, 2016 at 22:23
thanks - and the typo? in the heading? or is it supposed the be "tile"? the the $i is wrapped in <?php ?> I presume? and there is a form somewhere that yields the request of "ide"? — gavgrif
– gavgrif, Commented Mar 22, 2016 at 22:26
The code shown is incomplete - how is $sql executed? How is $row generated? — Don't Panic
– Don't Panic, Commented Mar 22, 2016 at 22:33
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
– O. Jones, Commented Mar 23, 2016 at 1:16

Community · Accepted Answer · 2017-05-23 11:45:08Z

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

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

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
    }
?>

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Mar 22, 2016 at 22:31

Darwin von Corax

5,2643 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Darwin von Corax Over a year ago

Yes. That's what I want. Does the query look as you expected it to look?

Darwin von Corax Over a year ago

Then are you sure this is the block of code that's causing trouble? I just ran the query on my own system and it worked fine.

Darwin von Corax Over a year ago

See my revised solution.

Darwin von Corax Over a year ago

Can you add the echo $sql; statement to the page at napier.ac.uk so I can see what's going on there?

Darwin von Corax Over a year ago

Now it's working for me. Do you need to clear your browser's cache?

|

Collectives™ on Stack Overflow

read data from database got error

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related