PHP string variable limit

Question

i have an html survey and the answers are stored in variables which should be loaded into the database via PHP.

For example, this is how questions (part of a table) that you can fill looks like in HTML:

 <td style="padding-top: 10px;" colspan="9"> Question1: 
        <p style="text-align:left;"><textarea name="tag1" cols="80" rows="6" 
 style="border: 1px solid #D0D0D0;"></textarea></p>
        </td>

 <td style="padding-top: 10px;" colspan="9"> Question2: 
        <p style="text-align:left;"><textarea name="tag2" cols="80" rows="6" 
style="border: 1px solid #D0D0D0;"></textarea></p>
        </td>'

The PHP code:

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

 $tag1 = $_POST["tag1"];
 $tag2 = $_POST["tag2"];

 $sql = "INSERT INTO survey (tag1, tag2)
 VALUES ('$tag1','$tag2')";

 if (mysqli_query($conn, $sql)) {
    echo "";
  } else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  }'

So the textarea name="tag1" ... in html loads the answer of a user to the database column named tag1 by PHP.

The problem is i cannot add too many items to the bracket in the PHP INSERT code (like tag1, tag2, tag3...) because the characters are limited.

It is a problem due to the numbers of questions i should include. And if i create two separate command it loads to two different rows in the database which looks confusing. One person's answers should be appear in one row. (in phpmyadmin)

How can i solve it? what should i change?

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Sep 01 '17 at 14:04
Normally you should normalize your tables... having a user (holds users) table an question (holds questions) table and a connection table user_question (holds the user questions) — Raymond Nijland, Sep 01 '17 at 14:07
Little Bobby did not mention that on questions like this, you should also include the SQL statements used to create the table(s) ... if you want to have any kind of useful suggestions from the community. Failing that, people will be shooting darts blindfolded. — YvesLeBorg, Sep 01 '17 at 14:09
you need two tables, one for survey and other one for answers and link the survey table with answers — Dawud Abdul Manan, Sep 01 '17 at 14:16

score 3 · Answer 1 · answered Sep 01 '17 at 14:07

3

For starters, this is not a great way to make your database because you will have to update your database structure if questions are added.

Anyway, you should give the user an id, so you can track what answers are from what user. After that, you can add the id to the survey table and if the user answers another question, you can simply update the answer.

UPDATE survey SET `tag2`='answer' WHERE `user_id` = 0;

answered Sep 01 '17 at 14:07

Jerodev

32,252
11
87
108

1

While I agree the table structure should be revised, I don't think it is an absolute and objective truth that the user id should be included; after all, most surveys are anonymous – William Perron Sep 01 '17 at 14:10
@William Perron most surveys are anonymous yes but you need to identify what user has answerd what question – Raymond Nijland Sep 01 '17 at 14:12
I did not mean the user should register, but maybe add an id in the session to identify the same user on different pages. – Jerodev Sep 01 '17 at 14:12
@Jerodev why Update? – Dawud Abdul Manan Sep 01 '17 at 14:25
it is an anonym survey, so basicly i just care about the answers, it does not matter who fills it, i do not need to track it by person, just focus on the content. The problem is i have more questions (around 17, which is 17 tags that is too many character, i only can add around 8 depends on the length of the tag) than i can include in the brackets after the INSERT because of the string limit problem which makes an error. I could change the name of the columns in the DB - so the tags - to make it shorter, it works, but it also looks bad if a column's name is just 2 or 3 character. – Bálint Torma Sep 01 '17 at 14:27

score 0 · Answer 2 · answered Sep 01 '17 at 14:43

Jerodev's answer is essentially correct. however, the recipe is slightly incomplete.

Since queries seem to have a length restriction, you have to split it into multiple queries (not really a way around that, apart from increasing the limit).

Your table must have some kind of unique id (a primary key for example), which must be set by the first query - an INSERT - perhaps by means of auto_increment column, or you copy an appropriate id from somewhere else.

if it is an auto_increment column, you get the id of the inserted row by mysqli_insert_id, otherwise, you should already have it. All other queries must be UPDATE queries, that use a WHERE clause that binds the id.

-- first query
INSERT INTO survey (id, tag1, tag2) VALUES (42, 'tag1', 'tag2');
-- other queries
UPDATE survey SET tag3='tag3', tag4='tag4' WHERE id=42

in php (YOU ABSOLUTELY DO WANT TO PREPARE QUERIES!):

$stmt = mysqli_prepare($conn, 'INSERT INTO survey (id, tag1, tag2) VALUES (?,?,?)');
$stmt->bind_param('iss', $id, $tag1, $tag2);
$stmt->execute();
$stmt2 = mysqli_prepare($conn, 'UPDATE survey SET tag3=?, tag4=? WHERE id=?');
$stmt2->bind_param('ssi', $tag3, $tag4, $id);

with auto_increment primary_key column:

$stmt = mysqli_prepare($conn, 'INSERT INTO survey (tag1, tag2) VALUES (?,?)'); // ! 
$stmt->bind_param('ss', $tag1, $tag2); // ! <-- no id anymore
$stmt->execute();
$id = $stmt->insert_id; // <--- this is new
$stmt2 = mysqli_prepare($conn, 'UPDATE survey SET tag3=?, tag4=? WHERE id=?');
$stmt2->bind_param('ssi', $tag3, $tag4, $id);

Dawud Abdul Manan · Answer 3 · 2017-09-01T15:26:51.880

You need two tables, one for survey and other one for answers and link the survey table with answers.

1 Database structure

Survey Table

id
Survey Name
date

Answers Table
id
survey_id
question
Answer

Date

HTML

<table>
     <td style="padding-top: 10px;" colspan="9"  name="question[]" value ="Question1"> Question1: 
            <p style="text-align:left;"><textarea name="tag[]" cols="80" rows="6" 
    style="border: 1px solid #D0D0D0;"></textarea></p>
            </td>


<td style="padding-top: 10px;" colspan="9" name="question[]" value ="Question12"> Question2: 
            <p style="text-align:left;"><textarea name="tag[]" cols="80" rows="6" 
    style="border: 1px solid #D0D0D0;"></textarea></p>
            </td>

PHP

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
    // assuming the survey id = 10
      survey_id = 10;
     date = date("Y-m-d");
     $question = mysqli_real_escape_string($_POST["question"]);
     $answers = mysqli_real_escape_string($_POST["tag"]);


    foreach(answers as $key => $val){
      $question[$key];
      $answers[$key];

     $sql = "INSERT INTO Answers (survey_id, question, answer, date)
     VALUES ('$survey_id ','$question','$answers','$date')";
    }
     if (mysqli_query($conn, $sql)) {
        echo "";
      } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
      }

AND TO RETRIEVE THE ANSWERS BASE ON SURVEY

 $sql = "SELECT * FROM ANSWERS WHERE survey_id = 10 ORDER BY id ASC"
  mysqli_query($conn, $sql)

it will retrieve all the answers for you

Please this is the professional way. hope it helps

Try and give me your feed back.

PHP string variable limit

3 Answers3