Search SQL

[davy_yg]

studentmgt.php

    $search = $_GET['search'];
    echo $search;
?>
<div align="center">
    <div style="width:700px;text-align:left;padding-top:5px;">
        <?php  if (isset($confirmation)) { echo $confirmation; } ?>  
        <form method="get" action="<?php $_SERVER['PHP_SELF'] ?>">

                                    <br/>

                <?php
                //LOAD STUDENTS INFO

                $result = mysql_query("SELECT * FROM `student`
                INNER JOIN `group`
                ON group.group_id = student.group_id
                INNER JOIN `access`
                ON access.access_level_id = student.access_level_id ORDER BY group_name ASC WHERE group.group_name = '".$search."' OR student.stu_fname = '".$search."' OR stu_lname = '".$search."' OR student.stu_address = '".$search."' OR student.stu_hp = '".$search."' OR student.access_level = '".$search."'") or die(mysql_error());
                ?>


                Search: <input type="text" size="20px" name="search" value=""><br><br>

If I input the search text box this appears:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE group.group_name = 'jakarta' OR student.stu_fname = 'jakarta' OR stu_lna' at line 5

I wonder why the error appears?

[pritaeas]

ORDER BY comes after WHERE

[davy_yg]

if(!empty($_GET['search'])){
       $search = $_GET['search'];
       }
    else
       {
       $search = "";
       }
?>
<div align="center">
    <div style="width:700px;text-align:left;padding-top:5px;">
        <?php  if (isset($confirmation)) { echo $confirmation; } ?>  
        <form method="get" action="<?php $_SERVER['PHP_SELF'] ?>">

                                    <br/>

                <?php
                //LOAD STUDENTS INFO

                $result = mysql_query("SELECT * FROM `student`
                INNER JOIN `group`
                ON group.group_id = student.group_id
                INNER JOIN `access`
                ON access.access_level_id = student.access_level_id WHERE group.group_name = '".$search."' OR student.stu_fname = '".$search."' OR stu_lname = '".$search."' OR student.stu_address = '".$search."' OR student.stu_hp = '".$search."' OR access.access_level = '".$search."' ORDER BY group_name ASC") or die(mysql_error());
                ?>


                Search: <input type="text" size="20px" name="search" value=""><br><br>

I wonder why if I leave search empty then there is no data return, but if I delete $search = ""; then it appears an error that search is not declared.

What's the alternative to make this program works?

[pritaeas]

I wonder why if I leave search empty then there is no data return

Because if $search is an empty string, then the WHERE needs to match those columns where the value is an empty string. Most likely, you don't have any in your database.

If you want to return everything when $search is empty, just omit the WHERE.