Tommy25
05-09-2005, 07:09 AM
Hi,
I am building a 'Room Request' website using PHP and MySQL. I've built several tables in mySQL and am trying to use PHP and SQL queries to manipulate and use the data in these tables.
the main table is the 'Request' table. It holds the foreign key fields from the Room and Module tables.
Room table>> RoomID, RoomNumber ...
Module table>> ModuleID, ModuleCode, Department
Request table>>RequestID, RoomID, ModuleID
I'm trying to add a record to the Request table. But for usability reasons I can't have a form asking the potential user to enter in the Room ID and Module ID fields as they won't be familiar with these.
I am trying to build a code where the user is given a Request form which prompts them to enter in:
Request ID: "auto"
RoomNumber: " "
ModuleCode: " "
and these will identify with their respective primary key field values - and enter these into the new record in the 'Request' table.
e.g. the user enters 'RoomNumber': "xx22" and 'ModuleCode' "W100"
the code then checks these tables and finds the ID of each of these is 20 and 110 respectively and enters these values into the 'RoomID' and 'ModuleID' fields in the Request table, as part of a new record.
PROBLEM A
Here's my code so far (I guess you must have got it by now that it does n't work!)
<?php
//construct variables to enter request details into
$RoomNo=$_POST['RoomNo'];
$ModuleCode=$_POST['ModuleCode'];
$Duration=$_POST['Duration'];
$Semester=$_POST['Semester'];
// Connect to the database
$MySQL_hostname = "***";
$MySQL_username = "***";
$MySQL_password = "***";
$db_name = "***";
$connection = @mysql_connect($MySQL_hostname,$MySQL_username,
$MySQL_password) or die("Couldn't connect");
$db = @mysql_select_db($db_name, $connection)
or die("Couldn't select database");
//use SQL query to add a new request record
$sql = "INSERT INTO request (RequestID, RoomNo, ModuleCode, Duration, Semester) VALUES (\"\", \"$RoomNo\", \"$ModuleCode\", \"$Duration\", \"$Semester\")";
$result = mysql_query($sql,$connection) or die("could not insert record");
$RequestID = mysql_insert_id();
//use SQL query to link foreign keys with the entered data.
$sql = 'SELECT Request.RequestID, Room.RoomNo, Module.ModuleCode,
. ' FROM ((Module'
. ' INNER JOIN Request ON Request.ModuleID = Module.ModuleID)'
. ' INNER JOIN Room ON Request.RoomID = Room.RoomID)' LIMIT 0, 30';
$result = mysql_query($sql,$connection) or die("could not insert record");
mysql_close();
?>
//create the form using HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content= "text/html; charset=iso-8859-1">
<title>COB292: 1.php</title>
</head>
<body>
<form action="insert.php" method="post">
RoomNo: <input type="varchar" name="RoomNo"><br>
ModuleCode: <input type="varchar" name="ModuleCode"><br>
Duration: <input type="time" name="Duration"><br>
Semester: <input type="int" name="semester"><br>
<input type="Submit">
</form>
</body>
</html>
This does not work. When activated it only shows 'Could not insert record' which tells me there a problem with the first sql query (insert into).
PROBLEM B
I decided to simplify it - so I decided to enter the actual foreign keys as the fields. i.e have the form like this:
Request ID: "auto"
Room ID: " "
Module ID: " "
This is the code for this: (it provides me with a form, but after submission says 'Could not insert record'. When I check the 'Request' table there is a new record but with RoomID and ModuleID etc as nulls/blank
<?php
//construct variables to enter request details into
$RoomID=$_POST['RoomID'];
$ModuleID=$_POST['ModuleID'];
$Duration=$_POST['Duration'];
$Semester=$_POST['Semester'];
//connect to the database
$MySQL_hostname = "***";
$MySQL_username = "***";
$MySQL_password = "***";
$db_name = "***";
$connection = @mysql_connect($MySQL_hostname,$MySQL_username,
$MySQL_password) or die("Couldn't connect");
$db01 = @mysql_select_db($db_name, $connection)
or die("Couldn't select database");
//adding request record
$sql = "INSERT INTO Request (RequestID, RoomID, ModuleID, Duration, Semester) VALUES (\"\", \"$RoomID\", \"$ModuleID\", \"$Duration\", \"$Semester\")";
$result = mysql_query($sql,$connection) or die("could not insert record");
$RequestID = mysql_insert_id();
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content= "text/html; charset=iso-8859-1">
<title>COB292: 1.php</title>
</head>
<body>
<form action="insert.php" method="post">
RoomID: <input type="varchar" name="RoomID"><br>
ModuleID: <input type="varchar" name="ModuleID"><br>
Duration: <input type="time" name="Duration"><br>
Semester: <input type="int" name="semester"><br>
<input type="Submit">
</form>
</body>
</html>
sorry about this being very long. Would be very appreciative of any help.
Thanks
I am building a 'Room Request' website using PHP and MySQL. I've built several tables in mySQL and am trying to use PHP and SQL queries to manipulate and use the data in these tables.
the main table is the 'Request' table. It holds the foreign key fields from the Room and Module tables.
Room table>> RoomID, RoomNumber ...
Module table>> ModuleID, ModuleCode, Department
Request table>>RequestID, RoomID, ModuleID
I'm trying to add a record to the Request table. But for usability reasons I can't have a form asking the potential user to enter in the Room ID and Module ID fields as they won't be familiar with these.
I am trying to build a code where the user is given a Request form which prompts them to enter in:
Request ID: "auto"
RoomNumber: " "
ModuleCode: " "
and these will identify with their respective primary key field values - and enter these into the new record in the 'Request' table.
e.g. the user enters 'RoomNumber': "xx22" and 'ModuleCode' "W100"
the code then checks these tables and finds the ID of each of these is 20 and 110 respectively and enters these values into the 'RoomID' and 'ModuleID' fields in the Request table, as part of a new record.
PROBLEM A
Here's my code so far (I guess you must have got it by now that it does n't work!)
<?php
//construct variables to enter request details into
$RoomNo=$_POST['RoomNo'];
$ModuleCode=$_POST['ModuleCode'];
$Duration=$_POST['Duration'];
$Semester=$_POST['Semester'];
// Connect to the database
$MySQL_hostname = "***";
$MySQL_username = "***";
$MySQL_password = "***";
$db_name = "***";
$connection = @mysql_connect($MySQL_hostname,$MySQL_username,
$MySQL_password) or die("Couldn't connect");
$db = @mysql_select_db($db_name, $connection)
or die("Couldn't select database");
//use SQL query to add a new request record
$sql = "INSERT INTO request (RequestID, RoomNo, ModuleCode, Duration, Semester) VALUES (\"\", \"$RoomNo\", \"$ModuleCode\", \"$Duration\", \"$Semester\")";
$result = mysql_query($sql,$connection) or die("could not insert record");
$RequestID = mysql_insert_id();
//use SQL query to link foreign keys with the entered data.
$sql = 'SELECT Request.RequestID, Room.RoomNo, Module.ModuleCode,
. ' FROM ((Module'
. ' INNER JOIN Request ON Request.ModuleID = Module.ModuleID)'
. ' INNER JOIN Room ON Request.RoomID = Room.RoomID)' LIMIT 0, 30';
$result = mysql_query($sql,$connection) or die("could not insert record");
mysql_close();
?>
//create the form using HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content= "text/html; charset=iso-8859-1">
<title>COB292: 1.php</title>
</head>
<body>
<form action="insert.php" method="post">
RoomNo: <input type="varchar" name="RoomNo"><br>
ModuleCode: <input type="varchar" name="ModuleCode"><br>
Duration: <input type="time" name="Duration"><br>
Semester: <input type="int" name="semester"><br>
<input type="Submit">
</form>
</body>
</html>
This does not work. When activated it only shows 'Could not insert record' which tells me there a problem with the first sql query (insert into).
PROBLEM B
I decided to simplify it - so I decided to enter the actual foreign keys as the fields. i.e have the form like this:
Request ID: "auto"
Room ID: " "
Module ID: " "
This is the code for this: (it provides me with a form, but after submission says 'Could not insert record'. When I check the 'Request' table there is a new record but with RoomID and ModuleID etc as nulls/blank
<?php
//construct variables to enter request details into
$RoomID=$_POST['RoomID'];
$ModuleID=$_POST['ModuleID'];
$Duration=$_POST['Duration'];
$Semester=$_POST['Semester'];
//connect to the database
$MySQL_hostname = "***";
$MySQL_username = "***";
$MySQL_password = "***";
$db_name = "***";
$connection = @mysql_connect($MySQL_hostname,$MySQL_username,
$MySQL_password) or die("Couldn't connect");
$db01 = @mysql_select_db($db_name, $connection)
or die("Couldn't select database");
//adding request record
$sql = "INSERT INTO Request (RequestID, RoomID, ModuleID, Duration, Semester) VALUES (\"\", \"$RoomID\", \"$ModuleID\", \"$Duration\", \"$Semester\")";
$result = mysql_query($sql,$connection) or die("could not insert record");
$RequestID = mysql_insert_id();
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content= "text/html; charset=iso-8859-1">
<title>COB292: 1.php</title>
</head>
<body>
<form action="insert.php" method="post">
RoomID: <input type="varchar" name="RoomID"><br>
ModuleID: <input type="varchar" name="ModuleID"><br>
Duration: <input type="time" name="Duration"><br>
Semester: <input type="int" name="semester"><br>
<input type="Submit">
</form>
</body>
</html>
sorry about this being very long. Would be very appreciative of any help.
Thanks