How to enter data into database

PHP
1

Hello,

I have two tables.
Table 1 contains 10 questions with options for answer like: ID Question OPTION1 OPTION2 OPTION3 OPTION4.

The second table contains : ID USERNAME QUESTION1-------QUESTION10 ANSWER!--------ANSWER10

Now what i want is that when user logins, he should be presented with those 10questions from TAble 1.
With those questions he should be presented with options. When he answer those, that should be inserted into table 2 Table 2 already has ID and USERNAME filled.

Till now, i have succeeded in displaying the data to the user page in below code. Here the question is represented through Placeholder. But i dont know how to take user input from this and how to insert it into database.

<?php
$sql = "SELECT * FROM mymake ";
$query = mysqli_query($db_conx,$sql);
$numrows = mysqli_num_rows($query);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style>
form > select { width:280px;}
</style>
</head>
<body>
<form method="post" action="hello.php">
<?php
if ($numrows > 0){
  while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
?>

    <input type="text" name="q[]" placeholder="<?php echo $row["question"];?>"  size="82" disabled >
    <select name="response[]" required="required">
      <option value=""></option>
      <option value="<?php echo $row["option1"]; ?>"><?php echo $row["option1"]; ?></option>
      <option value="<?php echo $row["option2"]; ?>"><?php echo $row["option2"]; ?></option>
      <option value="<?php echo $row["option3"]; ?>"><?php echo $row["option3"]; ?></option>
    </select>
    <br>
<?php
  }
}
?>
<br /><input type="submit" value="Ready to Go" name="submit" />
<input type="reset" value="Reset" name="reset" />
</form>

</body>
</html>
2

First, is there a reason <select name=“response[]” required=“required”> and name=“q” is assigned as an array? Remove the brackets if not.

Simply collect the data



if( $_POST ) {

$name = $_POST['name'];
$response = $_POST['response'];

}

then use the $name and $response variables in your insert query

3

Hi, i used , as there will be 10questions retrieved from the query. each answer needs to be entered into database

4

I’m sorry, I completely missed the query at the top of the script first time I read it.

Okay, so you would simply do this:



$query = "INSERT INTO table (name, response) VALUES ";

foreach($_POST['name'] as $i => $name) 
{ 
  // Get values from post.
 $name = $_POST['name'];
 $response = $_POST['response'];

  // Add to database
  $query = $query." ('$name','$url') ,";
}
$query = substr($query,0,-1); //remove last char
$result = mysqli_query($query);

If you do it like that, you will only hit the database once, rather than ten times.

5

Can you please let me understand the logic for above. I am new for the array. What $url is here,… Where are we entering response in that? Is the avove query will fill my all question and answers filled ?

6

No problem.

First, $url was a typo; I typed that from my phone, and it must have been autocomplete or something. $url is supposed to be $response

As for the logic behind that –

A lot of people make the mistake of putting their queries inside a loop. For example:

* please note that there is an error in this code, this is just for clarity

<?php

// Get values from post.
 $name = $_POST['name'];
 $response = $_POST['response'];

while( $name ) {

  $query = "INSERT INTO table (name, response) VALUES $name, $response";
  $result = mysqli_query($query);

}

?>

What that code means is " WHILE $name is set, insert this data into the database ".

So what happens is that the loop will check to see if $name is set, and if so, run the query.
After it runs the query, it checks to see if $name is still set. If so, it runs the query again.
And it keeps repeating that process until $name is no longer true, or set. In your case, it will have run through the loop 10 times, since you were expecting 10 responses.

That works as expected and does what it wants you to do, but running 10 different queries is very wasteful of server resources. Wouldn’t it be better if you could insert all 10 responses in just one query? That’s exactly what the code I showed above will do.

  1. You first define the sql query, but do not run it yet, with $query = "INSERT INTO table (name, response) VALUES ";

  2. Next, you set up the foreach loop. The foreach loop collects all the $_POST[‘name’] responses, and assigns it to a variable called $i foreach($_POST[‘name’] as $i => $name)

  3. Then, we append all the $names and $responses that were submitted to the query we setup in #1 above $query = $query." (‘$name’,‘$response’) ,";

  4. So far, $query now looks like this:

$query = " INSERT INTO table (name, response) VALUES (‘$name’,‘$response’), (‘$name’,‘$response’), (‘$name’,‘$response’), (‘$name’,‘$response’), (‘$name’,‘$response’), (‘$name’,‘$response’), (‘$name’,‘$response’), (‘$name’,‘$response’), (‘$name’,‘$response’), (‘$name’,‘$response’), ";

  1. We have one last thing to do…notice the extra comma at the end? I[/I],

  2. We remove that with $query = substr($query,0,-1);

  3. Finally, we can execute the query: $result = mysqli_query($query);

7

I haven’t tried it yet but i am impressed with your explanation. One more question, my table already have ID and username filled. Is it possible to insert the data on the basis of WHERE condition?

8 
<?php

// Get values from post.
 $name = $_POST['name'];
 $response = $_POST['response'];

while( $name ) {

  $query = "INSERT INTO table (name, response) VALUES $name, $response";
  $result = mysqli_query($query);

}

?>

That code is wide open to SQL Injection attacks, any data submitted by the user in any way needs to be sanitized and either escape or prepared statements used

9

Hello SpacePhoenix ,

In m original code i am using,
$ques=preg_replace(‘#[^a-z0-9]#i’, ‘’, $_POST[‘q’]);
$ans=preg_replace(‘#[^a-z0-9]#i’, ‘’, $_POST[‘response’]);

hope its fine?

10

I just figured out that WHERE doesnt works with INSERT. What shall i do to change this code to use for UPDATE statement

11

I know, like I mentioned in the note, it was just for clarity

12

@arout77 : I just tried your code, it is giving the error :Notice: Array to string conversion in C:\wamp\www\project\ ill.php on line 35

How can i use your code for update?

13

You can just use mysqli_real_escape_string() to escape user input.

If you are embedding strings with html, you can use htmlspecialchars() for that.

Your way is fine too, it’s just a little slower and more complicated than using the above…it will also strip out anything non-alphanumeric, so that means no html, etc, can be saved (if for some reason you want to let html through)

14

Sorry, $name and $response should have

$name[] = $_POST[‘name’];
$response[] = $_POST[‘response’];

15

still getting the same error. actually, the problem is in red marked line

foreach($_POST[‘q’] as $i => $name)
{
// Get values from post.
$name = $_POST[‘q’];
$response = $_POST[‘response’];

// Add to database
$query = $query." (‘$name’,‘$response’) ,";
}

16

5.4.16

17

Try this instead:

$stuff = array();

foreach ($_POST as $user) {
$stuff = ‘("’ . $user[‘q’] . ‘", "’ . $user[‘response’] . ‘")’;
}
$query = ‘INSERT INTO table (name,response) VALUES’ . implode(‘,’, $stuff);
mysqli_query($query);

18

See this::
Notice: Undefined index: q in C:\wamp\www\project1\hello.php on line 29
Notice: Undefined index: response in C:\wamp\www\project1\hello.php on line 29
Warning: Illegal string offset ‘q’ in C:\wamp\www\project1\hello.php on line 29
Warning: Illegal string offset ‘response’ in C:\wamp\www\project1\hello.php on line 29

19

Ok… i think now we should look my things in from your eyes. My basic idea is:

database table 1 : it has set of 10 questions and their three options and acts as a master file for questions. it is like [questions option1 option2 option3]

Database table 2: it has first two columns name as ID and Username. They are already filled.

Now what i want is that when a user logins, a page is opened in front of him showing those questions from master table 1. now i want this:
All those questions should be presented in a form to him, which my existing code is doing. Currently i am displaying the question in placeholder with three option in front of it. Here i want the user to choose option from list for each. Then the placeholder question should be automatically updated with answer to the database table 2.

earlier in table 2, i had id username question1…question10 answer1…answer10

now you suggest me, if i am you, then how will you proceed?

20

I apologize again; doing this from a phone is a PITA. Let’s try that again –

$stuff = array();

foreach ($_POST[‘q’] as $user) {
foreach ($_POST[‘response’] as $users)
$stuff = ‘("’ . $user[‘q’] . ‘", "’ . $users[‘response’] . ‘")’;
}
$query = ‘INSERT INTO table (name,response) VALUES’ . implode(‘,’, $stuff);
mysqli_query($query);

You were getting that error because I forgot to define the post index in those loops