database driven webpage-please please help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
kirti
Forum Newbie
Posts: 6
Joined: Sun Feb 19, 2006 4:46 am

database driven webpage-please please help

Post by kirti »

Pimptastic | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


hi guys! I'm a newbie. so wondering if someone could help me.

basically I've a dreamhome database which has details on the properties on rent. I've a webpage that connects to the database and it has a combo box. The combo box shows all the property nos that are being retrieved from the database using the while loop. now the combo box should allow the user to select the property no and display the other details of that property on the webpage such as 'no of rooms' 'owner' etc that is in the database. I can't figure out how to do it? can someone help me

this is the code I'm using which connects to the database, do a query and retrieves data into the combo box:

Code: Select all

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php

// connect to mySql
if (!$session = @mysql_connect("localhost", "root", "")) {
print ('Error: Could not connect to database server.<br>');
break;
}

// select the database
if (!@mysql_select_db("dreamhome")) {
print ('Error: Could not secect database.<br>');
break; 
}

$query = "select * from property_for_rent";

if (!$result = @mysql_query($query)) {
( "<P>Error doing a select: " . mysql_error() . "</P>" );
break;
} else {
print (" Number of rows read " . mysql_num_rows($result)."</br>");
$rr = mysql_num_rows($result); //this is calculating the number of rows read from database and puts it into variable rr
}

// close the connection
mysql_close($session);

?> 

<form name="form1" method="post" action="">
<select name="getPropNo" id="getPropNo">

<?php

//puts the property nos into the combo box from the database

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
print ("<option>" . $row['property_no'] . "</option>\n");
}

?>
</select>
</form>
<p> </p>
<p> </p>
</body>
</html>
the details that need to be shown on combo box user selection of property no are

Code: Select all

type________rooms_______ owner no_____________________

Rent___________________ Person/business name _____________

Address_________________ Address___________________________

Tel no_____________________________

Type of business____________________

Contact name_______________________

Managed by staff____________ Registered at branch _________________
thank you so much.


Pimptastic | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

if you post the structures of all the tables you have created to handle this application then we can help you with the join query.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

[please use php tags to help us read your code better]

Basically what you're going to want to do is this:

After they select an option from the select box and submit it, you should then process their action and query your database based on what they selected from the select box.

For example (and I have no clue how your database is set up), if they selected "dream_home_5" from the select box, you should then query your database for everything that is associated with "dream_home_5"

IE:

SELECT * FROM `property_details` WHERE `name` = 'dream_home_5'

Then, run mysql_fetch_assoc() on that query and it will put all of your details into an associative array.

You can then access each invidual field by doing something like this:

Code: Select all

<?php
echo "Rent: {$row['rent_price']}/month";
echo "Rooms: {$row['num_rooms']}";
echo "Garage: {$row['garage']}";
?>
Stuff like that.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Your options in the select box don't have any values so nothing would be posted. Here is a changed snippet.

Code: Select all

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
print ('<option value="' . $row['property_no'] . '">' . $row['property_no'] . "</option>\n");
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The index selected is submitted when no value is given.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

feyd wrote:The index selected is submitted when no value is given.
You mean the property number? I didn't know that. Something tells me that a value should still be added though. I don't know why, it's just a feeling I have.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

feyd is saying that you would anyway receive the index of the each item in the combo box when the user clicks on it because many of the people just use the index rather assigning different values to the value fields in option tags.
kirti
Forum Newbie
Posts: 6
Joined: Sun Feb 19, 2006 4:46 am

Post by kirti »

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


hi basically these are the tables created in the sql database dreamhome. this form uses fields from property_for_rent, staff, private_owner, business owner, telephone, branch i think. i'm very new to sql and php, never used it before so if you could explain me in code what i need to do. i'm very confused. please help. i used what scrotaye told me to do but i might be doing something wrong it doesn't seem to work. thanks

Code: Select all

/*================*
 * glogicaldm.sql *
 *  (mySQL)  *
 *================*/


/*===========*
 * RELATIONS *
 *===========*/


-- =========
--  BRANCH
-- =========
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE Branch;
CREATE TABLE Branch 
  (branch_no     CHARACTER(4)           PRIMARY KEY,
   street        CHARACTER VARYING(25),
   city          CHARACTER VARYING(15),
   postcode      CHARACTER(10)          UNIQUE,  -- ALTERNATE KEY
   mgr_staff_no  CHARACTER(5));      -- FOREIGN KEY  


-- ===========
--  TELEPHONE
-- ===========
DROP TABLE Telephone;
CREATE TABLE Telephone 
  (tel_no        CHARACTER VARYING(13)  PRIMARY KEY,
   branch_no     CHARACTER(4));      -- FOREIGN KEY 


-- ===========
--   STAFF
-- ===========
DROP TABLE Staff;
CREATE TABLE Staff 
  (staff_no            CHARACTER(5)           PRIMARY KEY,
   fname               CHARACTER VARYING(20),
   lname               CHARACTER VARYING(20),
   position_           CHARACTER VARYING(20),
   sex                 CHARACTER,
   dob                 DATE,
   salary              DECIMAL(9,2),
   supervisor_staff_no CHARACTER(5),       -- FOREIGN KEY
   branch_no           CHARACTER(4));      -- FOREIGN KEY


-- ===========
--  MANAGER
-- ===========
DROP TABLE Manager;
CREATE TABLE Manager 
 (staff_no       CHARACTER(5)           PRIMARY KEY, -- FOREIGN KEY
  mgr_start_date DATE,
  bonus          INTEGER);


-- ===============
--  PRIVATE OWNER
-- ===============
DROP TABLE Private_Owner;
CREATE TABLE Private_Owner 
  (owner_no      CHARACTER(5)           PRIMARY KEY,
   fname         CHARACTER VARYING(20),
   lname         CHARACTER VARYING(20),
   address       CHARACTER VARYING(50),
   tel_no        CHARACTER VARYING(13));      


-- ================
--  BUSINESS OWNER
-- ================
DROP TABLE Business_Owner;
CREATE TABLE Business_Owner 
  (owner_no      CHARACTER(5)           PRIMARY KEY,
   bname         CHARACTER VARYING(20)  UNIQUE,  -- ALTERNATE KEY
   btype         CHARACTER VARYING(30),
   contact_name  CHARACTER VARYING(30),
   address       CHARACTER VARYING(50),
   tel_no        CHARACTER VARYING(13)  UNIQUE); -- ALTERNATE KEY 


-- ====================
--  PROPERTY_FOR_RENT 
-- ====================
DROP TABLE Property_For_Rent;
CREATE TABLE Property_For_Rent 
  (property_no   CHARACTER(5)           PRIMARY KEY,
   street        CHARACTER VARYING(25),
   city          CHARACTER VARYING(15),
   postcode      CHARACTER VARYING(9),
   type          CHARACTER VARYING(15),
   rooms         INTEGER,
   rent          NUMERIC(9,2),
   owner_no      CHARACTER(5),       -- FOREIGN KEY
   staff_no      CHARACTER(5),       -- FOREIGN KEY
   branch_no     CHARACTER(4));      -- FOREIGN KEY 


-- ==========
--  VIEWING 
-- ==========
DROP TABLE Viewing;
CREATE TABLE Viewing 
  (client_no     CHARACTER(5)        NOT NULL, -- FOREIGN KEY
   property_no   CHARACTER(5)        NOT NULL, -- FOREIGN KEY
   view_date     DATE,
   comment       CHARACTER VARYING(50),           
   PRIMARY KEY (client_no, property_no));


-- =========
--  CLIENT 
-- =========
DROP TABLE Client;
CREATE TABLE Client 
  (client_no     CHARACTER(5)           PRIMARY KEY,
   fname         CHARACTER VARYING(20),
   lname         CHARACTER VARYING(20),
   tel_no        CHARACTER VARYING(13),
   pref_type     CHARACTER VARYING(15),
   max_rent      NUMERIC(9,2));      


-- ==============
--  REGISTRATION 
-- ==============
DROP TABLE Registration;
CREATE TABLE Registration  
  (client_no     CHARACTER(5)           PRIMARY KEY, -- FOREIGN KEY
   branch_no     CHARACTER(5),       -- FOREIGN KEY
   staff_no      CHARACTER(4),       -- FOREIGN KEY
   date_joined   DATE);


-- ========
--  LEASE 
-- ========
DROP TABLE Lease;
CREATE TABLE Lease  
 (lease_no       CHARACTER(8)           PRIMARY KEY,
  payment_method CHARACTER VARYING(15),
  deposit_paid   BOOL,
  rent_start     DATE,
  rent_finish    DATE,
  client_no      CHARACTER(5),       -- FOREIGN KEY
  property_no    CHARACTER(5),       -- FOREIGN KEY
  UNIQUE (property_no, rent_start),  -- ALTERNATE KEY
  UNIQUE (client_no, rent_start));   -- ALTERNATE KEY


-- =======================
--  LEASE TEMPORARY TABLE 
-- =======================
CREATE TEMPORARY TABLE Lease_Temp
AS SELECT L.*, (P.rent * 2) AS deposit, (rent_finish - rent_start) AS duration
  FROM Lease L, Property_For_Rent P
  WHERE L.property_no = P.property_no;

     
-- ===========
--  NEWSPAPER 
-- ===========
DROP TABLE Newspaper;
CREATE TABLE Newspaper 
 (newspaper_name CHARACTER VARYING(25)      PRIMARY KEY,
  address        CHARACTER VARYING(50),
  tel_no         CHARACTER VARYING(13)      UNIQUE, -- ALTERNATE KEY
  contact_name   CHARACTER VARYING(30));


-- =========
--  ADVERT 
-- =========
DROP TABLE Advert;
CREATE TABLE Advert 
 (property_no    CHARACTER(5)               PRIMARY KEY,  
                                         -- FOREIGN KEY
  newspaper_name CHARACTER VARYING(25),  -- FOREIGN KEY
  date_advert    DATE,
  cost           NUMERIC(9,2));
     



/*=======================*
 * INTEGRITY CONSTRAINTS *
 *=======================*/


-- =========
--  BRANCH
-- =========
ALTER TABLE Branch    
ADD FOREIGN KEY (mgr_staff_no) 
REFERENCES Manager(staff_no);


-- ===========
--  TELEPHONE
-- ===========
ALTER TABLE Telephone 
ADD FOREIGN KEY (branch_no) 
REFERENCES Branch(branch_no);


-- ===========
--   STAFF
-- ===========
ALTER TABLE Staff   
ADD FOREIGN KEY (supervisor_staff_no)  
REFERENCES Staff(staff_no);

ALTER TABLE Staff   
ADD FOREIGN KEY (branch_no)  
REFERENCES Branch(branch_no);


-- ===========
--  MANAGER
-- ===========
ALTER TABLE Manager   
ADD FOREIGN KEY (staff_no)  
REFERENCES Staff(staff_no);


-- ===================
--  PROPERTY_FOR_RENT 
-- ===================
ALTER TABLE Property_For_Rent 
ADD FOREIGN KEY (staff_no)  
REFERENCES Staff(staff_no) MATCH FULL;

ALTER TABLE Property_For_Rent 
ADD FOREIGN KEY (branch_no)  
REFERENCES Branch(branch_no);


-- ==========
--  VIEWING 
-- ==========
ALTER TABLE Viewing 
ADD FOREIGN KEY (client_no)  
REFERENCES Client(client_no);

ALTER TABLE Viewing 
ADD FOREIGN KEY (property_no)  
REFERENCES Property_For_Rent(property_no);


-- ==============
--  REGISTRATION 
-- ==============
ALTER TABLE Registration 
ADD FOREIGN KEY (client_no)  
REFERENCES Client(client_no);

ALTER TABLE Registration 
ADD FOREIGN KEY (branch_no)  
REFERENCES Branch(branch_no);

ALTER TABLE Registration 
ADD FOREIGN KEY (staff_no)  
REFERENCES Staff(staff_no);

   
-- ========
--  LEASE 
-- ========
ALTER TABLE Lease 
ADD FOREIGN KEY (client_no)  
REFERENCES Client(client_no);
 
ALTER TABLE Lease 
ADD FOREIGN KEY (property_no)  
REFERENCES Property_For_Rent(property_no);


-- ========
--  ADVERT 
-- ========
ALTER TABLE Advert 
ADD FOREIGN KEY (property_no)  
REFERENCES Property_For_Rent(property_no);

ALTER TABLE Advert 
ADD FOREIGN KEY (newspaper_name)  
REFERENCES Newspaper(newspaper_name);

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Just use mysql_fetch_assoc and use basic HTML to format values from the DB.

Code: Select all

select pr.type as RoomType, pr.rooms as Rooms, pr.rent as RoomRent,  pr.street as Street, pr.city as City, pr.postcode as PostCode, 

/* use bu.*, ow.* to retrieve all information about private_owner and business_owner */
ow.owner_no as OwnerId, ow.fname as OwnwerFirstName, ow.lname as OwnerLastName
bu.contact_name as ContactName,

st.fname as StaffFirstName, st.lname as staffLastName
br.street as BranchStreet, br.City as BranchCity  

from
Property_For_Rent pr
left join Private_Owner ow on
	ow.owner_no = pr.owner_no
left join Business_Owner bu on
	bu.owner_no = ow.owner_no
left join Staff st on
	st.staff_no = pr.staff_no
left join Branch br on
	br.branch_no = pr.branch_no
/*
where pr.property_no  = $propertyNumber
*/
I was so lazy to put the backticks so please do it for me...
kirti
Forum Newbie
Posts: 6
Joined: Sun Feb 19, 2006 4:46 am

Post by kirti »

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


hi thanks for reply. the joins are good but i'm very stuck with getting the user selection fom combo box to display data. if the user selects one from a list of property nos how do i get it to display all the details. i used the below code and it works but what i want is that for it to check for whatever property no is selected and then displaying the results instead of me repeating the below code for every property no. i hope you understand what i mean. i don't want to have $query3, $query4.....etc etc for each property no. instead it should automatically check the user input and select the required data from the database.

Code: Select all

$query2 = "select * from property_for_rent where property_no = 'PA14'";
$result2 = mysql_query($query2);

<?php
while ($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)) {
echo $row2['rent'];
echo $row2['rooms']; 
}
?>
thanks


feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

kirti, it would help if you started using the forums' posting features to post your code, like and .
Post Reply