Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,429

  • Joined

  • Last visited

  • Days Won

    807

 Content Type 

Posts posted by Barand

    Insert or Update duplicating data

    in PHP Coding Help

    Posted

    If it helps, this is how I would do it... 

    <?php
require 'db_inc.php';
$db = pdoConnect('humbug');

//
//   PROCESS POST DATA
//
if ($_SERVER['REQUEST_METHOD']=='POST') {
    $jobid = trim($_POST['jobid']);
    
    if (!empty($jobid)) {
        $placholders = [];
        $values = [];
        
        foreach ($_POST['qty'] as $did => $qty) {
            $placeholders[] = "(?,?,?)";
            array_push($values, $jobid, $did, intval($qty));
        }
        // single multiple-insert query
        $stmt = $db->prepare("INSERT IGNORE INTO ssm_drink_order 
                                  (job_id, drink_id, drink_qty)
                                  VALUES " . join(',', $placeholders) . 
                              "ON DUPLICATE KEY
                                  UPDATE drink_qty = VALUES(drink_qty) 
                             ");
        $stmt->execute($values);
        
        // clear zero qty ordere
        $db->exec("DELETE FROM ssm_drink_order WHERE drink_qty = 0");
    }
    
    // reload the form
    header("Location:?jobid=");
    exit;
}

//
//  PROCESS GET DATA
//
$jobid = $_GET['jobid'] ?? '';
$formdata = '';

    $stmt = $db->prepare("SELECT d.drink_id
                                 , d.drink_name
                                 , o.drink_qty
                            FROM ssm_drink d 
                                     LEFT JOIN
                                 ssm_drink_order o ON d.drink_id = o.drink_id
                                                   AND o.job_id = ?
                            ORDER BY d.drink_id
                            ");
    $stmt->execute([$jobid]);
    foreach ($stmt as $d) {
        $formdata .= "<label>{$d['drink_name']}</label>
                      <input type='number' name='qty[{$d['drink_id']}]' value={$d['drink_qty']}>
                      <br>
                     ";
    }
    

function jobOptions(PDO $db, $current)
{
    $res = $db->query("SELECT job_id, job_name FROM ssm_job");
    $opts = "<option value=''> - Choose Job - </option>\n";
    foreach ($res as $r) {
        $sel = $r['job_id'] == $current ? 'selected' : '';
        $opts .= "<option $sel value='{$r['job_id']}'>{$r['job_name']}</option>\n";
    }
    return $opts;
}  
?>
<!DOCTYPE html>
<html>
<head>
<title>Example Drinks Order</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type="text/css">
    label {color: #2DABE1; font-weight: 600; width: 250px; display: inline-block; }
    input[type='number']  { padding-right: 16px; text-align: right;}
</style>
</head>
<body>
    <header class="w3-blue w3-container w3-padding">
        <h1>Example Drinks Order</h1>
    </header>
    
    <form method="GET">
        <fieldset class="w3-content w3-border w3-padding w3-margin">
            <label>Job</label>
            <select class="w3-input w3-border" name="jobid" onchange="this.form.submit()">
                <?=jobOptions($db, $jobid)?>
            </select>
        </fieldset>
    </form>
    
    <form method="POST">
        <input type="hidden" name="jobid" value="<?=$jobid?>">
        <fieldset class="w3-content w3-border w3-padding w3-margin">
            <legend>Drinks Quantities</legend>
            <?=$formdata?>
            <br><br>
            <label>&nbsp;</label>
            <input type="submit" name="btnSub" value="Update Order">
        </fieldset>
    </form>
        
</body>
</html>

    Note:  Assumes ssm_drink_order is 

    CREATE TABLE `ssm_drink_order` (
  `job_id` int(11) NOT NULL,
  `drink_id` int(11) NOT NULL,
  `drink_qty` int(11) DEFAULT NULL,
  PRIMARY KEY (`job_id`,`drink_id`)
)

    or, at least, that (job_id, drink_id) is defined as UNIQUE

    Need help on getting products to insert into database

    in PHP Coding Help

    Posted

    I assume you are referring to the insert query on line 1,246. I do not see you checking for any MySQL error messages anywhere following that query. These might just give you a clue as to why it isn't working as expected.

    I suggest you put this line of code (if you haven't already) just before you connect to the db server 

    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

     

    ORDER BY vs Sort -- one column is NULL

    in MySQL Help

    Posted

    Going back to your original query ... 

    $query = "SELECT use,city,school from a_schools as s1
    LEFT JOIN a_schools as s2
    ON s1.bsect = s2.bsect
    WHERE '" . $query_school . "' = CONCAT(s1.city,' ',s1.school) || '" . $query_school . "' = s1.school
    ORDER BY concat(s2.city,s2.school) asc";

    why aren't you basing your search on IDs instead of names, especially as you have so many schools with almost identical names?

    ORDER BY vs Sort -- one column is NULL

    in MySQL Help

    Posted

    Of course, you could store the city and the full school name, then using the technique we discussed in this forum a couple of weeks ago, remove the city from the school name when required.

    I.E. 

    SELECT school
     , city
     , TRIM(REPLACE(school, COALESCE(city,''), '')) as short_school
FROM school;

+------------------------------+----------------+----------------------+
| school                       | city           | short_school         |
+------------------------------+----------------+----------------------+
| Bloomington North            | Bloomington    | North                |
| Columbus East                | Columbus       | East                 |
| Fort Wayne Bishop Dwenger    | Fort Wayne     | Bishop Dwenger       |
| Neil Armstrong High          | NULL           | Neil Armstrong High  |
| Davy Crockett School         | San Antonio    | Davy Crockett School |
| Kokomo                       | NULL           | Kokomo               |
| Logansport                   | NULL           | Logansport           |
| Marion                       | NULL           | Marion               |
| Jefferson                    | Lafayette      | Jefferson            |
| McCutcheon                   | Lafayette      | McCutcheon           |
| Harrison                     | West Lafayette | Harrison             |
| Carmel Greyhounds            | NULL           | Carmel Greyhounds    |
| Zionsville Eagles            | NULL           | Zionsville Eagles    |
| Fishers Tigers               | NULL           | Fishers Tigers       |
| Noblesville Millers          | NULL           | Noblesville Millers  |
| Westfield Shamrocks          | NULL           | Westfield Shamrocks  |
| Hamilton Southeastern Royals | Hamilton       | Southeastern Royals  |
+------------------------------+----------------+----------------------+

     

    • Like 1

    ORDER BY vs Sort -- one column is NULL

    in MySQL Help

    Posted · Edited by Barand

    17 minutes ago, Jim R said:

    and it matters to those who want to see their school represented as the school is named

    but not always, apparently ...

    8 minutes ago, Jim R said:

    There is another part of the site where I want them to be distinctly separate

    Sounds like you schools have a "formal name" and a "nickname", like users might have.

    ORDER BY vs Sort -- one column is NULL

    in MySQL Help

    Posted

    Then store the school name as it is named and don't mess around with removing/not removing city names. 

    Use | City           | School
----+----------------+---------------------- 
0   | NULL           | Kokomo 
0   | NULL           | Logansport 
0   | NULL           | Marion 
1   | Lafayette      | Lafayette Jefferson 
0   | Lafayette      | McCutcheon 
0   | West Lafayette | Harrison

    If you sort the above by school only you get the order you wanted

    Max User Connection error...can't connect to mysql database

    in MySQL Help

    Posted

    Extremely inefficient. Connecting to the db server probably takes longer than the query so that's quite an overhead you are adding to your script.

    You should connect once to the server at the start of the script and pass the connection as a parameter to any functions that need it. The connection will close automatically when the script finishes its execution.

    not able to create a facebook like timeline

    in PHP Coding Help

    Posted

    In my previous post I outlined the dangers of having duplicate column names in your tables and to use aliases to differentiate when this occurs.

    But it appears that you couldn't be bothered to read it because you immediately came up with this query with three sets of duplicates... 

    select distinct 
        gp.gp_id as gp1
        ,gp.pid
        ,gp.author_gp
        ,gp.gname
        ,gp.type                                --+
        ,gp.title                       --+       |
        ,gp.data                          | --+   |
        ,gp.pdate                         |   |   |
        ,gp.group_id                      |   |   |
        ,gp.author_id                     |   |   |
        ,u.avatar                         |   |   |
        ,u.user_id                        |   |   |
        ,up.update_id as up1              |   |   |
        ,up.update_body                   |   |   |
        ,up.time                          |   |   |
        ,up.title                       --+   |   |
        ,up.account_name                      |   |
        ,up.author                            |   |
        ,up.type                              | --+
        ,up.data                            --+
from group_posts as gp
    join user as u on u.uname=gp.author_gp 
    join updates as up on u.uname=up.author
where 
    gp.gname='MEP news' 
    and up.update_id >391  
    and up.author in("shan2batman", "aboutthecreator") 
order by time,pdate desc limit 0,5

    Stop wasting our time.

    not able to create a facebook like timeline

    in PHP Coding Help

    Posted

    On 1/1/2020 at 11:13 PM, narutofan said:

        $sql2="select distinct g.*,u.avatar,u.user_id,up.*

                from group_posts as g "

                . " join user as u on uname=g.author_gp "

                . " join updates as up on u.uname=up.author"

    Do not use "SELECT * ", specify the columns you need.

    This is especially true when joining tables and those tables have columns with the same names. In this case you should use column aliases if you need both.

    EG 

    SELECT g.type as gtype
       u.type as utype
FROM ...

     

    The default fetch mode is PDO::FETCH_BOTH so your output with contain each column value with both a column name  and a column number as array keys

    Examining you example output, this is the case for the first few columns ... 

    Array
(
    [gp_id] => 103
    [0] => 103
    [pid] => 0
    [1] => 0
    [gname] => MEP news
    [2] => MEP news
    [author_gp] => aboutthecreator
    [3] => aboutthecreator

    but then you have ... 

        [type] => a
    [4] => 0
    [title] => qwertyu
    [5] => 2

    and the relationship has broken down because a column "type" from a second table has overwritten the value for column "type" in the first table"

    Checking the later columns in your example output finds numeric keys without corresponding name keys (the duplicated names that were overwritten) .. 

        [19] => 0
    [20] => 0
    [21] => qwertyu

    Depending on how you process these results you will get different outcomes.

    When you open your PDO connection, specify a default fetch mode EG 

    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    then, on occasions when you specifically need the numeric keys, do ... 

    $row = $stmt->fetch(PDO::FETCH_NUM);

     

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