unistake

Posted May 18, 2016 · May 18, 2016

Cheers mac_gyver,

Fortunately I was thinking along those lines.

So far I have the code below however I do not know how to store my matched queries in a multidimensional array indexed firstly by $row['Code'] and then by $row['SectorDate'] as you mentioned.

This is the code I have so far:

<table class="">
<thead>
	<tr>
	  <th> </th>
<?php
 // Set timezone
 date_default_timezone_set('UTC');
 
 // Start date
 $date = date("Y-m-d");
 // End date
 $end_date = date ("Y-m-d", strtotime("+30 days", strtotime($date)));
 
 while (strtotime($date) <= strtotime($end_date)) {
				echo "<th>".date("D", strtotime($date))."<br />".date("d M", strtotime($date))."</th>";
				$date = date ("Y-m-d", strtotime("+1 day", strtotime($date)));
 }
 
?>
	</tr>
</thead>
<tbody>
<?php
$today = '2016-05-18';
$sql = "SELECT rosters.Code,rosters.SectorDate,rosters.Duty,rosters.BeginTime,rosters.Dep,rosters.Arr,rosters.EndTime,Users.Ffname FROM rosters INNER JOIN Users ON rosters.Code = Users.Code WHERE Users.Base = 'DUB' AND rosters.SectorDate BETWEEN '2016-05-18' AND '2016-05-28' ORDER BY rosters.Code";
$result = mysqli_query($cxn,$sql)
	or die($cxn->error);
while($row=mysqli_fetch_array($result)) {
	foreach($row as $key=>$value) {
		echo $key.' '.$value.'<br />';
	}
		echo '<p> </p>';
}
?>						
</tbody>
</table>

Posted May 18, 2016 · May 18, 2016

I just need to show about 30 days in advance. So there will not be too a huge amount of rows to extract data from. A few hundred..

I have tried writing the script but I know it is far past my experience so far. Without even considering code, I cant think logically how this would work so I can attempt the right way to do it with php!

Posted May 18, 2016 · May 18, 2016

Hi all,

I have some data in a table to do with 20 users appointments every day for a month.

I am trying to organise the appointments in to a large table so the clients can see all the appointments of every colleague by date (in columns) and by user name (in rows).

for example:

20th May | 21st May | 22nd May | 23rd May

Mike Jones London, Paris | Paris, Rome | Rome, London | London, New York

06:15 - 10:15 | 08:20 - 14:00 | 03:30-05:30 | 05:25-09:30

-----------------------------------------------------------------------------------------------------------------------------------------

Bethan Cray OFF | Belfast, Madrid | Madrid, Fez | Fez, Athens

11:15 - 16:35 | 08:45 - 12:10 | 13:55-15:30 | 09:25-13:20

The data is currently stored in the mysql table such as below, and has over 40 users with over 30 rows each in the same format.

SectorDate Code BeginTime Dep Arr EndTime

2016-05-20 Mike Jones 06:15 London Paris 10:15

2016-05-21 Mike Jones 08:20 Paris Rome 14:00

2016-05-20 Bethan Cray 11:15 OFF 16:35

Please can someone show how best to arrange the mysql database to give the output above!

Thanks

Posted January 31, 2016 · January 31, 2016

hi requinix,

Thanks for that. Actually there may well be multiple rows for each Code/SectorDate pair however its not guaranteed.

Posted January 31, 2016 · January 31, 2016

Hi all,

I am trying to make a list of users where two rows in a mysql database exist.

My attempt so far:

"SELECT * FROM mail_list INNER JOIN rosters ON mail_list.Code = rosters.Code WHERE rosters.SectorDate = '2016-01-04' AND EXISTS (SELECT * FROM rosters WHERE rosters.SectorDate = '2016-01-24' IS NOT NULL)"

So basically, I want to select all the users information from mail_list table only if in the rosters table the user has a row that exists with the date 2016-01-04 and a second row 2016-01-24.

I have tried several types of EXIST statements as above but no luck so far :/

Please help!!

Thanks

January 26, 2016

works a treat! thanks!!

    if (is_null($coldest) || $temp < $coldest) {
		$coldest_loc = $row['Name'];
        $coldest = $temp;
    }
    if (is_null($hottest) || $temp > $hottest) {
		$hottest_loc = $row['Name'];
        $hottest = $temp;
    }

January 26, 2016

thanks for removing my id!

sorry i just need the location and the temperature from the xml.

Edit: I could just link the $row['Name'] to the temperature value though, i guess.

January 26, 2016

I am using a weather XML from openweathermap to get the hottest and coldest location according to the locations found in a php query.

The php query below works fine but i know the way I am trying to sort the array values is not working. I presume because it is multiple arrays.

The output I got when printing the full array content previously was:

Array ( [0] => SimpleXMLElement Object ( [0] => 9.29 ) [1] => SimpleXMLElement Object ( [0] => 11.37 ) [2] => SimpleXMLElement Object ( [0] => 13 ) [3] => SimpleXMLElement Object ( [0] => 14 ) [4] => SimpleXMLElement Object ( [0] => 8 ) [5] => SimpleXMLElement Object ( [0] => 13.81 ) [6] => SimpleXMLElement Object ( [0] => 19.84 ) [7] => SimpleXMLElement Object ( [0] => 22 ) )

<?php
session_start();
include_once("cxn.inc");

$today = date("Y-m-d H:i:s");

$sql = "SELECT airports.Name,airports.Country FROM airports INNER JOIN rosters ON airports.IATA = rosters.Arr WHERE rosters.Code = '$code' AND rosters.SectorDate >= '$today'";
$result = mysqli_query($cxn,$sql)
   or die ($cxn->error);

$hottest = array();

while($row=mysqli_fetch_assoc($result)) {
   $link = 'http://api.openweathermap.org/data/2.5/weather?q='.$row['Name'].','.$row['Country'].'&units=metric&mode=xml&appid=*';   

    /* WEATHER API  */
    $xml=simplexml_load_file("$link") or die ("Cannot create object.");
    $hottest[] = $xml->temperature[value];
}

asort($hottest); // attempting to sort $hottest array by numerical value order

///  OUTPUT GOAL ///
echo reset($hottest); // coldest value
echo '<br />';
echo end($hottest); // hottest value

?>

January 25, 2016

yes! I have got confused as its not something I have attempted before and tried to mix multiple scripts found on other sites.

January 25, 2016

Yes it's from XML.

I have so far,

<?php
$sql = "SELECT * FROM airports GROUP BY airports.IATA ORDER BY Longitude + 0 DESC";
$result = mysqli_query($cxn,$sql)
   or die ($cxn->error);

$hottestcoldest = array();
while($row=mysqli_fetch_assoc($result)) {
   $xml=simplexml_load_file("$link") or die("Error: Cannot create object");
   $hottestcoldest[$row['Name']] = $xml->temperature[value];
}

$new = array();
foreach ($hottestcoldest as $key => $item) {
   $new[] = $item;
}
asort($new);

echo reset($new).' coldest <br />';
echo end($new. ' hottest';

?>

EDIT: I guess there is a much better way to try and do it!

January 25, 2016

Hi all,

I currently have an array that is shown like this when I use print_r(array_values($temperature));

Array ( [0] => SimpleXMLElement Object ( [0] => 9.29 ) [1] => SimpleXMLElement Object ( [0] => 11.37 ) [2] => SimpleXMLElement Object ( [0] => 13 ) [3] => SimpleXMLElement Object ( [0] => 14 ) [4] => SimpleXMLElement Object ( [0] => 8 ) [5] => SimpleXMLElement Object ( [0] => 13.81 ) [6] => SimpleXMLElement Object ( [0] => 19.84 ) [7] => SimpleXMLElement Object ( [0] => 22 ) )

I want to find the highest and lowest value from the above array such as here 8 is the lowest and 22 being the highest value.

I am not sure how I can order the array inside an array to do this.

Thanks

January 24, 2016

sql_big_selects was OFF. You mean to set the big selects to 1 before my php query i am trying to get working?

January 23, 2016

max_join_size - 33554432.

There are now just over 50,000 rows in the table.

January 23, 2016

I disabled the first query and still have the same error message.

I am not yet sure how to put the two queries together but i do not think that is the problem.

January 23, 2016

no that first query is fine.

January 23, 2016

about 20,000 rows in `rosters`, 260 in `airports`

January 23, 2016

sorry i missed one error!

Now i have this, The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

January 23, 2016

i did as above. you the errors i got i showed above.

January 23, 2016

I have the error,

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /customers/5/5/7/website.com/httpd.www/page.php on line 56 Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /customers/5/5/7/website.com/httpd.www/page.php on line 40

$sql = "SELECT * FROM rosters WHERE Code = '$Code' AND SectorDate >= '$today' AND Arr != '' GROUP BY RostersID ASC";
$result = mysqli_query($cxn,$sql) or die ("Cant do it!");
while($row=mysqli_fetch_assoc($result)) {  // LINE 40
	$sql = "SELECT 
	  d.Latitude AS departure_lat
	, d.Longitude AS departure_lng 
	, a.Latitude AS arrival_lat
	, a.Longitude AS arrival_lng 
	FROM rosters 
	INNER JOIN airports a ON a.IATA = rosters.Arr 
	INNER JOIN airports d ON d.IATA = rosters.Dep 
	WHERE rosters.Arr = 'ALC'
		AND rosters.Dep = 'MAN'
		AND rosters.Code = '$Code' 
		AND rosters.SectorDate >= '$today' 
	ORDER BY rosters.RostersID";
	$result = mysqli_query($cxn,$sql)
		or $cxn->error;
	while($row=mysqli_fetch_assoc($result)) {  //LINE 56
		echo $row['departure_lat'];
	}
}

January 23, 2016

hmm, still not working here.

I changed a few spelling mistakes above and input 'rosters.' inside a couple of the WHERE clauses.

Still get the error 'no can do'

$sql = "SELECT 
  d.Latitude AS departure_lat
, d.Longitude AS departure_lng 
, a.Latitude AS arrival_lat
, a.Longitude AS arrival_lng 
FROM rosters 
INNER JOIN airports a ON a.IATA = rosters.Arr 
INNER JOIN airports d ON d.IATA = rosters.Dep 
WHERE rosters.Arr = 'CRL'
	AND rosters.Dep = 'BGY'
	AND rosters.Code = '$Code' 
	AND rosters.SectorDate >= '$today' 
ORDER BY rosters.RostersID";
	//echo $sql;
	$result = mysqli_query($cxn,$sql)
		or die ("no can do");
	while($row=mysqli_fetch_assoc($result)) {
		echo $row['departure_lat'];
	}

January 23, 2016

Thanks Barand,

although I dont quite have the grasp of it. I knew I should be using the 'AS'.

This is what I have so far:

$sql = "SELECT * FROM rosters WHERE Code = '$Code' AND SectorDate >= '$today' AND Arr != '' GROUP BY RostersID ASC";
$result = mysqli_query($cxn,$sql) or die ("Cant do it!");
while($row=mysqli_fetch_assoc($result)) {
	$sql = "SELECT 
	  d.Latitude AS departure_lat
	, d.Longitude AS depatrure_lng 
	, a.Latitude AS arrival_lat
	, a.Longitude AS arrival_lng 
	FROM rosters 
	INNER JOIN airport a ON a.IATA = rosters.Arr 
	INNER JOIN airport d ON d.IATA = rosters.Dep 
	WHERE rosters.Arr = '{$row['Arr']}'
		AND rosters.Dep = '{$row['Dep']}'";
	echo $sql;
	$result = mysqli_query($cxn,$sql)
		or die ("no can do");
	while($row=mysqli_fetch_assoc($result)) {
		echo $row['departure_lat'];
	}
}

`rosters` table =

RostersID Dep Arr SectorDate

1 BGY CRL 2016-01-22

2 CRL BGY 2016-01-22

`airports` table =

AirportsID IATA Latitude Longitude

1 BGY 48.5320 -3.2400

2 CRL 50.3400 -1.4200

January 23, 2016

Hi all,

I am trying to get information from mysql by firstly joining a couple of tables such as:

"SELECT Latitude, Longitude FROM airports INNER JOIN rosters ON airports.IATA = rosters.Dep WHERE rosters.Dep = 'BGY'";

I would also however like to select another airports.Latitude and airports.Longitude in the same query.

Something like...

"SELECT Latitude AS departure_lat, Longitude AS depatrure_lng FROM airports INNER JOIN rosters ON airports.IATA = rosters.Dep WHERE rosters.Dep = 'BGY'

AND

SELECT airports.Latitude AS arrival_lat, airports.Longitude AS arrival_lng FROM airports INNER JOIN rosters ON airports.IATA = rosters.Arr WHERE rosters.Arr = 'CRL'";

So basically, I am trying to get the departure and destination latitude and longitudes from an airport where departure = BGY and arrival = CRL using one query.

Thanks for any help!

January 15, 2016

no errors on the page at all showng in the display_errors or error reports.

January 15, 2016

there are not any errors being displayed on that page.

I have errors that have just popped up on others.

January 15, 2016

i added it after to see if it was echoing.

Sign In

unistake

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by unistake

Arranging data from mysql in to a table sorted by users and dates

Arranging data from mysql in to a table sorted by users and dates

Arranging data from mysql in to a table sorted by users and dates

selecting user info if 2 rows in mysql exist

selecting user info if 2 rows in mysql exist

listing multiple arrays in order

listing multiple arrays in order

listing multiple arrays in order

listing multiple arrays in order

listing multiple arrays in order

listing multiple arrays in order

retrieving mysql data with table joins using one query

retrieving mysql data with table joins using one query

retrieving mysql data with table joins using one query

retrieving mysql data with table joins using one query

retrieving mysql data with table joins using one query

retrieving mysql data with table joins using one query

retrieving mysql data with table joins using one query

retrieving mysql data with table joins using one query

retrieving mysql data with table joins using one query

retrieving mysql data with table joins using one query

retrieving mysql data with table joins using one query

foreach breakout

foreach breakout

foreach breakout

Browse

Activity

Important Information