Jump to content

Barand

Moderators
  • Posts

    23,497
  • Joined

  • Last visited

  • Days Won

    699

Barand last won the day on January 26

Barand had the most liked content!

About Barand

Profile Information

Recent Profile Visitors

85,484 profile views

Barand's Achievements

Prolific Member

Prolific Member (5/5)

1.9k

Reputation

368

Community Answers

  1. Is the database on same computer as your script?
  2. If the username is correct then is your password valid for the usename?
  3. Check the code where you connect to the database server. You appear to be using the database name as the user name - is that correct?
  4. Have you considered using ifnull() on that too? SELECT ..., ifnull(b.qty_bot, 0) as qty_bot, ... Or an aternative to isnull() is coalesce() SELECT ..., coalesce(b.qty_bot, 0) as qty_bot, ...
  5. When you run this script, are you putting a value for "code" in the query string? EG http://localhost/myscript.php?code=123 Check whether code exists before trying to access it if (isset($_GET['code']) { $accessToken = $authProvider->getAccessToken('authorization_code', [ 'code' => $_GET['code'] ]); } else { // error - no code }
  6. You are referencing aliases before they have been defined. For example, you dont define the table alias "b" until the last line of the query but you are referencing it several time earlier subqueries in the query. Why output identical values twice? ... SELECT a.prod_name , a.prod_size , ifnull(a.qty_received, 0) qty_received , ifnull(b.qty_bot, 0) qty_bot , ifnull(a.qty_received, 0) - ifnull(b.qty_bot, 0) qty_remain FROM ( SELECT prod_id FROM tbl_distribution UNION SELECT prod_id FROM tbl_sales_bar ) t LEFT JOIN ( SELECT prod_id, prod_name, prod_size, SUM(prod_qty) qty_received FROM tbl_distribution WHERE staff_id = 2962 GROUP BY prod_id ) a on a.prod_id = t.prod_id LEFT JOIN ( SELECT prod_id, SUM(qty_bought) qty_bot FROM tbl_sales_bar WHERE staff_id = 2962 GROUP BY prod_id ) b on b.prod_id = t.prod_id; results +---------------+-----------+--------------+---------+------------+ | prod_name | prod_size | qty_received | qty_bot | qty_remain | +---------------+-----------+--------------+---------+------------+ | 33 | | 13 | 8 | 5 | | Star Wrangler | | 7 | 6 | 1 | | Star | | 19 | 7 | 12 | | Goldberg | | 10 | 0 | 10 | +---------------+-----------+--------------+---------+------------+
  7. OK, post it and I'll have look.
  8. If you have a different problem, post it in a new topic.
  9. Your query joins each record from 'a' to many records from 'b' thus multiplying your totals. Try SELECT a.prod_id, a.prod_name , a.qty_received , b.qty_bot , a.qty_received - b.qty_bot as qty_remain FROM ( SELECT prod_id , prod_name , sum(prod_qty) as qty_received FROM tbl_distribution WHERE staff_id = 2962 GROUP BY prod_id ) a LEFT JOIN ( SELECT prod_id , sum(qty_bought) as qty_bot FROM tbl_sales_bar WHERE staff_id = 2962 GROUP BY prod_id ) b USING (prod_id); Your tables are in dire need of normalization. For example, "prod_name" should occur once in your database (in a product table) and not be repeated in several tables. PS In future, don't post pictures of your data, post the data. It was not a great deal of fun building the test tables from those images so that I could test your query.
  10. What do get if you run the subquery on its own? SELECT GROUP_CONCAT(DISTINCT Average ORDER BY Average DESC ) FROM mark; I suspect that your "average" column is varchar instead of a numeric type. (in my table it's DECIMAL(8,2) so that it sorts correctly.
  11. The problem is his posting the text parts of the posts in the code boxes so that no line wrapping occurs. And yes, a PIA. (I usually skip posts that require me to scroll right into the middle of next week in order to read them.)
  12. I get results 1 and 3, which look right. DATA +---------+----------+--------+--------+------+---------+ | mark_id | emp_name | emp_no | branch | year | average | +---------+----------+--------+--------+------+---------+ | 1 | Curly | 1 | A | 2023 | 9.00 | | 2 | Larry | 2 | B | 2023 | 75.00 | | 3 | Mo | 3 | C | 2023 | 66.67 | +---------+----------+--------+--------+------+---------+ BPOSITION SELECT Average , Emp_Name , FIND_IN_SET( Average, ( SELECT GROUP_CONCAT(DISTINCT Average ORDER BY Average DESC ) FROM mark WHERE Branch = 'A' )) AS rank FROM mark WHERE Emp_Name = 'Curly' AND Year = '2023' ORDER BY Average DESC; +---------+----------+------+ | Average | Emp_Name | rank | +---------+----------+------+ | 9.00 | Curly | 1 | +---------+----------+------+ NPOSITION SELECT Average , Emp_Name , FIND_IN_SET( Average, ( SELECT GROUP_CONCAT(DISTINCT Average ORDER BY Average DESC ) FROM mark )) AS rank FROM mark WHERE Branch = 'A' AND Emp_Name = 'Curly' AND Year = '2023' ORDER BY Average DESC; +---------+----------+------+ | Average | Emp_Name | rank | +---------+----------+------+ | 9.00 | Curly | 3 | +---------+----------+------+ PS I don't think you should be using DISTINCT in your GROUP_CONCAT. If the 3 values were 9.00, 75.00, 75.00 then, with your DISTINCT it would be ranked 2 whereas it should still be 3.
  13. Use your browser's developer tools network tab to check what is being sent and received by the ajax request
  14. For $_POST['submit'] to exist there must be an input form element with the name 'submit'. A better way to check is if ($_SERVER['REQUEST_METHOD'] == 'POST') { // data was posted - process it }
  15. You're making it look too easy
×
×
  • 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.