1,105,534 Community Members

Help with php and mysql

Member Avatar
stevenbeatsmith
Newbie Poster
6 posts since Mar 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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??

Member Avatar
jkon
Posting Pro in Training
444 posts since Jan 2009
Reputation Points: 110 [?]
Q&As Helped to Solve: 74 [?]
Skill Endorsements: 6 [?]
 
0
 

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.

Member Avatar
subrata_ushasi
Junior Poster
115 posts since Aug 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 6 [?]
Skill Endorsements: 0 [?]
 
0
 

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.....

Member Avatar
stevenbeatsmith
Newbie Poster
6 posts since Mar 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
stevenbeatsmith
Newbie Poster
6 posts since Mar 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
jkon
Posting Pro in Training
444 posts since Jan 2009
Reputation Points: 110 [?]
Q&As Helped to Solve: 74 [?]
Skill Endorsements: 6 [?]
 
1
 

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…

Member Avatar
stevenbeatsmith
Newbie Poster
6 posts since Mar 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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....

Question Answered as of 2 Years Ago by jkon and subrata_ushasi
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: