SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Run MySQL Query x Times

    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.

    Code:
    $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
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  2. #2
    SitePoint Guru r2d2's Avatar
    Join Date
    Dec 2003
    Location
    In my van, fool!
    Posts
    647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Enthusiast
    Join Date
    Sep 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL will let you do multiple inserts in a single query, take this for example:

    Code:
    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. #4
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like this should do it - off top of my head so untested:

    PHP Code:

    $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. #6
    SitePoint Guru r2d2's Avatar
    Join Date
    Dec 2003
    Location
    In my van, fool!
    Posts
    647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could use something like:

    PHP Code:
    $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. #7
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  8. #8
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.

    Anthony.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  10. #10
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    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.

    Anthony.
    The IP addresses of the UK consumer that did the lead, SubID values, Transaction IDs, all sorts
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  11. #11
    SitePoint Guru r2d2's Avatar
    Join Date
    Dec 2003
    Location
    In my van, fool!
    Posts
    647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #12
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r2d2 View Post
    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.
    I cant because each lead will have different data in them, read comment above

    Thanks
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  13. #13
    SitePoint Guru r2d2's Avatar
    Join Date
    Dec 2003
    Location
    In my van, fool!
    Posts
    647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AdWarm View Post
    The IP addresses of the UK consumer that did the lead, SubID values, Transaction IDs, all sorts
    So you have other columns in the `affleads` table?

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

  14. #14
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r2d2 View Post
    So you have other columns in the `affleads` table?

    (I didn't see this post when I wrote my last one)
    Yes
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  15. #15
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  16. #16
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    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.
    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...
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  17. #17
    SitePoint Guru r2d2's Avatar
    Join Date
    Dec 2003
    Location
    In my van, fool!
    Posts
    647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  18. #18
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r2d2 View Post
    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
    Yea I've implemented it already and it works just how i wanted it too

    Thank you for the help guys

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

    Thanks.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •