Another php and sql question!...sorry

**andrewsco** · 07-12-2004, 06:24 PM

I know I have asked a lot of questions on this subject recently but just one more for the moment. The website that all this work is going towards is shore2sure and with the help of you all I have managed to accomplish a login page, connect to mysql datamase and most importantly be able to use the database to email people through a script.

I feel that the type of site mine is, and i think there is much more that I can do. I mean it is easy for me to enter every piece of information I have into my database, and I can also have a person input thier data and have it update the database. At the moment though the way I have to put a property on for a person, is they fill in an online form, and then I have to create a new web[age and do it all myself. Now as all the pages have the same layout, I was wondering if this is possible to do using mysql and perhaps php?

Another feature I would like is to be able to set up a search facility using the database that I have. I remember that celticbrue u mentioned that you have set up a website similar to mine before, is this the type of thing that I should aim for?

I think once i have cracked this then it will really help 'fill a whole' in my website and also in my persoanl learning. Sorry for asking so many questions, but i am still at uni so I am kind of used to it

Thanks

Andy

**Kyrnt** · 07-12-2004, 07:00 PM

Andy,

First, don't apologize for asking questions. Without questions, the forums would be pretty boring with just the Word Association "thread" going on.

Now, when a new user registers, you definitely want to put that information into a database -- you seem clear on that. You should not be writing new pages for every member. Use PHP to make queries to the database to retrieve the individual details on the user (after he signs in). Then display the information out of the database using one page which is smart enough to extract those details from the database.

Now, how is that done. There are a number of threads in this forum for writing information to and pulling data out of the database. I would recommend you doing some searches and reading those -- then if you have some specific implementation questions - others may have scripts ready to post.

**andrewsco** · 07-13-2004, 07:52 AM

hi

I am looking to put all my info into a database, but at the moment I am not sure the best way to go about it. At the moment I have the table 'contacts' which includes all property owners contact details. If I am to expand though, should I include another table inside my database called for example 'property' in which I can enter property address, country, etc. Can I do this?

I have managed to manipulate a code I think celticbrue used in another post:

PHP Code:


			
<html> 

<head> 

<title>My Results Page</title> 

<style type="text/css" media="screen"> 

    #header {background:#c0ffc0; font-family:arial, sans-serif; color:#215908; font-weight:bold; size:100%;} 

    #data {background:#ffffc0; font-family:arial, sans-serif; color:#215908; font-weight:bold; size:90%; text-align:center;} 

</style> 

</head> 

<body> 



<!-- Set up the table to receive the results --> 

<table width="80%" border="1" cellpadding="1" cellspacing="1" bordercolor="#215908"> 

<tr> 

<th id="header">Age</th> 

<th id="header">Home Team</th> 

<th id="header">Away Team</th> 

<th id="header">Date</th> 

</tr> 



<?php 

//Define Database Variables 

$host = "localhost"; 

$user = ""; 

$pass =""; 

$dbname = "cndjfl_fixtures"; 



//These variables would normally be passed from the search form - uncomment them to check this script on its own 

/* 

$age="%"; 

$team="%"; 

$date="%"; 

*/ 





//Connect to database 

$link = mysql_connect($host, $user, $pass) 

or die("Could not connect : " . mysql_error()); 



//Run the query 

$query = "SELECT * FROM fixtures 

WHERE age LIKE'$age' 

AND ((hometeam LIKE '$team') OR (awayteam LIKE '$team')) 

AND date LIKE '$date' ORDER BY date"; 



//Get the Result 

$result = mysql_db_query($dbname, $query, $link) or die("Query failed : " . mysql_error()); 



// adds a new line to table for each record in database 



while ($row=mysql_fetch_array($result)) { 

  print("<tr><td id=\"data\">$row[age]</td>\n<td id=\"data\">$row[hometeam]</td>\n<td id=\"data\">$row[awayteam]</td>\n<td 



id=\"data\">$row[date]</td>\n</tr>\n"); 

    } 



//Close the table 



print("</table>\n"); 



//Close the database connection 



mysql_close($link); 



?> 



<!-- close html --> 



</body> 

</html>

This when manipulated can output my contact details so I assuyme i could manipulate it to display property info when the database is set up, but instead of a table I will try entering it into my page.

Is this the right way to go about it, before I put all the wrong info in the wrong database! lol

Andy

**Kyrnt** · 07-13-2004, 03:05 PM

Andy,

Before you design any pages or write any code, you need to get the database design out of the way. To do this properly, you'll need a little knowledge of how relational databases (such as MySQL) work. You're on the right track for having one table for owners and another for properties because a single owner may own more than one property, etc. If this is a project you are really dedicated to, I would suggest you get a good text on database design and the correct way to go about it. Given the scale of what you want to do, it's probably going to be difficult to do just through the help of others on the forum. You will inevitably run into problems with code, SQL pulls, table joins, etc., so it's infinitely better to be ready to handle those situations on your own.

**celticbrue** · 07-14-2004, 08:24 PM

Hi Andy,

Sorry, I have been away from the forum for a couple of days with work and family commitments. I am also tied up for the next few days, but thought I would just stick my head through the door and let you all now I haven't forgotten you!

In relation to this question of yours, I echo what Kyrnt has said about getting to grips with PHP and MySQL by buying a half decent book. I would recommend "PHP and MySQL" by Larry Ullman, published as a Visual Quick Pro guide by Peachpit Press. ISBN is 0-321-18648-6 and you should be able to get hold of it through any decent high street book retailer.

To give you a hint on the set up of your database tables, I would anticipate something along these lines: -

Owners Table to include such things as owner_id, name, address, phone number, email address etc. owner_id would be the primary key and unique.

Property Table to include property_id, description, type, photograph(s) etc.
In order for you to cross reference the owner with the property, you would need to include the owner_id as a foreign key in the Property table.

Locations table, to show just a list of locations, such as USA, GB, France etc, with location_id being set as the primary and unique key.

This location_id key could then be incorporated in the Property table as another foreign key, so that the property can be cross referenced with the location.

Hope this is not too late and has set you on the right lines

Regards

Ian.

**andrewsco** · 07-15-2004, 06:16 AM

Hi. Thanks Ian, thats very helpful.

I think I will buy a book as I am in a little over my head, and doing some research on this cant hurt my chances of getting a better mark next year at uni!

Thanks

Andy

**Republikin** · 07-15-2004, 06:53 AM

Once you have the basics of php and mysql down I suggest going over to hotscripts and following some of the tutorials for php that can be found there. This way you can learn practical ways to do things.

**david** · 07-15-2004, 07:54 AM

What you're looking to do is certainly possible and with some basic PHP/MySQL knowledge you should get on fine. As Kyrnt says, make sure you get your database design right. It will make things far easier later.

You might want to take a look here:
http://www.freewebmasterhelp.com/tutorials/phpmysql/
that should give you enough knowledge to create most of what you want to do.