Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Unhappy PHP For Each loop - struggling

    Evening all,

    New to this forum and relatively new to PHP! Although I have been learning fast, this one has stumped me...

    I have written PHP script which queries my database for my selected Model ID, it will then find the model, save the results with json formatting with the file name 'results.json' on my server. (code for this is below).

    Trouble is, there are multiple Model ID's in my db and I now need to regularly do this for each ID (seperate json file for each Model ID). I want to have a php script that will query the db and for each model id it finds output the required attributes and save as a json file with the filename *Model ID*.json. I will then set up a CRON job to activate the php script every day or so.

    I know the answer sits within For each function but no matter what I have tried i get numerous error points. Please can someone out there help me?

    I know I need to make the sql query Select * without the WHERE statement.

    Oh and thank you to this forum, its helped me solve numerous other problems and learn a lot along the way

    Will

    [CODE]

    <?php

    set_time_limit(0);
    ini_set('memory_limit', '2000000M');
    $sql = mysql_query("SELECT * FROM `phndeals` WHERE `Model.ID` = '2296'");
    $ID = $_GET["ID"];

    $response = array();
    $posts = array();
    while($row=mysql_fetch_array($sql))
    {

    $OfferID=$row['Offer.ID'];

    $phone=$row['Model.ID'];

    $Mins = $row['Tariff.FreeMins'];

    $Texts = $row['Tariff.FreeTxts'];

    $Data = $row['Tariff.DataAllowance'];

    $PhnCost = $row['Offer.PhoneCost'];

    $OverallMonthlyCost = $row['Offer.MonthlyCost'];

    $Info = $row['Offer.OfferCashback'];

    $link = $row['Offer.Link'];

    $length = $row['Tariff.Rental'];

    $posts[] = array('length'=> $length, 'Mins'=> $Mins, 'Texts'=> $Texts, 'Data'=> $Data, 'Info'=> $Info, 'PhnCost'=> $PhnCost, 'OverallMonthlyCost'=> $OverallMonthlyCost, 'link'=> $link);


    }

    $response['posts'] = $posts;

    $fp = fopen('results.json', 'w');
    fwrite($fp, json_encode($response));
    fclose($fp);

    echo "json file created.";
    ?>

    [CODE]

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Do you have an actual reason to create a separate .json file?
    Something like this I'd suggest simply passing the querystring as a part of the request for a "json" file which is simply a PHP script which outputs the JSON.
    I suggest this simply because if you're already looking at grouping separate files by modelid.json and would need a method of determining it, it'd just be as easy to ask for json.php?modelid=2296 (or configure the .json to execute php code and simply use results.json?modelid=2296 for example).
    If you actually need separate files, then yeah I can show you how to do that, but I'm thinking your overcomplicating this.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Fou Lu,

    Thanks for the input - yes I had explored the querystring route, unfortunately the db is fairly large - 1mil rows and querying for certain model ids on the fly and outputting json was adding a good couple of seconds to my overall user request.

    Therefore I want the server to do most of the processing overnight (split db into multiple json by model id) and on user request it will select and use the correct json file accordingly which will decrease the overall request time.

    Please could you show how that is done? Thanks again.

  • #4
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,040
    Thanks
    10
    Thanked 92 Times in 90 Posts
    PHP Code:
    <?php
    set_time_limit
    (0);
    ini_set('memory_limit''2000000M');
    $id=2296;
    $sql mysql_query("SELECT * FROM `phndeals` WHERE `Model.ID` = $id");
    $response = array();
    $posts = array();
    while(
    $row=mysql_fetch_array($sql)){
        
    $posts[] = array(
        
    'length'=> $row['Tariff.Rental'], 
        
    'Mins'=> $row['Tariff.FreeMins'],
        
    'Texts'=> $row['Tariff.FreeTxts'],
        
    'Data'=> $row['Tariff.DataAllowance'], 
        
    'Info'=> $row['Offer.OfferCashback'],
        
    'PhnCost'=> $row['Offer.PhoneCost'], 
        
    'OverallMonthlyCost'=> $row['Offer.MonthlyCost'], 
        
    'link'=> $row['Offer.Link']
        );


    }

    $response['posts'] = $posts;

    $fp fopen('results.'.$id.'.json''w');
    fwrite($fpjson_encode($response));
    fclose($fp);
    ?>
    but I am with Fou-Lu, even with a million rows it should not take that long to grab the results from a properly indexed table?
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

  • Users who have thanked firepages for this post:

    WillyWonka (09-23-2013)

  • #5
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for the code firepages.

    Unless I'm being stupid this just creates the json file with the model Id as its filename.

    I do want to do that but rather than 'SELECT * FROM `phndeals` WHERE `Model.ID` = $id ' which creates one file for one model, I am after 'SELECT * FROM `phndeals`' and for the script to loop through all model ids in the table, saving multiple json files with the corresponding model Id as the name.

    Is that possible? I find it hard to explain! Appreciate your help !

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Nope, you're right that's what it'll do.
    Problem here is if you're talking about 1M+ records, you're going to run into memory issues using PHP. You see, you'll need to iterate and store *every* record in order to do what you need to do in a single script.
    Here's my suggestion.
    Create a primary script that has the purpose of querying and copying all distinct model.id into a temporary or memory table.
    Then, using a separate script (you can queue this every few minutes on the cron, and technically leave it to run indefinitely every 5 minutes), you do as such:
    • SELECT * FROM phndeals WHERE Model.ID IN (SELECT Model.ID FROM temporarytable LIMIT 1) (I'm pretty sure you can do a limit in a sub :P)
    • Iterate the records and dump them into $posts[$row['Model.ID']]. The $row['Model.ID'] will key the record so you can use that on a further iteration.
    • Do your JSON thing.
    • fopen the file 'results' . key($posts) . '.json'
    • Delete from the temptable the key($posts). That will be the id you stored in the temp. This will prevent it from doing it again during this iteration.

    Cron tab up that second script. The trick is to make sure you verify that there is records in the query. You can limit it by time, and technically you can create a mini-service type idea, but I personally don't like those. A single extra query every 10 minutes or so isn't a big deal.

    Edit:
    Hindsight's 20/20; use a memory table. I believe you'll loose the temp if you don't use persistent connections.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    WillyWonka (09-23-2013)

  • #7
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Thumbs up

    Hi Fou-Lou,

    Thought i'd follow this up and say thanks.

    In summary, I ended up creating a temptable which has each unique model.id inserted every morning. I then have a cron job set up every 5 minutes or so which selects a single model id (limit 1) and then queries the main db using that id. My json code then creates the json file and saves it under the model id name. Finally it deletes the model.id from the temp table and the process loops until all model.id's have been completed

    You are right about memory issues, on average it completes about 10 model.id's before timeout and then i have to wait for the next cron job to kick in and re-submit the php page.

    The only issue I am now having is that my fwrite function will not work with anything over 2000 rows, just times out - but this is something i will figure out separately.

    Thank you both for you help.

    Will


  •  

    Posting Permissions

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