0

I wrote a SQL statement. It works on SQL workspace but if embedded in PHP, it doesn't work:

$xcourse = mysql_query("select result.student, result.course,
                             course.course_code, result.score,course.unit 
                        from result 
                            left join course on result.course = course.id 
                            right join user on result.student = user.username 
                        where result.level = $xlevel 
                        and result.semester = $xsemester 
                        and result.class = '$dept' 
                        and result.year = '$xsession' 
                        and user.specialization = '2' 
                        order by result.student asc");

If values are inserted in SQL workspace, it will work. Please I will like to know what's wrong.

RiggsFolly
  • 83,545
  • 20
  • 96
  • 136
Boladale Kolade
  • 1
  • 3
  • Which errors do you get? – JimL Oct 07 '17 at 15:31
  • 1
    don't use `mysql_*`: https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Sebastian Brosch Oct 07 '17 at 15:32
  • 1
    `mysql_query` Is officially deprecated as of PHP 5.5 so check your php version using phpinfo(); and start using mysqli or more preferably PDO for any database ops. :) – BlackBurn027 Oct 07 '17 at 15:37
  • 1
    Please [don't use `mysql_*`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1); the `mysql_*` functions are outdated, [deprecated](http://us3.php.net/manual/en/intro.mysql.php), and insecure - they have been removed entirely from modern versions of PHP (version 7.0 and higher). Use [`MySQLi`](http://us3.php.net/manual/en/book.mysqli.php) or [`PDO`](http://us3.php.net/manual/en/intro.pdo.php) instead. – elixenide Oct 07 '17 at 15:51
  • 2
    Also, you are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). This is a possible reason your query fails, as well, but you haven’t actually given us enough information to solve your problem. – elixenide Oct 07 '17 at 15:52
  • thanks ED. looking at how to prevent SQL injection. that might be the best option for now. thanks once again – Boladale Kolade Oct 08 '17 at 14:58
  • i hve a list of students in a department offering different options i had to modify the sql so that i can also request for the options and not lump everything together. – Boladale Kolade Oct 08 '17 at 15:05
  • yes it worked. thank you everyone – Boladale Kolade Oct 09 '17 at 08:50
  • so i finally got this code working on my wamp server but on my cloud server, it gives me the following error : Warning: mysql_query(): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay . now my question is where do i set this. i have tries doing this $big = mysql_query("SET MAX_JOIN_SIZE=10"); need some help. thank you – Boladale Kolade Oct 09 '17 at 13:25

0 Answers0