Jump to content

Adamhumbug

Members
 View Profile  See their activity

  • Posts

    590

  • Joined

  • Last visited

 Content Type 

Posts posted by Adamhumbug

    For each loop not getting all data

    in PHP Coding Help

    Posted · Edited by Adamhumbug

    I have a function that lists out all items that should appear on a quote - i know the sql is correct as when i was using a whille($stmt -> fetch()...... everything worked fine.

    I am now building an array so that i can display it by a certain value but i am only getting 2 results output and there should be 16. 

     $row = $stmt -> fetchAll();

    foreach($row as $item){
        if(isset($data[$item['sectionName']])){
            $data[$item['sectionName']] = [];
        }
        $data[$item['sectionName']][]= ["itemName" =>$item['name'], "quantity" => $item['quantity']];
    }

    foreach($data as $section => $items){
        $out.= "<br/>".$section."<br/>";
        foreach($items as $i){
            $out.= "-".$i['itemName'];
        }
    }

    Below are the rows i am expecting to see.

    Screenshot2023-08-18at13_24_30.png.ace72f847638a5413e3b3774db1bab16.png

    This is what is being output using 

    $out = "<pre>".print_r($data,1)."</pre>";

     

    Screenshot 2023-08-18 at 13.25.09.png

    Prepared Statement Defualt Values

    in PHP Coding Help

    Posted

    Hi All,

    I have an insert query where sometimes all of the fields will have values to insert and sometimes they will not.

    I have allowed null on those fields in the database but i still get a warning (i have them turned on but ideally i would like to remedy the warning rather than turn it off). 

    $stmt->execute([
            ':quote_id' => $_GET['quoteid'],
            ':item_id' => $_POST['itemId'],
            ':quantity' => $_POST['quantity'],
            ':start_date' => $_POST['startDate'],
            ':end_date' => $_POST['endDate']
        ]);

    Start and End date may not have values so i would like to pass in something intentional like 0 or NULL but im not sure how to do that or what doing so is called.

    More efficient rounding calculator

    in Javascript Help

    Posted · Edited by Adamhumbug

    Hi All,

    I have a rental system - if you rent for 3 or more days over a week you pay for 2 weeks, 3 or more days over 2 weeks and you pay for 3 and so on.

    As i need to be able to see how many weeks someone is renting equipment for i have built a js function but wondered if there was a more effience way of getting the weeks value than the following. 

                if ($days == 0){
                $weeks = 0
            }else if ($days < 10) {
                $weeks = 1
            } else if ($days >= 10 && $days < 16) {
                $weeks = 2
            } else if ($days >= 17 && $days < 23) {
                $weeks = 3
            } else if ($days >= 24 && $days < 30) {
                $weeks = 4
            }

    Thanks as always

    On duplicate key update always inserting new row

    in MySQL Help

    Posted

    2 minutes ago, Barand said:

    You should be getting a mysql error - you have 7 placeholders but only passing 6 values in the execute. The way you have defined it you need to pass the $_POST['quotename'] twice.

    A better way is 

    ...
ON DUPLICATE KEY UPDATE
        name=VALUES(name)

    telling it to use the same value as in the VALUES clause. The you only need provide the name once.

    As for your problem, what has to be duplicated to throw a duplicate key error? Your id will be auto_incremented so that won't be duplicated.

    Thats my issue right there - i was relying on the primary key (id) - i didnt realise that it would automatically increment when using this method.

    While with foreach not giving correct value

    in PHP Coding Help

    Posted

    On 8/16/2023 at 4:29 PM, mac_gyver said:

    what's the difference between accepted and open and why doesn't the first query use accepted = 1 too?

    i'm betting that the $out = ""; variable in the first posted code is being used to build the output in the image, listing the results by the left-hand ranges (guessing per client), and the echo statements are just there for debugging?

    this is going to be a case of - don't show us your non-working attempt and expect us to figure out what's wrong, instead show us sample data and what result you expect from that data.

    also -

    don't waste your time with bindColumn() statements, this is wasted typing. just fetch the data and reference the data by its associative index (column) name.

    lastly, why are you preparing a non-prepared query that doesn't have any values being supplied to it? just directly use the ->query() method.

     

    The value in question that is not working correctly is the small grey value - the others are set using queries from other functions called at the top of this one.

     

    I take your point on the prepared not being required and in terms of the bind column, i am brand new to PDO - actually made the switch due to @Barand suggestions over the years.  I am still finding my feet.

    While with foreach not giving correct value

    in PHP Coding Help

    Posted

    On 8/16/2023 at 3:52 PM, Phi11W said:

    BIG problem with this SQL. 

    You want the total of total_value for each client, but which values of company_name, country, etc. should the query return? 

    On any other DBMS (and even a properly-configured MySQL), this query would be thrown out with an error because the database cannot work it out which one you would want and so gives up.  Your [poorly-configured] MySQL is simply returning any old, arbitrary value for each field that happens to be lying around in its buffer as it reads the data.

    I will take your word for this as i am here for help - however, when i have logged out what this sql produces, it seems that i am getting the data that i expect and only this.  When i change the data in the database and re run the query i am stil getting the data that i expect.

     

    if it helps here is the complete function.

      

    function showClientList(){
    include 'includes/dbconn.php';

    $grandTotal = sumAllQuoteValues();
    $sumOpenAndAcceptedValueByClient = sumOpenAndAcceptedValueByClient();

    echo "<pre>";
    var_dump($sumOpenAndAcceptedValueByClient);
    echo "</pre>";


    $sql = "
            SELECT client.id as clientid, company_name, country, DATE_FORMAT(client.date_created, '%d %M %Y') as date_created, sum(total_value) as tval
            from client
            left join quote on client.id = quote.client_id and 1 = quote.open
            group by client.id
            ";
    $stmt = $pdo -> prepare($sql);
    $stmt -> execute();
    $stmt -> bindColumn('company_name', $coName);
    $stmt -> bindColumn('country', $country);
    $stmt -> bindColumn('date_created', $dCreated);
    $stmt -> bindColumn('tval', $tval);
    $stmt -> bindColumn('clientid', $cId);
    $out = "";




    while ($stmt -> fetch()){
        foreach ($sumOpenAndAcceptedValueByClient as $row){
            if($row['client_id'] == $cId){
                $valByClient = $row['sum(total_value)'];
            }
        }


        if($cId != null){        
        $created = new DateTime($dCreated);
        $now = new DateTime(date("Y-m-d H:i:s"));
        if($created->modify('+3 year') < $now){
            $age = "Long Standing Client";
        }else if ($created->modify('+2 year') < $now) {
            $age = "Established Client";
        }else if ($created->modify('+1 year') < $now) {
            $age = "Second Year";
        }else{
            $age = "New Client";
        }

        $percentageOfGrandTotal = ($valByClient/$grandTotal)*100;
        $roundedPercentage = round($percentageOfGrandTotal,2);


        $out .=<<<EOD
            <tr class='align-middle'>
                <td>
                    <div>$coName</div>
                    <div class='small text-medium-emphasis'><span>$age</span> | Created: $dCreated</div>
                </td>
                <td class='text-center'>
                    <svg class='icon icon-xl'>
                        <use xlink:href='vendors/@coreui/icons/svg/flag.svg#cif-$country'></use>
                    </svg>
                </td>
                <td>
                    <div class='clearfix'>
                        <div class='float-start'>
                            <div type="button"
                            tabindex="0"
                            data-coreui-toggle="popover" 
                            data-coreui-placement="top"
                            data-coreui-custom-class="custom-popover"
                            data-coreui-title="Potential New Revenue Value Of Client"
                            data-coreui-trigger="focus"
                            data-coreui-content="Total open quotes come to £$tval." class='fw-semibold'>£$tval</div>
                        </div>
                        
                        <div type="button"
                        tabindex="0"
                        data-coreui-toggle="popover" 
                        data-coreui-placement="top"
                        data-coreui-custom-class="custom-popover"
                        data-coreui-title="Total Value Of Client"
                        data-coreui-trigger="focus"
                        data-coreui-content="Total open and accepted quotes come to £$valByClient." 
                 class='float-end'><small class='text-medium-emphasis'>£$valByClient</small></div>
                    </div>
                    <div class='progress progress-thin'>
                        <div type="button"
                        tabindex="0"
                        data-coreui-toggle="popover" 
                        data-coreui-placement="top"
                        data-coreui-custom-class="custom-popover"
                        data-coreui-title="Total Value Of Client"
                        data-coreui-trigger="focus"
                        data-coreui-content="This client makes up $roundedPercentage% of total revenue." class='progress-bar bg-success' role='progressbar' style='width: $percentageOfGrandTotal%'></div>
                    </div>
                </td>
                <td class='text-center'>
                    <svg class='icon icon-xl'>
                        <use xlink:href='vendors/@coreui/icons/svg/brand.svg#cib-cc-mastercard'></use>
                    </svg>
                </td>
                <td>
                    <div class='small text-medium-emphasis'>Active/Completed</div>
                    <div class='fw-semibold'>2/8</div>
                </td>
                <td>
                    <div class='dropdown'>
                        <button class='btn btn-transparent p-0' type='button' data-coreui-toggle='dropdown' aria-haspopup='true' aria-expanded='false'>
                            <svg class='icon'>
                                <use xlink:href='vendors/@coreui/icons/svg/free.svg#cil-options'></use>
                            </svg>
                        </button>
                        <div class='dropdown-menu dropdown-menu-end' style=''><a class='dropdown-item' href='#'>Info</a><a class='dropdown-item' href='#'>Edit</a><a class='dropdown-item text-danger' href='#'>Delete</a></div>
                    </div>
                </td>
            </tr>
EOD;
        }
        
    }
    return $out;
}

     

    On duplicate key update always inserting new row

    in MySQL Help

    Posted

    Hi All,

    I have a php function to insert data into my database which works fine but i want to update the record using the same button and have thus used on duplicate key update. 

    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    if (isset($_POST['createNewQuote'])) {
        
        $sql = 
        "INSERT into quote (name, internal_ref, currency, kit_delivery, kit_return, job_id) 
        values (:name, :internal_ref, :currency, :kit_delivery, :kit_return, :jobId)
        ON DUPLICATE KEY UPDATE
        name=:name
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            ':name' => $_POST['quoteName'],
            ':internal_ref' => $_POST['internalReference'],
            ':currency' => $_POST['currency'],
            ':kit_delivery' => $_POST['kitDeliveryDate'],
            ':kit_return' => $_POST['kitCollectionDate'],
            ':jobId' => $_POST['jobId']
        ]);
    }
}

    The issue that i have is that everytime i click the save button that triggers this function, i am getting a new row in the database created - is there something obvious i am doing wrong?

    I have attached the structure just in case that helps

    Screenshot2023-08-17at21_16_13.png.633f734ac2ed07a3e339c3f64de117ec.png

    While with foreach not giving correct value

    in PHP Coding Help

    Posted · Edited by Adamhumbug

    12 minutes ago, Barand said:

    It looks like your default fetch mode is

    PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set

    which gives you every value twice.

    Change your fetch default to PDO::FETCH_ASSOC in your connection options.

    i have done that, i am now seeing:

      

    array(2) {
  [0]=>
  array(2) {
    ["client_id"]=>
    int(1)
    ["sum(total_value)"]=>
    string(4) "1000"
  }
  [1]=>
  array(2) {
    ["client_id"]=>
    int(2)
    ["sum(total_value)"]=>
    string(3) "211"
  }
}

    But still have the extra value.

    While with foreach not giving correct value

    in PHP Coding Help

    Posted

    29 minutes ago, Barand said:

    Does $sumOpenAndAcceptedValueByClient contain the results from another query?

    Which outputs this

      

    array(2) {
  [0]=>
  array(4) {
    ["client_id"]=>
    int(1)
    [0]=>
    int(1)
    ["sum(total_value)"]=>
    string(4) "1000"
    [1]=>
    string(4) "1000"
  }
  [1]=>
  array(4) {
    ["client_id"]=>
    int(2)
    [0]=>
    int(2)
    ["sum(total_value)"]=>
    string(3) "211"
    [1]=>
    string(3) "211"
  }
}

     

    While with foreach not giving correct value

    in PHP Coding Help

    Posted

    27 minutes ago, Barand said:

    Does $sumOpenAndAcceptedValueByClient contain the results from another query?

    it comes from this function

      

    function sumOpenAndAcceptedValueByClient(){
    include 'includes/dbconn.php';
    $sql = "SELECT client_id, sum(total_value) from quote where accepted = 1 or open = 1 group by client_id";
    $stmt = $pdo-> prepare($sql);
    $stmt -> execute();
    $out = $stmt -> fetchAll();
    return $out;
}

     

    While with foreach not giving correct value

    in PHP Coding Help

    Posted

    Hi All,

    Sorry for the rubbish title, wasnt sure how else to word it.

    I have a select statement grabbing some info from my DB. 

        $sql = "
            SELECT client.id as clientid, company_name, country, DATE_FORMAT(client.date_created, '%d %M %Y') as date_created, sum(total_value) as tval
            from client
            left join quote on client.id = quote.client_id and 1 = quote.open
            group by client.id
            ";
    $stmt = $pdo -> prepare($sql);
    $stmt -> execute();
    $stmt -> bindColumn('company_name', $coName);
    $stmt -> bindColumn('country', $country);
    $stmt -> bindColumn('date_created', $dCreated);
    $stmt -> bindColumn('tval', $tval);
    $stmt -> bindColumn('clientid', $cId);
    $out = "";

    i then have a while loop that is comparing the output from another function to match IDs and give a value to the output if they match. 

        while ($stmt -> fetch()){
        foreach ($sumOpenAndAcceptedValueByClient as $row){
            if($row['client_id'] == $cId){
                $valByClient = $row['sum(total_value)'];
                echo $cId;
                echo " ".$row['client_id'];
                echo " ".$row['sum(total_value)'];
                echo "<br/><br/>";
            }
        }

    It seems to be working, however i have a client in the list that has no value so its sum(total_value) should be blank, actually it wont even show in the array i am foreaching.

    The issue is that in the table it is producing, it is giving me the same value of whatever comes before it - you see the £211 in the table image below.

    Screenshot2023-08-16at13_44_37.png.5ecdced4ed70479ec0a68f7355396c4b.png

     

    I have tried debugging this by echoing the values that are getting passed around which gives me the following output.

    1 1 1000

    2 2 211

    I am therefore not sure why i am getting a value put into the last row.

    I have tried adding and else to the for each making it 

        while ($stmt -> fetch()){
        foreach ($sumOpenAndAcceptedValueByClient as $row){
            if($row['client_id'] == $cId){
                $valByClient = $row['sum(total_value)'];
                echo $cId;
                echo " ".$row['client_id'];
                echo " ".$row['sum(total_value)'];
                echo "<br/><br/>";
            }else{
                $valByClient = 0;
            }
        }

    that gives me the same echo values but now the first row =0 as well as the last.

    Screenshot2023-08-16at13_47_34.png.6d18b8e090a1f286dc38947d9e90e2c6.png

    I am a bit stumped on this and would appreciate your help.

    If you need more info or for me to show more code, i will be happy to do so.

    As always, thanks in advance.

    Total count as well as total per client

    in MySQL Help

    Posted

    2 hours ago, mac_gyver said:

    you would add GROUP BY client.id to the query to get a SUM() per client. in fact, if your database was set to STRICT mode, you would be getting an  error with the current query about using an aggerate function without a GROUP BY term.

    then, if you want both the SUM() per client and the grand total, you can add WITH ROLLUP to the end of the GROUP BY ... term.

    Amazing, that is absolutely what i was looking for but does lead me on to another question.

    As i am wanting to show on each row what percentage of the overall total each client makes up i would need to do something different with the rollup as currently it adds another row to the table.

    Any suggestions on how to deal with that?

    Total count as well as total per client

    in MySQL Help

    Posted

    Hi All,

    I fear this may be very simple but i have been struggling a little with it - brain fog.

    I am running the following query: 

    SELECT client.id, company_name, country, DATE_FORMAT(date_created, '%d %M %Y') as date_created, sum(total_value) as tval
            from client
            inner join quote on client.id = quote.client_id

    but i also want to do a count of everything that is in the total_value column so i end up with a number per client and a total of everything.

    Any pointers appreciated.

    Sanitising old PHP functions with functions and params from functions in sql

    in PHP Coding Help

    Posted

    Just now, Adamhumbug said:

    This has worked for one of my queries but actually for the one in the example it has not.

    Your example would require that i re-write all of the output of all of the queries that come back from the other functions that are run.

    Is there a way for me to do this without having to manually evaluate the functions and then hard code what they produce for the prepared statements?

    Sorry to be more clear about what i mean - the where_terms i dont really want to have to write out explicitly if there is a way around it.

    I dont know if there is a good way of doing this, especially as they include params that have been passed in from the front end.  Can you think of another way or do you think i am beat?

    Sanitising old PHP functions with functions and params from functions in sql

    in PHP Coding Help

    Posted

    9 minutes ago, mac_gyver said:

    in your example, does $opsrch['srch'] contain all that WHERE ... sql that also contains the $srch value and if so, where is $opsrch['srch'] built relative to the code you have posted?

    what php version will this be used with, because as of php8.1, you can simply supply an array of values to the ->execute([...]) call end eliminate all the explicit input binding and the type format string. all you would need to do is build an array with the input values that corresponds to the sql that is being built.

    If you are not using php8.1+, to convert this to a prepared query, using mysqli, you need to build the type format string and an array of input parameters that matches the sql that is being built.

    My issue with this is that i will have to have several arrays as i am putting the code in several places in the query which i struggled to work out when referencing the stack overflow post i quoted.

×
×
  • 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.