Inserting an array to mysql using php

Question

i have this two text fields that ask the user to put in two numbers that are limited to 49 numbers, so that i can have an array of number 1 to 50, or 151 to 200, or 27551 to 27600 any number but a series of 49 consecutive numbers, my problem is i dont know how to put them inside the database, i have no clue i have been searching for everything about inserting arrays but they dont work on my case,

This is my form

 <form id="form3" name="form1" method="post" action="">
    <p>From:
    <input type="text" name="from" id="form_number" class="from" /> 
    - To:
    <input type="text" name="to" id="form_number" class="to" />
    </p>
    <p>Waybill Booklet:
    <select name="waybill_booklet[]" id="form_list">
        <?php
            do {  
        ?>
        <option value="<?php echo $row_Booklet['id_waybill_booklet']?>"><?php echo $row_Booklet['booklet_no']?></option>
        <?php
            } while ($row_Booklet = mysql_fetch_assoc($Booklet));
            $rows = mysql_num_rows($Booklet);
            if($rows > 0) {
            mysql_data_seek($Booklet, 0);
            $row_Booklet = mysql_fetch_assoc($Booklet);
            }
        ?>
    </select>
    </p>
    <p>
    <input type="hidden" name="status[]" value="4" />
    <input type="submit" name="button" id="form_button" value="OK!" />
    </p>
</form>

the 49 series of consecutive numbers will be inserted into the database with a foreign key what is chosen from the drop down menu, and a value of 4 that is in the hidden field, so basically there are 4 columns to my table 1 for primary key 1 for the series of numbers and 1 for the foreign key and the last will be the value of the numbers.

This is my php code to get the series of numbers

<?php
$booklet = $_POST['waybill_booklet'];
$status = $_POST['status'];
$from = $_POST['from'];
$to = $_POST['to'];
$number = range($from,$to);

$count = 0;
$myArray = range($from,$to);
while($count<=49){
if($count<49){
    echo $myArray[$count]. ", ";
}else{
    echo $myArray[$count];
}
$count++;
}
?>

i dont know how to insert the data's

If from and two are always 49 apart, why does the user need to enter both numbers? Just enter the first or last number, and calculate the other one. — Barmar
– Barmar, Commented Feb 7, 2014 at 3:49
i have all of that connection and stuff. i just need help on how to insert the data, i dont think implode will work, i just need that the series of numbers will be inserted into the database with different primary id's each — user3282248
– user3282248, Commented Feb 7, 2014 at 3:49
If that's what you need to do, just write a loop that inserts each number into a new row. — Barmar
– Barmar, Commented Feb 7, 2014 at 3:50
@Barmar it used to be any number but i limited it to 49 to lessen the user error of inputting i want it to be flexible so i can change it if the users wants to have 1-100 series — user3282248
– user3282248, Commented Feb 7, 2014 at 3:51

Barmar · Accepted Answer · 2014-02-07 04:14:00Z

1

$waybill = mysql_real_escape_string($_POST['waybill_booklet'][0]);
$status = mysql_real_escape_string($_POST['status'][0]);
foreach (range($from, $to) as $number) {
    $sql = "INSERT INTO yourTable (id, waybill, status) VALUES($number, '$waybill', '$status')");
    mysql_query($sql) or die(mysql_error());
}

You should also switch to PDO or mysqli, so you can use parametrized queries instead of substituting strings into the query. Then you don't need to escape the values like that.

edited Feb 7, 2014 at 4:14

answered Feb 7, 2014 at 4:01

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

4 Comments

user3282248 Over a year ago

by looking at this code, i think this will lead me to the correct path thanks barmar gonna test this out and accept the answer if it works out.

user3282248 Over a year ago

mysql_real_escape_string() expects parameter 1 to be string, array given i get this for $waybill and $status

Barmar Over a year ago

See my comment above about not needing brackets after these input names.

user3282248 Over a year ago

thanks barmar, im almost there with this code you gave me. just some minor problems about variables being undefined.

Abishek · Accepted Answer · 2014-02-07 03:53:06Z

1

Instead of storing this as an array (since you want to store this as bulk, I assume it will not involve any direct database level aggregation or computation), you can store it as a json string using the json_encode($myArray_series_of_numbers). This gives you the flexibility to store them as a string column and when you retrieve it back, you can use json_decode($model->series_of_numbers_column,true) to get it back as an array for easy computation back in PHP.

Hope this helps

answered Feb 7, 2014 at 3:53

Abishek

11.8k19 gold badges75 silver badges111 bronze badges

2 Comments

user3282248 Over a year ago

i wont store them in the database as an array, i just need them to be inside the datbase each with different primary id's each not as an array

Abishek Over a year ago

Can you show an example of how it should look in the DB. that will probably help understand what you are exactly looking for?

clancer · Accepted Answer · 2014-02-07 03:59:23Z

0

Here is a tutorial on using mysql in php http://www.w3schools.com/php/php_mysql_insert.asp specifically the INSERT command. just build your data into variables instead of echo'ing it and then follow the guide to interact with a database

here is the auto increment tutorial to generate primary ids for each array element http://www.w3schools.com/sql/sql_autoincrement.asp

you can greatly increase the speed of the inserts and do it in one submit by building a multiple insert sql string.. and then using the insert guide above to run it.

INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

edited Feb 7, 2014 at 3:59

answered Feb 7, 2014 at 3:49

clancer

6134 silver badges10 bronze badges

6 Comments

user3282248 Over a year ago

yea i know the basic insert but how can i insert the variable $numbers to the database $numbers holds the series of numbers

clancer Over a year ago

@user3282248 after you clarified in abisheks answer you can do this using an auto increment table easily... just insert as you normally would associating them with the foreign key.. the primary auto increment key will keep them all separate

user3282248 Over a year ago

yea i have auto increment key for my primary id, but my problem here is how can i insert record for all of the numbers with just one submit,

Abishek Over a year ago

Will each series be a new row or one single row for each series?

Abishek Over a year ago

Yes, @clancer, answer should help you with multiple inserts with a single insert call, prevent using a for-loop to insert if you are sure about the bulk inserts, if you want to perform any operation for each insert, then you will have to use @Barmar's answer

|

Collectives™ on Stack Overflow

Inserting an array to mysql using php

3 Answers 3

4 Comments

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related