Unable to get Data from database in php

Question

<?php 
    if(isset($_POST['search']))
    {
        $department = $_POST['department'];
        $class = $_POST['class'];
        $i = 1;
        $sql = "SELECT `student_id` , `name`, `htno`, `department`, `class` , `image_name` FROM student_detail where department=$department && class=$class"; 
        $query = mysqli_query($conn, $sql);
        if (!$query) {
            die ('SQL Error: ' . mysqli_error($conn));
        }
        while ($row = mysqli_fetch_array($query)) {?>

<tr>
    <td><?php echo $i++; ?></td>
    <td><?php echo $row['name']; ?></td>
    <td><?php echo $row['htno']; ?></td>
    <td><?php echo $row['department']; ?></td>
    <td><?php echo $row['class']; ?></td>
    <td>
        <button data-toggle="modal" data-target="#view-modal" data-id="<?php echo $row['student_id']; ?>" id="getStudent" class="btn cur-p btn-info"><i class="glyphicon glyphicon-eye-open"></i> View</button>                                             
        <a href="<?php echo BASE_URL; ?>/controller/delete.php?student_id=<?php echo $row['student_id']; ?>" class="btn cur-p btn-danger" onClick="return confirm('Are you sure you want to delete?')">Delete</a></button>
    </td>
</tr>

<?php }
    if ($result === false) {
    echo "No Data Found";
    }
}
?>

Getting error while using this code

SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'A' at line 1

What is wrong?

If department or class are strings, then you need to put quotes round them in your SQL - BUT you should be using prepared statements instead. — Nigel Ren, Apr 22 '18 at 07:24
Avoid the usage of `&&`, use `AND` instead. `&&` is bad practice. — Armin Šupuk, Apr 22 '18 at 07:25
@Armin Šupuk I have never heard that before, can you please elaborate? I use that all the time and have never had an issue. — Joseph_J, Apr 22 '18 at 07:29
Whoever is allowed to edit, please correct the intendation. There is code missing — caylee, Apr 22 '18 at 07:30
In the above code the class and department have some values i.e CLASS A or Department A. i want to search the student where the CLASS and Department having same values. — Rahul Kumar Sharma, Apr 22 '18 at 07:31
Change `department=$department && class=$class` to `department='$department' && class='$class'` — Nigel Ren, Apr 22 '18 at 07:32
But preferably (looking for a suitable source) - http://php.net/manual/en/mysqli.prepare.php — Nigel Ren, Apr 22 '18 at 07:34
@NigelRen please post that as an answer so this question can be closed. — Nick, Apr 22 '18 at 07:35
@Joseph_J, roundAbout, xanoetux It's not part of the official standard, so it's not implemented everywhere. It creates unnecessary incompatibilities, when switching between different engines. — Armin Šupuk, Apr 22 '18 at 07:46

score 3 · Answer 1 · answered Apr 22 '18 at 07:34

You should use prepared statements.

if(isset($_POST['search']))
    {
        $department = $_POST['department'];
        $class = $_POST['class'];
        $i = 1;
        $query = "SELECT 
            `student_id`,
            `name`,
            `htno`,
            `department`,
            `class`
            FROM student_detail 
            WHERE department = ? && class = ? ";

            $stmt = $conn->prepare($query);
            $stmt->bind_param('ss', $department, $class);  
            $stmt->execute();
            $result = $stmt->get_result();

        if (!$result) {
            die ('SQL Error: ' . mysqli_error($conn));
        }
        while ($row = $result->fetch_assoc()) { ?>

<tr>
    <td><?php echo $i++; ?></td>
    <td><?php echo $row['name']; ?></td>
    <td><?php echo $row['htno']; ?></td>
    <td><?php echo $row['department']; ?></td>
    <td><?php echo $row['class']; ?></td>
    <td>
        <button data-toggle="modal" data-target="#view-modal" data-id="<?php echo $row['student_id']; ?>" id="getStudent" class="btn cur-p btn-info"><i class="glyphicon glyphicon-eye-open"></i> View</button>                                             
        <a href="<?php echo BASE_URL; ?>/controller/delete.php?student_id=<?php echo $row['student_id']; ?>" class="btn cur-p btn-danger" onClick="return confirm('Are you sure you want to delete?')">Delete</a></button>
    </td>
</tr>

<?php }
    if ($result === false) {
    echo "No Data Found";
    }
}

score 2 · Answer 2 · answered Apr 22 '18 at 07:36

In your SQL, you're not enclosing the variables within quote marks:

where department=$department && class=$class

this will translate to

where department=My Department && class=My Class

(depending on the data in your variables of course).

You need to put quotes around the variables, and then you'll stop seeing this particular error:

where department='$department' AND class='$class'

also, use AND rather than &&

Finally - and most importantly, this is not good SQL practise, as you are open to SQL injection attacks, ie. where someone passes a string into the variable to try and break the query, or worse, edit/delete your data.

You would do well looking at how to construct SQL queries using prepared statements. There's a great guide here:

https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

Charis Moutafidis · Answer 3 · 2018-04-22T07:40:12.047

0

$sql = "SELECT `student_id` , `name`, `htno`, `department`, `class` , `image_name` FROM student_detail where department=$department && class=$class";

try changing that to

$sql = "SELECT student_id , name, htno, department, class , image_name FROM student_detail WHERE department= " . $department . " AND class=" . $class . ";";

Edit: As Aniket Sahrawat suggested, prepared statements is something you need to implement.

$sth = $dbh->prepare('SELECT name, colour, calories, fruit WHERE calories < ? AND  colour= ?');
$sth->execute(array(150, 'red'));

Edit2: Thanks Nigel Ren for noticing my mistake on the prepared statement ;p

edited Apr 22 '18 at 07:40

answered Apr 22 '18 at 07:32

Charis Moutafidis

363
1
4
17

1

Be `PREPARED`. . . Btw, you are missing the `'` for string literals. – Aniket Sahrawat Apr 22 '18 at 07:32
Well first he needs to fix the error. Prepared statements is something he has to add after that. – Charis Moutafidis Apr 22 '18 at 07:33
1

There is no any difference in above two codes. – Rahul Kumar Sharma Apr 22 '18 at 07:33
It is, you have added the ` character. – Charis Moutafidis Apr 22 '18 at 07:36
1

Your prepared statement is missing the last column name in the where clause – Nigel Ren Apr 22 '18 at 07:38

Unable to get Data from database in php

3 Answers3