PHP For loop required for storing data in MySQL

Question

mysqli_stmt_bind_param($proc, "iss$is", $respondent_id, $ip, $browser, $qStr1);

It's just this that is causing the problem, even though $qStr1 contains the text $q1, $q2 etc. as things stand I think $qStr1 is being treated as a single variable within the code, I guess I need to extract the text and then use it but no sure how?

Using the answer provided below, I have modified and added to:

$qStr = '';
    $markStr = '';
    for($i=1; $i<11; $i++)
    {
        $qStr .= 'q'.$i.'';
        $qStr1 .= '$q'.$i.'';
        $markStr .= '?';
        $is .= 'i';
        if($i < 10)
        {
            $qStr .= ', ';
            $qStr1 .= ', ';
            $markStr .= ', ';
        }
    }

$proc = mysqli_prepare($link, "INSERT INTO tresults (respondent_id, ip, browser, $qStr) VALUES (?, ?, ?, $markStr);");
mysqli_stmt_bind_param($proc, "iss$is", $respondent_id, $ip, $browser, $qStr1);

Now, I'm having a problem with $qStr1 - even though this is looping through and providing the correct output $q1, $q2 etc. - it's not saving to the DB, if I manually place $q1, $q2, etc in the mysqli_stmt_bind_param and leave the rest using the loop it works correctly.

I have the following code:

$proc = mysqli_prepare($link, "INSERT INTO tresults (respondent_id, ip, browser, q1, q2, q3, q4, q5, q6, q7, q8, q9, q10) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");

mysqli_stmt_bind_param($proc, "issiiiiiiiiii", $respondent_id, $ip, $browser, $q1, $q2, $q3, $q4, $q5, $q6, $q7, $q8, $q9, $q10);

mysqli_stmt_execute($proc);

What I am trying to achieve is a loop (at least I think it is) that will place the q1, q2, q3, q4, q5 etc. automatically into that code and also place the correct numbers of ? in there are well.

Is the question clear and can anyone assist?

What are you starting with – like an array of column names, or just a number of columns and all of the column names are build as q+number, or …? And what have your tried so far? — C3roe
– C3roe, Commented Jul 25, 2013 at 10:49
Hi @CBroe - hopefully the updates above are enough - just looking to resolve one final issue and then this will be perfect. Any suggestions? — Homer_J
– Homer_J, Commented Jul 25, 2013 at 12:02

Khawer Zeshan · Accepted Answer · 2013-07-25 14:30:13Z

1

Try this

<?php
    $qStr = '';
    $markStr = '';

    for($i=1; $i<11; $i++)
    {
        $qStr .= 'q'.$i.'';
        $markStr .= '?';
        if($i < 10)
        {
            $qStr .= ', ';
            $markStr .= ', ';
        }
    }


    $proc = mysqli_prepare($link, "INSERT INTO tresults (respondent_id, ip, browser, $qStr) VALUES (?, ?, ?, $markStr);");
?>

The output of $qStr & $markStr will be like this

$qStr = q1,q2,q3,q4,q5,q6,q7,q8,q9,q10
$markStr = ?,?,?,?,?,?,?,?,?,?

edited Jul 25, 2013 at 14:30

answered Jul 25, 2013 at 10:51

Khawer Zeshan

9,6568 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Homer_J Over a year ago

Does it not put a ',' at the end of the last variable?

Khawer Zeshan Over a year ago

No, Should it put a comma at the end of last variable? I don't think so

Homer_J Over a year ago

No - it shouldn't so that should be correct. Just testing now.

Khawer Zeshan Over a year ago

Please see the updated answer, The values should be like this VALUES (?, ?, ?, $markStr) the 3 ? are for respondent_id, ip, browser

Homer_J Over a year ago

Hi Khawer, OK this works until I try to apply to the mysqli_stmt_bind_param section - I've updated the question above. Thanks for your help, any further assistance welcomed.

|

MBaas · Accepted Answer · 2013-07-25 12:20:44Z

0

edited!

$proc = mysqli_prepare($link, "INSERT INTO results 
          (respondent_id, ip, browser, $qStr) VALUES (?, ?, ?, 
            ?,?,?,?,?,
            ?,?,?,?,?);"
            );
mysqli_stmt_bind_param($proc, "iss$is", $respondent_id, $ip, $browser, 
            $q1,$q2,$q3,$q4,$q5,
            $q6,$q7,$q8,$q9,$q10
            );
mysqli_stmt_execute($proc);

Isn't that easier to read...?

edited Jul 25, 2013 at 12:20

answered Jul 25, 2013 at 11:20

MBaas

7,5318 gold badges50 silver badges66 bronze badges

12 Comments

Homer_J Over a year ago

See, the update above - that's what I am trying to implement based on Kkawers code but it's not saving to the DB.

Homer_J Over a year ago

Thanks @MBaas - I've just tried implementing and again, not saving to the DB.

Homer_J Over a year ago

Works with

mysqli_stmt_bind_param($proc, "iss$is", $respondent_id, $ip, $browser, $q1, $q2, $q3, $q4, $q5, $q6, $q7, $q8, $q9, $q10);

but not with mysqli_stmt_bind_param($proc, "iss$is", $respondent_id, $ip, $browser, $qStr1);

Homer_J Over a year ago

I think it is because the bind-param $qStr1 is being treated as a single variable and not as the $q1, $q2 etc. that it actually contains - I guess I need to print it or echo it but not sure how to achieve this within PHP itself to make it a series of PHP variables that the bin-param can use.

MBaas Over a year ago

Sorry, I see that I misunderstood your code :( Do I understand right that you always have q1..q10 and you are trying to simplify the whole thing by building the bind-params programactically?

|

Collectives™ on Stack Overflow

PHP For loop required for storing data in MySQL

2 Answers 2

8 Comments

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related