manipulating data display

[franches]

hi,
i would like to ask your help about my code. i'm doing a borrowing form. what i really want to do is display the available data. i have a table workstation wherein it contains all the computer stations. i have a table borrow which contains the field ToolID, BrDate,BrFrom,Station,PIC,RtDate,RtTo,Authorized_B
y.

In my combo box i only want to display the available station. The data displayed from the workstation table will depend on the borrow table. The station will only be displayed if in the borrow table ToolID and RtDate are not null.

i really don't know what to do. i'm still on the process of learning php.

thanks in advance.

PHP:
--------------------------------------------------------------------------------

<?php
$db=mysql_connect("localhost","root");
mysql_select_db("mydatabase",$db);
$query = "SELECT * FROM workstation ORDER BY station ASC";
$result = mysql_query($query) or die(mysql_error());

echo "<select name=\"Station\" size=\"1\">";
while($row = mysql_fetch_array($result))
{
$query1 = "select * from borrow where ToolID='$ToolID' and RtDate='$RtDate' and Station='$row'";
$result1 = mysql_query($query1) or die(mysql_error());
$row1=mysql_fetch_row($result1);
if ($row1[0]==0) {
echo "<option>".$row['station'];
}
}
echo "</select>";
?>

[ergophobe]

Franches,
A warm welcome to WebmasterWorld!

Maybe I'm just being dense, but I'm having trouble understanding a few things about the way your database is set up. So I understand the situtation to be this:

- you have a set of computer stations that can be checked out and returned
- you want to know and show which stations are currently not checked out and create a dropdown that lets you chose from the available workstations.

I can't figure out how the tables are related, though. In other words, why both a Station and a ToolID in the 'borrow' table? Can you tell me what the primary keys are for the two tables and what key is used to relate them? Once we know that, it should be a piece of cake to help with the actual query.

Tom

[franches]

There are no primary key. ToolID exist because it contains the hardwarelock that the user will borrow. The Station exist because that is computer station they're going to use for the hardwarelock. hope im making sense.

2 tables(workstation,borrow). The workstation table contains all the list of computer stations and this is where the combo box list get the data, however, it also check if the certain comp. stations are being used in the borrow table. In the borrow table the station can be considered available if the ToolID and RtDate are filled.

I'm doing this so that the user will only see the available stations which they can use.

Please check again my code i've made some changes but still the combo box doesn't show any list.

<?php
$db=mysql_connect("localhost","root");
mysql_select_db("mydatabase",$db);
$tablename = "workstation";
$query = "SELECT * FROM $tablename ORDER BY station ASC";
$result = mysql_query($query) or die(mysql_error());

echo "<select name=\"Station\" size=\"1\">";
while($row = mysql_fetch_array($result))
{
$query1 = "select PIC borrow where ToolID<>'' and RtDate<>'' and Station=$row[0]";
$result1 = mysql_query($query1) or die(mysql_error());
$row1=mysql_fetch_row($result1);
if ($row1[0]==NULL) {
echo "<option>". $row[0];
}
}
echo "</select>";
?>

[ergophobe]

There are still problems with your query, but since I still don't know what your data structure is, it's a little hard to debug.
When you say this:

select PIC borrow

do you mean

SELECT PIC FROM borrow

Also, you say that you want the case where ToolID and RtDate are not null, so the where should be
WHERE ToolID NOT NULL AND RtDate NOT NULL AND Station='$row[0]'

That said, if you are relating two tables, you should define a primary key. I can't think of a reason to have two queries in this case. You should do one query on multiple tables and just get your result set. If your data is set up right, this will be much easier and better.

Tom

[franches]

This is what you will see on my database tables:
borrow:
Field name ----- Type ----- Allow nulls? ----- Key
ToolID --------- varchar(50)---Yes --------- None
BrDate --------- datetime -----Yes --------- None
BrFrom --------- varchar(50) --Yes --------- None
Station -------- varchar(50) --Yes --------- None
PIC ------------ varchar(50) --No ---------- None
RtDate --------- datetime -----Yes --------- None
RtTo ----------- varchar(50)---Yes --------- None
Authorized_By -- varchar(50)---Yes --------- None

sample borrowdata
ToolID -- BrDate ---------------- BrFrom -- Station ----- PIC --- RtDate -------------- RtTo -- Authorized
arcv03 -- 2004-09-03 00:00:00 - p126 ---- ACHILLES -- p001 -- 2004-09-03 00:00:00 - p120 -- p060
null ----- null ------------------ null ------ ALTIS ------ p126 -- null ----------------- null ----- null
arcv01 -- 2004-09-03 00:00:00 - p120 ---- APOLLO02 --- p090 -- 2004-09-03 00:00:00 - p120 -- p060

workstation:
Field name Type Allow nulls? Key

station varchar(50) No None

workstationdata:
station
ACHILLES
ALTIS
ARCGISO1
APOLLO02
APOLLO07

Kindly check my script. Please let me know if I forgot something or lack of fields on my tables. Please feel free to correct my code.

Fields on Borrow table
ToolID - contains the tool they will borrow
BrDate - date borrowed the tool
BrFrom - (borrowed from )person who assisted the borrower
Station - computer station they will use
PIC - Pin No. or ID No. of the employee
RtDate - date returned the tool
RtTo - (returned to) person who received the tool
Authorized_by - the superior of the borrower

Fields on Workstation table
station- contains the list of all stations

Before the user can borrow the tool he/she has to fill up the borrowing form first which asks for a PIN#:(ID #) and Workstation:(to be used). What I would like to do is on my borrowing form the Workstation is a combo box which will display all the stations from the Workstation table. However, I am thinking what if someone is using already the station "achilles" and another user will fill up the borrowing form there is the tendency that he/she is going to choose the station "achilles" also. To avoid this I would like to display the available station in my combo box. All transactions by the user are inserted on the borrow table. On this table I will be able to know if the station is available or not. I will only check the ToolID and RtDate if not null then no one is using the station.

Hope I made myself clear. If you still have questions please let me know so that I could explain further. Thanks for being so patient with me.

HERE'S MY CODE:borrowform.php

<html>
<body background="http:\\copernicus\rhodora\testing\lgrey116.jpeg" topMargin=100 leftMargin=50>
<?php
$db=mysql_connect("localhost","root");
mysql_select_db("mydatabase",$db);
if(isset($_POST['submit']) && isset($_POST['PIC']))
{ // Probably should be $_POST['submit'] <- I agree
$PIC=$_POST['PIC'];
//set up the query <- use COUNT, this is less resource intensive
$query ='SELECT COUNT(*) FROM staff WHERE PIC="' . $PIC . '"';
//run the query and get the number of affected rows
$result = mysql_query($query) or die('error making query: ' . mysql_error());
$row=mysql_fetch_row($result);
//if there's exactly no result, the user is not validated.
if($row[0] == 0)
{
print 'Please enter a valid ID number.';
}
else // only do this with validated PIN!
{
if(isset($Station))

$query = 'INSERT INTO borrow(PIC, Station) VALUES ("' . $PIC . '", "' . $Station . '")';
mysql_query($query) or die('error making query: ' . mysql_error()); // This does not have to be assigned to a var since it returns true/false if anything
}
}

?>
<table boarder=0><tr><td align=right>
<font size=4 face=arial color=#330066>
<form method="post" action="<?php echo $PHP_SELF?>">
Borrower: <input type="text" name="PIC" style="TEXT-TRANSFORM: uppercase"><Br><Br>
Work Station:
<?
include ("stations.inc");
?>
<br><br>
<input type="submit" name="submit" value="Finish">
<input name="Reset" type="reset" value="Cancel">
</form>
</td></tr>
</table>
<marquee align="middle" border="0" direction="left" scrolldelay="100" behavior=alternate><font color=maroon>A Web-based database application of hardware lock borrowing form</font></marquee>
</body>
</html>

code for: Stations.inc (which contains for combo box code)

<?php
$db=mysql_connect("localhost","root");
mysql_select_db("mydatabase",$db);
$tablename = "workstation";
$query = "SELECT * FROM $tablename ORDER BY station ASC";
$result = mysql_query($query) or die(mysql_error());

echo "<select name=\"Station\" size=\"1\">";
while($row = mysql_fetch_array($result))
{
$query1 = "select * from borrow where ToolID<>'' and RtDate<>'' and Station='$row[station]'";
//echo "<option>".$query1;
$result1 = mysql_query($query1) or die(mysql_error());
$row1=mysql_fetch_row($result1);
if ($row1[0]<>NULL) {
echo "<option>". $row[0];
}
}
echo "</select>";
?>

[franches]

hope someone out there would help me with my code? I would be very grateful for it.

[ergophobe]

Franches,

You may find this helpful: Basics of Extracting Data from MySQL Using PHP [webmasterworld.com]

What you want to do is this

1. Find available stations (one query)
2. Loop through your result set and list them as options.

First, the select:

$query = "SELECT workstation.station, ToolID, BrDate, BrFrom, PIC, RtDate, RtTo, Authorized_By
FROM workstation, borrow
WHERE ToolID NOT NULL AND RtDATE NOT NULL
ORDER BY station";

Now you need to loop through your result set.

$select = '<select name="stations">';
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
$stations .= '<option>' . $row['station'] . '</option>';
}

$stations .= '</select>';

That should do what you want. There are some things to note about your data though. As I read it, the 'borrow' table is a table of tools that can be borrowed. Logically, the fundamental unit of this table is a 'tool' identified by ToolID. In which case, that should be your primary key, and therefore never null.

ASSUMPTIONS
- any given tool can be at only one station at once and used by one user at once, while any station or user might have several tools in use at once (so tools to stations and tools to employee are many-to-one relationships)
- any station has at most one user and any user has at most one station (one-to-one).
- when a tool gets checked out, its due date is set to the date it's due, but the return date is set to null (because it hasn't been returned).
- when a tool is checked in, its return date is set to now().

If some users have more than one workstation or some stations are shared by more than one user, then you would need additional tables to handle the many-to-many relationships.

- pk = 'primary key' and thus indexed, unique and not null
- fk = 'foreign key'

table tools

Field name ----- Type ----- Allow nulls?
ToolID (pk)----- varchar(50)---No
PIC (fk)-------- varchar(50)-- Yes
BrDate --------- datetime -----Yes
BrFrom --------- varchar(50) --Yes
DueDate -------- datetime -----Yes
RtDate --------- datetime -----Yes
RtTo ----------- varchar(50)---Yes
Authorized_By -- varchar(50)---Yes

table workstation
station_id (pk)
station _name
PIC (fk)

table users
PIC (pk)
firstname
lastname

In this case, your SELECT would like this

SELECT workstation.station_id, station_name, employees.PIC, ToolID, BrDate, BrFrom, RtDate, RtTo, Authorized_By
FROM workstation, users, tools
WHERE RtDate < now()
AND tools.PIC=users.PIC AND users.PIC=workstation.PIC
ORDER BY station

So basically, this would return all stations that have been returned. You would need to put default values of 0000-00-00 00:00:00 in the RtDate field for tools that have never been borrowed.

Tom