Jump to content

Psycho

Moderators
  • Posts

    12,118
  • Joined

  • Last visited

  • Days Won

    124

Posts posted by Psycho

  1. @LeonLatex We all have those moments where something obvious escapes us. I think the responses you have received have the tone that they do because the problem (as posed) shows a lack of discipline and it appears you were not even trying. Plus, you never provided the error messages you were getting.

    For example, how does one write all those lines of code and THEN discover all those syntax errors? I typically only write a "section" of code to do something specific, then run it to see that it is doing what I intend. Having typos is to be expected when writing code. What are you using as an editor? Most IDEs will highlight many basic typos.

    • Thanks 1
  2. Here is some very rough code on one way to approach validation logic. As I said before, with all the fields you need to validate, I would create functions or a class to do this. But, this will give you something to build on. Ideally, you would present the original input form and repopulate the valid values so the user doesn't have to re-enter everything again. You can show the errors at the top of the page or show them in-line with the input fields (or both).

    //Create an array to hold errors
    $errors = array();
    
    //Get the learnign end date value
    $learning_actual_end_dateVal = isset($_POST['learning_actual_end_date']) ? trim($_POST['learning_actual_end_date']) : '';
    //Check that not empty (i.e. required)
    if(empty($learning_actual_end_dateVal)) { $errors[] = "The learning end date is required"; }
    elseif (!strtotime($learning_actual_end_dateVal))  { $errors[] = "The learning end date must be a valid date value"; }
    
    //Get the participant complete course value
    $partcipant_complete_courseVal = isset($_POST['partcipant_complete_course']) ? trim($_POST['partcipant_complete_course']) : '';
    if(empty($learning_actual_end_dateVal)) { $errors[] = "Participant complete course is required"; }
    
    // - - - Repease necessary validations for each field
    
    //Check if there were any validation errors
    if(count($errors))
    {
        //There were validation errors
        $msg = "The following validation errors occured:<ol>";
        foreach($errors as $error)
        {
            $msg .= "<li>{$error}</li>";
        }
        $msg .= "</ol>";
    }
    else
    {
        //There were no input validation errors
        // . . . attempt to process the data
        if(process_was_successful)
        {
            $msg = "The data was saved successfully";
        }
        else
        {
            $msg = "There was a problem saving the data";
        }   
    }
        
    echo $msg;

     

  3. OK, after looking at the code more closely, you need better validation logic than just checking all fields have "a" value. You should validate each field individually to make sure it has a value (if required) AND that the value is a valid value. E.g. a date should be a date, a number should be a number, etc. If only a few fields I would write the validation logic in-line. But, for this many fields, I would write separate functions for the validations based on the type of expected inputs. Those functions can be built to include parameters for such things as minimum/maximum inputs, required or not, etc.

  4. What results ARE you getting? Are you getting a blank page or any output?

     

    EDIT: Looking at the logic flow there is an if() condition with a LONG list of conditions which will result in "$msg    =   '';" if the result is true. I am guessing that the condition is always true resulting in the message variable getting defined as an empty string. I also don't see that $msg is ever getting output regardless of what it is defined. Change the value for $msg on that one line to something and add a line at the end of the code to echo $msg to the page. Then you can see if it is hitting that branch of code, or the other branch where it is defined as "New Soft Skills Entry Added". IN any event that LONG list of conditions in the if() statement seem overly complicated.

  5. On 7/26/2022 at 10:17 AM, mac_gyver said:

    if your database table doesn't already have an id/autoincrment column, add it. when you query to get the existing data to edit, add this column to the SELECT ... list (you should actually list out the columns you are selecting, rather than use *). output the id in a hidden form field. in the form processing code, if the id input is a true value, execute the code for an UPDATE query. if it is not a true value, execute the code for an INSERT query.

    That is generally how I would handle that logic as well. But, I would add a couple comments on that. Based on what I see in the OP's code it would seem that the 'dates' value should be unique. If only relying upon the id value being present or not, it could lead to duplicate records for the same 'dates' value. A unique constraint can be added to the column in the DB, but there will also need to be code to check for that condition. That can be done by doing a SELECT first to see if there is a record for that date (as is already being done), but that does allow for a race condition to allow duplicates. Depending on the needs of the application that may not be an issue. But, the proper way is to execute the INSERT statement and capture the error when it fails and check the error to see if it was due to a uniqueness issue. I'll be honest that I almost never do this:)

     

  6. 1 hour ago, wongle said:

    I came on here for some help, not to have the **** taken out of me.

    This forum was friendly at first.

    Understand that the members here provide help out of good will and receive no compensation. When someone takes the time and energy to read through a person's post and provide a response - then the poster ignores the help that was provided it can certainly feel like the person helping was unappreciated or even disrespected. 

    You posted 124 lines of code, of which almost 100 lines were unnecessary and add no value (those lines which simply populate object values into distinct variables). In fact, all those lines of code do nothing other than increase chances of bugs. Far too often I've seen people reassign the wrong value to a variable when doing such things. Instead of parsing the $_POST['array'] into an object, have it parsed into an array (look at the other attributes for json_decode). Then simply prepare your query with named placeholder and pass the  array.

    Here is a quick rewrite that would accomplish the same thing with only about 25% of the code which is much more readable and maintainable. I may have made a typo or two, so itg may not work out of the box. There is a LOT more I would do to make this code better, but that's all the time I am willing to invest at this time

    try {
        $pdo = new PDO('mysql:host=' . db_host . ';dbname=' . db_name . ';charset=' . db_charset, db_user, db_pass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        //Read data into an array
        $data = json_decode($_POST['array'], TRUE);
    
        // Update the record
        //Create placeholders using a colon and the index names from the $data array
        //Ideally I would also list out the field names before the values 
        $query = 'INSERT INTO contacts VALUES (null, :learner_id, :email, :phone, :title, :created, :status, :riskdv, :nin, :dob,
                      :nextofkin, :address, :refname, :refagency, :refadd, :refemail, :refnum, :riskvoi, :riskar, :risksat,
                      :riskpph, :rismpmh, :riskpos, :mentalhealth, :hisdrug, :exoff, :learndif, :hisalc, :fled, :rousl, :trave,
                      :yplc, :psd, :hivadids, :hep, :phyneeds, :mentneeds, :meds, :subdose, :methmil, :alcdepend, :ivdrug,
                      :onmeth, :compdetox, :drugdepend, :dip, :attcdp, :heroin, :canna, :meth, :coc, :crack, :solvents, :amph,
                      :spice, :premed, :halluc, :subu, :alchevel, :otherinfo, :btype, :bamount, :bstartddate, :bnextpay,
                      :padd1, :padd2, :preoff, :pendcase, :precon, :onprob, :ship, :cook, :money, :cleaning, :perh,
                      :housing_benefit_claim_date, :housing_benefit_reference, :>housing_benefit_cancelled, :date_left_left,
                      :number_of_times_at_reflections, :amount_of_service_charge_owed, :reason_for_leaving, :home_town,
                      :town_moved_to, :imagesave, :datemovedin, :gpsurgery, :rflfull, :last_date_left, :med_cons, :addinfo,
                      :compldetox, :complprog, :dietaryreq');
        $stmt = $pdo->prepare($query);
        $result = $stmt->execute($data);
    
        var_dump($data);
    
         echo "Contact added at Kenobi";
         
    } catch (PDOException $exception) {
        // If there is an error with the connection, stop the script and display the error.
        exit('Failed to connect to database!');
    }

     

  7. Your question is not clear. When you say "I want to remove the placeholder image and show only the title if no featured image is present." are you saying that you just want the "text" part of the image above  ("This is a draft post with a really long . . . ") to be displayed? If so, find that image on your server (i.e. image ID 272) and edit the image to remove the AT&T part of the image from the left side.

    However, if the "title" you are wanting to show is actual text, you can't do it within the code you shared. That code you showed determines an $featured_image_ID to use. Somewhere else in the code there is logic to use that image ID to display the appropriate image. What you could do is:

    1. Make the else part of that code set $featured_image_ID to false

    2. Find the part of the code that uses the $featured_image_ID and add a branch of code that will test if the value is false and, if so, shows the "title" text wherever it would get that from.

     

  8. I don't have any prior experience with mod_rewrite and am trying to create internal redirects to create a test harness for an application. I am using Apache on an XAMPP install. I have enabled mod_rewrite by modifying the conf file. I can get some rules to work that will modify the URL in the browser (i.e. external redirect), but I cannot get ones to work that do an internal redirect.

    For example, if I have two files: foo.html and bar.html

    If I use a Redirect in my .htaccess file

    RewriteEngine  on
    Redirect "/foo.html" "/bar.html"

    Then attempting to load localhost:8080/foo.html is redirected in the browser address bar and in the loaded content. However, what I want to happen is to enter the URL for foo.html and only see the content for bar.html - i.e. the address in the browser should still display foo.html. Based on the examples, I've seen I need to use a RewriteRule. I have tried a couple different ways to do this (see below) based on info at apache.org. But when I do, I still get the content for foo.html. It's as if the RewriteRule is not doing anything.

    Here is the format I think I should be using so the url in the browser is not changed:

    RewriteEngine  on
    RewriteRule    "^/foo\.html$"  "/bar.html" [PT]

    Here is another version using RewriteRule, that I think should work just like Redirect (which is working)

    RewriteEngine  on
    RewriteRule    "^/foo\.html$"  "bar.html"  [R]

    But, as stated above, neither of these work - I just get the content for foo.html (and the URL is unchanged). Any ideas?

     

    FWIW: Here is the actual logic I want to achieve using a RedirectMatch but, as stated above, I want to do with as an internal redirect which is hidden from the calling agent.

    RedirectMatch "/data/FAMStaticData/(.*).txt(.*)" "http://localhost:8080/index.php?file=$1"

     

  9. And, for good measure, I will provide another option: array_chunk()

    $columns = 3;
    
    //Put results into an array
    $results = $pdo->fetchAll();
    
    echo "<table>";
    foreach(array_chunk($results, $columns) as $rowData)
    {
        echo "<tr>";
        foreach($rowData as $record) { echo "<td>{$record['name']}</td>";
        echo "</tr>";
    }
    echo "</table>";

    EDIT: Fixed inner TD tags

  10. Apparently the DateInterval class supports milliseconds, but the default method does not support it as an input value. You need to instead use the createFromDateString class of that method

    // convert your date to DateTime object
    $date = '10:00:00.500000';
    $dt = new DateTime($date);
    // convert your period to 
    $interval = '00:25:10.300000';
    
    //Extract time parts
    list($hours, $minutes, $totalSeconds) = explode(':', $interval);
    list($wholeSeconds, $milliSeconds) = explode('.', $totalSeconds);
    
    //Create interval with milliseconds
    $intervalString = "{$hours} hours + {$minutes} minutes + {$wholeSeconds} seconds + {$milliSeconds} microseconds";
    $interval = DateInterval::createFromDateString($intervalString);
    
    // Add interval to date
    $dt->add($interval);// Format date as you needecho $dt->format('H:i:s'); 
    
    echo $dt->format('Y-m-d\TH:i:s.u'); //Output: 2021-11-12T10:25:10.800000

     

    • Like 1
    • Great Answer 1
  11. I assume you mean milliseconds. Can you describe what you are trying to accomplish? I.e. do you need\want to include milliseconds or is the problem that there is input data with milliseconds that is causing a failure? The solution to your problem could be very different based on what you are trying to accomplish.

    The manual shows that the DateTime class does support milliseconds. So, "where" in your code are you wanting to support milliseconds? Will it be the $date, will it be the defined interval, and/or do you need the milliseconds included in the output??? Need some more info.

  12. 1 hour ago, TechnoDiver said:

    Not a problem, but I had a few if statements within the loop that seemed would work best if I could use 'break' and 'continue' if certain conditions weren't met, which I couldn't with the foreach()

    Why are you unable to use break and continue within a foreach() loop? From the manual

    Quote

    break 

    (PHP 4, PHP 5, PHP 7, PHP 8 )

    break ends execution of the current for, foreach, while, do-while or switch structure.

    Quote

    continue 

    (PHP 4, PHP 5, PHP 7, PHP 8 )

    continue is used within looping structures to skip the rest of the current loop iteration and continue execution at the condition evaluation and then the beginning of the next iteration.

    Note: While the description for continue doesn't explicitly state it works for foreach() loops, the examples show that it clearly does.

  13. In your if/then results, create a variable that will determine the checked status of the checkbox. I typically ise a ternary operator for these for brevity. Also, I would avoid using a name like "checkbox", give it a name as to what it is intended for.

    $Datei = file('test.txt')[2]; 
    $checkboxChecked = ($Datei == "Line 2") ? 'checked' : ''; //If true, set value to 'ckecked' else value is empty string
    echo "<input type='checkbox' name='Checkbox' value='checkbox' {$checkboxChecked}>Checkbox</input>';

     

  14. <?php
    
    //Var to hold totCool result
    $totCool = false;
    
    //Query for an exact match on both gpw and ewt
    $query = "SELECT gpm, ewt, totCool from {$uniluxModel} where gpm = :uniluxGpm AND ewt = :uniluxEwt";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm' => $uniluxGpm, ':uniluxEwt' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was an exact match
        $totCool = $stmt->fetchColumn()
    }
    //If no result try getting the two closest records matching gpm and high/low for ewt
    if (!$totCool)
    {
        $query = "(
                      SELECT gpm, ewt, totCool
                      FROM {$uniluxModel}
                      WHERE gpm = :uniluxGpm1 AND ewt < :uniluxEwt1
                      ORDER BY gpm DESC
                      LIMIT 1
                  )
                  UNION
                  (
                      SELECT gpm, ewt, totCool
                      FROM {$uniluxModel}
                      WHERE gpm = :uniluxGpm2 AND ewt > :uniluxEwt2
                      ORDER BY gpm ASC
                      LIMIT 1
                  )";
        $stmt = $dbConn->prepare($queryStr);
        $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
        if($stmt->rowCount() > 0)
        {
            //There was a result, get results into an array
            $result = $stmt->fetchAll();
            //Debug line to see the results
            echo "Results from DB: <pre>".print_r($result, true)."</pre>";
            //Add logic here to calculate the totCool value from the results
            $totCool = "Need to calculate totCool value value";
        }
    }
    //If no result try getting the two closes records matching ewt and high/low for gpm
    if (!$totCool)
    {
        $query = "(
                      SELECT gpm, ewt, totCool
                      FROM {$uniluxModel}
                      WHERE gpm < :uniluxGpm1 AND ewt = :uniluxEwt1
                      ORDER BY gpm DESC
                      LIMIT 1
                  )
                  UNION
                  (
                      SELECT gpm, ewt, totCool
                      FROM {$uniluxModel}
                      WHERE gpm > :uniluxGpm2 AND ewt = :uniluxEwt2
                      ORDER BY gpm ASC
                      LIMIT 1
                  )";
        $stmt = $dbConn->prepare($queryStr);
        $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
        if($stmt->rowCount() > 0)
        {
            //There was a result, get results into an array
            $result = $stmt->fetchAll();
            //Debug line to see the results
            echo "Results from DB: <pre>".print_r($result, true)."</pre>";
            //Add logic here to calculate the totCool value from the results
            $totCool = "Need to calculate totCool value value";
        }
    }
    //If no result get the two closest records that are both above gpm and ewt and both below gpm and ewt
    if (!$totCool)
    {
        $query = "(
                      SELECT gpm, ewt, totCool
                      FROM {$uniluxModel}
                      WHERE gpm < :uniluxGpm1 AND ewt < :uniluxEwt1
                      ORDER BY gpm DESC, ewt DESC
                      LIMIT 1
                  )
                  UNION
                  (
                      SELECT gpm, ewt, totCool
                      FROM {$uniluxModel}
                      WHERE gpm > :uniluxGpm2 AND ewt > :uniluxEwt2
                      ORDER BY gpm ASC, ewt ASC
                      LIMIT 1
                  )";
        $stmt = $dbConn->prepare($queryStr);
        $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
        if($stmt->rowCount() > 0)
        {
            //There was a result, get results into an array
            $result = $stmt->fetchAll();
            //Debug line to see the results
            echo "Results from DB: <pre>".print_r($result, true)."</pre>";
            //Add logic here to calculate the totCool value from the results
            $totCool = "Need to calculate totCool value value";
        }
    }
    
    //Determine if there were results
    if(!$totCool)
    {
        //No records that match either value
        echo "Not enough data to compute";
    }
    else
    {
        //Do something with the retrieved\calculated value
        echo "totCool value = {$totCool}";
    }
    
    ?>

     

  15. You can give this a try. I think it's probably a little more verbose than it needs to be, but should do what you are asking. Not going to guarantee it is what you want, so run it through some tests first

    <?php
    
    //Var to hold result
    $result = false;
    
    //Query for an exact match on both gpw and ewt
    $query = "SELECT gpm, ewt, totCool from {$uniluxModel} where gpm = :uniluxGpm AND ewt = :uniluxEwt";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm' => $uniluxGpm, ':uniluxEwt' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was an exact match
        $result = $stmt->fetchColumn()
    }
    //If no result try getting the two closest records matching gpm and high/low for ewt
    if (!$result)
    {
        $query = "(
                      SELECT gpm, ewt, totCool
                      FROM wxyz
                      WHERE gpm = :uniluxGpm1 AND ewt < :uniluxEwt1
                      ORDER BY gpm DESC
                      LIMIT 1
                  )
                  UNION
                  (
                      SELECT gpm, ewt, totCool
                      FROM wxyz
                      WHERE gpm = :uniluxGpm2 AND ewt > :uniluxEwt2
                      ORDER BY gpm ASC
                      LIMIT 1
                  )";
        $stmt = $dbConn->prepare($queryStr);
        $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
        if($stmt->rowCount() > 0)
        {
            //There was a result
            $result = $stmt->fetchColumn()
        }
    }
    //If no result try getting the two closes records matching ewt and high/low for gpm
    if (!$result)
    {
        $query = "(
                      SELECT gpm, ewt, totCool
                      FROM wxyz
                      WHERE gpm < :uniluxGpm1 AND ewt = :uniluxEwt1
                      ORDER BY gpm DESC
                      LIMIT 1
                  )
                  UNION
                  (
                      SELECT gpm, ewt, totCool
                      FROM wxyz
                      WHERE gpm > :uniluxGpm2 AND ewt = :uniluxEwt2
                      ORDER BY gpm ASC
                      LIMIT 1
                  )";
        $stmt = $dbConn->prepare($queryStr);
        $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
        if($stmt->rowCount() > 0)
        {
            //There was a result
            $result = $stmt->fetchColumn()
        }
    }
    //If no result get the two closest records that are both above gpm and ewt and both below gpm and ewt
    if (!$result)
    {
        $query = "(
                      SELECT gpm, ewt, totCool
                      FROM wxyz
                      WHERE gpm < :uniluxGpm1 AND ewt < :uniluxEwt1
                      ORDER BY gpm DESC, ewt DESC
                      LIMIT 1
                  )
                  UNION
                  (
                      SELECT gpm, ewt, totCool
                      FROM wxyz
                      WHERE gpm > :uniluxGpm2 AND ewt > :uniluxEwt2
                      ORDER BY gpm ASC, ewt ASC
                      LIMIT 1
                  )";
        $stmt = $dbConn->prepare($queryStr);
        $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
        if($stmt->rowCount() > 0)
        {
            //There was a result
            $result = $stmt->fetchColumn()
        }
    }
    
    
    
    if(!$result)
    {
        //No records that match either value
        echo "Not enough data to compute";
    }
    else
    {
        //Do something with the data
    }
    
    ?>

     

    • Like 1
  16. 1 hour ago, halfsparrow said:

    oh now i got it,

    So what i want is if, Gpm is 1.75 and ewt is 45 then i want totcool value for:

    1) The record with lower value for gpm and lower value for ewt. for this example, totcool for  1.5 gpm and 40 ewt

    2) The record with higher value for gpm and higher value for ewt. for this example, totcool for  2.0 gpm and 50 ewt 

    But, there is not necessarily a logical determination for those. Let's just take option #1 to illustrate the problem. The target values the user has entered are gpm = 1.5 and ewt = 40. Now, let's say there are the following records in the database

    id | gpm | ewt
    --------------
    A    1.3  36
    B    1.4  34

    Which one do you choose for condition #1? Both of these records are below the two target values and are candidates for the record needed for #1. Record A has an ewt value that is closer to the target (40) than record B. But, the ewt value of record B is closer to the target (1.5) than record A

    That was why one solution @Barand provided resulted in four records. It has a deterministic approach based on the two variables and the two conditions.

     

  17. 1 hour ago, halfsparrow said:

    Hope it makes clear now

    No, it is not. I completely understand how to get min/max values from a database. There is a logic problem that I guess I am not doing well at explaining. 

    Quote

    so for example if ewt is anywhere between 30-40, lets say 36 then lowest ewt should be 30 and highest ewt should be 40

    Yes, determining the lower and upper bounds for ewt is trivial. However, you are not specifically looking for the ewt values - you are after the totCool values. And there are multiple records where ewt = 30 and multiple records where ewt = 40. So, what is the logic to determine which record where ewt = 30 (or where ewt = 40) should be retrieved?

    @Barand provides one solution where four records are retrieved. It finds:

    1. The record with the highest gpm value below the target AND the highest ewt value below the target
    2. The record with the lowest gpm value above the target AND the highest ewt value below the target
    3. The record with the highest gpm value below the target AND the lowest ewt value above the target
    4. The record with the lowest gpm value above the target AND the lowest ewt value above the target

    You would then have to determine in the code how you want to use those to calculate a relevant totCool value.

  18. On 11/1/2021 at 12:54 PM, halfsparrow said:

    If user enters gpm, = 1.75 and ewt as 35 i will have to do calculation for totCool for 1.5 gpm and 2 gpm  and 30 and 40 ewt

    Basically the value below and the value above

    That really doesn't answer the question. What is the LOGIC to be implemented to determine which records to get? The sample values I copied are probably too "easy" for that question. Since there is a 'logical' record for both the low values and both the high records. But, taking the same example values of gpm, = 1.75 and ewt = 35, lets say these values exist

    gpm | ewt
    1.5 | 50 --- Lower gpm but higher ewt
    2.0 | 10 --- Higher gpm but very low ewt
    4.5 | 30 --- Much higher gpm but ewt is only a little lower

    If you can explain the logic you want to use to determine which records to pull, I can look at providing something.

  19. 2 hours ago, halfsparrow said:

    This looks perfect to me but just a few changes instead of getting getting average in query i want simple query to select * from table and then will do maths in if statement, also for when neither records are matched can you help me with query, i want that query to be treated same as before like when we did'nt found gpm we took one value below or above it so same way we want one value up and one value down for both gpm and ewt 

    The first part in bold is easy enough. But, you need to provide more details to do the second item. If no records match ewt or gpm, then you need to define how you want to determine which records to return. For example, if the user enters gpm = 1.75 and ewt = 35, which records do you want returned from the sample data below? Do you want two records or four?

    image.png.d3e1d695632d5e268e3e5e3ce97f32c7.png

     

  20. 7 hours ago, mac_gyver said:

    to determine if a full case needs to be opened and broken into unit items, you would then query to find the current unit quantity for the item id that was just inserted. if it is a negative value, you would then insert a row into the inventory table that deducts enough full case(s) and insert a row into the inventory table that adds that many case quantity of unit items.

    The challenge with programatically determining when cases are broken down or not is that it won't always match reality. A person fulfilling an order may break down a case to fulfill an order for individual units even when there were sufficient quantities of individual units on hand (e.g. one or more individual units may be damaged requiring another case to be opened). If the system must explicitly monitor physical cases vs assuming cases based on total quantity divided by case size, then the fulfillment process should be included in the application workflow where the person fulfilling the order confirms the programatically determined quantities of cases\single units or can edit based on what they actually execute. Otherwise, the actual inventory and the application inventory will get out of alignment.

    The OP needs to determine what should happen in a situation such as: There are no physical cases in the actual inventory, but there are 20 loose units (a case holds 15). Should the system allow a user to purchase a case and the fulfillment would simply send 15 loose units or can a user only purchase a case when there is an actual physical unopened case?

  21. A potential problem is that such a system could never really "know" how many cartons you have vs. loose items unless the person fulfilling the order explicitly enters the number of cartons vs/ loose items. If that is not important, than I would define each product with a column to identify the "bulk quantity". Store the inventory in the total number of items (regardless of cartons). Then, whenever calculating a transaction just divide by the bulk quantity. The whole divisor would be the number of whole cartons and the remainder would be the individual units.

  22. 9 hours ago, TechnoDiver said:

    Hi, Psycho, I've finally gotten some smaller issues out of the way so I can start on this again. I was looking through you response and it pretty much makes sense to me except the third item (reply_id) I'm picturing comment_id as the primary key but I don't understand exactly what you're saying with the reply_id. How could it be empty or 0?

    If you get the time and will do you mind going into a bit more detail on this?! Thanks, I'd love to read whatever else you'd have to say about it.

    Taking reddit as an example, anyone makes an original comment to a post it would not be replying to another person's comment. So, the reply ID of those comments would be empty/zero. However, if anyone replies to one of those initial comments, then you need to know which comment they replied to - in that case you put the comment_id that someone is replying to as the reply_id. Then, if someone replies to one of those comments you use it as the reply_id

     

  23. OK, here is how I would tackle this problem.

    1. Run a query with an AND condition. If a record is returned, then it is an exact match and use the totCool value. If no record is returned, then there was no record matching both. Proceed to step 2
    2. Run a query to get the two records matching gpm with the closest (high and low) values for ewt and return the average of totCool for those two record. If no record is returned, then there was were not records matching gpm  with one above and one below the ewt value. Proceed to step 3
    3. Run a query to get the two records matching ewt with the closest (high and low) values for gpm and return the average of totCool for those two record. If no record is returned, then there was were not records matching ewt  with one above and one below the gpm value. Exit with error condition.
    <?php
    
    //Var to hold result
    $result = false;
    
    //See if there is an exact match on both gpw and ewt
    $query = "SELECT totCool from {$uniluxModel} where gpm = :uniluxGpm AND ewt = :uniluxEwt";
    $stmt = $dbConn->prepare($queryStr);
    $stmt->execute(array(':uniluxGpm' => $uniluxGpm, ':uniluxEwt' => $uniluxEwt));
    if($stmt->rowCount() > 0)
    {
        //There was an exact match
        $result = $stmt->fetchColumn()
    }
    //If no result try getting the avereage records matching gpm and high/low ewt
    if (!$result)
    {
        $query = "SELECT AVG(totCool)
                  FROM 
                  (
                    (
                      SELECT totCool
                      FROM wxyz
                      WHERE gpm = :uniluxGpm1 AND ewt < :uniluxEwt1
                      ORDER BY gpm DESC
                      LIMIT 1
                    )
                    UNION
                    (
                      SELECT totCool
                      FROM wxyz
                      WHERE gpm = :uniluxGpm2 AND ewt > :uniluxEwt2
                      ORDER BY gpm ASC
                      LIMIT 1
                    )
                  ) as avgTable";
        $stmt = $dbConn->prepare($queryStr);
        $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
        if($stmt->rowCount() > 0)
        {
            //There was a result
            $result = $stmt->fetchColumn()
        }
    }
    //If no result try getting the avereage records matching ewt and high/low gpm
    if (!$result)
    {
        $query = "SELECT AVG(totCool)
                  FROM 
                  (
                    (
                      SELECT totCool
                      FROM wxyz
                      WHERE gpm < :uniluxGpm1 AND ewt = :uniluxEwt1
                      ORDER BY gpm DESC
                      LIMIT 1
                    )
                    UNION
                    (
                      SELECT totCool
                      FROM wxyz
                      WHERE gpm > :uniluxGpm2 AND ewt = :uniluxEwt2
                      ORDER BY gpm ASC
                      LIMIT 1
                    )
                  ) as avgTable";
        $stmt = $dbConn->prepare($queryStr);
        $stmt->execute(array(':uniluxGpm1' => $uniluxGpm, ':uniluxEwt1' => $uniluxEwt, ':uniluxGpm2' => $uniluxGpm, ':uniluxEwt2' => $uniluxEwt));
        if($stmt->rowCount() > 0)
        {
            //There was a result
            $result = $stmt->fetchColumn()
        }
    }
    
    if(!$result)
    {
        //No records that match either value
        echo "Not enough data to compute";
    }
    else
    {
        echo "totValue: {$result}";
    }
    
    ?>

     

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.