millsy007

I know the schema is rubbbish, but really I need to work with what I have to get something working. Would something like: set $seat variable select $seat as check from journey where id = $id if returned check value is not null increment $seat - do query with next seat value else do update query ?

Hi agreed that ideally I would add an extra table but alot of the program has already been produced so if I could keep the table in this format it would be great. I dont have a problem displaying the records or deleting them, it is just the part when I need to assign a passenger to a seat that is tricky. Is there something I could write that would allow this functionality?

I have a coach booking program, Each Coach/Shuttle trip is made of up of journeys, on each of these passengers names are booked into a seat. For example: shuttle_id Journey_id route_id depart_dttm seat1 seat2 seat3 seat4 seat5 1 3 3 01-01-2009 Jos Jan Joe When I insert a record I want to insert the current passenger name into the first available seat, so for the above example it would be seat 4. My current insert statement works by: INSERT INTO journey (seat1) VALUES ('$name') WHERE shuttle_id=$id AND route_id=$route Obviously this is only okay if seat 1 is available, how can I make it go into the first available/blank seat column?

Thanks Sorted

I have a query that brings though a list of passenger names, is there a way in which I can set the length of each of these names to equal 10 characters. So even if I have shorter names eg "bob, demarcus, adam and scott" they would display with the shorter names filled as spaces, eg: bob |demarcus |adam |scott My Query is: (SELECT tb2.journey_dttm as departure, ('8' - tb2.occupancy) AS seats, (SELECT IF(GROUP_CONCAT(passenger_name SEPARATOR '|') IS NULL, '', GROUP_CONCAT(passenger_name ORDER BY passenger_name SEPARATOR '|')) FROM passengers WHERE journey_id = tb2.id ORDER BY passenger_name) AS passengers FROM shuttle AS tb1 LEFT JOIN journey AS tb2 ON "; ( tb1.id = tb2.shuttle_id ) LEFT JOIN trip_route AS tb3 ON ( tb2.route_id = tb3.id ) WHERE tb1.depart_dttm = '$depart_dttm' ORDER BY tb2.id ASC, tb2.route_id ASC);

Yes that does it, I was adding the order by to the wrong part, cheers

I tried: (SELECT tb1.depart_dttm as departure, tb3.route_desc as route, ('7' - tb2.occupancy) AS spaces, (SELECT IF(GROUP_CONCAT(passenger_name SEPARATOR '|') IS NULL, '', GROUP_CONCAT(passenger_name SEPARATOR '|')) FROM passengers WHERE journey_id = tb2.id ORDER BY passenger_name) AS passengers FROM shuttle AS tb1 LEFT JOIN journey AS tb2 ON ( tb1.id = tb2.shuttle_id ) LEFT JOIN trip_route AS tb3 ON ( tb2.route_id = tb3.id ) WHERE DATE(tb1.depart_dttm) = '2009-01-30' ORDER BY tb2.id ASC, tb2.route_id ASC)

Hi thanks for the posts, this is pretty much the last issue I have with my program so would preferably not like to change too much. But it sounds like there isnt an 'easy' way to order the values in the passenger_name field?

Hi, I know this is a fairly common issue, and I have tried a number of methods (adding a random number to the GET, changing the GET to POST etc.) But I still get the same issue of internet explorer working with an old recordset that the user has since updated on my page. What happens is the user inserts a record (into my coach booking form) but when the user clicks to show the schedule the changes are not reflected in internet explorer, how can I force my function to work with the latest set of data? The floowing is the function that is used to show the latest schedule: <script type="text/javascript"> <!-- //Browser Support Code function GetSchedule(){ var ajaxRequest; // The variable that makes Ajax possible! try{ // Opera 8.0+, Firefox, Safari ajaxRequest = new XMLHttpRequest(); } catch (e){ // Internet Explorer Browsers try{ ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP"); } catch (e) { try{ ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP"); } catch (e){ // Something went wrong alert("Your browser broke!"); return false; } } } // Create a function that will receive data sent from the server ajaxRequest.onreadystatechange = function(){ if(ajaxRequest.readyState == 4){ var ajaxDisplay = document.getElementById('ajaxDiv'); ajaxDisplay.innerHTML = ajaxRequest.responseText; } } var date = document.getElementById('date').value; var time = document.getElementById('time').value; // var queryString = "?date=" + date + "&time=" + time; // // ajaxRequest.open("GET", "getschedule.php" + queryString,+'&nocache = '+nocache true); // ajaxRequest.send(null); var date = document.getElementById('date').value; var time = document.getElementById('time').value; var queryString = "?date=" + date + "&time=" + time; ajaxRequest.open("GET", "getschedule.php" + queryString, true); ajaxRequest.send(null); } //--> </script>

Hi I have some results that I would like to order but I am not sure how this is done.I have a query to bring out a list of passengers on a journey. To make it clearer for the user I would like to be able to order the way in which the contents of a particular field are shown. Currently the passengers are shown in the passenger_name field in a seemingly random way: departs spaces passengers 2009-02-27 10:00:00 4 lee | rob | scott | paul 2009-02-27 10:25:00 4 lee | paul | scott |rob 2009-02-27 10:55:00 7 lee For clarity I would like to order the contents of this field alphabetically: departs spaces passengers 2009-02-27 10:00:00 4 lee | paul | rob | scott 2009-02-27 10:25:00 4 lee | paul | rob | scott 2009-02-27 10:55:00 7 lee I know there is an order by clause but I think that this just orders all the results vertically as opposed to within the actual field? This is my sql: (SELECT tb2.journey_dttm as departure, ('8' - tb2.occupancy) AS spaces, (SELECT IF(GROUP_CONCAT(passenger_name SEPARATOR '|') IS NULL, '', GROUP_CONCAT(passenger_name SEPARATOR '|')) FROM passengers WHERE journey_id = tb2.id) AS passengers FROM shuttle AS tb1 LEFT JOIN journey AS tb2 ON ( tb1.id = tb2.shuttle_id ) LEFT JOIN trip_route AS tb3 ON ( tb2.route_id = tb3.id ) WHERE tb1.depart_dttm = '$depart_dttm' ORDER BY tb2.id ASC, tb2.route_id ASC) Is there something I can add to achieve what I need?

Yeah thats perfect thanks

Thanks, but when I run this I just get the names input all without a suffix, eg bob | bob | bob ?

What I want to do is where a name already exists, create a new name to insert that is basically the name they have with a number at the end. So at first it will find a bob, as bob exists I want to Create bob1 if bob1 exists then I would try bob2 and so on... I basically want to create a unique name that I can then use for the next part of my program

I am writing a piece of code to insert names, part of the program needs to check for a duplicate name, upon there being a duplicate name I want to add a numerical suffix, and then increment this each time another duplicate is found/ or if a duplicate value has already been assigned. So far I have: $suffix = '0'; while ($check!= 'no duplicates') { $suffix ++; $query = " SELECT passenger_name FROM passengers, journey WHERE journey.shuttle_id = '$id' AND journey.id = passengers.journey_id AND passengers.passenger_name = '$name' "; $qry_result = mysql_query($query) or die(mysql_error()); $num_rows = mysql_num_rows($qry_result); if ($num_rows > 0) { while($row = mysql_fetch_array($qry_result)){ $name = $row[passenger_name];} $name .= "$suffix"; } else { $check = 'no duplicates'; } } It works, but is setting the names as bob|bob1|bob12|bob123|bob1234|bob12345|bob123456|bob1234567 as opposed to just adding the one number suffix, how could I remedy this?

Works now, error on my behalf, thanks

Hello I want to run a query to update my 'journey' table. The table contain a list of individual journeys that make up the overall trip('shuttle'). I want to add departure times for each individual journey, these are based on the overall shuttle departure time. For example for the second part of the journey I would set the journey departure time to be 45 minutes after the initial shuttle departure: UPDATE journey, shuttle SET journey_dttm = shuttle.depart_dttm *+45 Minutes* WHERE journey.shuttle_id = shuttle.id AND journey.route_id = '2' However I am not sure how I should format the set part of my query?

Hi, I added the script but the value that is shown in the time list doesnt update on the screen, do I need to add something so that the value shown is related to the newly assigned index value?

I have a page from which the user can select a date (in a text box updated via a calendar) and a time (selected from an options list) I have two links that let the user cycle through the date field. <a href="#" onclick="shiftDate(1, 'down');">Previous</a> <a href="#" onclick="shiftDate(1, 'up');">Next</a> My javascript then changes the date accordingly function shiftDate(days, up_or_down) { date = new Date(document.getElementById('date').value); if (up_or_down == 'up') { date.setDate(date.getDate() + days); } else { date.setDate(date.getDate() - days); } document.getElementById('date').value = (date.getMonth()+1)+"/"+date.getDate()+"/"+date.getFullYear(); GetSchedule(); return false; } This works fine but i need to incorporate the time option too. The time dropdown list has: <select name="time" id="time" style="width:150px"> <option>Select Time</option> <option value="9:00">Morning</option> <option value="12:00">Afternoon</option> <option value="16:00">Evening</option> <option value="22:00">Late</option> </select> What I would like to happen is that when the user clicks the 'next' it firstly updates the time, moving on one option value in the time field, and then only if the option value is <option value="22:00">Late</option> shifting/moving on 1 day. Is this possible? I cant seem to get this functionality working

Thanks Okay here is what I am thinking, is this what you meant? elseif ($depart == 1 and $arrive == 3) { $inserted .= insertjourney($name, $id, 1); $inserted .= insertjourney($name, $id, 2); //Combine String //IF string contains 'Journey Not Available' //HandleConflict($name, $id) } function insertjourney($name, $id, $route) { $query = " SELECT journey.id, occupancy FROM journey WHERE journey.shuttle_id = '$id' AND journey.route_id='$route' "; $qry_result = mysql_query($query) or die(mysql_error()); $row = mysql_fetch_array($qry_result); $journid = $row[id]; $occupancy = $row[occupancy]; if ($occupancy <= 6) { $query = " INSERT INTO passengers (journey_id, passenger_name) VALUES ('$journid', '$name') "; $qry_result = mysql_query($query) or die(mysql_error()); $query = " UPDATE journey SET occupancy=occupancy+1 WHERE journey.id = '$journid' "; $qry_result = mysql_query($query) or die(mysql_error()); $inserted = "variables input<br>"; return $inserted; } else { $inserted = "Journey Not Available"; return $inserted; } } function handleconflict($name, $id) { $query = " SELECT journey.id FROM journey WHERE journey.shuttle_id = '$id' AND journey.route_id='$route' "; $qry_result = mysql_query($query) or die(mysql_error()); $row = mysql_fetch_array($qry_result); $journid = $row[id]; $query = " UPDATE journey j, passengers p, shuttle s SET j.occupancy=j.occupancy - 1 WHERE p.journey_id = j.id AND s.id = j.shuttle_id AND s.id = '$id' AND p.passenger_name = '$name' "; $result = mysql_query($query) or die(mysql_error()); $query = " DELETE FROM passengers WHERE journey_id = '$journid' AND passenger_name = '$name' "; $result = mysql_query($query) or die(mysql_error()); }

I am writing some code to book people into a coach schedule, part of my code will check which stops/journey the person is going on and book them in accordingly. The program works by firstly seeing what their departure and destination is, for this example 1(Beach) to 3(City Center) An IF ELSE statement then captures this and passes in the route details that will make up the journey between Beach and City Center, this will be route_id 1 and route_id 2 //LINE 105 elseif ($depart == 1 and $arrive == 3) { while ( $result != "Journey Not Available" ) { $inserted .= insertjourney($name, $id, 1); //passing in first route id $inserted .= insertjourney($name, $id, 2); } } The insertjourneyFunction then checks the occupancy for each route of the journey, if there is space available the passenger is booked in, however if any of the routes are full then the function deletes the passenger record, adjust the occupancy back to what it was and sends a message saying the journey was not available. I added the while ( $result != "Journey Not Available" ) part so that as a route is not available on the journey is not available the inserting stops. However I think it could be causing me problems, when I try to insert a record the passenger is added twice on the second journey journey_id pssenger_name 421 wyne 422 wayne 421 wayne and the occupancy for both the journeys is filled up, what am I doing wrong!? function insertjourney($name, $id, $route) { $query = " SELECT journey.id, occupancy FROM journey WHERE journey.shuttle_id = '$id' AND journey.route_id='$route' "; $qry_result = mysql_query($query) or die(mysql_error()); $row = mysql_fetch_array($qry_result); $journid = $row[id]; $occupancy = $row[occupancy]; if ($occupancy <= 6) { $query = " INSERT INTO passengers (journey_id, passenger_name) VALUES ('$journid', '$name') "; $qry_result = mysql_query($query) or die(mysql_error()); $query = " UPDATE journey SET occupancy=occupancy+1 WHERE journey.id = '$journid' "; $qry_result = mysql_query($query) or die(mysql_error()); $insertinfo = "variables input<br>"; return $insertinfo; } else { $query = " UPDATE journey j INNER JOIN passengers p ON p.journey_id = j.id INNER JOIN shuttle s ON s.id = j.shuttle_id SET j.occupancy=j.occupancy-1 WHERE s.id = '$id' AND p.passenger_name = '$name' "; $result = mysql_query($query) or die(mysql_error()); $query = " DELETE FROM passengers WHERE journey_id = $journid AND passenger_name = $name' "; $result = mysql_query($query) or die(mysql_error()); $result = "Journey Not Available"; return $result; } }

Hi, I would appreciate some help, I cannot get my query to work: UPDATE journey j SET j.occupancy=j.occupancy-1 Inner Join passengers p On p.journey_id = j.id Inner Join shuttle s On s.id = j.shuttle_id WHERE s.id = '1' AND p.passenger_name = 'bill gates' It is used when a cancelation is made to reduce the occupancy for that journey to reflect that passenger cancelling, my table/field names are okay and my links are right but I get the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Inner Join journey On passengers.journey_id = journey.id' at line 3 Not sure if relevant but I have MySQL client version: 4.1.22 - Server version: 5.0.51a-community

Apologies, you mentioned the reason that using the table prefix wouldnt work. However I am unsure at to why I get Unknown column 'route_id' in 'order clause' This is unique and definitaly in the journey table?

Thanks for your help (again) unfortunately I am getting the error: MySQL ERROR: Unknown column 'route_id' in 'order clause' When I try adding the tb2 prefix to the order clause I get: MySQL ERROR: Table 'tb2' from one of the SELECTs cannot be used in global ORDER clause

I have a function that will create a html table based on the query that is passed in: { $Table = ""; //initialize table variable $Table.= "<table border='1' style=\"border-collapse: collapse;\">"; //Open HTML Table $Result = mysql_query($Query); //Execute the query if(mysql_error()) { $Table.= "<tr><td>MySQL ERROR: " . mysql_error() . "</td></tr>"; } else { //Header Row with Field Names $NumFields = mysql_num_fields($Result); $Table.= "<tr style=\"background-color: #000066; color: #FFFFFF;\">"; for ($i=0; $i < $NumFields; $i++) { $Table.= "<th>" . mysql_field_name($Result, $i) . "</th>"; } $Table.= "</tr>"; //Loop thru results $RowCt = 0; //Row Counter while($Row = mysql_fetch_assoc($Result)) { //Alternate colors for rows if($RowCt++ % 2 == 0) $Style = "background-color: #00CCCC;"; else $Style = "background-color: #0099CC;"; $Table.= "<tr style=\"$Style\">"; //Loop thru each field foreach($Row as $field => $value) { $Table.= "<td>$value</td>"; } $Table.= "</tr>"; } $Table.= "<tr style=\"background-color: #000066; color: #FFFFFF;\"><td colspan='$NumFields'>Query Returned " . mysql_num_rows($Result) . " records</td></tr>"; } $Table.= "</table>"; return $Table; } This function works but I am trying (and failing) to change the format of the output of the table. My query $query = "SELECT tb1.depart_dttm, tb2.occupancy, tb3.route_desc, "; $query .= "(SELECT IF(GROUP_CONCAT(passenger_name SEPARATOR '|') IS NULL, '', GROUP_CONCAT(passenger_name SEPARATOR '|')) "; $query .= "FROM passengers WHERE journey_id = tb2.id) AS passengers FROM coach AS tb1 LEFT JOIN journey AS tb2 ON "; $query .= "( tb1.id = tb2.coach_id ) LEFT JOIN route AS tb3 ON ( tb2.route_id = tb3.id ) "; $query .= "WHERE DATE(tb1.depart_dttm) = '2009-02-10' ORDER BY tb2.id ASC, tb2.route_id ASC;"; Returns: depart_dttm occupancy route_desc passengers 2009-02-10 09:00:00 3 hotel to airport 2009-02-10 09:00:00 3 airport to city center 2009-02-10 09:00:00 4 City Center to Downtown 2009-02-10 09:00:00 1 Downtown to Airport Tom 2009-02-10 09:00:00 0 Airport to Hotel 2009-02-10 12:00:00 0 hotel to airport 2009-02-10 12:00:00 0 airport to city center 2009-02-10 12:00:00 0 City Center to Downtown 2009-02-10 12:00:00 2 Downtown to Airport Bill|Mark 2009-02-10 12:00:00 0 Airport to Hotel 2009-02-10 16:00:00 0 hotel to airport 2009-02-10 16:00:00 0 airport to city center 2009-02-10 16:00:00 0 City Center to Downtown 2009-02-10 16:00:00 0 Downtown to Airport 2009-02-10 16:00:00 0 Airport to Hotel 2009-02-10 22:00:00 0 hotel to airport 2009-02-10 22:00:00 0 airport to city center 2009-02-10 22:00:00 0 City Center to Downtown 2009-02-10 22:00:00 0 Downtown to Airport 2009-02-10 22:00:00 0 Airport to Hotel However I would like to change the format to be: table width="100%" border="1" cellspacing="0" cellpadding="0"> <tr> <td width="20%"></td> <td width="20%">9:00</td> <td width="20%">12:00</td> <td width="20%">16:00</td> <td width="20%">22:00</td> </tr> <tr> <td>hotel to airport </td> <td>Bill | Mark</td> <td></td> <td></td> <td></td> </tr> <tr> <td>City Center to Downtown</td> <td></td> <td></td> <td>Tom</td> <td></td> </tr> <tr> <td>Downtown to Airport</td> <td></td> <td></td> <td></td> <td></td> </tr> <tr> <td>Airport to Hotel</td> <td></td> <td></td> <td></td> <td></td> </tr> </table> Because of the way the table is built using a loop in the SQLResultTable function is this possible?

Thats great thanks What would be the best way to display these results, currently I am setting each result as a variable and then outputting each of them in a table: for example: $qry_result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($qry_result)){ $name = $row[passenger_name]; } } //Build Result String $display_string = " <table width=100% border=1 cellspacing=2> <tr> <td width=32%><br></td> <td width=17% valign=top><div align=center><strong>09:00</strong></div></td> <td width=17% valign=top><div align=center><strong>12:00</strong></div></td> <td width=17% valign=top><div align=center><strong>16:00</strong></div></td> <td width=17% valign=top><div align=center><strong>22:00</strong></div></td> </tr> <tr> <td><strong>Beach Hostel to Schipoll</strong></td> <td valign=top><table width=100% border=1 cellspacing=0 cellpadding=0> <tr> <td>$name</td> Is this a sensible approach to getting the query result into a table?