Several questions regarding PHP and sql queries

[Eskimo887]

I am very close to finishing my website which utilises PHP coupled with a MySQL database. However, I am at the stage where I only have a few very difficuly (in my opinion) things left to do.

I think I'll just ask the questions then post the code if it is needed.

1. I have a member list, the details are taken from the member's table with a simple query. This works fine, however, I wish to add times from another table and put them into this member list as a total time.
   Query that gets the simple information, name, id etc

  

// Request details of pilots
 $result = mysql_query("SELECT pid, first_name, last_name, vatsimid FROM roster");
  if (!$result) {
   echo("<P>Error performing query: " . mysql_error() . "</P>");
   exit();  }

Query that gets the times from the other table, subtracts them from each other to get total time, and only gets those times related to each member seperately:

// Select all the pireps the pilot has filed and add up the hours
   $sql = mysql_query("SELECT pirep.pid, roster.pid, SUM(pirep.arrtime-pirep.deptime) AS 'time' FROM pirep, roster WHERE pirep.pid = roster.pid GROUP BY pirep.pid");
  if (!$sql) {
   echo("<P>Error performing query: " . mysql_error() . "</P>");
   exit(); }

This doesn't work as it only gets the times for the first member and only displays that one member on the member list.

Page located here: http://southerncrossairlines.ausvirtual.com/New%20Site/Complete/roster.php

2. I have another form where the user types in start and finish times, and I want this to be inserted into the table in a format so I can use the above code to get a correct total time. Is there a way to do this and how?

3. On this same form, users select an id number, I want some text to appear next to this option list displaying the details of that id. How do I do this?

http://southerncrossairlines.ausvirtual.com/New%20Site/Complete/pirep.php

That's all the questions I have for now, I'll post again if I think of more.

[bpat1434]

1.) You almost have it.... you're correct in joining the tables, but your queries need to be combined to something like say:

SELECT p.pid, SUM(p.arrtime-p.deptime) AS `time`,
    r.pid, r.first_name, r.last_name, r.vatsimid
FROM roster AS r
INNER JOIN pirep AS p
  ON r.pid = p.pid
GROUP BY p.pid

I think the reason you only see one entry is because of your "group by" statement. Try removing that to see what you get 😉

2.) Depending upon how they type the date and time in determines how you'll convert the date. Typically, it'd be best to have drop-downs for the date, and have them enter the time in HH:MM AM/PM format. But you can choose. If done with drop-downs, you can easily do this:

$time = strtotime($_POST['month'] . ' ' . $_POST['day'] . ', ' . $_POST['year'] . ' ' . $_POST['time']);

3.) First of all: your options will send no values since you don't specify what each options value is 🙁 Secondly, you'd need to get the information via the query, and use that as the content of the <option> tag, and the id number as the value of the <option> tag.

<option value="ID_NUMBER">INFORMATION</option>

[Eskimo887]

bpat1434 wrote:

1.) You almost have it.... you're correct in joining the tables, but your queries need to be combined to something like say:

SELECT p.pid, SUM(p.arrtime-p.deptime) AS `time`,
    r.pid, r.first_name, r.last_name, r.vatsimid
FROM roster AS r
INNER JOIN pirep AS p
  ON r.pid = p.pid
GROUP BY p.pid

I think the reason you only see one entry is because of your "group by" statement. Try removing that to see what you get 😉

This partly works, it gets each member's hours correctly, however, it only gets those members who are in the "pirep" database. What I would like it to do is even if they are not in the "pirep' database, it displays their details with "0.0" under the hours.

bpat1434 wrote:

2.) Depending upon how they type the date and time in determines how you'll convert the date. Typically, it'd be best to have drop-downs for the date, and have them enter the time in HH:MM AM/PM format. But you can choose. If done with drop-downs, you can easily do this:

$time = strtotime($_POST['month'] . ' ' . $_POST['day'] . ', ' . $_POST['year'] . ' ' . $_POST['time']);

I changed the text box to a drop down, put values on each of the options and in my code put $time = strotime($POST[departtimeh] . $POST[departtimem]; (for hours and minutes repectively). However, this puts 1152 into the database, regardless of what I put in the two drop down boxes.

bpat1434 wrote:

3.) First of all: your options will send no values since you don't specify what each options value is 🙁 Secondly, you'd need to get the information via the query, and use that as the content of the <option> tag, and the id number as the value of the <option> tag.

<option value="ID_NUMBER">INFORMATION</option>

How would I then get text to appear next to the drop down menu when the option is selected?

[Eskimo887]

Anyone?

[Blulagoon]

You could try this and see if it helps:

SELECT p.pid, SUM(p.arrtime-p.deptime) AS `time`,
    r.pid, r.first_name, r.last_name, r.vatsimid
FROM pirep AS p
JOIN roster AS r
  ON r.pid = p.pid
GROUP BY p.pid

You might also want to try it without the Group By
Blu

[bpat1434]

How would I then get text to appear next to the drop down menu when the option is selected?

AJAX technology.... some fancy javascript 😉

I changed the text box to a drop down, put values on each of the options and in my code put $time = strotime($POST[departtimeh] . $POST[departtimem]; (for hours and minutes repectively). However, this puts 1152 into the database, regardless of what I put in the two drop down boxes.

When separating hours & minutes, you need to use a colon [:] and not just concatenate the string. Plus a date wouldn't hurt 😉

This partly works, it gets each member's hours correctly, however, it only gets those members who are in the "pirep" database. What I would like it to do is even if they are not in the "pirep' database, it displays their details with "0.0" under the hours

Well, with no "WHERE" clause it shouldn't really limit it, but try this:

SELECT p.pid, SUM(p.arrtime-p.deptime) AS `time`,
    r.pid, r.first_name, r.last_name, r.vatsimid
FROM roster AS r
INNER JOIN pirep AS p
  ON r.pid = p.pid
WHERE r.pid = p.pid
  OR p.pid = NULL
GROUP BY p.pid