multiple inserts into mysql using php

Question

I have a script that inserts a company id, user id and a datetime of the transaction when someone enters their email. Now I have to add a dropdown select box that allows for the user to select the number of donations they want to make so the only have to enter their email address once. What is the best way to go about this? I was thinking something like this:

$coid = $row['companyid'];
$userid = $_SESSION['userid'];
$selectbox = $_POST['select']; // number value 1-10

// old query
mysql_query = ("INSERT INTO donations(company, user)");

// new query
$i=1
while($i<=$selectbox) {
mysql_query = ("INSERT INTO donations(company, user)");
$i++
}

or something along those lines. Is that the best way to go about it? Better ways?

You are missing some things in your query + please use PDO or mysqli instead of mysql + escape your user data! In this case a PDO prepared statement would work great. — Green Black, Nov 26 '12 at 20:17
Thanks I will move to PDO and all my user data is escaped on my home computer....I was just writing this from memory and didn't feel like writing all the extra escape parts = lazy — robk27, Nov 27 '12 at 13:45

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

First, stop using mysql_ functions as they are being deprecated. Use mysqli_ or PDO functions instead.

You should use prepared statements or sanitize your variables to prevent against SQL injection.

Regarding a better approach, you can put them into an array then implode when executing. This is a good starting point. It uses PDO to insert an array.

With your current code, I'm not clear how you're tracking the number of users wishing to do donations, but you can do something like:

$i = 1;
while($i <= $selectbox) {
$insertArray[] = "$coid, $userid";
$i++;
}

mysql_query("INSERT INTO donations (company, user) VALUES (" . implode('), (', $insertArray) . ")");

edited May 23 '17 at 11:48

Community

1
1

answered Nov 26 '12 at 20:21

Kermit

33,827
13
85
121

Thank you for the reply. All my user data is escaped...I was just too lazy to write it all out but I will move towards PDO. Also, can you tell me putting into an array is better...just curious. – robk27 Nov 27 '12 at 13:52
1

@robk27 Because you can loop through the array and perform any string functions much easier. – Kermit Nov 27 '12 at 15:27

ABC · Answer 2 · 2012-11-26T21:04:34.507

You should make a database table with 3 columns (for this question).

I will give you the code to make the required table, just copy and paste this into PHPmyAdmin:

Create Table donations(
company_id int,
user_id int,
donate_num int,
datetime DATETIME
)

Then use the INSERT INTO function to update the database using the new mysqli functions (Replace the server/username etc with your database details):

$mysqli= new mysqli('SERVER','USERNAME','PASSWORD','DATABASE NAME')
if ($mysqli->connect_error) {
            die('Connect Error (' . $mysqli->connect_errno . ') '
                    . $mysqli->connect_error);
        }

function escapeStringSlash($value) {

            global $mysqli;
           if (get_magic_quotes_gpc()) {
               $value = stripslashes($value);
           }

           if (!is_numeric($value)) {
               $value = $mysqli->real_escape_string($value);
           }
           return $value;
        }

$coid=escapeStringSlash($row['companyid']);
$userid = escapeStringSlash($_SESSION['userid']);
$selectbox = escapeStringSlash($_POST['select']);
$date = new DateTime();
$currentTime = $date->format("Y-m-d H:i:s");


if($insertDonations=$mysqli->query("INSERT INTO donations(company_id,user_id,donate_num,datetime) VALUES(".$coid.",".$userid.",".$selectbox.",'".$currentTime."')"){
echo "Number of donations received";
}else{
echo "There was a problem inserting this.";
}
$mysqli->close();

Thanks for the code...that would definitely work but I need to keep track of each individual donation so I would need separate inserts for each donation. — robk27, Nov 27 '12 at 13:50

multiple inserts into mysql using php

2 Answers2