Barand

Yes, the values come from the select query. Yours will work only if the structures of the tables are identical. If not, you need to specify the the destination and source columns, for example INSERT INTO table1 (col1, col3, col2) SELECT x, y, z FROM table2 WHERE ...

try $file = '/var/www/html/Cute_browser/abc/xyz.pdf'; $shortPath = join('/', array_slice(explode('/', $file),-3)); echo $shortPath; //--> Cute_browser/abc/xyz.pdf

prev = ''; while fetch next row if prev != topic output topic prev = topic endif output news endwhile

This should do it SELECT ko_time , c.home , c.away , av_goals , meets FROM championship c LEFT JOIN ( SELECT teams , AVG(home_score + away_score) as av_goals , COUNT(*) as meets FROM ( SELECT home_score , away_score , @seq := IF(@prev <> CONCAT(LEAST(home,away),GREATEST(home,away)),1,@seq+1) as seq , @prev := CONCAT(LEAST(home,away),GREATEST(home,away)) as teams FROM championship JOIN (SELECT @prev:='', @seq:=0) init WHERE match_date < '2015-09-19' ORDER BY CONCAT(LEAST(home,away),GREATEST(home,away)), match_date DESC ) last5 WHERE seq < 6 GROUP BY teams ) H ON teams = CONCAT(LEAST(c.home,c.away),GREATEST(c.home,c.away)) WHERE match_date = '2015-09-19' ORDER BY c.home, c.away; +---------+--------------------+---------------+----------+-------+ | ko_time | home | away | av_goals | meets | +---------+--------------------+---------------+----------+-------+ | 15:00 | Blackburn | Charlton | 2.8000 | 5 | | 15:00 | Brentford | Preston | NULL | NULL | | 15:00 | Bristol City | Reading | NULL | NULL | | 15:00 | Huddersfield | Bolton | 1.4000 | 5 | | 15:00 | Hull City | QPR | NULL | NULL | | 15:00 | Milton Keynes Dons | Leeds | NULL | NULL | | 12:30 | Nottingham | Middlesbrough | 2.6000 | 5 | | 15:00 | Rotherham | Cardiff | 4.0000 | 1 | | 15:00 | Sheffield Wed | Fulham | 3.0000 | 2 | | 15:00 | Wolves | Brighton | 3.0000 | 4 | +---------+--------------------+---------------+----------+-------+ 10 rows in set (0.08 sec)

He used === false which means it must be the same value and type (boolean false, not 0, or any of the others that evaluate to false)

BTW, your query is wrong. LIMIT limits the number of rows output by the query, it doesn't limit the input to the latest 5. That's why I had to use the subqueries with @seq to get the latest 5.

The only way I could think of to get the two averages is to join twice to the subquery containing the team average calculation SELECT c.ko_time , c.home , c.away , ROUND(h.average,1) as home_av , ROUND(a.average,1) as away_av FROM championship c INNER JOIN ( SELECT team , AVG(score) as average FROM ( SELECT score , venue , @seq:=IF(team<>@prev,1,@seq+1) as seq , @prev:=team as team FROM ( SELECT 'H' as venue , match_date , home as team , home_score as score FROM championship WHERE match_date < CURDATE() UNION ALL SELECT 'A' as venue , match_date , away as team , away_score as score FROM championship WHERE match_date < CURDATE() ORDER BY team, match_date DESC ) scores JOIN (SELECT @prev:='', @seq=0) init ) av WHERE seq < 6 GROUP BY team ) h ON h.team = c.home INNER JOIN ( SELECT team , AVG(score) as average FROM ( SELECT score , venue , @seq:=IF(team<>@prev,1,@seq+1) as seq , @prev:=team as team FROM ( SELECT 'H' as venue , match_date , home as team , home_score as score FROM championship WHERE match_date < CURDATE() UNION ALL SELECT 'A' as venue , match_date , away as team , away_score as score FROM championship WHERE match_date < CURDATE() ORDER BY team, match_date DESC ) scores JOIN (SELECT @prev:='', @seq=0) init ) av WHERE seq < 6 GROUP BY team ) a ON a.team = c.away WHERE c.match_date = '2015-09-19' ORDER BY h.team; +---------+--------------------+---------------+---------+---------+ | ko_time | home | away | home_av | away_av | +---------+--------------------+---------------+---------+---------+ | 15:00 | Blackburn | Charlton | 0.6 | 1.0 | | 15:00 | Brentford | Preston | 0.6 | 0.6 | | 15:00 | Bristol City | Reading | 1.0 | 1.6 | | 15:00 | Huddersfield | Bolton | 0.6 | 0.6 | | 15:00 | Hull City | QPR | 1.0 | 1.6 | | 15:00 | Milton Keynes Dons | Leeds | 0.2 | 0.8 | | 12:30 | Nottingham | Middlesbrough | 1.0 | 1.6 | | 15:00 | Rotherham | Cardiff | 1.0 | 1.2 | | 15:00 | Sheffield Wed | Fulham | 0.6 | 1.4 | | 15:00 | Wolves | Brighton | 1.0 | 1.4 | +---------+--------------------+---------------+---------+---------+ 10 rows in set (0.07 sec) The second question you asked will require you to use a similar technique (though I think only a single subquery join will be necessary in that one)

You might have to write the array contents to separate rows in a temporary table

I had to change CURDATE() in the final WHERE clause (no Sunday matches) mysql> SELECT ko_time -> , team -> , AVG(score) -> FROM -> ( -> SELECT score -> , @seq:=IF(team<>@prev,1,@seq+1) as seq -> , @prev:=team as team -> FROM -> ( -> SELECT -> 'H' as venue -> , match_date -> , home as team -> , home_score as score -> FROM championship -> WHERE match_date < CURDATE() -> UNION ALL -> SELECT -> 'A' as venue -> , match_date -> , away as team -> , away_score as score -> FROM championship -> WHERE match_date < CURDATE() -> ORDER BY team, match_date DESC -> ) scores -> JOIN (SELECT @prev:='', @seq=0) init -> ) av -> INNER JOIN -> championship c ON av.team IN (c.home, c.away) -> WHERE c.match_date = '2015-09-19' AND seq < 6 -> GROUP BY team; +---------+--------------------+------------+ | ko_time | team | AVG(score) | +---------+--------------------+------------+ | 15:00 | Blackburn | 0.6000 | | 15:00 | Bolton | 0.6000 | | 15:00 | Brentford | 0.6000 | | 15:00 | Brighton | 1.4000 | | 15:00 | Bristol City | 1.0000 | | 15:00 | Cardiff | 1.2000 | | 15:00 | Charlton | 1.0000 | | 15:00 | Fulham | 1.4000 | | 15:00 | Huddersfield | 0.6000 | | 15:00 | Hull City | 1.0000 | | 15:00 | Leeds | 0.8000 | | 12:30 | Middlesbrough | 1.6000 | | 15:00 | Milton Keynes Dons | 0.2000 | | 12:30 | Nottingham | 1.0000 | | 15:00 | Preston | 0.6000 | | 15:00 | QPR | 1.6000 | | 15:00 | Reading | 1.6000 | | 15:00 | Rotherham | 1.0000 | | 15:00 | Sheffield Wed | 0.6000 | | 15:00 | Wolves | 1.0000 | +---------+--------------------+------------+ 20 rows in set (0.02 sec)

try SELECT ko_time , team , AVG(score) FROM ( SELECT score , @seq:=IF(team<>@prev,1,@seq+1) as seq , @prev:=team as team FROM ( SELECT 'H' as venue , match_date , home as team , home_score as score FROM championship WHERE match_date < CURDATE() UNION ALL SELECT 'A' as venue , match_date , away as team , away_score as score FROM championship WHERE match_date < CURDATE() ORDER BY team, match_date DESC ) scores JOIN (SELECT @prev:='', @seq=0) init ) av INNER JOIN championship c ON av.team IN (c.home, c.away) WHERE c.match_date = curdate() AND seq < 6 GROUP BY team;

Sorry I couldn't be more helpful but I wasn't going to attempt to juggle data from five tables of unknown structure and content in my head.

Don't know, I can't see the data from here.

You can use "WITH ROLLUP" to get the overall totals. Add it at the end of the quey . . . . . . GROUP BY date WITH ROLLUP gives +------------+---------+--------+-----------+-----------+-------+ | date | created | offers | contracts | purchases | sales | +------------+---------+--------+-----------+-----------+-------+ | 2015-09-01 | 1 | 0 | 0 | 0 | 0 | | 2015-09-02 | 1 | 2 | 0 | 0 | 0 | | 2015-09-03 | 1 | 0 | 1 | 0 | 0 | | 2015-09-04 | 1 | 2 | 3 | 0 | 0 | | 2015-09-05 | 1 | 1 | 1 | 0 | 0 | | 2015-09-12 | 0 | 0 | 0 | 1 | 0 | | 2015-09-18 | 0 | 0 | 0 | 0 | 1 | | 2015-10-16 | 0 | 0 | 0 | 1 | 0 | | 2015-10-26 | 0 | 0 | 0 | 1 | 0 | | 2015-11-06 | 0 | 0 | 0 | 1 | 0 | | 2015-12-01 | 0 | 0 | 0 | 1 | 0 | | 2015-12-15 | 0 | 0 | 0 | 0 | 1 | | 2015-12-30 | 0 | 0 | 0 | 0 | 1 | | 2016-01-15 | 0 | 0 | 0 | 0 | 1 | | 2016-02-01 | 0 | 0 | 0 | 0 | 1 | | NULL | 5 | 5 | 5 | 5 | 5 | +------------+---------+--------+-----------+-----------+-------+ Back at the original insertion of the data. That table has more of a spreadsheet flavour than a db table. Is it created by collecting dates from other tables?

this is my query with my data SELECT date , SUM(IF(type='CR',1,0)) as created , SUM(IF(type='OF',1,0)) as offers , SUM(IF(type='CO',1,0)) as contracts , SUM(IF(type='PU',1,0)) as purchases , SUM(IF(type='SA',1,0)) as sales FROM ( SELECT 'CR' as type, created as date FROM property UNION ALL SELECT 'OF' as type, offer_date as date FROM property UNION ALL SELECT 'CO' as type, contract_date as date FROM property UNION ALL SELECT 'PU' as type, purchase_date as date FROM property UNION ALL SELECT 'SA' as type, sale_date as date FROM property ) props GROUP BY date; +------------+---------+--------+-----------+-----------+-------+ | date | created | offers | contracts | purchases | sales | +------------+---------+--------+-----------+-----------+-------+ | 2015-09-01 | 1 | 0 | 0 | 0 | 0 | | 2015-09-02 | 1 | 2 | 0 | 0 | 0 | | 2015-09-03 | 1 | 0 | 1 | 0 | 0 | | 2015-09-04 | 1 | 2 | 3 | 0 | 0 | | 2015-09-05 | 1 | 1 | 1 | 0 | 0 | | 2015-09-12 | 0 | 0 | 0 | 1 | 0 | | 2015-09-18 | 0 | 0 | 0 | 0 | 1 | | 2015-10-16 | 0 | 0 | 0 | 1 | 0 | | 2015-10-26 | 0 | 0 | 0 | 1 | 0 | | 2015-11-06 | 0 | 0 | 0 | 1 | 0 | | 2015-12-01 | 0 | 0 | 0 | 1 | 0 | | 2015-12-15 | 0 | 0 | 0 | 0 | 1 | | 2015-12-30 | 0 | 0 | 0 | 0 | 1 | | 2016-01-15 | 0 | 0 | 0 | 0 | 1 | | 2016-02-01 | 0 | 0 | 0 | 0 | 1 | +------------+---------+--------+-----------+-----------+-------+

I am working on the query for, but, in the meantime I would advise you use DATE, DATETIME or TIMESTAMP type columns for date/times. Not only are these immediately useful without conversion to a usable format, but you can tell what the the date is when you view your data, instead of an unintelligible string of digits So while I am testing I am using DATE columns and inserted this data (to preserve your dates) INSERT INTO `property` (`id`, `created`, `offer_date`, `contract_date`, `purchase_date`, `sale_date`) VALUES (1, FROM_UNIXTIME(1441065600), FROM_UNIXTIME(1441152000), FROM_UNIXTIME(1441238400), FROM_UNIXTIME(1442016000), FROM_UNIXTIME(1442534400)), (2, FROM_UNIXTIME(1441152000), FROM_UNIXTIME(1441152000), FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1445817600), FROM_UNIXTIME(1450137600)), (3, FROM_UNIXTIME(1441238400), FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1444953600), FROM_UNIXTIME(1451433600)), (4, FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1446768000), FROM_UNIXTIME(1452816000)), (5, FROM_UNIXTIME(1441411200), FROM_UNIXTIME(1441411200), FROM_UNIXTIME(1441411200), FROM_UNIXTIME(1448928000), FROM_UNIXTIME(1454284800)); giving a readable +----+------------+------------+---------------+---------------+------------+ | id | offer_date | created | contract_date | purchase_date | sale_date | +----+------------+------------+---------------+---------------+------------+ | 1 | 2015-09-02 | 2015-09-01 | 2015-09-03 | 2015-09-12 | 2015-09-18 | | 2 | 2015-09-02 | 2015-09-02 | 2015-09-04 | 2015-10-26 | 2015-12-15 | | 3 | 2015-09-04 | 2015-09-03 | 2015-09-04 | 2015-10-16 | 2015-12-30 | | 4 | 2015-09-04 | 2015-09-04 | 2015-09-04 | 2015-11-06 | 2016-01-15 | | 5 | 2015-09-05 | 2015-09-05 | 2015-09-05 | 2015-12-01 | 2016-02-01 | +----+------------+------------+---------------+---------------+------------+

Those need to be variables ($ prefixes) or you need to have defined them as constants of course you can. I didn't because I wanted to show a mysqli connection I normally use an included file in which I define HOST, USERNAME, PASSWORD and DATABASE

It would help if we knew what the data looks like. Table structure?

I notice you changed return filemtime( $b ) - filemtime( $a ); to return filemtime( $a ) - filemtime( $b ); That will now give you the 5 oldest files and not the 5 latest. This will retrieve filenames from the folder and all subfolder $dir = 'path/to/folder'; // set start directory $show = 5; $files = array(); // array to store retrieved filenames dirList($dir, $files); // call the recursive function to get all files usort( $files, create_function('$a, $b', 'return filemtime( $b ) - filemtime( $a );') ); for ( $i = 0; $i < $show; ++$i ) echo '<a href="', $file = $files[$i], '">', ucfirst( strtolower(substr($file, 0, strpos($file, '.'))) ), '</a> - ', date( 'D, d M y H:i:s', filemtime($file) ), '<br />', "\n"; /***************************************************************** ** function to retrieve files from folder and all subfolders ******************************************************************/ function dirList($dir, &$results, $level=0) { $files = glob($dir.'/*'); foreach ($files as $f) { if (is_dir($f)) continue; $results[] = $f; } $files = glob($dir.'/*', GLOB_ONLYDIR); foreach ($files as $f) { if (is_dir($f)) { dirList($f, $results, $level+1); } } }

Sounds like this is the model that are describing +--------------+ | parent | +--------------+ | parent_id |-----+ | parentname | | +--------------+ | +-------------+ | | child | | +-------------+ | | child_id | | | childname | +-------<| parent_id | +-------------+ in which case JOIN using the parent id column SELECT p.parent_id , p.parentname , c.childname FROM parent p LEFT JOIN child c ON p.parent_id = c.parent_id ORDER BY parentname Loop through the results outputting the parent name only when it changes to new value.

Use the big "Start New Topic" button at the top right of the forum page

Instead of just hijacking this thread, why don't you read it? You just might find the answer you are looking for.

Most of the cities in that sample begin with a space (or other non-printing character) and a "\n" In the example I picked out the length is 9 when it should be only 7. You need to trim() the elements in your data

... and is the file being opened? Check the value of $fil after opening to make sure it isn't = false.

foreach ($myarray as $yr => $yrdata) { echo $yr . '<br>'; foreach ($yrdata as $doc) { echo $doc['documentname'] . '<br>'; } }

execute the query and list the results. (I have use mysqli - which you should be using (or PDO) and not the deprecated mysql_ library) $mysqli = new mysqli(HOST,USERNAME,PASSWORD,'bikes'); $sql = "SELECT m.modell , m.antal , o.total , ROUND(o.total/m.antal*100, 1) as pc FROM modeller m LEFT JOIN ( SELECT mc as modell , COUNT(*) as total FROM owner GROUP BY modell ) o USING (modell) ORDER BY modell"; $res = $mysqli->query($sql); // execute the query while (list($model, $antal, $total, $pc) = $res->fetch_row()) { // list the results echo "We have $total pcs $model ( {$pc}% ) of produced $antal pcs in the database<br>"; }