Re: New to joins - this simple one doesn't work.
Try using '$id' :
SELECT properties.area, images.image_filename FROM properties,
images WHERE properties.reference_number=images.reference_number AND
properties.area='$id'
On Mon, 2003-12-08 at 23:18, fatblokeonbike wrote:
.
I'm new to MySQL joins with PHP and I can't get this to work - and I don't
understand *why* it won't work.
Table 1 - Countries - has fields: country
Table 2 - Properties - has fields: area, reference_number
Table 3 - Images - has fields: image_filename, reference_number
The first page goes -
$Query=SELECT country FROM countries;
$Result=mysql_db_query ($DBName, $Query, $Link);
while ($Row=mysql_fetch_array($Result))
{
print(a href=\country.php?id=$Row[country]\$Row[country]/abr);
}
The visitor, wanting to see an area, clicks on a particular country's link
and arrives in the country.php page, which contains -
$id = $HTTP_GET_VARS[country];.
$Query=SELECT properties.area, images.image_filename FROM properties,
images WHERE properties.reference_number=images.reference_number ;
This works, but it delivers the images of every country. To call the images
from just one country, I try -
$Query=SELECT properties.area, images.image_filename FROM properties,
images WHERE properties.reference_number=images.reference_number AND
properties.area=$id;
but it doesn't work - I get the usual ...not a valid MySQL result resource
I've played around with it, but I confess myself beat. I expect the
answer's terribly simple - but then, as everyone keeps telling me, so am I.
If you can help, thanks in advance.
Iain.
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
From: fatblokeonbike [mailto:[EMAIL PROTECTED] $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area=$id; but it doesn't work - I get the usual ...not a valid MySQL result resource If you can help, thanks in advance. If $id is a string and not an int, then you need to single- or double-quote it. Try this: $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area='$id'; HTH! Well, not really, I'm afraid - the plain $id takes the visitor to the next page with the correct POSTed value in the URL. Alas, alas, adding single or double quotes doesn't solve things. I've shuffled/altered names around in a (fruitless) attempt to correct things but still, clicking on the hyperlink for Spain and echoing the $Query and the $Result to screen I get - Query= SELECT properties.caption AS caption, properties.country, properties.area AS area, images.image_filename AS filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.country='Spain' Result= i.e. No Result. Boo-hoo. Anything stand out there? Let's try some debugging on your data, as the query looks fine to me. What about running the following queries right in the MySQL console? SELECT * FROM properties WHERE country='Spain'; That should give you all you need but images.image_filename. If that returns what it should, then take reference_number from the results and lookup in images using it: SELECT image_filename FROM images WHERE reference_number=[reference_number]; (where [reference_number] is from the previous result) If the first query returns nothing, then your problem is that there's no record for properties.country='Spain'. If the second returns nothing, then there's no record for images.reference_number matching Spain's reference_number in properties. Either of those things being off would result in the actual join query returning nothing. Let me know how those turn out... -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
. Dear List, My thanks to those who replied. I'm embarrassed as anything - the bloke I'm doing this thing for has now changed his tiny mind and has decided to have a set number of images per property, whereas before he was contemplating accepting a widely variable number. I can thus stick all the fields in the one table and, with that, my need for Joins goes west. While this now needs loads'v table reconstruction, at least I'm well able in that area. Thanx again for your assistance and offers of assistance. Yrs, Iain. - I keep having transmission trouble on my bike - my legs get tired www.johnstone-wheelers.co.uk The friendliest cycling club in Scotland?! And now - The Bunch! www.johnstone-wheelers.co.uk/phpBB2/index.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
From: fatblokeonbike [mailto:[EMAIL PROTECTED] . Dear List, My thanks to those who replied. I'm embarrassed as anything - the bloke I'm doing this thing for has now changed his tiny mind and has decided to have a set number of images per property, whereas before he was contemplating accepting a widely variable number. I can thus stick all the fields in the one table and, with that, my need for Joins goes west. While this now needs loads'v table reconstruction, at least I'm well able in that area. Thanx again for your assistance and offers of assistance. Damn, I was really hoping to get to the bottom of the problem. Ah well. FYI, might I suggest going with your original design anyway? If this guy changes his mind like this often, then he may very well decide, a month after launch, that he wants to go back to a variable # of images per property. In that case you'd be right back at the drawing board. If you build it that flexible from day one, though, he can change his mind to his heart's content. Anyway, good luck! -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
From: fatblokeonbike [mailto:[EMAIL PROTECTED] $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area=$id; but it doesn't work - I get the usual ...not a valid MySQL result resource I've played around with it, but I confess myself beat. I expect the answer's terribly simple - but then, as everyone keeps telling me, so am I. If you can help, thanks in advance. If $id is a string and not an int, then you need to single- or double-quote it. Try this: $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area='$id'; HTH! -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
. $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area=$id; but it doesn't work - I get the usual ...not a valid MySQL result resource If you can help, thanks in advance. If $id is a string and not an int, then you need to single- or double-quote it. Try this: $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area='$id'; HTH! Well, not really, I'm afraid - the plain $id takes the visitor to the next page with the correct POSTed value in the URL. Alas, alas, adding single or double quotes doesn't solve things. I've shuffled/altered names around in a (fruitless) attempt to correct things but still, clicking on the hyperlink for Spain and echoing the $Query and the $Result to screen I get - Query= SELECT properties.caption AS caption, properties.country, properties.area AS area, images.image_filename AS filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.country='Spain' Result= i.e. No Result. Boo-hoo. Anything stand out there? Iain. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
Hi Iain, Did you alter the tables since your original message? In the original message, you didn't have a caption column in the properties table. It might be easiest if you showed us your current table structures and partial data, like this: Countries +--+---+ | Field| Type | +--+---+ | id | integer | | country | varchar(20) | +--+---+ Partial data in Countries table +-+---+ | id| country | +-+---+ | 1 | United Kingdom| | 2 | Spain | | ... | | +-+---+ Properties ...etc... Also, I suggest you have an id (or reference_number) field in the countries table. I have found that if you don't, and you need to make a change to a country name, it can become a big mess. Bob -Original Message- From: fatblokeonbike [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 4:39 PM To: Mike Johnson Cc: [EMAIL PROTECTED] Subject: RE: New to joins - this simple one doesn't work. .. $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area=$id; but it doesn't work - I get the usual ...not a valid MySQL result resource If you can help, thanks in advance. If $id is a string and not an int, then you need to single- or double-quote it. Try this: $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area='$id'; HTH! Well, not really, I'm afraid - the plain $id takes the visitor to the next page with the correct POSTed value in the URL. Alas, alas, adding single or double quotes doesn't solve things. I've shuffled/altered names around in a (fruitless) attempt to correct things but still, clicking on the hyperlink for Spain and echoing the $Query and the $Result to screen I get - Query= SELECT properties.caption AS caption, properties.country, properties.area AS area, images.image_filename AS filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.country='Spain' Result= i.e. No Result. Boo-hoo. Anything stand out there? Iain. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
