current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I had a problem inserting arrays into a MySQL database. I think found out that I cannot insert an array into columns of the table, and I should separate the values of the array and then do the insert action. But I don't know how to separate the values and insert those values. Should I separate the values and not use array?

Also I would like to create the table to shows all values(number1-10) of db stored.

Thanks for everyone!

Here's my codes below:

  $varNum = array('1','2','3','4','5','6','7','8','9','10');
  //an array showed in the selection box

  <form action="testing_Rand.php" method="post">
    <p><b><center>Choose an amount of random numbers in the selection box</center></b></p>
    <p>
            <select name="selectNum">
                <?php
                    foreach($varNum as $key => $value):
                    echo '<option value="'.$key.'">'.$value.'</option>';
                    endforeach;
                ?>
            </select>
    </p>
    <center><input type="Submit" value="submit"></center>
</form> 
    //A POST function to generate the random numbers


    //do post function
    if(isset($_POST["selectNum"]) ){
    $arrayRand=intval($_POST["selectNum"]);    
    for($i=0;$i<=$arrayRand;$i++){
         $varNum[$i]=rand(1,10000);    
    } 

    var_dump($varNum);  //show results  
    $newRand = "INSERT INTO testing_Rand (number1, number2, number3, number4, number5, number6, number7, number8, number9, number10) VALUES ('$varNum')";
    mysql_query($newRand);
}

    //show mysql database results

        $sqlDBrand = "SELECT id, number1, number2, number3, number4, number5, number6, number7, number8, number9, number10 FROM rand.testing";
$result = mysql_query($sqlDBrand) or die('MySQL query error');
while($row = mysql_fetch_array($result)){
    echo $row['id'];
    echo $row['number1'];
    echo $row['number2'];
    echo $row['number3'];
    echo $row['number4'];
    echo $row['number5'];
    echo $row['number6'];
    echo $row['number7'];
    echo $row['number8'];
    echo $row['number9'];
    echo $row['number10'];
}
share|improve this question
    
Where's your INSERT query in that code? –  Hanky 웃 Panky Jun 12 '14 at 9:32
    
are you miss out INSERT query? –  n3ISe Jun 12 '14 at 9:33
    
Show us your insert statement –  Lemuel Botha Jun 12 '14 at 9:33
    
"INSERT INTO rand.testing (number1, number2, number3, number4, number5, number6, number7, number8, number9, number10) VALUES ('$varNum')"; –  chch Jun 12 '14 at 9:33
    
but this isn't work because it cannot insert arrays into db also the array values didn't separate.i don't know how to code this function, sorry. –  chch Jun 12 '14 at 9:34

3 Answers 3

up vote 1 down vote accepted

i think this may help you, first make a string $sql and then use it in mysql_query()

  $sql="INSERT INTO rand.testing (number1, number2, number3, number4, number5, number6, number7, number8, number9, number10) VALUES (";
       $sql.=implode(',', $varNum);
       $sql.=")";
mysql_query($sql);
share|improve this answer
up vote 0 down vote

It is depending on your Data and your Goal.

Take a look at the function implode:

$arr = [1,2,3,4,5];
$data = implode ('|', [1,2,3,4,5]) //1|2|3|4|5
$arr = explode('|', $data); //Converted back

http://www.php.net/manual/de/function.implode.php

If you need it searchable, considering a serialization to XML

$arr = [1,2,3,4,5];
$xml = new SimpleXMLElement('<root/>');
array_walk_recursive($arr, array ($xml, 'addChild'));

http://stackoverflow.com/a/1397164/2441442

If you need it not searchable, you can also take a look at serialize

$arr = [1,2,3,4,5];
$data = serialize($arr);
unserialize($data);

http://php.net/manual/de/function.serialize.php

share|improve this answer
up vote 0 down vote

Going back a stage, you have a table with a fixed number of columns but a variable number of fields of data to store. This is generally a bad idea.

It would be better to split this into 2 tables. One just stores something to identify the group of numbers, and another table that has several rows for each group of numbers; one row per random number per group.

This is a basic part of normalisation of data.

You could insert the to the 2 tables and grab the values with something like this:-

<?php
    //A POST function to generate the random numbers


    //do post function
    if(isset($_POST["selectNum"]) )
    {
        $arrayRand=intval($_POST["selectNum"]);    
        for($i=0;$i<=$arrayRand;$i++)
        {
            $varNum[$i]=rand(1,10000);    
        } 

        if (count($varNum))
        {
            var_dump($varNum);  //show results  
            $newRand = "INSERT INTO testing_rand (id) VALUES(NULL)";
            mysql_query($newRand);
            $inserted_id = mysql_insert_id ();
            $newRand = "INSERT INTO testing_rand_numbers (rand_id, number) VALUES($inserted_id, ".implode("),($inserted_id, ", $varNum).")";
        }
    }

    //show mysql database results

    $sqlDBrand = "SELECT id, GROUP_CONCAT(b.number) AS all_numbers FROM testing_rand a LEFT OUTER JOIN testing_rand_numbers b ON a.id = b.rand_id";
    $result = mysql_query($sqlDBrand) or die('MySQL query error');
    while($row = mysql_fetch_array($result))
    {
        echo $row['id'];
        echo $row['all_numbers'];
    }
?>
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.