How to insert data if I have foreign & primary key multi tables

[RomeoX]

Hi everybody

actually I need your help in fixing my code. Actually I have a library system that can be applied in university or any school and I'm stucking in a page that for loan student book. I have student table, Library table, books table, usersystem table and Bookoutonloan table as I mention it below so here in this table I have 4 foreign keys from different tables as I mentioned them before so here in the php page when I want to loan student I can't do it actually it will work but it's totally wrong so I want for example the students who are registered in the student table so in the attach php webpage I enter the student ID manual and might the number that I'm entering not in the origianal table for students for example I have 100 students registered so they will have from 1 to 100 number in their ID so if I write 500 in the loan it will accept and assign the the value to Bookoutonloan table but I want to change the way either I search by student name from stu ID number or what shall I do. Please I really need your help. Thanks

this is my SQL table

CREATE TABLE `library`.`bookoutonloan` (
`borrowid` INT( 12 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`stid` INT( 12 ) NOT NULL,
`bookid` INT( 12 ) NOT NULL,
`userid` INT( 12 ) NOT NULL,
`libraryid` INT( 12 ) NOT NULL,
`dateborrow` DATE NOT NULL ,
`datedueforreturn` DATE NOT NULL ,
`datereturned` DATE NOT NULL ,
`amountoffine` INT( 12 )
) ENGINE = MYISAM ;
ALTER TABLE bookoutonloan 
ADD FOREIGN KEY (stid) REFERENCES studentorstaff(stid);
ALTER TABLE bookoutonloan 
ADD FOREIGN KEY (bookid) REFERENCES books(bookid);
ALTER TABLE bookoutonloan 
ADD FOREIGN KEY (userid) REFERENCES usersystem(userid);
ALTER TABLE bookoutonloan 
ADD FOREIGN KEY (libraryid) REFERENCES librarybranch(libraryid);

This is the html with php code

<?php
$submit = &$_POST["submitt2"];
$studid = $_POST["studid"];
$bookid = $_POST["bookid"];
$userid = $_POST["userid"];
$libraryid = $_POST["libraryid"];
$dateofloan = $_POST["dateofloan"];
$datereturn = $_POST["datereturn"];
$amountfine = $_POST["amountfine"];

if ($submit)
{
 
 $connect = mysql_connect("localhost", "root", "");
 mysql_select_db("library"); 
 
 $queryreg = mysql_query(" INSERT INTO bookoutonloan VALUES('','$studid','$bookid','$userid', '$libraryid','$dateofloan','$datereturn','')
       ");
 die("Loaned has been successed");
  
 
 }
 else 
 echo "please fill in <b>all</b> fields!";
?>
<html>
<body>
<form name="form1" method="post" action="loanbook.php">
  <table width="37%" border="0">
    <caption>
      Loan Books to students
    </caption>
    <tr>
      <td>Student ID:</td>
      <td><input name="stuid" type="text" id="stuid" /></td>
    </tr>
    <tr>
      <td>Book ID:</td>
      <td><input name="bookid" type="text" id="bookid" /></td>
    </tr>
    <tr>
      <td>User ID:</td>
      <td><input name="userid" type="text" id="userid" /></td>
    </tr>
    <tr>
      <td>Library ID:</td>
      <td><input name="libraryid" type="text" id="libraryid" /></td>
    </tr>
    <tr>
      <td>Date of Loan:</font></strong></td>
      <td><input name="dateofloan" type="text" id="dateofloan" /></td>
    </tr>
    <tr>
      <td>Date Due return:</td>
      <td><input name="datereturn" type="text" id="datereturn" /></td>
    </tr>
    <tr>
    <td>Amount of fine:</td>
      <td><input name="amountfine" type="text" />
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><input type='submit' name='submitt2' value='Register' id='submitt2'></td>
    </tr>
  </table>
</form>
</body>
</html>

[dgreenhouse]

You've got a couple of issues right off the bat...

You should name the receiving columns in the query correctly
i.e. INSERT INTO bookoutonloan (stid,bookid,...) VALUES ($studid, $bookid, ...)

Plus, you can't have a blank at the beginning of the values to be inserted.
VALUES ('', '$studid'…
That’ll try to put a char into an integer column (the auto-increment column as a matter of fact)

Also, if the conditional on $submit is true and after the query is executed (when fixed), the code just dies, but I guess that's what you wanted.

$queryreg = mysql_query( /* your original bad query here */
  "INSERT INTO bookoutonloan VALUES (
   '','$studid','$bookid','$userid','$libraryid','$dateofloan','$datereturn','')"
);

// Falls of the face of the earth.
die("Loaned has been successed");

The code should actually redirect the visitor somewhere else and not just fall off the face of the earth.

Homework's a bear isn't it?...! :-) Do you have anyone at school to work with?

[Reminds me of writing my first program...
Add 2 + 2 on a main frame using 80 column punched cards...
With all the JCL language to setup the environment first...
Those were the days... :-)]

After you've fixed these things, thought out your logic a bit better, write some more REAL code, come back with your results.

[RomeoX]

Thanks man for your reply actually I posted the wrong code by mistake and I've just checked it now. By the way the first two quotes in insert command "" it will work fine because there is borrowid will an increment by itself so no problem it will be fine and I'm already tried it, it works fine. but my problem is I really don't know how to think for this page and as I'm in the beginning of php and I don't have anyone to work with unless you are guys my last hope to find a soulution for my code. I'll explain more actually in this page I want to loan Student a book and I have in my database that for this page I post it up that I collect all the IDs from different tables as I'm showing it here in this link http://uploadpic.org/showpic-39097/libr.png it's only simple not a complete one just to see the other tables with the primary keys.


so I have here 5 tables and I'm working now for bookoutonloan table that I have 4 foreign keys and they R IDs but the first one is borrowid is primary key for this table and I want to make this page to be easy for user either by entering student ID and get his name because you know someone students might make mistake in their ID and if I have for example 100 students so their IDs will be from 1 to 100 but I tried to make a test for this loan like entering 400 and found that it success passing and it should check the student table that if the ID really exist there or not. Same thing with book it's difficult for user to memorize the ID for all books so wither I put drop down list to see all books and also it's difficult thing to do it. Anyway hope if you can really help I really need help and I couldn't find a solution by myself please stay with me.

This is the correct PHP code

<?php

$submit = &$_POST["submitt2"];

$studid = $_POST["stuid"];
$bookid = $_POST["bookid"];
$userid = $_POST["userid"];
$libraryid = $_POST["libraryid"];
$dateofloan = $_POST["dateofloan"];
$dateduereturn = $_POST["dateofloan"];
$datereturned = $_POST["datereturn"];
$amountfine = $_POST["amountfine"];

if ($submit)
{
 
 $connect = mysql_connect("localhost", "root", "");
 mysql_select_db("library"); 
 
 $queryreg = mysql_query(" INSERT INTO bookoutonloan VALUES('','$studid','$bookid','$userid', '$libraryid','$dateofloan','$dateduereturn','$datereturned','$amountfine')
       ");
 die("Loaned has been successed! <a href='main.php'> return to Main page</a>");
  
 
 }
 else 
 echo "please fill in <b>all</b> fields!";
?>
<html>
<body>
<form name="form1" method="post" action="looaan.php">
  <table width="37%" border="0">
    <caption>
      Loan Books to students
    </caption>
    <tr>
      <td>Student ID:</td>
      <td><input name="stuid" type="text" id="stuid" /></td>
    </tr>
    <tr>
      <td>Book ID:</td>
      <td><input name="bookid" type="text" id="bookid" /></td>
    </tr>
    <tr>
      <td>User ID:</td>
      <td><input name="userid" type="text" id="userid" /></td>
    </tr>
    <tr>
      <td>Library ID:</td>
      <td><input name="libraryid" type="text" id="libraryid" /></td>
    </tr>
    <tr>
      <td>Date of Loan:</font></strong></td>
      <td><input name="dateofloan" type="text" id="dateofloan" /></td>
    </tr>
    <tr>
      <td>Date Due return:</td>
      <td><input name="datereturn" type="text" id="datereturn" /></td>
    </tr>
    <tr>
    <td>Amount of fine:</td>
      <td><input name="amountfine" type="text" />
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><input type='submit' name='submitt2' value='Register' id='submitt2'></td>
    </tr>
  </table>
</form>
</body>
</html>

[RomeoX]

I'm still waiting for answer

[RomeoX]

please guys I really need a help

[dgreenhouse]

Sorry for not being unavailble, but I've been really swamped... Won't have any bandwidth for another couple of days...

My one recommendation though is to rewrite your problem description(s) using short, logical, bullet points and only write a narrative (long description) when it's really warranted.

I realize that english is not your first language, but for those of us who weren't requred to study multiple languages in school, it will allow us to help you more efficiently.

I'd also recommend learning how to use 'use case modeling' and the basis of 'standard flow charting' to assist you in solidifying your application logic & design and to help clear up your thinking when it comes to your programming in general.

An excellent "Use Case Modelling" tool called "Case Complete" is from Serlio software.
http://www.casecomplete.com/. You can download it (only on Windows) and use it for 30 days for free during which time you can learn some new techniques. There are a number of free flow charting tools available on the web.

Sorry again, but I'm really burning the "midnight oil" at the moment.