Jump to content

Adamhumbug

Members
  • Posts

    357
  • Joined

  • Last visited

Posts posted by Adamhumbug

  1. 2 minutes ago, Barand said:

    Always avoid subqueries like that one - they really slow down the performance of the query.

    Try

    SELECT p.first_name
        , p.last_name
        , p.id as playerid
        , sum(runs_scored) as runs
        , count(runs_scored) as balls
        , sum(runs_scored = 4 ) as fours
        , sum(runs_scored = 6) as sixes
    FROM game g
        JOIN player p ON g.batting_first = p.team_id
        JOIN deliveries d ON p.id = d.on_strike_batter_id
                          AND g.id = d.game_id
    WHERE g.id = 51
    GROUP BY p.id;
    
    +------------+-----------+----------+------+-------+-------+-------+
    | first_name | last_name | playerid | runs | balls | fours | sixes |
    +------------+-----------+----------+------+-------+-------+-------+
    | Brian      | Lara      |       20 |   58 |    19 |     7 |     2 |
    | Freddie    | Flintoff  |       21 |   46 |    14 |     6 |     2 |
    +------------+-----------+----------+------+-------+-------+-------+

     

    Amazing - thanks.

     

    I will try and avoid using them.

  2. Answered my own question.

    Apologies for my impatience.

    SELECT p.first_name
    	, p.last_name
    	, p.id as playerid
    	, sum(runs_scored) as runs
    	, count(runs_scored) as balls
    	, (Select count(runs_scored) from deliveries where runs_scored = 4 and deliveries.on_strike_batter_id = p.id) as fours
    FROM game g
    	JOIN player p ON g.batting_first = p.team_id
    	JOIN deliveries d ON p.id = d.on_strike_batter_id
    					  AND g.id = d.game_id
    WHERE g.id = ?
    GROUP BY p.id

     

  3. Just as an extension to this.

    I am also wanting to count how many times each player has scored 4 runs.

    I thought the following sub qry might do it but alas it is giving me the same number for each player.

    SELECT p.first_name
    	, p.last_name
    	, p.id as playerid
    	, sum(runs_scored) as runs
    	, count(runs_scored) as balls
    	, (Select count(runs_scored) from deliveries where runs_scored = 4) as fours
    FROM game g
    	JOIN player p ON g.batting_first = p.team_id
    	JOIN deliveries d ON p.id = d.on_strike_batter_id
    					  AND g.id = d.game_id
    WHERE g.id = ?
    GROUP BY p.id

    would you be so kind as to point me in the right direction for extending your original sql (you will see some extensions that are working per player)

  4. 30 minutes ago, Barand said:

    Does this come close to what you were after?

    SELECT p.first_name
         , p.last_name
         , p.id as playerid
         , sum(runs_scored) as runs
    FROM game g
         JOIN player p ON g.batting_first = p.team_id
         JOIN deliveries d ON p.id = d.on_strike_batter_id
                           AND g.id = d.game_id
    WHERE g.id = 51
    GROUP BY p.id ;
    +------------+-----------+----------+------+
    | first_name | last_name | playerid | runs |
    +------------+-----------+----------+------+
    | Brian      | Lara      |       20 |   58 |
    | Freddie    | Flintoff  |       21 |   46 |
    +------------+-----------+----------+------+

     

    That is what I thought but what connects a squad to team?

    EDIT:

    I would have expected the squad to have a key (team_id, game_id) to show is was the team squad for that game.

    Absolutely perfect - as always, thank you very much!

  5. I am assuming that it is possible to have 100 or more players in a team.  But the game can only have 11 per side.  The squad is the 11 players that have been picked per team to play for that team in that game.

  6. apologies, i do have this.

     

    CREATE TABLE `team` (
      `id` int(11) NOT NULL,
      `name` varchar(100) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    --
    -- Dumping data for table `team`
    --
    
    INSERT INTO `team` (`id`, `name`) VALUES
    (1, 'Town CC 1XI'),
    (2, 'Other Test CC 1XI');
    
    --
    -- Indexes for dumped tables
    --
    
    --
    -- Indexes for table `team`
    --
    ALTER TABLE `team`
      ADD PRIMARY KEY (`id`);
    
    --
    -- AUTO_INCREMENT for dumped tables
    --
    
    --
    -- AUTO_INCREMENT for table `team`
    --
    ALTER TABLE `team`
      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
    COMMIT;
    
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

     

  7. Thanks for this Barand.

    CREATE TABLE `game_squad` (
      `id` int(11) NOT NULL,
      `game_id` int(11) NOT NULL,
      `player_id` int(11) NOT NULL,
      `wicket_keeper` tinyint(1) NOT NULL,
      `captain` tinyint(1) NOT NULL,
      `batting_number` tinyint(2) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    --
    -- Dumping data for table `game_squad`
    --
    
    INSERT INTO `game_squad` (`id`, `game_id`, `player_id`, `wicket_keeper`, `captain`, `batting_number`) VALUES
    (14, 51, 1, 0, 0, 0),
    (15, 51, 6, 0, 0, 0),
    (16, 51, 3, 0, 0, 0),
    (17, 51, 11, 0, 0, 0),
    (18, 51, 2, 0, 0, 0),
    (19, 51, 7, 0, 0, 0),
    (20, 51, 5, 0, 0, 0),
    (21, 51, 10, 0, 0, 0),
    (22, 51, 9, 0, 0, 0),
    (23, 51, 12, 0, 0, 0),
    (24, 51, 8, 0, 0, 0),
    (25, 51, 20, 0, 0, 0),
    (26, 51, 21, 0, 0, 0),
    (27, 51, 17, 0, 0, 0),
    (28, 51, 16, 0, 0, 0),
    (29, 51, 14, 0, 0, 0),
    (30, 51, 18, 0, 0, 0),
    (31, 51, 13, 0, 0, 0),
    (32, 51, 15, 0, 0, 0),
    (33, 51, 19, 0, 0, 0),
    (34, 51, 23, 0, 0, 0),
    (35, 51, 22, 0, 0, 0),
    (36, 52, 20, 0, 0, 0),
    (37, 52, 21, 0, 0, 0),
    (38, 52, 17, 0, 0, 0),
    (39, 53, 20, 0, 0, 0),
    (40, 53, 21, 0, 0, 0),
    (41, 53, 17, 0, 0, 0),
    (42, 56, 1, 0, 0, 0),
    (43, 56, 6, 0, 0, 0),
    (44, 56, 3, 0, 0, 0),
    (45, 56, 20, 0, 0, 0),
    (46, 56, 21, 0, 0, 0),
    (47, 56, 17, 0, 0, 0);
    
    --
    -- Indexes for dumped tables
    --
    
    --
    -- Indexes for table `game_squad`
    --
    ALTER TABLE `game_squad`
      ADD PRIMARY KEY (`id`);
    
    --
    -- AUTO_INCREMENT for dumped tables
    --
    
    --
    -- AUTO_INCREMENT for table `game_squad`
    --
    ALTER TABLE `game_squad`
      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=48;
    COMMIT;
    
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

     

    
    CREATE TABLE `game` (
      `id` int(11) NOT NULL,
      `home_team` int(11) NOT NULL,
      `away_team` int(11) NOT NULL,
      `date` date NOT NULL,
      `result_id` int(11) DEFAULT NULL,
      `venue` text NOT NULL,
      `toss_won_by` int(11) DEFAULT NULL,
      `batting_first` int(11) DEFAULT NULL,
      `bowling_first` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    --
    -- Dumping data for table `game`
    --
    
    INSERT INTO `game` (`id`, `home_team`, `away_team`, `date`, `result_id`, `venue`, `toss_won_by`, `batting_first`, `bowling_first`) VALUES
    (51, 1, 2, '2022-02-01', NULL, 'Avenue Park', 1, 2, 1),
    (55, 1, 2, '2022-11-06', NULL, 'TEST', 2, 1, 2),
    (56, 1, 2, '2022-11-06', NULL, 'adam', NULL, NULL, 0);
    
    --
    -- Indexes for dumped tables
    --
    
    --
    -- Indexes for table `game`
    --
    ALTER TABLE `game`
      ADD PRIMARY KEY (`id`);
    
    --
    -- AUTO_INCREMENT for dumped tables
    --
    
    --
    -- AUTO_INCREMENT for table `game`
    --
    ALTER TABLE `game`
      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=57;
    COMMIT;
    
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

     

     

    CREATE TABLE `player` (
      `id` int(11) NOT NULL,
      `team_id` int(11) NOT NULL,
      `first_name` varchar(100) NOT NULL,
      `last_name` varchar(100) NOT NULL,
      `player_type_id` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    --
    -- Dumping data for table `player`
    --
    
    INSERT INTO `player` (`id`, `team_id`, `first_name`, `last_name`, `player_type_id`) VALUES
    (1, 1, 'Adam', 'Hewitt', 4),
    (2, 1, 'Jack', 'Farmer', 4),
    (3, 1, 'Chuggy', 'Jones', 4),
    (4, 1, 'Sean', 'Baines', 3),
    (5, 1, 'James', 'Rist', 1),
    (6, 1, 'Brett', 'Lee', 1),
    (7, 1, 'James', 'Anderson', 1),
    (8, 1, 'Sean', 'Smith', 2),
    (9, 1, 'Ricky', 'Ponting', 2),
    (10, 1, 'Justin', 'Langer', 2),
    (11, 1, 'Ian', 'Bell', 2),
    (12, 1, 'Robert', 'Key', 2),
    (13, 2, 'John', 'Smith', 2),
    (14, 2, 'James ', 'Johnson', 3),
    (15, 2, 'Lawrence', 'Stroll', 4),
    (16, 2, 'Ian', 'Chapell', 2),
    (17, 2, 'Graham', 'Gooch', 2),
    (18, 2, 'Jaques', 'Kallis', 1),
    (19, 2, 'Maddison', 'Muttiah', 2),
    (20, 2, 'Brian', 'Lara', 1),
    (21, 2, 'Freddie', 'Flintoff', 4),
    (22, 2, 'Simon', 'Jones', 4),
    (23, 2, 'Richard', 'Bull', 4);
    
    --
    -- Indexes for dumped tables
    --
    
    --
    -- Indexes for table `player`
    --
    ALTER TABLE `player`
      ADD PRIMARY KEY (`id`);
    
    --
    -- AUTO_INCREMENT for dumped tables
    --
    
    --
    -- AUTO_INCREMENT for table `player`
    --
    ALTER TABLE `player`
      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;
    COMMIT;
    
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

     

    CREATE TABLE `deliveries` (
      `id` int(11) NOT NULL,
      `game_id` int(11) NOT NULL,
      `on_strike_batter_id` int(11) NOT NULL,
      `runs_scored` int(11) NOT NULL,
      `fair_delivery` tinyint(1) NOT NULL,
      `over_number` int(11) NOT NULL,
      `ball_in_over` int(11) NOT NULL,
      `over_called_this_ball` tinyint(1) NOT NULL,
      `wicket` int(11) DEFAULT 0
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    --
    -- Dumping data for table `deliveries`
    --
    
    INSERT INTO `deliveries` (`id`, `game_id`, `on_strike_batter_id`, `runs_scored`, `fair_delivery`, `over_number`, `ball_in_over`, `over_called_this_ball`, `wicket`) VALUES
    (126, 51, 21, 1, 1, 1, 1, 0, 0),
    (127, 51, 20, 1, 1, 1, 1, 0, 0),
    (128, 51, 21, 1, 1, 1, 1, 0, 0),
    (129, 51, 20, 2, 1, 1, 1, 0, 0),
    (130, 51, 20, 2, 1, 1, 1, 0, 0),
    (131, 51, 20, 2, 1, 1, 1, 0, 0),
    (132, 51, 20, 2, 1, 1, 1, 0, 0),
    (133, 51, 20, 3, 1, 1, 1, 0, 0),
    (134, 51, 21, 3, 1, 1, 1, 0, 0),
    (135, 51, 20, 1, 1, 1, 1, 0, 0),
    (136, 51, 21, 1, 1, 1, 1, 0, 0),
    (137, 51, 20, 3, 1, 1, 1, 0, 0),
    (138, 51, 21, 3, 1, 1, 1, 0, 0),
    (139, 51, 20, 6, 1, 1, 1, 0, 0),
    (140, 51, 20, 6, 1, 1, 1, 0, 0),
    (141, 51, 20, 1, 1, 1, 1, 0, 0),
    (142, 51, 21, 6, 1, 1, 1, 0, 0),
    (143, 51, 21, 6, 1, 1, 1, 0, 0),
    (144, 51, 21, 4, 1, 1, 1, 0, 0),
    (145, 51, 21, 4, 1, 1, 1, 0, 0),
    (146, 51, 21, 1, 1, 1, 1, 0, 0),
    (147, 51, 20, 4, 1, 1, 1, 0, 0),
    (148, 51, 20, 4, 1, 1, 1, 0, 0),
    (149, 51, 20, 4, 1, 1, 1, 0, 0),
    (150, 51, 20, 4, 1, 1, 1, 0, 0),
    (151, 51, 20, 4, 1, 1, 1, 0, 0),
    (152, 51, 20, 4, 1, 1, 1, 0, 0),
    (153, 51, 20, 4, 1, 1, 1, 0, 0),
    (154, 51, 20, 1, 1, 1, 1, 0, 0),
    (155, 51, 21, 4, 1, 1, 1, 0, 0),
    (156, 51, 21, 4, 1, 1, 1, 0, 0),
    (157, 51, 21, 4, 1, 1, 1, 0, 0),
    (158, 51, 21, 4, 1, 1, 1, 0, 0);
    
    --
    -- Indexes for dumped tables
    --
    
    --
    -- Indexes for table `deliveries`
    --
    ALTER TABLE `deliveries`
      ADD PRIMARY KEY (`id`);
    
    --
    -- AUTO_INCREMENT for dumped tables
    --
    
    --
    -- AUTO_INCREMENT for table `deliveries`
    --
    ALTER TABLE `deliveries`
      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=159;
    COMMIT;
    
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

    What i am wanting to get out of this as a structure is the following:

    First Name     Last Name     ID     SumOfRunsScored

     

    I will be adding more to this such as number of 4's scored, number of 6's scored, number of deliveries faced, strike rate etc but happy to do that work myself following your assistance.

    This is for a cricket scoring app if that gives you any greater insight.

    Let me know if this doesnt give you what you need.

     

    Thanks Again

  8. Hi All,

    I have a query that selects people from my DB

     

    SELECT player.first_name, player.last_name, player.id
    FROM game_squad
    	inner join player on game_squad.player_id = player.id
    	inner join game on 51 = game.id
    	where game_id = 51 and team_id = game.batting_first

    The ids that i get in this query, i need to use to do some sums on other data.

    I amended the suery to be

    SELECT player.first_name, player.last_name, player.id, (Select sum(deliveries.runs_scored) from deliveries where deliveries.on_strike_batter_id = 20) 
    FROM game_squad
    	inner join player on game_squad.player_id = player.id
    	inner join game on 51 = game.id
    	where game_id = 51 and team_id = game.batting_first

    the sub query value that is currently 20, needs to be the ID per row that has been selected from the first part of the query.

    Is this possible?

     

    If my explanation is not clear enough or more info required around table structure, i will be happy to provide.

  9. 17 hours ago, ginerjm said:

    If we are understanding you, you are saying that when the user creates a new record there will not be an id provided, since it is unknown until you add it to the database. 

    SO in that case, why are you looking for it in the post data when you know it may not there?  What does a good programmer do when he is not sure of something?  He CHECKS.  In your case, you need to do an 'isset' check on the id element and if it is not there you are probably supposed to assume that you need to do an add.  Of course we don't know anything about your app so perhaps you will assume something else but this is how you can avoid the error message and do something more appropriate.

    So if the user selects a client from a dropdown (named clientName) then there will be an id available, if the user selects add new client, the dropdown is replaced with an input named clientNameNew.  So one of the two will be posted but there is no way of knowing which it will be.

    But ultimately, as set up, i dont know if there will be a post value of clientNewName and need to do something different if it is posted from if an existing client is selected from the list.


    The reading that i have done suggests that isset is the way to go and shouldnt produce a warning, but i am still getting one:

    Quote

    This is from - https://www.php.net/manual/en/language.operators.comparison.php#language.operators.comparison.coalesce

    
    <?php
    // Example usage for: Null Coalesce Operator
    $action = $_POST['action'] ?? 'default';
    
    // The above is identical to this if/else statement
    if (isset($_POST['action'])) {
        $action = $_POST['action'];
    } else {
        $action = 'default';
    }
    ?>

    The expression (expr1) ?? (expr2) evaluates to expr2 if expr1 is null, and expr1 otherwise.

    In particular, this operator does not emit a notice or warning if the left-hand side value does not exist, just like isset(). This is especially useful on array keys.

     

     

  10. On 6/21/2022 at 6:43 PM, Barand said:

    or something like this to find the max quoteIds to be matched

    SELECT 
      mx.quoteId, 
      job_id as jobId, 
      job.name as jobName, 
      job.client_id as clientId, 
      client.name as clientName, 
      quote.currency, 
      quote.version 
    from job
         inner join client on client.id = job.client_id
         inner join (
                        select job_id
                             , max(id) as quoteId
                        from quote
                        where quote.closed != '1'
                        group by job_id
                    ) mx using (job_id)
         inner join quote on mx.quoteId = quote.id
    group by job_id

     

    ok this is interesting.

    On first try i am getting "Unknown column 'job_id' in 'from clause'" but there isnt a job_id in the from clause?

  11. I have a from that posts when i click submit, one of the inputs is created if you create a new client rather than picking one from a select.

    If creating a new client, an id will not be passed in and therefore will be undefined.

    I thought i had handled it with the following but i am getting a notice of undefined index on "clientNameNew"

    	//if createing a new client name
    	if ($_POST['clientNameNew']) {
    		$clientName = $_POST['clientNameNew'];
    		$clientId = createNewClient($pdo, $clientName);
    	} else {
    		$clientId = $_POST['clientName'];
    	}

    Is there a better way of handling this than the way i have above?

  12. I have a php function that is called via ajax:

    function getItemAttributesById($itemId){
    	require 'includes/dbconn.php';
    	$sql = "SELECT id, name, type, price_uk, price_us, price_ca, price_au, price_eu, billing_freq from item where id = :itemId";
    	$stmt = $pdo->prepare($sql);
    	$bindData = [
    		'itemId' => $itemId
    	];
    	$stmt->execute($bindData);
    	$item = $stmt->fetch(PDO::FETCH_ASSOC);
    	echo json_encode($item);
    }

    when i echo the $item at the end of the function and console.log the response in ajax, i can see the array.

    When i use the return keyword instead of echo i just see the word "Array".  From my understanding of what they do, i wouldnt have thought there would have been a difference in what got back to the ajax function.  Am i using it wrong?

  13. 37 minutes ago, mac_gyver said:

    yes. just name it $item, because that is what it is in the code that's calling that function. then reference elements in item,  e.g. $item['quantity'] inside the function code.

    this actually would have prevented the initial error at the top of this thread, because there would be no need to match up all the  different parameters. it will also allow you to add more values, such as the lineNotes, simply by selecting that data where you are querying for it, then using that data inside the function. you won't have to edit the list of call-time parameters every time you change the data being passed.

    great advice, thank you for this.

  14. 18 hours ago, mac_gyver said:

    an additional point about the function implementation. the purpose of the markup being built inside of getItemsForQuote is to edit the existing item row values. to do so, you need to supply all those initial existing values to the code, which actually changes to become the submitted form data, when you redisplay the form upon a validation error. do NOT add a discrete call-time parameter for each of these values. simply pass an array of values as one call-time parameter. when you initially produce the form, the array of data would be that which you query for and fetch from the database. when you redisplay the form upon a validation error, the array of data would be the submitted form data.

    for this do you mean that

    function getItemsForQuote($itemType, $currency, $qty = null, $itemId = null, $startDate = null, $endDate = null,$priceQuoted = null,  $notes = null)

    should be replaced with one array containing all of this data rather than lots of individual elements?

  15. 3 hours ago, mac_gyver said:

    another issue with the posted code.

    when you are building the 'selected' option choices, in both the quantity and the item menus, you are outputting the option with the selected attribute when the existing value is the same as the value being output AND you are repeating the same option choice without the selected attribute. e.g. if the selected quantity is 3, you are outputting an option choice for 3 that is selected, followed by another option choice for 3 that is not selected.

    i doubt this is the intended result. the only conditional logic should be to determine what the selected attribute should be, the option markup should only be output once per value, that is either selected or not. note: you can simply put the 'selected' attribute in or leave it out. you don't have to put it in AND set it to a (true, i.e. any non-empty string) value to cause the option to be selected.

    if by some chance this is the intended result, in the case of the item select/option menu, the data-... attribute(s) are used in the lineTotal/line-total calculation, so they must be output in every option choice. the current code is not outputting them in the 'selected' option choice, which will prevent the calculation code from producing the total.

    All good points here - you are correct and i have ammended my code.

  16. 33 minutes ago, mac_gyver said:

    this code is killing your database server with connections. a database connection is one of the slowest, resource consuming operations you can do. your main application code should make one database connection. you would then pass it as a call-time parameter to any function that needs it.

    the getItemsForQuote function has the wrong responsibility. it is mixing the database specific code with the presentation code,  it should only get the data, like its name indicates and my not actually be needed to get the data one item at a time. see the next point.

    this code is executing a select query within a loop (getting all the items for the item_type of the current item), which is also inefficient. even if there's only one item per item_type in a quote, and even worse if there is more than one, you should get all this data in one query, index it by the item_type when you fetch it, then access it when building each itemSelected select/option menu.

    I think i need to read this a few more times and see if i can make that work.  I may be back for this comment.  Thanks for taking the time, it is appreciated.

  17. 1 hour ago, maxxd said:

    I'm pretty sure I don't understand the issue you're having, but right off the top I see this. This is your function definition:

    function getItemsForQuote($itemType, $currency, $qty = null, $startDate = null, $endDate = null, $itemId = null, $notes = null)

    and this is what you're calling:

    getItemsForQuote($item['itemType'], $item['quoteCurrency'], $item['quantity'], $item['itemId']);

    You're passing the itemId as the startDate parameter.

    Thank you, this was the answer! 🥱

  18. Hi All,

    I think this may be a bit of a slog to get to the answer here as there are a fair few function calls and quite long functions.

    I have a function that is called by ajax to create some form items.  This works fine. The form can be filled out and all of the data goes to the DB.

    Now, when wanting to recreate the form, filled with the data, so that users can edit i have part of the function that should be setting the selected item in an option list that is outputting the value where no match has been found rather than the value when a match has been found.

    here is the big function

    function getItemsForQuote($itemType, $currency, $qty = null, $startDate = null, $endDate = null, $itemId = null, $notes = null)
    {
    	require 'includes/dbconn.php';
    
    
    
    	$sql = "SELECT id, name, price_uk, price_us, price_ca, price_au, price_eu, billing_freq from item where type = :item_type";
    	$stmt = $pdo->prepare($sql);
    	$stmt->bindParam(':item_type', $itemType);
    	$stmt->execute();
    	$items = $stmt->fetchAll(PDO::FETCH_ASSOC);
    	if ($items) {
    		$qtyArray = array(
    			1 => '1',
    			2 => '2',
    			3 => '3',
    			4 => '4',
    			5 => '5',
    			6 => '6',
    			7 => '7',
    			8 => '8',
    			9 => '9',
    			10 => '10',
    			11 => '11',
    			12 => '12',
    			13 => '13',
    			14 => '14',
    			15 => '15',
    			16 => '16',
    			17 => '17',
    			18 => '18',
    			19 => '19',
    			20 => '20'
    		);
    
    		$out = "<div class='row mb-3 completeLine'><div class='col-1'>
    					<select name='itemQty[]' class='itemQty form-select'>";
    		foreach ($qtyArray as $key => $value) {
    			if ($qty == $key) {
    				$selected = 'selected';
    				$out .= "<option selected='{$selected}' value='{$key}'>{$value}</option>";
    			}
    			$out .= "<option value='{$key}'>{$value}</option>";
    		}
    		$out .= "</select>
    				</div>
    				<div class='col-4'>
    						<select name='itemSelected[]' class='itemSelected form-select'><option selected disabled>Please Select</option>";
    						
    		foreach ($items as $item) {
    			if ($itemId == $item['id']) {
    				$selected = 'selected';
    				$out .= "<option selected='{$selected}' value='{$item['id']}'>{$item['name']}</option>";
    			}
    			$out .= "<option data-billingfreq='{$item['billing_freq']}' data-priceuk='{$item['price_uk']}' data-priceus='{$item['price_us']}' data-priceca='{$item['price_ca']}' data-priceau='{$item['price_au']}' data-priceeu='{$item['price_eu']}'  value='{$item['id']}'>{$item['name']}</option>";
    		}
    		$out .= "</select>
    				</div>
    					<div class='col-2'>
    							<input name='fromDate[]' type='date' class='form-control from-date'>
    						</div>
    						<div class='col-2'>
    							<input name='toDate[]' type='date' class='form-control to-date'>
    						</div>
    						<div class='col-2'>
    							<input name='lineTotal[]' type='text' disbaled placeholder='Total' class='line-total form-control'>
    						</div>
    						<div class='col-1'>
    							<div class='btn btn-primary w-100'>Opt</div>
    						</div>
    						<div class='col-7 mt-3'>
    							<div class='input-group mb-3'>
    								<span class='input-group-text'>Notes</span>
    								<input name='lineNotes[]' type='text' class='line-notes form-control' placeholder='Add notes for line item'>
    							</div>
    						</div>
    						<div class='col-3 mt-3'>
    							<div class='input-group mb-3'>
    								<span class='input-group-text pricePerWeek'>PPW</span>
    								<input type='text' class='pricePerWeek form-control' placeholder='PPW'>
    							</div>
    						</div>
    						<div class='col-2 mt-3'>
    							<div class='input-group mb-3'>
    								<span class='input-group-text noOfWeeks'>Weeks</span>
    								<input type='text' class='noOfWeeks form-control' placeholder='Weeks'>
    							</div>
    						</div>
    					</div>
    				</div>
    				";
    	}
    	return $out;
    }

    The section that is not working correctly is

    foreach ($items as $item) {
    			if ($itemId == $item['id']) {
    				$selected = 'selected';
    				$out .= "<option selected='{$selected}' value='{$item['id']}'>{$item['name']}</option>";
    			}
    			$out .= "<option data-billingfreq='{$item['billing_freq']}' data-priceuk='{$item['price_uk']}' data-priceus='{$item['price_us']}' data-priceca='{$item['price_ca']}' data-priceau='{$item['price_au']}' data-priceeu='{$item['price_eu']}'  value='{$item['id']}'>{$item['name']}</option>";
    		}

    itemId is being passed into this function correctly i believe which has been checked with the function that calls this one below.

    The $itemId which is passed in = 2 and the $item['id'] that is being pulled in from the database in this query should match.

     

    The above function is called from the following function

    function rebuildQuote($quoteId, $version)
    {
    	include 'includes/dbconn.php';
    	$sql = "SELECT item_id as itemId,quote.currency as quoteCurrency, item.type as itemType, quantity, from_date, to_date, price_quoted from quote_items
    			inner join item on item.id = quote_items.item_id
    			inner join quote on quote.id = quote_items.quote_id
    			where quote_id = :quoteId and quote_items.version = :version";
    	$stmt = $pdo->prepare($sql);
    	$bindData = [
    		'quoteId' => $quoteId,
    		'version' => $version
    	];
    	$stmt->execute($bindData);
    	$items = $stmt->fetchAll(PDO::FETCH_ASSOC);
    	$out = "";
    	if ($items) {
    		foreach ($items as $item) {
    			$out .= getItemsForQuote($item['itemType'], $item['quoteCurrency'], $item['quantity'], $item['itemId']);
    			//  $out .= $item['itemId']."and";
    		}
    	} else {
    		$out .= "no items";
    	}
    
    	return $out;
    }

    You will see that i have been changing the $out at the end to check what variables are being sent into the function call.

    I have been going around the houses on this but any pointers in the right direction would be appreicted.  I am hoping that it is a "cant see the woods for the trees" issue.

     

     

     

  19. 1 minute ago, Adamhumbug said:

    i updated to this with no change

    function rebuildQuote($quoteId, $version){
    	include 'includes/dbconn.php';
    	$sql = "SELECT item_id as itemid, quantity, from_date, to_date, price_quoted from quote_items
    			where quote_id = :quoteId and version = :version";
    	$stmt = $pdo->prepare($sql);
    	$bindData = [
    		'quoteId' => $quoteId,
    		'version' => 1
    	];
    	$stmt->execute($bindData);
    	$items = $stmt->fetchAll(PDO::FETCH_ASSOC);
    	$out = "";
    	if($items){
    		foreach($items as $item){
    			$out .= "this";
    		}
    	}else{
    		$out .= "no items";
    	}
    
    	return $out.$version;
    	
    }

     

    Actually, that did seem to do the trick - i had been playing with the version definition in the $bindData - when i set that back to $version it worked.

    Thanks for that, i had a feeling it would be something small.

    Do you know why that has been causing the issue, i have tons of other queries with quotes around them that seem to work ok?

  20. 6 minutes ago, ginerjm said:

    Try dropping the quotes on the args in the query.  The PDO prepare will correct what is necessary

    i updated to this with no change

    function rebuildQuote($quoteId, $version){
    	include 'includes/dbconn.php';
    	$sql = "SELECT item_id as itemid, quantity, from_date, to_date, price_quoted from quote_items
    			where quote_id = :quoteId and version = :version";
    	$stmt = $pdo->prepare($sql);
    	$bindData = [
    		'quoteId' => $quoteId,
    		'version' => 1
    	];
    	$stmt->execute($bindData);
    	$items = $stmt->fetchAll(PDO::FETCH_ASSOC);
    	$out = "";
    	if($items){
    		foreach($items as $item){
    			$out .= "this";
    		}
    	}else{
    		$out .= "no items";
    	}
    
    	return $out.$version;
    	
    }

     

  21. i have the following function

    function rebuildQuote($quoteId, $version){
    	include 'includes/dbconn.php';
    	$sql = "SELECT item_id as itemid, quantity, from_date, to_date, price_quoted from quote_items
    			where quote_id = ':quoteId' and version = ':version'";
    	$stmt = $pdo->prepare($sql);
    	$bindData = [
    		'quoteId' => $quoteId,
    		'version' => 1
    	];
    	$stmt->execute($bindData);
    	$items = $stmt->fetchAll(PDO::FETCH_ASSOC);
    	$out = "";
    	if($items){
    		foreach($items as $item){
    			$out .= "this";
    		}
    	}else{
    		$out .= "no items";
    	}
    
    	return $out;
    	
    }

    when i return $quoteId and $version i can see 19,4 so i know that vars are coming into this function.

    When i run the SQL manually in the db i get 2 rows of data.

    64057530_Screenshot2022-06-21at17_04_22.thumb.png.516f987f6ce42c7657a34510cca25b9e.png

    When i run the function as is i get "no items"

    I cant see what i am doing wrong here.

  22. I have the below query:

    SELECT 
      max(quote.id) as quoteId, 
      job_id as jobId, 
      job.name as jobName, 
      job.client_id as clientId, 
      client.name as clientName, 
      currency, 
    	version 
    from quote
    inner join job on job.id = quote.job_id
    inner join client on client.id = job.client_id
    where quote.closed != '1'
    group by job_id

    i have attached an image of the data structure

    2105224186_Screenshot2022-06-21at16_44_10.png.7d3a1ab42afe9f1c3bc712a860732385.png

    when i run the qry i am getting

    quoteId	jobId	jobName	clientId	clientName	currency	version 	
    13 	21 	Test Job 	12 	Test Client 	GBP 	1
    14 	22 	JOB JOB 	3 	Testing LTD 	USD 	1
    19 	24 	Adams Job 	13 	Adams Co 	GBP 	1

    but i would be expecting the version numbers of the quote ids selected to be 8, 1 and 4.

    Could anyone point me at what i am doing wrong with my sql?

     

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