-
Posts
24,612 -
Joined
-
Last visited
-
Days Won
834
Everything posted by Barand
-
Getting statistics from 3 different tables using foreign keys
Barand replied to blmg2009's topic in MySQL Help
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) -
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.
-
Getting statistics from 3 different tables using foreign keys
Barand replied to blmg2009's topic in MySQL Help
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) -
Getting statistics from 3 different tables using foreign keys
Barand replied to blmg2009's topic in MySQL Help
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. -
Why are there two $conn = xxx ? (Line 4 and line 71) Do you need to connect to two servers in the same script?
-
That's the theory. Whatever the current month is, you will get the total for that month.
-
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'] "
-
Yes, SQL has a MONTHNAME() function http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html
-
... WHERE MONTH(exrdate) = MONTH(CURDATE()) AND YEAR(exrdate) = YEAR(CURDATE())
-
exrdate is not a function. SELECT SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE exrdate >= CURDATE() - INTERVAL 1 MONTH
-
You need two separate SUM()s SELECT SUM(job_cost) as job_cost, SUM(profit) as profit .....
-
Retrieving Birthday from database MySQL php problem
Barand replied to cobusbo's topic in PHP Coding Help
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()) -
Insert multiple rows in mysqli prepared statement
Barand replied to thara's topic in PHP Coding Help
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(); } -
Insert multiple rows in mysqli prepared statement
Barand replied to thara's topic in PHP Coding Help
If your bank_branch table stores the bank_id then you don't need it in user_bank table. -
Insert multiple rows in mysqli prepared statement
Barand replied to thara's topic in PHP Coding Help
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(); } -
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.
-
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]; }
-
So, is it working? You need to tell us something.
-
Or, as in this case, on the actual line number stated in the message
-
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.
-
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 {..}
-
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
-
You are having problems yet you comment out those lines. Unbelievable!.