How get data from MySQL database by specific id in url

Question

How i can get data from MySQL database by specific id in url

Url

http://localhost/php/edit_student.php?id=11

My function

function selected_students($connect){
$sentence = $connect->prepare('SELECT students.student_name, students.student_thumbnail FROM students JOIN courses_students ON courses_students.student_id = students.student_id JOIN courses ON courses_students.course_id = courses'.$_GET['id'].'GROUP BY courses_students.student_id');
$sentence->execute(array());
return $sentence->fetchAll();
}

(1) Your code is wide open to SQL injection. (2) In what way is your code failing? — David, Mar 16 '17 at 12:24
You would run into problem of sql injection by directly passing query param into sql query. You should use the recommended way for eg. use of `bindParam()` method... — Sagar Guhe, Mar 16 '17 at 12:24
how is your id field data in the database? is it a string or int? — bansi, Mar 16 '17 at 12:26
It's working when id is (courses_students.course_id = 10) but with $_get['id'] not working — bdroid, Mar 16 '17 at 12:27
try `courses_students.course_id = '.$_GET['id'].' GROUP ` but i won't recommend it. too unsecure — bansi, Mar 16 '17 at 12:28
Your query reads like : 1`ON courses_students.course_id = courses'.10`. — Masivuye Cokile, Mar 16 '17 at 12:30
@bdroid before u use `$_GET[]` u need to check if id isset and not empty — Masivuye Cokile, Mar 16 '17 at 12:31
@bdroid: (1) Google "PHP SQL injection" to learn more. Basically, you're allowing users to execute any code they want on your database by putting user input directly in your SQL code. (2) Why can't you? What's stopping you? — David, Mar 16 '17 at 12:32
@bdroid you can google about sql injection and will get lot of information about it. — Sagar Guhe, Mar 16 '17 at 12:39

Masivuye Cokile · Accepted Answer · 2017-03-16T12:53:18.397

0

As I have mentioned you need to check if the $_GET['id'] isset and is not empty in the comments, and also when u using prepare() you must not inject a variable direct into your query you must use placeholder, then bind and execute. PDO have two types of placeholders which are :PlaceHolderName and ?

 <?php
    function selected_students($connect)
    {

        if (isset($_GET['id']) && !empty($_GET['id'])) {

            $id = intval($_GET['id']);

            $sentence = $connect->prepare("SELECT students.student_name,students.student_id,students.student_thumbnail,courses_students.student_id,courses.course_id from students,courses join courses_students on students.student_id = courses_students.student_id and courses.course_id = ? GROUP BY courses_students.student_id");
            $sentence->execute([$id]);

            $results = $sentence->fetchall();

            if ($results > 0) {
                // Results exists display them



            }


        } else {

            // return error the id is not set
        }


    }
    ?>

Here are the good places where u can learn pdo

https://phpdelusions.net/pdo

http://jayblanchard.net/demystifying_php_pdo.html

edited Mar 16 '17 at 12:53

answered Mar 16 '17 at 12:47

Masivuye Cokile

4,754
3
19
34

in (?) i want to get the id from url(...edit_student.php?id=11) like this (SELECT students.student_name,students.student_id,students.student_thumbnail,courses_students.student_id,courses.course_id from students,courses join courses_students on students.student_id = courses_students.student_id and courses.course_id = 11 GROUP BY courses_students.student_id) – bdroid Mar 16 '17 at 13:08
`?` is a placeholder for the id.... comment out all your code and type `var_dump($_GET['id']);` tell m what u get – Masivuye Cokile Mar 16 '17 at 13:11
i get this: string(2) "11" – bdroid Mar 16 '17 at 13:17
good, can u update u question with the latest code u have and also do include your database – Masivuye Cokile Mar 16 '17 at 13:19
I need to see the code u currently have so I can be able to knw where the error is – Masivuye Cokile Mar 16 '17 at 13:26
I resolve it with: $id = $_GET['id']; $sentence = $connect->prepare("SELECT students.student_name,students.student_id,students.student_thumbnail,courses_students.student_id,courses.course_id from students,courses join courses_students on students.student_id = courses_students.student_id and courses.course_id = ".$id); $results = $sentence->fetchall(); but i cant add GROUP BY... – bdroid Mar 16 '17 at 13:57
`courses.course_id = ".$id); ` should be `courses.course_id = ?); ` then `$sentence->execute([$id]);` – Masivuye Cokile Mar 16 '17 at 14:00
i get error with your code PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: no parameters were bound in functions.php on line 230 – bdroid Mar 16 '17 at 14:26
its working with: if (isset($_GET['id']) && !empty($_GET['id'])) { $id = intval($_GET['id']); $sentence = $connect->prepare("SELECT students.student_name,students.student_id,students.student_thumbnail,courses_students.student_id,courses.course_id from students,courses join courses_students on students.student_id = courses_students.student_id and courses.course_id = ? GROUP BY courses_students.student_id"); $sentence->execute([$id]); $results = $sentence->fetchall(); }} – bdroid Mar 16 '17 at 14:37
yes that should work if the answer helped u may upvote and accpt – Masivuye Cokile Mar 16 '17 at 14:41
`isset($_GET['id']) && !empty($_GET['id'])` should not exist in any code for any reason. [Why check both isset() and !empty()](https://stackoverflow.com/a/4559976/2943403) – mickmackusa Mar 11 '22 at 02:05

How get data from MySQL database by specific id in url

Url

My function

1 Answers1

Linked