Jump to content

Barand

Moderators
  • Posts

    24,612
  • Joined

  • Last visited

  • Days Won

    834

Everything posted by Barand

  1. The results for questions 2, 3 and 4 can be got from a single query SELECT m.gender, COUNT(*) as total FROM teams_info ti LEFT JOIN team_players tp ON ti.team_id = tp.team_id LEFT JOIN members m ON tp.members_id = m.members_id WHERE ti.entry_year = 2015 GROUP BY gender WITH ROLLUP ; +--------+-------+ | gender | total | +--------+-------+ | female | 3 | | male | 4 | | NULL | 7 | <--- ROLLUP gives the total of the subtotals +--------+-------+ 3 rows in set (0.00 sec)
  2. When you first generate the page, populate the first menu. Then use the onchange event to generate an ajax call, passing the selected state. The script on the server will then query the database for counties in that state and pass them back as an array. Use that array to populate the second menu.
  3. try SELECT COUNT(*) as accepted FROM team_players tp JOIN teams_info ti USING (team_id) WHERE ti.entry_year = 2015 AND tp.status = 1; +----------+ | accepted | +----------+ | 4 | +----------+ 1 row in set (0.00 sec)
  4. First, look at the PRIMARY KEY/FOREIGN KEY relationships +------------+ +------------+ | teams_info | | members | +------------+ +------------+ | team_id |------+ +-------| members_id | | team_name | | | | first_name | | entry_year | | | | surname | | status | | | | gender | +------------+ | | +------------+ | | | | | +--------------+ | | | team_players | | | +--------------+ | | | player_id | | +----<| team_id | | | status | | | members_id |>-----+ | position | +--------------+ Those will be the JOINS in your queries, for example FROM teams_info JOIN team_players USING (team_id) So start by looking at the fields you need in your result and which tables you need to get those fields. SELECT fields, needed FROM tables needed Then look at the conditions imposed and put those in a WHERE clause SELECT fields, needed FROM tables needed WHERE conditions If you can get it to list the correct records, you are on your way. You can then worry about how to count them.
  5. Why are there two $conn = xxx ? (Line 4 and line 71) Do you need to connect to two servers in the same script?
  6. That's the theory. Whatever the current month is, you will get the total for that month.
  7. Of course you don't (unless you want "February" output every time). That is what is known as "an example". Strangely, the writers of the MySQL manual did not know that your date field would be "exrdate", so you need to substitute that yourself. SELECT MONTHNAME(exrdate) as month, .... then you would output it with " $row['month'] "
  8. Yes, SQL has a MONTHNAME() function http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html
  9. ... WHERE MONTH(exrdate) = MONTH(CURDATE()) AND YEAR(exrdate) = YEAR(CURDATE())
  10. exrdate is not a function. SELECT SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE exrdate >= CURDATE() - INTERVAL 1 MONTH
  11. You need two separate SUM()s SELECT SUM(job_cost) as job_cost, SUM(profit) as profit .....
  12. That will only get those who were born today, not whose birthday falls today. SELECT username , pdob , YEAR(CURDATE())-YEAR(pdob) as age FROM table WHERE MONTH(pdob)=MONTH(CURDATE()) AND DAY(pdob)=DAY(CURDATE())
  13. then $beneficiary = 123; // or whatever $bank_data = array ( array($branchId1 , $acc1), array($branchId2 , $acc2) ); $sql = "INSERT INTO user_bank (beneficiary_id, branch_id, account VALUES (?,?,?))"; $stmt = $db->prepare($sql); $stmt->bind_param('ii', $benficiary,$branch, $acc); foreach ($bank_data as $bdata) { list($branch, $acc) = $bdata; $stmt->execute(); }
  14. If your bank_branch table stores the bank_id then you don't need it in user_bank table.
  15. When using prepared statements in a situation like this you define query, prepare and bind parameters once only. After doing that, loop through the data values to set the parameters and execute. $beneficiary = 123; // or whatever $bank_data = array ( $branchId1 , $branchId2 ); $sql = "INSERT INTO user_bank (beneficiary_id, branch_id VALUES (?,?))"; $stmt = $db->prepare($sql); $stmt->bind_param('ii', $benficiary, $branch); foreach ($bank_data as $branch) { $stmt->execute(); }
  16. I did not say that would work. Read my reply again. I said "if you restructure your data and use arrays then you could reduce it to something like that". It was an example of what might be possible, not code for you to blindly copy/paste into your existing code.
  17. As soon as you see something like "position1, position2, position3,...,positionN" or "seat1, seat2,..., seatN" then that is a clue that something is wrong. You should be using arrays. That mass of if() statements could then be reduced to something like foreach ($seatnumbers->results()as $seatresults) { $seats=$seatresults->seat[$q]; }
  18. SELECT IFNULL(paid_status, 'Some Text') as paid_status FROM depanarecuora_clients
  19. I am having difficulty seeing any connection between your query and the question
  20. So, is it working? You need to tell us something.
  21. Or, as in this case, on the actual line number stated in the message
  22. No. If you do that then the form will not be displayed. It should extend to the end of that PHP code that should be processed only if data is posted.
  23. You check if $_POST{'submit'] is set but that that check ends at line 51. All the code that is dependent on it being set needs to be inside the {..}
  24. I would recommend using a datepicker component in your form, if you aren't already. Expecting all users to enter consistent date formats will lead to disappointment, also you get confusion with text field dates. Is 06-07-2015 in d-m-Y format or is it m-d-Y? With a datepicker you can display (and post) the same format every time. I usually set my datepickers to display 07-Jun-2015 format which is universally understood by users and is unambiguous. What's more it unambiguous and understood by the PHP strtotime() function and DateTime class. $d = new DateTime('07-Jun-2015'); echo $d->format('Y-m-d'); // 2015-06-07 echo date('Y-m-d', strtotime('07-Jun-2015')); // 2015-06-07
  25. You are having problems yet you comment out those lines. Unbelievable!.
×
×
  • 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.