Using PHP variable to fetch data from mysql

Question

I have a daily question quiz page and I have two database tables with the following names:

1) qbank 2) users_log

Now once the user submits the answer, it takes the user to the grade.php where I access if he/she answered the question correctly. My problem is the following code:

$sql = ("SELECT * FROM users_log WHERE username = '$username_wanted'");
$result = $conn->query($sql);
            if ($result->num_rows > 0) {
                while($row = $result->fetch_assoc()) {
                    $myuser = $row["username"];
                    echo $myuser;
                    echo "It found the user";
                    $mycolumn = $row_y[$_POST['question-id']];
                    echo $mycolumn;
                                        }
            }

What I would like to do is to select that COLUMN from "users_log" TABLE where the COLUMN name is equal to the question-id from "qbank" table, where the user submitted in the previous page.

Let's just say I define a variable like such:

$questionID = "001"

What is the correct way of fetching with that variable:

$mycolumn = $row_y[$questionID];

Thanks, oliver_foxx

Structure for the users_log table: username | 001 | 002 | 003 | etc... — oliverfoxx, Apr 15 '16 at 07:27

score 0 · Accepted Answer · edited May 23 '17 at 12:31

0

Your must construct a query like this (if i understood correctly)

$sql = "SELECT username, ".$_POST['question-id']." FROM users_log WHERE username = '".$username_wanted."'";

Of course there are some issues here but there are beyond the scope of this question. You may wanna research on these:

1) You are vulnerable to sql injection

2) I'm not very confident your sql structure is a good one. Seems like you have a column for each question; that sounds terribly denormalized

edited May 23 '17 at 12:31

Community

1
1

answered Apr 15 '16 at 07:34

Sharky

6,154
3
39
72

I dont think you should ever put raw request data into a query – Alexander Holman Apr 15 '16 at 07:38
@AlexanderHolman i'm specifically telling this in my answer. ***"but there are beyond the scope of this question"*** – Sharky Apr 15 '16 at 07:39
thanks, what I'd like to in users_log is to add +/- for each question to see if the user answered correctly or not and get points that. Do you suggest any other alternative design? – oliverfoxx Apr 15 '16 at 07:41
@OliverFox your approach would be good if users must have a record for **all** these questions, and the questions will **never** change, and no questions will **ever added or removed**. Well that sounds highly unlikely. i would suggest a table `users_log` with columns `username,question_id` so you can add and remove questions at will. Also, be sure to see that sql injection problem. To be honest you should go read that right now instead of normalizing/denormalizing your db structure. – Sharky Apr 15 '16 at 07:45
You dont specify why. I just think your example should exclude basic issues like that one – Alexander Holman Apr 15 '16 at 07:45
@AlexanderHolman it's not practical for me to write an essay for every answer that suffers from sql injection. But i always ring the bell, OP can clearly see it, then its up to him if he wants to investigate further. – Sharky Apr 15 '16 at 07:46

Using PHP variable to fetch data from mysql

1 Answers1