sql with php

[marcusth]

hey anyone help me with this plx.

i'm doing a catalogue application loaning application. Supposingly, when i click on "Submit" button to submit my loan request, it will store the pending loan request in mySQL databse with a tablewith the following attributes:

table name : loan_request
table columns : "loan_no" (primary key), "grp_no" , "grp_name" , "loan_items" , "loan_quantity" , "loan_date";

I realise that this is not a working table structure because if i'm to have multiple items of different quantities, there will be a repeat of rows to be entered to database which is wrong. I cant come up with a normalised tables with these few columns, any suggestion??

Secondly, my another question is let say in my database, i got many loan request from many groups(grp) and are coming in at anytime of the day a new request comes in, how can i make it such that i got a link in my application let say "View loan request" link, i click on it and will automatically show me all the new loan request from the different groups??

thanks a million for any advice given because my dateline for this aplication is round the corner, in fact 4weeks from now, making me panic

[Twey]

I would have, in Propel schema format (you really should use an ORM for any decent-sized project, it will make your job a heck of a lot easier):

<database name="loans">
  <table name="loan">
    <column name="loan_id" type="integer" required="true"
      primaryKey="true" autoIncrement="true" />

    <column name="group_id" type="integer" required="true" />
    <foreign-key foreignTable="group">
      <reference local="group_id" remote="group_id" />
    </foreign-key>

    <column name="date" type="date" required="true" />
  </table>

  <table name="group">
    <column name="group_id" type="integer" required="true"
      primaryKey="true" autoIncrement="true" />

    <column name="name" type="varchar" size="255"
      required="true" />
  </table>

  <table name="item">
    <column name="item_id" type="integer" required="true"
      primaryKey="true" autoIncrement="true" />

    <column name="loan_id" type="integer" required="true" />
    <foreign-key foreignTable="loan">
      <reference local="loan_id" remote="loan_id" />
    </foreign-key>

    <column name="name" type="varchar" size="255"
      required="true" />

    <column name="quantity" type="integer" required="true" />
  </table>
</database>

If there are no more details to be stored about the group, you might want to consider removing the group table entirely and just storing the group name directly in the loan table:

<database name="loans">
  <table name="loan">
    <column name="loan_id" type="integer" required="true"
      primaryKey="true" autoIncrement="true" />

    <column name="group" type="varchar" size="255"
      required="true" />

    <column name="date" type="date" required="true" />
  </table>

  <table name="item">
    <column name="item_id" type="integer" required="true"
      primaryKey="true" autoIncrement="true" />

    <column name="loan_id" type="integer" required="true" />
    <foreign-key foreignTable="loan">
      <reference local="loan_id" remote="loan_id" />
    </foreign-key>

    <column name="name" type="varchar" size="255"
      required="true" />

    <column name="quantity" type="integer" required="true" />
  </table>
</database>

[marcusth]

if i'm to seperate them up to the 3 tables as u suggest, i'm lost in linking all of the data up, cox i have to make use of the group_id to identify how much each group had equipment loaned, with all the quantities and dates accompanying it for auditing purpose.

any guidance tnx

[Twey]

I'm not sure that I understand why you're having trouble.

[marcusth]

sorry, initially i was rather confuse with it as i'm not really into SQL. But after some research i understand wad u are trying to tell me.

thanx a million Twey.


But now i face another issue which i believe is php coding issue. you c after i query with php:

........................................................................................................

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<?php
session_start();
$con = mysql_connect("localhost","root","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db(test, $con);


$query = " SELECT DISTINCT grp.group_id, grp.group_name, loan.loan_id, item.item_id , item.item_name, item.item_quantity ".
" FROM loan, grp, item ".
" WHERE loan.group_id = grp.group_id".
" AND loan.loan_id = item.loan_id";

$result =mysql_query($query);
$row = mysql_fetch_array($result);

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['group_id']. " - ". $row['group_name']. " - " . $row['loan_id']. " - ". $row['item_id']. " - ". $row['item_name']. " - ". $row['item_quantity'];
echo "<br />";
}
?>

</body>
</html>


........................................................................................................


my output is :

group_id , group_name, laon_id , item_id , item_name , quantity(This is the sequence of the output, ps: its not shown in the output)

........................................................................................................

1 - marcus - 1 - BA002 - battery marcus2 - 4
1 - marcus - 1 - BA003 - battery marcus3 - 3
2 - darren - 2 - BA001 - battery marcus - 2
2 - darren - 2 - BA005 - battery darren - 9

.......................................................................................................

so now i'm figuring how can i make it such that it appear in a nicer manner, without repeating the group_id, loan_id

any help??

[Twey]

I strongly suggest you use Propel. It will make for much neater code.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

XHTML isn't really suitable for use on most websites yet, and Transitional is ten years outdated.