booking system design...

[burnside]

hi,

have the following mysql tables

book
bookid slotid date carid
1 1 20060602 1
1 2 20060602... 3

mottimes
id mott slotid
1 9:00 1
2 10:00 2
3 11:00 3

am making a query that lists All mot times in a table
and puts the car that is having an mot in the correct row in the table.
the problem im having is that i want all times in mottimes to be listed regardless if there is a record in book.

I want this so that someone can choose a time from the list and add a booking for that time.

another issue i am having is getting the standard date format coverted to yyyymmdd to use in the sql query.

so far...

<% 
'conn string
motdate = Request.QueryString("date")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str 
'mot1sql
motsql1 = "SELECT mot.mott, mot.motslot, car.reg, book.bookdate, cus.fname, cus.lname, cus.pcode, cus.cusid "_
& "FROM book "_
& "RIGHT JOIN mottimes AS mot ON mot.motslot = book.slotid "_
& "INNER JOIN car ON car.carid = book.carid "_
& "INNER JOIN cus ON cus.cusid = car.cusid "_
& "WHERE mot.dayid =1 "_
& "AND mot.bayid =1 "_
& "AND book.bookdate = '[motdate]'"
'open rs
Set rsmot1 = Server.CreateObject("ADODB.Recordset")
rsmot1.CursorLocation = 3
rsmot1.Open motsql1, conn, 1, 2 
'html
<table>
<tr>
<td colspan="3" class = mfitsml>Mot Booking - Bay 1</td>
</tr>
'loop values
<%
Do While (Not rsmot1.Eof)
'rsmot1.movefirst
x_mottime1 = rsmot1("mott")
x_reg = rsmot1("reg")
x_bookdate = rsmot1("bookdate")
x_fname = rsmot1("fname")
x_lname = rsmot1("lname")
x_pcode = rsmot1("pcode")
x_cusid = rsmot1("cusid")
%>
<tr>
<td class = mfitsml><%response.write x_mottime1%></td>
<% 
'if no record mark time as free
if isnull(x_cusid) OR x_cusid = "" then %>
<td class = mfitsml>FREE</td>
<% else 
'or put in details of existing booking
%>
<td class = mfitsml><%response.write x_reg & " - " & x_fname & " " & x_lname & " " & x_pcode & x_bookdate%>
<%end if%>
</td>
<% 
'link to enter new booking
if isnull(x_cusid) OR x_cusid = "" then %>
<td class = mfitsml>
<a href=<%Response.Write "custaddmot.asp?motdate=" & Server.URLEncode(motdate)%> target="_blank" onClick="
window.open(this.href, this.target, 'height=380,width=600,status=yes,resizable=yes');
return false;">Book></a>
</td>
<%else
'if booking exists add link to change
%>
<td class = mfitsml>
<a href=<%Response.Write "custaddmot.asp?key=" & Server.URLEncode(x_cusid) & "&motdate=" & Server.URLEncode(motdate)%> target="_blank" onClick="window.open(this.href, this.target, 'height=380,width=600,status=yes,resizable=no');return false;">Change></a>
</td>
<%end if%>
</tr>
<%
'loop rs
rsmot1.MoveNext
Loop
%>
</table>

 

[Sheco]

If I were you I would try to get my SQL so that it does exactly what it is supposed to do before mixing it in with the ASP code. I find it a whole bunch easier to debug them separately...

If you want the times to be in the resultset even when there is no corresponding record in the car table then you probably want a LEFT JOIN instead of the current INNER JOIN in your SQL syntax... but this is just a guess, best to consult a mySql expert.

 

[burnside]

thanks scheco - have tried LEFT/RIGHT joins still doesnt produce the rs i need - guna try UPPER!
will try the mysql forum

 

[DotNetGnat]

can you post the output of the query you are looking for...i did not understand what result you wanted...

-DNG

 

[burnside]

thanks for your reply...

Time Booking
9:00 Free
10:00 Mr Johnson - HJ55JRX
11:00 Free
12:00 Free
13:15 Mrs Smith - HJ06TGY

the ones that say Free will be links to a page to add a record/the ones that are booked will link to page to change details

tables

book
bookid slotid date carid
1 1 20060602 1
2 2 20060602 3
3 3 20060602 2

mottimes
id mott slotid dayid bayid
1 9:00 1 1 1
2 10:00 2 1 2
3 11:00 3 1 1

car
carid makeid reg cusid
1 1 HJ55JRX 1

 

[burnside]

solution- thread436-1238094