Save SQL data FROM an ARRAY such as print_r()

Question

I have the following question How do I save data from an array within the same file after registrations, here is the scenario.

user register on a site, he get as usual an ID as primary key for the SQL, for also a random code, in this case a random number, this number will later be use, but for now I only need a away to save my data...

so, when user register for the very first time, the script will generate 3 random questions from the sql which the table has over 400 question anyway, each user gets only 3 questions. so

here is my code to get this random questions...

 $questionsID = mysql_query("SELECT * FROM questions ORDER BY RAND() LIMIT 3");
        $ids = array();
        while($row = mysql_fetch_array($questionsID)){
            $ids[] = $row['id'];
                    }
print_r($ids);

with that I get only the ID's and thats all I need for now...

the end result is 3 ID's as I have stated in the SQL query that I only want 3 random ID's from the whole table, so it looks like this:

    Array
(
    [0] => 2
    [1] => 1
    [2] => 3
)

but, I don't see that, only if I go to my browser, please bear with me I'm still learning so, at this point I have my special code of 6 digits which is store in a different table

this code will be associated with the questions as the code is also associated to the user... my tables are: User, Special_code, questions, question_for_user

at the end my table questions_for_users should look like this

ID | special_code | question_id
1  | 444444       | 2
2  | 444444       | 1
3  | 444444       | 3
4  | 232323       | 56
5  | 232323       | 4

... and son on for each user per special code...

So my question is, how do I save my data into the sql table questions_for_user from the array, if the array is in the print_r($ids) ... or if there is another way to save the data please share... Thank you.

So, I thank you all for helping me, some of your ideas did worked as standalone... using jeroen solution did worked but as I put it in to my full code it stop working... I have never used serialize(), so I really don't know how that work, but I can guess something like this: to be use to save data in my sql

$array_string=mysql_escape_string(serialize($array));

and something like

$array= unserialize($results['ids']);

to get the information... I don't know, I may be wrong.... anyways...

so I apology for what you are about to see: I know this is not the best way to do it, and some developers will want to hit me in the head!...

in order for my to save the data I made another query inside my previews query as follow:

$questionsID = mysql_query("SELECT * FROM questions ORDER BY RAND() LIMIT 3");
//        $ids = array();
                while($row = mysql_fetch_array($questionsID)){
        $ids = $row['id'];
                mysql_query("INSERT INTO user_questions(special_code, question_id) VALUES ('$specialCode_by_POST','$ids')") or  die("".mysql_error());
}

This did the trick... :) I got the table just the way I wanted

If you think that I should do this in a different way please share a solution, Thank you.

What exactly is the problem? As I read it you just need to insert 3 rows in your questions_for_users table, all with the same special_code and each with its own question_id. — jeroen
– jeroen, Commented Jun 15, 2013 at 1:11
Also, you can just use PHP's implode() function and create a string out of your array, which can look like "11|22|33", and then you can use PHP's explode() function to make an array out of it again. That way, you can just store regular strings in your database — Filippos Karapetis
– Filippos Karapetis, Commented Jun 15, 2013 at 1:11
@jeroen , the problem I have is that how do I store the data from the array... — Tanker
– Tanker, Commented Jun 15, 2013 at 1:34

jeroen · Accepted Answer · 2013-06-15 01:44:58Z

1

Based on the comments, it seems that the problem is populating the questions_for_users table.

You can build a simple insert statement that will insert all entries from your array at once.

Using PDO:

$values = array(':special_code' => $special_code);
$recs = array();
$sql = 'INSERT INTO questions_for_users (ID, special_code, question_id) VALUES ';
foreach ($ids as $id)
{
    $recs[] = "(NULL, :special_code, :question_id_{$id})";
    $values[":question_id_{$id}"] = $id;
}
$sql .= implode(',', $recs);

// prepare statement
$stmt = $db->prepare($sql);

// execute statement
$stmt->execute($values);

answered Jun 15, 2013 at 1:44

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

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

3 Comments

Tanker Over a year ago

Thank you, I did a very basic code just to tryout the examples provided here, and your code did worked, but when I put it together with the rest of my code, it didn't work, the error I was getting was : Fatal error: Call to a member function prepare() on a non-object... I may be missing something, but overall it did worked thank you.... however I did a very nasty solution to my question...

jeroen Over a year ago

@Tanker Note that I am using PDO and not the mysql_* functions. If you want to use this code, you need to connect using PDO ($db is my PDO connection in the example here).

Tanker Over a year ago

Thank you, I should pay more attention, that's what I was missing, I wanted to give you a +1 but this thing is asking for 15 on reputation I only have 11 :( ... I don't log here to often, and I'm kind of new to this site... but you get the idea.. Thank you, your answer was useful to me.

amigura · Accepted Answer · 2013-06-15 01:19:37Z

0

you could use where IN id(2,1,3) for question table or join tables

    $query = "SELECT questions.id, Special_code.code FROM questions, Special_code  
WHERE questions.id = Special_code.id ORDER BY RAND() LIMIT 3";

http://www.w3schools.com/sql/sql_join.asp

answered Jun 15, 2013 at 1:19

amigura

5393 silver badges6 bronze badges

1 Comment

Tanker Over a year ago

for some reason every time I log in, I get different questions, the idea is to when you register for the first time to get your 3 random questions and that's it, you can log in as many time as you want but your questions wont change, so to my question is, now that I have my array in print_r($ids) how do I save that data in to another table... I mean, I know how to INSERT INTO table.... blah blah... what I don't know is how to strip the array so I can send the data to it's correct colum... Thank you.

Collectives™ on Stack Overflow

Save SQL data FROM an ARRAY such as print_r()

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related