unistake

Barand thanks so much for your help! Mac_gyver you just finished it off Works a treat.

I replace them when I post on here for ease of reading. Still changing them for example to $departure = MAN $destination = DUB It is not working

I just used A - Z for a simple explanation of what I needed

-- phpMyAdmin SQL Dump -- version 3.5.8.1 -- http://www.phpmyadmin.net -- -- Host: website.com.mysql:3306 -- Generation Time: Nov 25, 2015 at 05:15 PM -- Server version: 5.5.45-MariaDB-1~wheezy -- PHP Version: 5.3.3-7+squeeze15 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `website_com` -- -- -------------------------------------------------------- -- -- Table structure for table `rosters` -- CREATE TABLE IF NOT EXISTS `rosters` ( `RostersID` int(11) NOT NULL AUTO_INCREMENT, `Code` varchar(7) NOT NULL, `Ffname` varchar(60) NOT NULL, `Fuid` varchar(100) NOT NULL, `SectorDate` date NOT NULL, `Duty` varchar(12) NOT NULL, `Dep` varchar(10) NOT NULL, `BeginTime` varchar(10) NOT NULL, `EndTime` varchar(10) NOT NULL, `Arr` varchar(10) NOT NULL, `AddedOn` datetime NOT NULL, `Random` int(4) NOT NULL, PRIMARY KEY (`RostersID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=42 ; -- -- Dumping data for table `rosters` -- INSERT INTO `rosters` (`RostesID`, `Code`, `Ffname`, `Fuid`, `SectorDate`, `Duty`, `Dep`, `BeginTime`, `EndTime`, `Arr`, `AddedOn`, `Random`) VALUES (1, 'TESTUSER', 'NAME', '102058860356986429', '2015-11-23', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:39', 272), (2, 'TESTUSER', 'NAME', '102886039864529', '2015-11-24', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:39', 272), (3, 'TESTUSER', 'NAME', '10065886039686429', '2015-11-25', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:39', 272), (4, 'TESTUSER', 'NAME', '1025886039876429', '2015-11-26', '3246', 'MAN', '12:15', '14:10', 'STR\r', '2015-11-25 13:21:39', 272), (5, 'TESTUSER', 'NAME', '10206539864276859', '2015-11-26', '3247', 'STR', '14:35', '16:30', 'MAN\r', '2015-11-25 13:21:39', 272), (6, 'TESTUSER', 'NAME', '1020658839867887429', '2015-11-26', '1557', 'MAN', '17:30', '18:35', 'DUB\r', '2015-11-25 13:21:39', 272), (7, 'TESTUSER', 'NAME', '10206588698678429', '2015-11-26', '1556', 'DUB', '19:00', '20:05', 'MAN\r', '2015-11-25 13:21:39', 272), (8, 'TESTUSER', 'NAME', '1020658838768429', '2015-11-27', '1557', 'MAN', '17:30', '18:35', 'DUB\r', '2015-11-25 13:21:39', 272), (9, 'TESTUSER', 'NAME', '10206588656873986429', '2015-11-27', '1556', 'DUB', '19:00', '20:05', 'MAN\r', '2015-11-25 13:21:39', 272), (10, 'TESTUSER', 'NAME', '102065886873546986429', '2015-11-27', '4776', 'MAN', '20:30', '21:40', 'SNN\r', '2015-11-25 13:21:39', 272), (11, 'TESTUSER', 'NAME', '1020658860768986566429', '2015-11-27', '4777', 'SNN', '22:05', '23:15', 'MAN\r', '2015-11-25 13:21:39', 272), (12, 'TESTUSER', 'NAME', '1020658860376986429', '2015-11-28', 'SBY1030-Z', 'MAN', '11:45', '22:30', '', '2015-11-25 13:21:39', 272), (13, 'TESTUSER', 'NAME', '102065886039675429', '2015-11-29', 'SBY1030-Z', 'MAN', '10:30', '22:30', '', '2015-11-25 13:21:39', 272), (14, 'TESTUSER', 'NAME', '157658860398426759', '2015-11-30', '3187', 'MAN', '17:20', '20:00', 'MAD\r', '2015-11-25 13:21:39', 272), (15, 'TESTUSER', 'NAME', '10206588603986429', '2015-11-30', '3186', 'MAD', '20:35', '23:15', 'MAN\r', '2015-11-25 13:21:39', 272), (16, 'TESTUSER', 'NAME', '5464506567886036657986429', '2015-12-01', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:39', 272), (17, 'TESTUSER', 'NAME', '10206587860653986429', '2015-12-02', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:39', 272), (18, 'TESTUSER', 'NAME', '1020655567768603986429', '2015-12-03', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:39', 272), (19, 'TESTUSER', 'NAME', '1020]88603986429', '2015-12-04', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:39', 272), (20, 'TESTUSER', 'NAME', '10206588603984569', '2015-12-05', '4776', 'MAN', '06:55', '08:05', 'SNN\r', '2015-11-25 13:21:39', 272), (21, 'TESTUSER', 'NAME', '16575472065886076867867', '2015-12-05', '4777', 'SNN', '08:30', '09:40', 'MAN\r', '2015-11-25 13:21:39', 272), (22, 'TESTUSER', 'NAME', '1677765876429', '2015-12-05', '4096', 'MAN', '10:05', '12:45', 'RIX\r', '2015-11-25 13:21:39', 272), (23, 'TESTUSER', 'NAME', '10206588603986429', '2015-12-05', '4097', 'RIX', '13:10', '16:05', 'MAN\r', '2015-11-25 13:21:39', 272), (24, 'TESTUSER', 'NAME', '10206588603986429', '2015-12-06', '4007', 'MAN', '06:40', '09:30', 'ALC\r', '2015-11-25 13:21:39', 272), (25, 'TESTUSER', 'NAME', '154674557429', '2015-12-07', 'SBY0400-Z', 'MAN', '04:00', '16:00', '', '2015-11-25 13:21:39', 272), (27, 'TESTUSER', 'NAME', '102603986429', '2015-12-08', 'SBY0400-Z', 'MAN', '04:00', '16:00', '', '2015-11-25 13:21:39', 272), (28, 'TESTUSER', 'NAME', '10240688606429', '2015-12-09', 'SBY0400-Z', 'MAN', '04:00', '16:00', '', '2015-11-25 13:21:40', 272), (29, 'TESTUSER', 'NAME', '14354356658638429', '2015-12-10', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:40', 272), (30, 'TESTUSER', 'NAME', '10206788603986429', '2015-12-11', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:40', 272), (31, 'TESTUSER', 'NAME', '102686803986429', '2015-12-12', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:40', 272), (32, 'TESTUSER', 'NAME', '10205888603986429', '2015-12-13', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:40', 272), (33, 'TESTUSER', 'NAME', '102065603986429', '2015-12-14', '4332', 'MAN', '13:25', '18:00', 'TFS\r', '2015-11-25 13:21:40', 272), (34, 'TESTUSER', 'NAME', '1020658689090903986429', '2015-12-14', '4331', 'TFS', '18:35', '23:10', 'MAN\r', '2015-11-25 13:21:40', 272), (35, 'TESTUSER', 'NAME', '1020658886429', '2015-12-15', 'SBY1030-Z', 'MAN', '11:40', '22:30', '', '2015-11-25 13:21:40', 272), (36, 'TESTUSER', 'NAME', '10206358893986429', '2015-12-16', 'SBY1030-Z', 'MAN', '10:30', '22:30', '', '2015-11-25 13:21:40', 272), (37, 'TESTUSER', 'NAME', '1020658889986429', '2015-12-17', 'SBY1030-Z', 'MAN', '10:30', '22:30', '', '2015-11-25 13:21:40', 272), (38, 'TESTUSER', 'NAME', '102063503986429', '2015-12-18', '4332', 'MAN', '13:25', '18:00', 'TFS\r', '2015-11-25 13:21:40', 272), (39, 'TESTUSER', 'NAME', '1027860633588603986429', '2015-12-18', '4331', 'TFS', '18:35', '23:10', 'MAN\r', '2015-11-25 13:21:40', 272), (40, 'TESTUSER', 'NAME', '10208868903986429', '2015-12-19', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:40', 272), (41, 'TESTUSER', 'NAME', '102058889877603986429', '2015-12-20', 'OFF(Z)', 'MAN', '00:00', '21:00', '', '2015-11-25 13:21:40', 272); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Thanks Barand, Although now I can not return any values in the script below. <?php session_start(); include_once("cxn.inc"); $time = "03:00"; $today = "2015-11-26"; $departure = "A"; $destination = "Z"; $sql = "SELECT * FROM ( SELECT Dep as depA , BeginTime as deptimeA , Arr as arrA , EndTime as arrtimeA FROM rosters WHERE Dep='$departure' AND BeginTime > '$time' AND SectorDate = '$today' ) a LEFT JOIN ( SELECT Dep as depB , BeginTime as deptimeB , Arr as arrB , EndTime as arrtimeB FROM rosters ) b ON arrA = depB AND arrtimeA < deptimeB LEFT JOIN ( SELECT Dep as depC , BeginTime as deptimeC , Arr as arrC , EndTime as arrtimeC FROM rosters ) c ON arrB = depC AND arrtimeB < deptimeC WHERE '$destination' IN (arrA, ArrB, ArrC); "; $result = mysqli_query($cxn,$sql) or die ("Cant"); while($row=mysqli_fetch_assoc($result)) { foreach ($row as $key => $value){ echo $key.' - '.$value.'<br />'; } echo '<p> </p>'; } ?>

Whoops! Just noticed I didn't change the mysql table names above!

Thanks Barand! Looks fantastic! Just I do not have enough experience to be able to use such a complex query. I have tried to put it in to my own php but not sure where I am going wrong below.. <?php session_start(); include_once("cxn.inc"); // $time = "00:01"; // $today = "2015-11-26"; $departure = "MAN"; $destination = "DUB"; $sql = "SELECT * FROM ( SELECT Dep as depA , BeginTime as deptimeA , Arr as arrA , EndTime as arrtimeA FROM rosters WHERE Dep='$departure' AND BeginTime > '06:00' ) a LEFT JOIN ( SELECT Dep as depB , BeginTime as deptimeB , Arr as arrB , EndTime as arrtimeB FROM journey ) b ON arrA = depB AND arrtimeA < deptimeB LEFT JOIN ( SELECT depart as depC , BeginTime as deptimeC , Arr as arrC , EndTime as arrtimeC FROM journey WHERE Arr='$destination' ) c ON arrB = depC AND arrtimeB < deptimeC"; $result = mysqli_query($cxn,$sql) or die ("Cant"); while($row=mysqli_fetch_assoc($result)) { echo 'options<br />'; } ?>

Hi guys, I am trying to make a quick code to find a route between two Place A and Place Z on 2015-11-23 that starts to leave after 6am. Example table rows: DATE DEPARTURE LEAVE ARRIVE ARRIVAL 2015-11-23 Place A 04:00 Place Z 06:00 (this direct route leaves too early.) 2015-11-23 Place U 13:30 Place Z 14:20 2015-11-23 Place A 07:00 Place T 08:00 2015-11-23 Place A 09:00 Place U 12:00 2015-11-23 Place T 09:00 Place B 12:00 2015-11-23 Place B 13:00 Place Z 15:00 The output will show all routes available such as: OPTION 1: Place A to Place U (09:00-12:00) **01:30 wait** Place U to Place Z (13:30-14:20) OPTION 2: Place A to Place T (07:00-08:00) **01:00 wait** Place T to Place B (09:00-12:00) **00:45 wait** Place B to Place Z (12:45-15:00) Thanks for the help

Sorry the above output should be like this, I don't know where I got those previous times from!

Sorry to be 100% correct, this is how I want my output to be for this particular rows in the mysql db. $base = "TPS"; $crewcode = "TESTR";

for information:$base = TPS; the below is what I am trying to acheive. They outputs below are organised by date from the table above. From: HHN CIA TPS 16:15-21:45 From: TPS BGY 18:30-20:15 From: TPS BVA 14:20-20:00 From: TPS '' 11:30-22:30

This is an example of my table. CREATE TABLE IF NOT EXISTS `rosters` ( `RostersID` int(11) NOT NULL AUTO_INCREMENT, `Code` varchar(7) NOT NULL, `SectorDate` date NOT NULL, `Duty` varchar(12) NOT NULL, `Dep` varchar(10) NOT NULL, `BeginTime` varchar(10) NOT NULL, `EndTime` varchar(10) NOT NULL, `Arr` varchar(10) NOT NULL, `AddedOn` datetime NOT NULL, `Random` int(4) NOT NULL, PRIMARY KEY (`RostersID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13004 ; -- -- Dumping data for table `rosters` -- INSERT INTO `rosters` (`RostersID`, `Code`, `SectorDate`, `Duty`, `Dep`, `BeginTime`, `EndTime`, `Arr`, `AddedOn`, `Random`) VALUES (10854, 'TESTR', '2015-03-18', 'HSBY1130Z', 'TPS', '11:30', '22:30', '', '2015-03-06 09:06:29', 2135), (10853, 'TESTR', '2015-03-17', '8642', 'BVA', '17:25', '20:00', 'TPS\r', '2015-03-06 09:06:28', 2135), (10852, 'TESTR', '2015-03-17', '8641', 'TPS', '14:20', '17:00', 'BVA\r', '2015-03-06 09:06:28', 2135), (10850, 'TESTR', '2015-03-16', '9062', 'TPS', '18:30', '20:15', 'BGY\r', '2015-03-06 09:06:27', 2135), (10849, 'TESTR', '2015-03-15', '6078', 'CIA', '20:35', '21:45', 'TPS\r', '2015-03-06 09:06:27', 2135), (10848, 'TESTR', '2015-03-15', '6079', 'TPS', '19:00', '20:10', 'CIA\r', '2015-03-06 09:06:27', 2135), (10847, 'TESTR', '2015-03-15', '9858', 'HHN', '16:15', '18:35', 'TPS\r', '2015-03-06 09:06:27', 2135);

my goal is to combine each row from the mysql database where the date is the same. Then I am putting the departure, arrival, duty, begintimes and endtimes in to separate arrays. I want to show first deptarture airport of the day, every arrival airport != $base and the first begintime and last endtime. Kind of complicated! Hope it makes sense!

Hi guys, I have this code here http://pastebin.com/WK36Jx0G It works as it should for the first loop but for each re-loop though I get the output 'Array' echoing from line 41's output. Example shown below.. From: MAN STR DUB 12:15 - 20:05 From: MANArray DUB SNN 17:30 - 23:15 Anyone understand why? Thanks

Hi guys, Having trouble trying to show values from my $friend array. Only the first friend code is being displayed in my output. There are several fields in my mysql associated with the $sql. Any ideas? Thanks $sql = "SELECT * FROM friends WHERE Code = '$code' OR FriendCode = '$code' AND Status ='A'"; $result = mysqli_query($cxn,$sql) or die ("Can't get friends."); $friend = array(); while($row=mysqli_fetch_array($result)) { if($code == $row['FriendCode']) { $friend[] = $row['Code']; } elseif($code == $row['Code']) { $friend[] = $row['FriendCode']; } foreach($friend as $key => $value) { echo $value.'<br />'; }

Hi all, I have a looping error in my PHP. I am trying to add use a while statement to gather the users first and last name in to a string, add them to an array called $friendname, then check that the $friendname[] is only output/echoed once. At the moment the code looks good to me but with multiple rows with the same name associated, the name is echoed the same amount of times as rows, instead of once as it is supposed to be. Any ideas anyone? while($row2 = mysqli_fetch_assoc($result2)) { $friendcode = $row2['Code']; $names = "SELECT FirstName, LastName from users WHERE Code = '$friendcode' AND Code != '$crewcode'"; $resultnames = mysqli_query($cxn,$names) or die ("Can't get friends names."); while($rownames = mysqli_fetch_assoc($resultnames)) { $friendname = array(); $newfriendname = ($rownames['FirstName'].' '.$rownames['LastName']); if (!in_array($newfriendname, $friendname)) { array_push($friendname, "$newfriendname"); echo $newfriendname; } } }

Hi guys, I am trying to echo multiple calendar events from a mysql table and order them by the date column. There are several events on each day so I am trying to echo the date once then list the associated events under that date. Such as 2014-05-10 Event 1, event 2, event 3 2014-05-11 Event 1, event 2, event 3 instead of 2014-05-10 event 1, 2014-05-10 event 2, 2014-05-10 event 3, 2014-05-11 event 1 etc... This is my coding so far: while($row = mysqli_fetch_assoc($result)) { $dutydate = $row['MyDate']; if($dutydate != $previousdate) { echo $dutydate.'<br />'; } elseif(!empty($dutydate)) { echo $dutydate.'<br />'; } echo $event1.' | '.$event2.' | '.$event3.'<br />'; $previousdate = $dutydate; } ?>

Thanks requinix, I wrote the outputs by hand and forgot to include the Rank as you said. The data comes out as I need it except the first output from the first while statement is coming out several times.

Hi guys, I am trying to create a while condition that would display an output such as: "Duty: 1234, = Dep: 01:00, End: 03:35 code1, code4, code5" The problem I am having is instead of echoing the first line once, with my while statements the first line will echo as many times are there are codes associated with it that I am trying to output from another mysql table. Have a look below and you will see what I mean! My output at the moment looks like: "Duty: 1234, = Dep: 01:00, End: 03:35 code1 Duty: 1234, = Dep: 01:00, End: 03:35 code4 Duty: 1234, = Dep: 01:00, End: 03:35 code5" $sql = "SELECT * FROM calendar WHERE Duty = '$duty' AND BeginTime = '$begintime' AND EndTime = '$endtime'"; $result = mysqli_query($cxn,$sql) or die ("Can't execute query."); while($row=mysqli_fetch_assoc($result)) { echo 'Duty: '.$row['Duty'].' = Dep:'.$row['BeginTime']. ', Land:'.$row['EndTime'].'<br />'; $sql1 = "SELECT * FROM users WHERE Code = '{$row['Code']}'"; $result1 = mysqli_query($cxn,$sql1) or die ("Can't execute query1."); while($row1 = mysqli_fetch_assoc($result1)) { echo $row1['Rank'].': '.$row['Code'].'</br>'; } } Thanks!

Thanks Psycho, your a legend!

Hi Psycho, Apologies I wrote a reply to my previous thread but somehow got logged out and decided to just create another now that I have the full idea of the information that would be provided. Thank you so much for your time, the code above would work perfectly.

Any ideas guys?

Hi guys, Users of my website will be copying and pasting their work schedule from another website that displays it in a html table, as shown below, in to a textarea on my website which I can then decode and store in a mysql db. To do this I am trying to put every value in to an array with the associated column name/key. |Date| |Duty| |Dep| |Start| |End| |Arr| 4 Apr 14, Fri 3005 (Z) CIA 04:15 Z 07:05 Z STN 4 Apr 14, Fri 3002 STN 07:45 Z 10:15 Z CIA 5 Apr 14, Sat OFF (Z) CIA 00:00 Z 21:00 Z The small problem being both the 'Duty' and 'Arr' column can have any numerical, text or blank spaces in them. All the other columns have the same format in every row. (such as the 'Dep' column is always 3 uppercase characters etc). I have tried to separate the parts of each line as you can see in the code below but this would not be consistent due to the extra spaces the sometimes occur in the 'Duty' column. Does anyone see a way to make this fault proof?! Thanks <?php $lines = explode("\n", $_POST['test']); $sector = 1; foreach($lines as $line) { if(substr($line, 0, 4)=='Date') { //Skip header line continue; } $parts = preg_split('/\s+/', $line); echo 'Sector '.$sector.'<br />'; $date = $parts[0] . '/' . $parts[1] . '/20' . trim($parts[2], ','); $duty = $parts[4]; $dep = $parts[5]; $begin = $parts[6]; $end = $parts[8]; $arr = $parts[10]; $sector++; } ?>

Hi guys, I am trying to find a way for a website user to copy a standard html table from a website and paste it in to a form on my website, which would then upload it to php to a mysql db. The table they would copy follows the same format however the fields could sometimes have either or numberical or characters as values. An example of a table I would be trying to upload to my DB is this: "Date Duty Dep Begin End Arr 1 Apr 14, Wed 7743 HME 04:40 Z 06:20 Z AWY 1 Apr 14, Wed 6473 AWY 06:45 Z 08:55 Z FRG 1 Apr 14, Wed 1231 FRG 09:20 Z 11:25 Z AWY 1 Apr 14, Wed 2222 AWY 11:50 Z 13:25 Z HME 2 Apr 14, Tue STDY0300-Z HME 03:00 Z 15:00 Z 3 Apr 14, Thu 6666 HME 05:00 Z 06:05 Z WAG 3 Apr 14, Thu 5555 WAG 06:30 Z 07:40 Z HME 3 Apr 14, Thu 2222 HME 08:05 Z 10:50 Z NWR 3 Apr 14, Thu 1111 NWR 11:15 Z 13:50 Z HME 4 Apr 14, Fri 4444 HME 04:15 Z 07:05 Z CKC 4 Apr 14, Fri 2222 CKC 07:45 Z 10:15 Z HME" Does anyone know the best way to try and separate the columns in each row to process with PHP? Thanks,

where would you recommend me putting any banners? or maybe put a few box adverts in between the other aircraft adverts? I dont want to destroy the design of the site by adding lots of banners and flashing flash scripts! What websites make £10k a day with a single advert?!