Barand
-
Posts
24,344 -
Joined
-
Last visited
-
Days Won
795
Community Answers
-
Barand's post in delete all but latest 6 rows was marked as the answer
try something like this
DELETE benchmarks FROM benchmarks LEFT JOIN ( SELECT id FROM benchmarks ORDER BY id desc LIMIT 6 ) lastsix USING (id) WHERE lastsix.id IS NULL; -
Barand's post in Help with sorting was marked as the answer
If you have the data in a database, sort it when you retrieve the date using an ORDER BY clause.
Otherwise, don't use variables like $JOBDISPLAY10, $JOBDISPLAY11, use an array and sort the array.
EG
<?php $jobdisplay = [ 10 => [ 'title' => 'Job title 10', 'company' => 'Company name 10', 'country' => 'Somewhere', 'start' => '2016-05-01', 'finish' => 'present', 'duties' => 'description of duties ...' ], 11 => [ 'title' => 'Job title 11', 'company' => 'Company name 11', 'country' => 'Somewhere else', 'start' => '2014-01-01', 'finish' => '2014-12-31', 'duties' => 'another description of duties ...' ], 12 => [ 'title' => 'Job title 12', 'company' => 'Company name 12', 'country' => 'Somewhere', 'start' => '2016-04-01', 'finish' => 'present', 'duties' => 'yet another description of duties ...' ], 13 => [ 'title' => 'Job title 13', 'company' => 'Company name 13', 'country' => 'Somewhere other', 'start' => '2015-01-01', 'finish' => '2016-03-31', 'duties' => 'and yet another description of duties ...' ] ]; function customSort($a, $b) { $finA = $a['finish']=='present' ? date('Y-m-d') : $a['finish']; $finB = $b['finish']=='present' ? date('Y-m-d') : $b['finish']; $x = strcmp($finB, $finA); // if same finish dates, sort by start date if ($x==0) { return strcmp($b['start'], $a['start']); } return $x; } usort($jobdisplay, 'customSort'); echo '<pre>'; foreach ($jobdisplay as $j) { printf('%-20s | %-15s | %-15s<br>', $j['title'], $j['start'], $j['finish']); } echo '</pre>'; ? Gives
Job title 10 | 2016-05-01 | present Job title 12 | 2016-04-01 | present Job title 13 | 2015-01-01 | 2016-03-31 Job title 11 | 2014-01-01 | 2014-12-31 -
Barand's post in Variable sent in form was marked as the answer
Use a hidden field in the form for the goalie value
form.php
<?php $goalie = 'someCalculatedValue'; // calculate the goalie value ?> <html> <body> <form method="post" action="penalty.php"> <input type="hidden" name="goalie1" value="<?=$goalie?>"> <!-- store the goalie value in a hidden field --> <input type="checkbox" name="angle" value="1"> <input type="submit" name="btnSubmit" value="Submit"> </form> </body> </html> penalty.php
<?php $goalie = $_POST['goalie']; $angle = $_POST['angle'] ... ?> -
Barand's post in Table structure based on row count? was marked as the answer
You will need to process the query results differently if you are not using PDO. Try
<?php $sqlsn = "SELECT system_name FROM tbl_sub_systems WHERE section=\"21a\""; $rssn = mssql_query( $sqlsn, $conn) or die ("Cannot execute"); // // read data into an array first // $rows = []; while ($row = mssql_fetch_row($rssn)) { $rows[] = $row[0]; } // // now chunk this array // $data = array_chunk($rows,2); echo "<table border=\"1\">"; foreach ($data as $row) { echo "<tr>"; if (count($row)==2) { foreach ($row as $n) echo "<td>".$n."</td>"; } else { echo "<td colspan=\"2\">".$row[0]."</td>"; } echo "</tr>"; } echo "</table>"; ?> -
Barand's post in Group By Year, Name, and Count from Two Fields on Same Row was marked as the answer
You need to use a UNION
SELECT Year , Team , COUNT(*) as Played FROM ( SELECT Year, Away_Team as Team FROM table UNION SELECT Year, Home_Team as Team FROM table ) teams GROUP BY Year, Team -
Barand's post in how to fetch images from images table and display along with topic? was marked as the answer
SELECT topic_name , image_name FROM topic t LEFT JOIN image i ON i.tid = t.id I used LEFT join in case some topics have no image. If all topics have an image, use INNER JOIN, it's more efficient.
-
Barand's post in Diffrence in days in two dates was marked as the answer
You need to use the DATEDIFF() function
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_datediff
but the best way to do it is not to do it at all.
Derived data should not be stored in your database. Calculate it when required instead of continually updating the table.
-
Barand's post in how would i query these tables for availables for room reservation was marked as the answer
Should be. These are my reservations
+----+---------+------------+------------+------+---------------------+ | id | room_id | checkin | checkout | ip | date | +----+---------+------------+------------+------+---------------------+ | 1 | 6 | 2016-03-01 | 2016-03-05 | NULL | 2016-03-10 18:08:16 | | 2 | 2 | 2016-03-02 | 2016-03-06 | NULL | 2016-03-08 15:12:34 | | 3 | 4 | 2016-03-03 | 2016-03-07 | NULL | 2016-03-10 18:08:16 | <-booked | 4 | 3 | 2016-03-06 | 2016-03-07 | NULL | 2016-03-10 18:08:16 | <-booked | 5 | 5 | 2016-03-07 | 2016-03-08 | NULL | 2016-03-08 16:07:57 | <-booked | 6 | 1 | 2016-03-08 | 2016-03-10 | NULL | 2016-03-10 18:08:16 | | 7 | 7 | 2016-03-08 | 2016-03-09 | NULL | 2016-03-08 15:12:34 | | 8 | 8 | 2016-03-09 | 2019-03-10 | NULL | 2016-03-08 15:12:34 | +----+---------+------------+------------+------+---------------------+ If I want to check in on the 6th and out on the 8th then the rooms indicated are already booked (rooms 3, 4 and 5)
SELECT * FROM rooms WHERE room_id NOT IN ( SELECT room_id FROM reservations WHERE checkin < '2016-03-08' AND checkout > '2016-03-06' ); +---------+-----------+-------------+---------------+-------------+ | room_id | room_name | room_number | room_capacity | room_status | +---------+-----------+-------------+---------------+-------------+ | 1 | Deluxe | 101 | 2 | 1 | | 2 | Standard | 102 | 2 | 2 | rooms 3,4,5 | 6 | Executive | 203 | 1 | 1 | not available | 7 | Executive | 301 | 2 | 2 | | 8 | Standard | 302 | 2 | 3 | | 9 | Executive | 303 | 1 | 3 | | 10 | Suite | 401 | 6 | 5 | | 11 | Suite | 501 | 4 | 2 | +---------+-----------+-------------+---------------+-------------+ -
Barand's post in CONCAT was marked as the answer
You would put it in the SELECT clause. Also note you cannot use aliases in WHERE clauses
SELECT groups.grid , groups.code1 , products.codeDX , products.CodeSX , products.prodid , products.groupid , CONCAT (code1, ".", codeDX) AS op1 , CONCAT (code1, ".", codeSX) AS op2 FROM groups LEFT JOIN products ON groups.grid = products.groupid WHERE (CONCAT(code1, ".", codeDX) LIKE '%$trimm%' OR CONCAT(code1, ".", codeSX) LIKE '%$trimm%' ) ORDER BY groups.grid -
Barand's post in Graphing number of people from two times was marked as the answer
Set up the array structure that you need for your chart data then process your input, accumulating the data for each hour
// // INPUT DATA // $input = [ ['0900', '2000', 1, 5], ['1000', '2200', 1, 3] ]; // // PREPARE ARRAYS TO STORE CHART DATA // $init = array_fill_keys(range(0,23), 0); $data = [ 0 => ['name'=>'Supervisors', 'data'=>$init ], 1 => ['name'=>'Staff', 'data'=>$init ] ]; // // PROCESS THE INPUT // foreach ($input as $row) { list($start, $end, $sup, $staff) = $row; for ($hr=$start/100; $hr<$end/100; $hr++) { $data[0]['data'][$hr]+=$sup; $data[1]['data'][$hr]+=$staff; } } // // CHECK CHART DATA // echo '<pre>',print_r($data, true),'</pre>'; result
Array ( [0] => Array ( [name] => Supervisors [data] => Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 [4] => 0 [5] => 0 [6] => 0 [7] => 0 [8] => 0 [9] => 1 [10] => 2 [11] => 2 [12] => 2 [13] => 2 [14] => 2 [15] => 2 [16] => 2 [17] => 2 [18] => 2 [19] => 2 [20] => 1 [21] => 1 [22] => 0 [23] => 0 ) ) [1] => Array ( [name] => Staff [data] => Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 [4] => 0 [5] => 0 [6] => 0 [7] => 0 [8] => 0 [9] => 5 [10] => 8 [11] => 8 [12] => 8 [13] => 8 [14] => 8 [15] => 8 [16] => 8 [17] => 8 [18] => 8 [19] => 8 [20] => 3 [21] => 3 [22] => 0 [23] => 0 ) ) ) -
Barand's post in change date after midnight was marked as the answer
here's one way
$arr = [ '2016-02-24 16:55', '2016-02-24 17:55', '2016-02-24 19:55', '2016-02-24 23:55', '2016-02-24 00:35', '2016-02-24 01:34' ]; for ($i=1, $j=0, $k=count($arr); $i<$k; $i++, $j++) { $dj = new DateTime($arr[$j]); $di = new DateTime($arr[$i]); if ($di < $dj) { $arr[$i] = $di->modify('+1 days')->format('Y-m-d H:i'); } } echo '<pre>',print_r($arr, true),'</pre>'; /* RESULT ************* Array ( [0] => 2016-02-24 16:55 [1] => 2016-02-24 17:55 [2] => 2016-02-24 19:55 [3] => 2016-02-24 23:55 [4] => 2016-02-25 00:35 [5] => 2016-02-25 01:34 ) ****************************/ -
Barand's post in Query help was marked as the answer
Replace the AND in the ORDER BY with a comma
...ORDER BY t2.`turma` , t1.`nome` ASC -
Barand's post in Accessing complete table data with inner join was marked as the answer
If you can't work out that my name on my posts is in exactly the same place that your name is on your posts, then I am not sure you are ready for this.
Anyway, your table should look like this.
no | seq | s_line_no -----+--------+------------ 32 | 1 | 315 34 | 1 | 12R 34 | 2 | 12R 34 | 3 | 12R 35 | 1 | 12R 37 | 1 | Other If the user enters more than one value then you write more than one row.
Now all you need is a count of the rows where s_line_no = 12R to get the answer of 4.
-
Barand's post in Selecting count and other info was marked as the answer
Don't know what your column names are but something along these lines
SELECT id, COUNT(*) as tot FROM tablename GROUP BY id -
Barand's post in Math God needed was marked as the answer
If you want them spaced around the circumference then the attached diagram will tell you what you need to know
-
-
Barand's post in Implementing new table was marked as the answer
From what you have said, a lesson can have many classes and each class has many lessons. You therefore have a many-to-many relationship between class and lesson. This would be resolved by using another table to link the two. In this case I suggest it could be a "timetable" table which schedules when each class has a lesson. (See attached model)
Given a class it is then easy to find the students in that class.
Following the links in the diagram you can find which lessons a student has.
-
Barand's post in How do I use MySQL Quarter() function was marked as the answer
my data
mysql> SELECT * FROM history; +-----------+----------+--------------+-------------+ | historyid | memberid | last_payment | amount_paid | +-----------+----------+--------------+-------------+ | 1 | 1 | 2015-12-20 | 1000.00 | | 2 | 1 | 2016-01-01 | 150.00 | | 3 | 1 | 2016-01-10 | 50.00 | | 4 | 1 | 2016-02-03 | 60.00 | | 5 | 1 | 2016-02-25 | 40.00 | | 6 | 1 | 2016-05-30 | 300.00 | | 7 | 1 | 2016-06-22 | 200.00 | | 8 | 2 | 2016-01-05 | 155.00 | | 9 | 2 | 2016-02-14 | 205.00 | +-----------+----------+--------------+-------------+ mysql> SELECT * FROM quarters; +-----+ | qtr | +-----+ | 1 | | 2 | | 3 | | 4 | +-----+ the query and results - I told you to put the conditions on the history table into the ON clause, not in the WHERE clause.
mysql> SELECT qtr AS Quarter, SUM(amount_paid) as Total -> FROM quarters q -> LEFT JOIN history h ON qtr = QUARTER(last_payment) -> AND YEAR(last_payment) = YEAR(CURDATE()) -> AND memberid = '1' -> GROUP BY qtr; +---------+--------+ | Quarter | Total | +---------+--------+ | 1 | 300.00 | | 2 | 500.00 | | 3 | NULL | | 4 | NULL | +---------+--------+ If you have them in the WHERE clause then the LEFT JOIN behaves as as an INNER JOIN like this
mysql> SELECT qtr AS Quarter, SUM(amount_paid) as Total -> FROM quarters q -> LEFT JOIN history h ON qtr = QUARTER(last_payment) -> WHERE YEAR(last_payment) = YEAR(CURDATE()) -> AND memberid = '1' -> GROUP BY qtr; +---------+--------+ | Quarter | Total | +---------+--------+ | 1 | 300.00 | | 2 | 500.00 | +---------+--------+ -
Barand's post in Converting date format was marked as the answer
You can use sql's STR_TO_DATE() function. Concatenate "-01" to the end first so you don't get day 0.
Example
CREATE TABLE `test_date` ( `id` int(11) NOT NULL AUTO_INCREMENT, `str_date` varchar(20) DEFAULT NULL, `date` date DEFAULT NULL, PRIMARY KEY (`id`) ) mysql> SELECT * FROM test_date; +----+----------+------+ | id | str_date | date | +----+----------+------+ | 1 | 16-Jan | NULL | | 2 | 16-Feb | NULL | | 3 | 16-Mar | NULL | +----+----------+------+ UPDATE test_date SET date = STR_TO_DATE(CONCAT(str_date, '-01'), '%y-%b-%d'); mysql> SELECT * FROM test_date; +----+----------+------------+ | id | str_date | date | +----+----------+------------+ | 1 | 16-Jan | 2016-01-01 | | 2 | 16-Feb | 2016-02-01 | | 3 | 16-Mar | 2016-03-01 | +----+----------+------------+ -
Barand's post in Mysql query help was marked as the answer
Use AND instead of OR.
Alternatively,
... WHERE idsala NOT IN (23,24,39) -
Barand's post in Join Tables Part 2 was marked as the answer
Sorry, I copied and edited the wrong query
SELECT c.name AS county_name , c.id AS county_id , c.population , group_concat(DISTINCT r.name separator '<br /> ') AS recruiter , group_concat(DISTINCT u.name separator '<br /> ') AS u_name FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id LEFT JOIN universities u ON c.id = u.county_id WHERE c.state_id = $StateID GROUP BY c.name -
Barand's post in Grouping / Joining / Ordering was marked as the answer
What is the group table for when you have the region parent in the region table?
Try
SELECT reg_name as region , region_id , parent_id FROM clc_crz_regions WHERE parent_id=0 UNION SELECT r1.reg_name , r2.region_id , r1.parent_id FROM clc_crz_regions r1 INNER JOIN clc_crz_regions r2 ON r1.parent_id = r2.region_id WHERE r2.parent_id=0 ORDER BY region_id, parent_id, region +--------------------+-----------+-----------+ | region | region_id | parent_id | +--------------------+-----------+-----------+ | Caribbean | 1 | 0 | | Eastern Caribbean | 1 | 1 | | Southern Caribbean | 1 | 1 | | Western Caribbean | 1 | 1 | | South America | 4 | 0 | | Amazon River | 4 | 4 | +--------------------+-----------+-----------+ -
Barand's post in splitting a serialized array was marked as the answer
unserializing once gave you a serialized string which you then unserialized to get the array.
Looks like your original was serialized twice.
-
Barand's post in results into array was marked as the answer
Do you mean
while($row = $result->fetch_assoc()){ $events[] = array( 'id' => $row['id'], 'title' => $row['event'], 'start' => $row['eventDate'] ); } -
Barand's post in Join Tables Help was marked as the answer
My data
mysql> select * from states; +----+-------------+------------+ | id | name | capital | +----+-------------+------------+ | 1 | Connecticut | Hartford | | 2 | Maine | Augusta | | 3 | Vermont | Montpelier | +----+-------------+------------+ mysql> select * from counties; +----+----------+------------+------------+ | id | state_id | name | population | +----+----------+------------+------------+ | 1 | 3 | Bennington | 37125 | | 2 | 3 | Addison | 36821 | | 3 | 3 | Caledonia | 31227 | | 4 | 1 | Fairfield | 916829 | | 5 | 1 | Hartford | 894014 | | 6 | 2 | Aroostook | 71870 | | 7 | 2 | Cumberland | 281674 | +----+----------+------------+------------+ mysql> select * from recruiters; +----+-----------+---------+ | id | county_id | name | +----+-----------+---------+ | 1 | 1 | Anne | | 2 | 1 | Bernard | | 3 | 2 | Charlie | | 4 | 2 | Dianne | | 5 | 3 | Emma | | 6 | 4 | Fred | | 7 | 4 | George | | 8 | 6 | Henry | | 9 | 6 | Ian | | 10 | 6 | Jane | +----+-----------+---------+ query
SELECT c.name , c.population , group_concat(r.name separator ', ') as recruiters FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id WHERE c.state_id = 2 GROUP BY c.name results
+------------+------------+------------------+ | name | population | recruiters | +------------+------------+------------------+ | Aroostook | 71870 | Henry, Ian, Jane | | Cumberland | 281674 | NULL | +------------+------------+------------------+