Help with php and mysql

[stevenbeatsmith]

I use dreamweaver, PHP and MYSQL.

I need some help and any is appreciated, I have alot of knowledge in html learnt from some college, little in php and none in mysql, I am self teaching php and mysql, so forgive me if the question is too easy. :)

Ok so I have a database with many tables, " Table A that is the main table which has some details in it (long_description, short_description, type ), a primary key and 4 foreign keys ( table B ID, Table C ID, Table D ID and Table E ID).

I have managed to display the data with all the tables joined to create 1 record.

but now I am stuck at trying to create a search, using a URL parameter passed between 2 pages using the get method.
The search page has a form, a drop down list populated by table B and the submit button. The URL parameter gets passed on with no problem, and I even see it on the url when I preview and test it.
I am trying to filter the results so that they show records that are related to a particular ID in table B and I have no Idea what syntax to use in the sql.

I have managed to get up to showing results of table A but I have no Idea on how to join the other tables to the results.

Hope that makes sense,

Can anyone Help??

[jkon]

To be honest I didn’t understood what you are asking. First you say

I have managed to display the data with all the tables joined to create 1 record.

And then,

I have managed to get up to showing results of table A but I have no Idea on how to join the other tables to the results.

The easy answer would be with a where clause, but you have to provide the query in order to help you more on that.

[subrata_ushasi]

In index.php page is

<td><select id="[B]mailto[/B]" name="[B]mailto[/B]" class="InputUserBg" >
			<option id="0" value="" class="UserNameText" selected="selected" >Select Topic</option></select

In the process.php page write this

$topic=$_REQUEST['mailto'];
$sql="select * from mytable where email='$topic'";

or update code etc.....

[stevenbeatsmith]

thanks for the reply jkon, you can ignore the "I have managed to display the data with all the tables joined to create 1 record." that is from a different part of the site, which excludes the url parameter and in there I used the where clause and the code for that is below.

see below

"SELECT property_listings_db.property_listing_title, property_listings_db.short_description, ireland_states_db.state_name, listing_type.type_name, listing_images_db.proimagethumbFilename, city_db.city_name, property_listings_db.propertydb_ID FROM property_listings_db, ireland_states_db, listing_type, listing_images_db, city_db WHERE property_listings_db.city_ID = city_db.city_ID AND city_db.state_ID = ireland_states_db.state_ID AND property_listings_db.type_ID = listing_type.type_ID AND property_listings_db.propertydb_ID = listing_images_db.propertydb_ID ORDER BY property_listings_db.propertydb_ID DESC"

the url parameter being passed is state_ID and it looks something like this
estate.com/properties/property_listings.php?state_ID=1&Submit=Search

since the sql statement above worked without the url paramerter, is ther a way to filter it using the url parameter, i.e to only show results of state_ID=1? hope that makes sence,

I had tried to use colname in the where clause (of the statement above

(AND city_db.state_ID = ireland_states_db.state_ID = state_ID = %s

)) but it gave me an error, "State_ID where clause is ambiguous "

the below statement works using the URL parameter but leaves out all the other 4 tables. and only shows the property_listings_db table.

"SELECT propertydb_ID, property_listing_title, bathrooms, bedrooms, state_ID, city_ID, property_listing_date, type_ID, property_listing_price, long_description, short_description FROM property_listings_db WHERE state_ID = %s ORDER BY property_owner_ID DESC"

hope that explains alot.

[stevenbeatsmith]

In index.php page is

<td><select id="[B]mailto[/B]" name="[B]mailto[/B]" class="InputUserBg" >
			<option id="0" value="" class="UserNameText" selected="selected" >Select Topic</option></select

In the process.php page write this

$topic=$_REQUEST['mailto'];
$sql="select * from mytable where email='$topic'";

or update code etc.....

Thanks Subrata Ushasi, but still no luck.. maybe if you can explain more ( as if you are explaining to a 3 year old

[jkon]

As you said in the last statement state_ID is in property_listings_db so the query is…

SELECT a.state_IDa.property_listing_title, a.short_description, b.state_name, c.type_name, d.proimagethumbFilename, e.city_name, a.propertydb_ID
FROM property_listings_db AS a, ireland_states_db AS b, listing_type AS c, listing_images_db AS d, city_db AS e
WHERE a.state_ID = ?
AND a.city_ID = e.city_ID
AND e.state_ID = b.state_ID
AND a.type_ID = c.type_ID
AND a.propertydb_ID = d.propertydb_ID
ORDER BY a.propertydb_ID DESC

But… if what you are trying is listing all the “properties” for a state the above could be …

SELECT a.property_listing_title, a.short_description, b.state_name, c.type_name, d.proimagethumbFilename, e.city_name, a.propertydb_ID
FROM property_listings_db AS a, ireland_states_db AS b, listing_type AS c, listing_images_db AS d, city_db AS e
WHERE b.state_ID = ? 
AND e.state_ID = b.state_ID
AND a.state_ID = b.state_ID
AND a.type_ID = c.type_ID
AND a.propertydb_ID = d.propertydb_ID
ORDER BY a.propertydb_ID DESC

The all data structure looks more complicated than what is needed. Also if a city has a state_ID then why a property has also a city_ID and a state_ID, having the same data more than once probably means that you could use database normalization (http://en.wikipedia.org/wiki/Database_normalization) . Also there are many views in naming conventions, but wouldn’t harm if the tables were just properties,states,cities and so on, also if their primary key is an ID , that could be its name instead of state_ID , that it is a state you know it by the table.

These are only some thoughts that I hope to help you … don’t stick to those … the important thing is that you are trying and I am sure that this way you will find your way…

[stevenbeatsmith]

As you said in the last statement state_ID is in property_listings_db so the query is…

SELECT a.state_IDa.property_listing_title, a.short_description, b.state_name, c.type_name, d.proimagethumbFilename, e.city_name, a.propertydb_ID
FROM property_listings_db AS a, ireland_states_db AS b, listing_type AS c, listing_images_db AS d, city_db AS e
WHERE a.state_ID = ?
AND a.city_ID = e.city_ID
AND e.state_ID = b.state_ID
AND a.type_ID = c.type_ID
AND a.propertydb_ID = d.propertydb_ID
ORDER BY a.propertydb_ID DESC

But… if what you are trying is listing all the “properties” for a state the above could be …

SELECT a.property_listing_title, a.short_description, b.state_name, c.type_name, d.proimagethumbFilename, e.city_name, a.propertydb_ID
FROM property_listings_db AS a, ireland_states_db AS b, listing_type AS c, listing_images_db AS d, city_db AS e
WHERE b.state_ID = ? 
AND e.state_ID = b.state_ID
AND a.state_ID = b.state_ID
AND a.type_ID = c.type_ID
AND a.propertydb_ID = d.propertydb_ID
ORDER BY a.propertydb_ID DESC

The all data structure looks more complicated than what is needed. Also if a city has a state_ID then why a property has also a city_ID and a state_ID, having the same data more than once probably means that you could use database normalization (http://en.wikipedia.org/wiki/Database_normalization) . Also there are many views in naming conventions, but wouldn’t harm if the tables were just properties,states,cities and so on, also if their primary key is an ID , that could be its name instead of state_ID , that it is a state you know it by the table.

These are only some thoughts that I hope to help you … don’t stick to those … the important thing is that you are trying and I am sure that this way you will find your way…

OH MY !!!!! THANNNKKK YOOUUUUU A BILLION... if I could I would kiss you right now but unfortunately it is against forum regulations... I tried what you said above and it works and I have learnt new things, AS statement and giving them letters or numbers, my knowledge grows,

Thanks Again Jkon....