Run MySQL Query x Times

PHP
1

Hi Guys,

I have a form that has 3 fields:

AffID
ProgramID
NumberOfLeads

Lets say i fill in the form with the following values:

1
1
32

When i hit the submit button it will then take me to a page and will run the following MySQL query.


$query="INSERT INTO affleads VALUES ('$AffID','$ProID')";

But how would i run the MySQL query a number of times depending on what was entered in the NumberOfLeads field, for example i enter 32 then that Query will be ran 32 times.

Any help would be great.

Thanks

2

If you just store $AffID and $ProID, wouldn’t you be better off having a ‘numberOfLeads’ column also, that you would store 32 in (or add 32 to)?

Do you have other columns in affleads?

3

MySQL will let you do multiple inserts in a single query, take this for example:


INSERT INTO affleads VALUES ('aff1', 'pro1'), ('aff2', 'pro2'), ('aff3', 'pro4')

That will insert 3 rows. Just build the string so that you include as many rows as you want to insert.

4

R2D2:

I cant do that unfortunately.

the182guy:

Thanks for your comment, i know about that already, but the problem is i dont understand how to do it so if 32 is entered it adds the data in 32 times or if i enter 15 in then it only enters the data in 15 times.

Any help would be great guys.

Thanks

5

Something like this should do it - off top of my head so untested:



$numberOfLeads = (int)$_POST['numberOfLeads']; // pickup from the form data and clean it

// I don't know if these are ints so change if they are strings - use mysql_real_escape_string()
$affId = (int)$_POST['affId'];
$proId = (int)$_POST['proId'];

// begin the SQL str build
$sql = 'INSERT INTO affleads VALUES';

$first = true; // needed to know when to use the comma in query

// loop x number of times
for($i=0; $i<$numberOfLoads; $i++)
{
	$comma = $first ? ',' : ''; // use the comma or not? on first time, don't use it
	$sql .= "$comma ('$affId', '$proId')";
	
	$first = false;
}

// you now have the query in $sql ready to use with mysql_query()
6

You could use something like:

$query = "INSERT INTO affleads VALUES ".implode(",", array_fill(0, $NumberOfLeads, "('$AffID','$ProID')"));

This will create an array of $NumberOfLeads (eg 32) identical strings of (‘$AffID’,‘$ProID’), then join them together with a comma between each.

7

You shouldn’t need to do this, you’re duplicating data which is a good sign you’re “Doing It Wrong”.

Can you explain what problem this approach is trying to solve?

8

Hi Anthony,

We need it to be like this, I’m creating an affiliate tracking platform and each 1 is classed as a lead. Its how i have coded everything and how everything works. That is the reason why i need to add the same records as each 1 is classed as a lead.

Thanks

9

Hey.

So, why can’t you store the record as 1, 1, 32? What do the individual records bring to the table? I’m curious. :slight_smile:

Anthony.

10

The IP addresses of the UK consumer that did the lead, SubID values, Transaction IDs, all sorts :slight_smile:

11

Rewriting so you do this the ‘right’ way will be a lot easier at this earlier stage than it will be when your application starts to get some serious usage. I would really recommend biting the bullet and doing your rewriting now. Otherwise, you’ll end up with a system which becomes increasingly difficult to manage.

12

I cant because each lead will have different data in them, read comment above :slight_smile:

Thanks

13

So you have other columns in the affleads table?

(I didn’t see this post when I wrote my last one)

14

Yes :slight_smile:

15

How does the 32 come into play? Does someone say I want 32 leads, then these leads are made available to the publishers? Wouldn’t only qualified leads reduce the 32? You could have loads of incomplete records, taking up space/processing, until these have been completed.

I know we’re going a bit off-topic here, and you’re not necessarily after addressing this. :wink:

16

Hi Anthony,

No this is the Admin side of things im in the middle of creating, for example let say for some strange reason leads where not tracked (because the cookies where deleted) we will still be able to see from our clients how many leads affiliates have brought in and if we see in our system there are less leads then we add more leads to the affiliates account (earning them more cash).

You understand how affiliate networks work right? AffiliateWindow, TradeDoubler, CJ etc…

17

So you’ll add the extra details to each of the 32 rows you added at some point later in the admin system? In which case, my 12:10 should do what you want :slight_smile:

18

Yea I’ve implemented it already and it works just how i wanted it too :slight_smile:

Thank you for the help guys :slight_smile:

Anthony, i hope you understand what it was i was trying to do and the system im creating :slight_smile:

Thanks.