9 replies to this topic

[Astha]

Hi,


I've created three tables :

property (property_ID, property_name)

specification (spec_ID, spec_name)

property_spec (property_ID, spec_ID) - in this i've assigned required specification to each property

Now, I want to display all the property names (from property table) and their corresponding specification names next to each property name .

EX:

Quote

Size: 14

15

Font: Arial

Times

Where Size, Font are property names and 14, 15, arial, times are spec names.

Give me some idea on how to do this.

Thanks

[Orjan]

What you are asking for isn't the simpliest SQL, but it's not very advanced either. You achieve this by JOINing tables together ON certain conditions


This should do the work for you (untested)

SELECT p.property_name, s.spec_name 
FROM property_spec ps
JOIN property p ON p.property_ID = ps.property_ID
JOIN specification s ON s.spec_ID = ps.spec_ID

read the code, try to understand it, then give your reflections and I'll try help you understand it better (most don't understand this so easily on their own first times)

[Astha]

Hi Orjan,

The query is asking to select two field names property_name (from property table) and spec_name (from specification table) by

joining property_spec table to property table (using property_ID as a common field)

joining property_spec table to specification table (using spec_ID as a common field)

I didn't understand why did u mention, p, s, and ps .

Instead can we write it like this:

SELECT property.property_name, specification.spec_name 

FROM property_spec 

JOIN property  ON property.property_ID = property_spec.property_ID

JOIN specification  ON specification.spec_ID = proerty_spec.spec_ID

[Orjan]

those are abbreviations for table names so you don't have to write full tablenames and make the query much longer (no problem at execution, just the look of it) and sometimes harder to read.
You declare the abbreviation by add the abbr after the FROM or JOINs tablename.

[Astha]

Hi Orjan,

Tried working on the code. I'm getting the desired output, but Property Names are duplicated (times the number of entry in property_spec table). I tried using DISTINCT. But same issue.

Here's my code:

<?php
include "Connections/database_connect.php"; 

$query="SELECT DISTINCT p.property_name, s.spec_name 
FROM property_spec ps
JOIN property p ON p.property_ID = ps.property_ID
JOIN specification s ON s.spec_ID = ps.spec_ID";
$result=mysql_query($query);

while($noticia = mysql_fetch_array($result)) { 
echo  "<BR>"."$noticia[property_name]".": 	 ";


echo "<select name='design' ><option value=''>Select one</option>";
echo "<option selected value='$noticia[spec_ID]'>$noticia[spec_name]</option>"."<BR>";
echo "</select>"."<BR>"."<BR>";



}
?>

[Orjan]

Ah, I think it might be a better idea to select from property, join property_spec and then join specification...

[Astha]

Is this correct?

SELECT DISTINCT p.property_name, s. spec_name

FROM property p

JOIN property_spec ps ON ps.property_ID = p.property_ID

JOIN specification s ON s.spec_ID = ps.spec_ID

[Astha]

Hello Orjan,

How are you? Hope you are fine. Just wondering because you didn't reply to my last thread.

I tried all options, interchanging tables but didn't found a solution. Can you please help me?


Thanks,
Astha

[Orjan]

I'm doing fine, but haven't had the time recently. not now either really. I wonder if we should take my first suggestion, and make it a from, right join, left join in that order....

[Astha]

Hello Orjan,

I've modified the code slightly, by adding more statements. Now spec names are properly displayed, but only property names are not displayed.(Check attached screenshot.)

Here is my code:

<?php

include "Connections/database_connect.php"; 


$query="SELECT * FROM property_spec ps 

JOIN property p ON p.property_ID = ps.property_ID 

JOIN specification s ON s.spec_ID = ps.spec_ID";


//p.property_name, s.spec_name 

//FROM property_spec ps

//JOIN property p ON p.property_ID = ps.property_ID

//JOIN specification s ON s.spec_ID = ps.spec_ID";

$result=mysql_query($query);


$flag=0;

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


if($flag==0) 

{	//if flag is 0, make $t=property_id and print it and make flag=1


	$t=$noticia[property_ID];

	echo  "<BR>"."$noticia[property_name]".": 	 ";

	$flag=1;

}


if($t!=$noticia[property_ID])

{	//this condition will be false for same property id records

	//will be true if product id chnges in next record

	$flag=0;

	$t=$noticia[property_ID];

	echo  "<BR>"."$noticia[property_name]".": 	 ";

	$flag=1;

}

?>

<FORM name=spec>


<?php

echo "<INPUT TYPE=CHECKBOX name='design' value='$noticia[spec_ID]'>$noticia[spec_name]"."<BR>";

}

?>

</FORM>

Attached Files

•  SC.png   4.93K   31 downloads