Combining and echoing results from different tables

[booklive]

Appreciate your help on this - I think the heat has got to me

 

I have 3 x tables:

supplier

price-tax

city 

 

I am trying to display the values any supplier [price_guide] plus all the travel costs for each supplier and echo 'N/A' if they will not travel to a city. The field is left blank in the admin where a supplier will not travel to a city. I need that empty field to display N/A on the page.

 

ie:

 

Birmingham ?1200

Cardiff ?1400

London ?1600

Scotland ?1800

Wales N/A

 

This seems to get the supplier price including travel costs when one city is selected. How do I display the values for all 36 x cities?

 

<?php

if($_GET['city'] != "")

{

/*$selecttax = "select * from price_tax where bandid=$rowartist->id and cityid=$rowmycity->id";

$restax = mysql_query($selecttax) or die(mysql_error());

if(mysql_num_rows($restax) > 0)

{

$rowtax = mysql_fetch_object($restax);

$total = number_format(($rowartist->price_guide + $rowtax->price) * 1.2,2);

echo $total;

}

else

{

echo number_format($rowartist->price_guide * 1.2,2);

}*/

echo number_format($rowartist->totalprice * 1.2,2);

}

 

//echo number_format($rowartist->totalprice * 1.2,2);

?>

 

Thanks ever so much in advance

 

 

 

[ManiacDan]

All of this code is commented out.  What query do you have already?  Show us table structures and example data/output

[booklive]

My bad - the uncommented:

 

echo number_format($rowartist->totalprice * 1.2,2);

}

else

{

echo number_format($rowartist->price_guide * 1.2,2);

}

 

Sorry I don't know how to export table structure. I hope this helps:

Table name: supplier

Fields: id, price_guide, artist_base, category

 

Table name: city

Fields: id, city_name

 

Table name: price_tax

Fields: id, bandid, cityid, price

 

I did not write this code -  I am trying to learn and understand so I can change previous developers work

 

 

This is the entire query:

<?php
			  	$selartist = "select bid from assigned_cat where cid='$rowcategory->id'";
				$resartist = mysql_query($selartist) or die(mysql_error());
				if(mysql_num_rows($resartist) > 0)
				{
					$tids ="0";
					while($rowartist = mysql_fetch_object($resartist))
					{
						$tids .= ",".$rowartist->bid;
					}
					if($_GET['city'] != "")
					{
						$selmycity = "select * from city where REPLACE(REPLACE(REPLACE(REPLACE (REPLACE (REPLACE (LOWER(cityname)  ,'&', 'and'),'?', ''),'\'',''),' ','-'),'~',''),'&','and')='".strtolower($_GET['city'])."'";
						$resmycity = mysql_query($selmycity) or die(mysql_error());
						$rowmycity = mysql_fetch_object($resmycity);

					//	echo $selartist = "select * from supplier where id in (".$tids.") and ( artist_base like '%,".$rowmycity->id."' or artist_base like '".$rowmycity->id.",%' or artist_base like '%,".$rowmycity->id.",%'  or artist_base like '".$rowmycity->id."') order by $sortorder";
			if($_GET['sortorder'] != "")
			{	
				if($_GET['sortorder'] == "priceh")
				{
					$sortorder = "order by totalprice desc";
				}

				if($_GET['sortorder'] == "pricel")
				{
					$sortorder = "order by totalprice  asc";
				}					
			}else{

			$sortorder = "order by featured desc";
			}						
							  $selartist = "select DISTINCT(supplier.id),supplier.*,(supplier.price_guide+price_tax.price) as totalprice from supplier,price_tax where supplier.id in (".$tids.") and  price_tax.bandid=supplier.id and   price_tax.cityid=".$rowmycity->id." and ( artist_base like '%,".$rowmycity->id."' or artist_base like '".$rowmycity->id.",%' or artist_base like '%,".$rowmycity->id.",%'  or artist_base like '".$rowmycity->id."')  $sortorder";


							  	//$selecttax = "select * from price_tax where bandid=$rowartist->id and cityid=$rowmycity->id";
					//echo $selartist = "select * from supplier where id in (".$tids.") and ( FIND_IN_SET(".$rowmycity->id.",artist_base) order by $sortorder";
						//$selartist1 = "select count(*) from supplier where id in (".$tids.") and ( artist_base like '%,".$rowmycity->id."' or artist_base like '".$rowmycity->id.",%' or artist_base like '%,".$rowmycity->id.",%'  or artist_base like '".$rowmycity->id."') order by $sortorder";



						//$selartist1 = "select count(*) from supplier where id in (".$tids.") and ( FIND_IN_SET(".$rowmycity->id.",artist_base) order by $sortorder";
						 $selartist1 = "select count(*) from supplier,price_tax where supplier.id in (".$tids.") and  price_tax.bandid=supplier.id and    price_tax.cityid=".$rowmycity->id." and  ( artist_base like '%,".$rowmycity->id."' or artist_base like '".$rowmycity->id.",%' or artist_base like '%,".$rowmycity->id.",%'  or artist_base like '".$rowmycity->id."') ";

					}

					else
					{
						$selartist = "select * from supplier where id in (".$tids.") order by $sortorder";
						$selartist1 = "select count(*) from supplier where id in (".$tids.") order by $sortorder";



				//echo 	$selartist = "select DISTINCT(supplier.id),supplier.*,(supplier.price_guide+price_tax.price) as totalprice from supplier,price_tax where supplier.id in (".$tids.") and  supplier.id=price_tax.bandid order by $sortorder";
					//$selartist1 = "select count(*) from supplier where id in (".$tids.") ";
}

?>

 

Thanks in advance

[xyph]

Please use the

tags around your code.

[booklive]

I know I can solve this problem so I have to start with the logic first. Please excuse me while I talk out loud. It is important to note that I am trying to make one formula that can be resolved on 1 single page for any supplier when displayed > supplierpage.php

 

Unfortunately, the db structure is particularly complicated. These are the key tables with only the relevant corresponding key rows

 

Tablename = city > id, cityname

Tablename = price_tax > id, bandid, price, cityid (cityid has the same ids as Tablename = city > id)

Tablename = supplier > id, price_guide, artist_base (artist _base has the same ids as Tablename = city > id)

 

The cities are numbered 59 - 97

 

I need to return all city names regardless of whether there is any data to output

I need to add the supplier cost (price_guide) to the corresponding travel expenses (artist_base) for only that unique supplier id and output the data regardless of whether there is any data in that field

I need to return "Not available" if a supplier will not distribute in a city id which is left blank

 

It has taken me all day trying to get my head around this so I'd be amazed if anyone can understand it as well.

 

One cheat I considered would be to display the list of 38 x cities in column 1 on the supplierspage.php:

<?php
$sql = mysql_query("SELECT * FROM city order by cityname asc");?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<?php
while($row = mysql_fetch_array($sql)){
echo "<tr><td>$row[cityname]</td>";										
}
?>
</table>

and then return the data required in column 2 with this logic:

 

Suppliers price_guide + travel costs for city a =

If travel costs for city a = 0 then show suppliers price_guide

If city a = blank then Supplier will not travel to city a

 

Suppliers price_guide + travel costs for city b =

If travel costs for city b = 0 then show suppliers price_guide

If city b = blank then Supplier will not travel to city b

 

Suppliers price_guide + travel costs for city c =

If travel costs for city c = 0 then show suppliers price_guide

If city c = blank then Supplier will not travel to city c

 

etc

 

N.B: I cannot write the code for this yet - I am hoping someone can help with my logic before I even begin to learn how to write the query.

Thank you if you made it this far

[ManiacDan]

what is in the column artist_base, and how does the supplier table relate to the other two?  You seem to think artist_base is important, but it's a long string field which doesn't have an ID in it.

[booklive]

Thanks for replying

 

The artist_base is a list of all the cities that the supplier WILL travel to. If a cityid is not included where supplier > id is the same as price_tax >bandid then the cityid is omitted from the data. These include cityids which are the same as the table named city > id. When the suppliers artist_base includes a cityid I believe it returns the data from the price_tax table where bandid = supplier id.

 

[Everything has been done via the cms admin database table. The artist_base table is essentially 38 x city fields - each with a checkbox. If the checkbox is ticked then the artist WILL travel to that city and add add the fee for that city. If no checkbox is ticked for any city then the supplier will NOT travel to that city.The end results is that the suppliers basic cost is added to each and every city and needs to be included on the supplier.php page. The complicated bit is ensuring that only the data for a unique supplier is returned after cross-referencing the other data.

 

It's ugly. Really ugly If you made it this far you deserve a prize!

[ManiacDan]

Oh, so you have a comma-delimited list of items in a database column?  That's why we're unable to help you: that's wrong.  Make a second table which links these two, then you can just use LEFT JOIN to get every piece of data you need. 

[booklive]

Doh! And I was looking at the JOIN function yesterday. I hadn't appreciated that an empty table can still be the go-between for 2 x seperate tables. Thank you!