SELECT im.filelocation, im.description, im.width, im.height,
tbreeding.dow, tbreeding.rd, tbreeding.no_male_puppies, tbreeding.no_female_puppies, tbreeding.male_available, tbreeding.female_available,
sire.callname AS sire_callname,
dam.callname AS dam_callname,
sire.regname AS sire_regname,
dam.regname AS dam_regname
FROM `tblimages` im,
`tblbreeding` tbreeding
LEFT JOIN `tbldogs` sire ON sire.dog_id = tbreeding.sireFK
LEFT JOIN `tbldogs` dam ON dam.dog_id = tbreeding.damFK
LEFT JOIN `tbldogs` dogid ON dogid.dog_id = im.dogfk
WHERE breedFK = '87'
LIMIT 0 , 1000
Is it because ’tblimages’ im statement is before the ’tblbreeding tbreeding statement??
Please help... I get the following error in my db...
#1054 - Unknown column 'im.dogfk' in 'on clause'
The important thing is not to stop questioning. Curiosity has its own reason for existing. One cannot help but be in awe when he contemplates the mysteries of eternity, of life, of the marvelous structure of reality. It is enough if one tries merely to comprehend a little of this mystery every day. Never lose a holy curiosity.
-Albert Einstein (1879 - 1955)
Apache/2.2.11 (Unix) mod_ssl/2.2.11 OpenSSL/0.9.8e-fips-rhel5 DAV/2 mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635
perhaps not im.dogfk but im.dogFK
#1054 - Unknown column ’im.dogFK’ in ’on clause’
When I use im.dogFK
The important thing is not to stop questioning. Curiosity has its own reason for existing. One cannot help but be in awe when he contemplates the mysteries of eternity, of life, of the marvelous structure of reality. It is enough if one tries merely to comprehend a little of this mystery every day. Never lose a holy curiosity.
-Albert Einstein (1879 - 1955)
Apache/2.2.11 (Unix) mod_ssl/2.2.11 OpenSSL/0.9.8e-fips-rhel5 DAV/2 mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635
maybe something like that is it what you need?
SELECT
sireim.filelocation as sireim_filelocation,
sireim.description as sireim_desc,
sireim.width as sireim_width,
sireim.height as sireim_height,
damim.filelocation as damim_filelocation,
damim.description as damim_desc,
damim.width as damim_width,
damim.height as damim_height,
tbreeding.dow, tbreeding.rd, tbreeding.no_male_puppies, tbreeding.no_female_puppies, tbreeding.male_available, tbreeding.female_available,
sire.callname AS sire_callname,
dam.callname AS dam_callname,
sire.regname AS sire_regname,
dam.regname AS dam_regname
FROM `tblbreeding` tbreeding
LEFT JOIN `tbldogs` sire ON sire.dog_id = tbreeding.sireFK
LEFT JOIN `tbldogs` dam ON dam.dog_id = tbreeding.damFK
LEFT JOIN `tblimages` sireim, ON sireim.dogFK = sire.dog_id
LEFT JOIN `tblimages` damim, ON dameim.dogFK = dam.dog_id
WHERE breedFK = '87'
LIMIT 0 , 1000
Thanks for the help....
My query looks like this now
SELECT sireim.filelocation AS sireim_filelocation, sireim.description AS sireim_desc, sireim.width AS sireim_width, sireim.height AS sireim_height, damim.filelocation AS damim_filelocation, damim.description AS damim_desc, damim.width AS damim_width, damim.height AS damim_height, tbreeding.dow, tbreeding.rd, tbreeding.no_male_puppies, tbreeding.no_female_puppies, tbreeding.male_available, tbreeding.female_available, sire.callname AS sire_callname, dam.callname AS dam_callname, sire.regname AS sire_regname, dam.regname AS dam_regname
FROM `tblbreeding` tbreeding
LEFT JOIN `tbldogs` sire ON sire.dog_id = tbreeding.sireFK
LEFT JOIN `tbldogs` dam ON dam.dog_id = tbreeding.damFK
LEFT JOIN `tblimages` sireim ON sireim.dogFK = sire.dog_id
LEFT JOIN `tblimages` damim ON damim.dogFK = dam.dog_id
LEFT JOIN `tblimages` usepicim ON usepicim.use_pict = 'Y'
WHERE breedFK = '87'
and should return 2 rows.
But the result set being returned is 710 rows!!! lol
The important thing is not to stop questioning. Curiosity has its own reason for existing. One cannot help but be in awe when he contemplates the mysteries of eternity, of life, of the marvelous structure of reality. It is enough if one tries merely to comprehend a little of this mystery every day. Never lose a holy curiosity.
-Albert Einstein (1879 - 1955)
Apache/2.2.11 (Unix) mod_ssl/2.2.11 OpenSSL/0.9.8e-fips-rhel5 DAV/2 mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635
Do an INNER JOIN rather than a LEFT JOIN.
shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect |
github |
splittingred.com
Nope... Still get 710 rows being returned.
The important thing is not to stop questioning. Curiosity has its own reason for existing. One cannot help but be in awe when he contemplates the mysteries of eternity, of life, of the marvelous structure of reality. It is enough if one tries merely to comprehend a little of this mystery every day. Never lose a holy curiosity.
-Albert Einstein (1879 - 1955)
Apache/2.2.11 (Unix) mod_ssl/2.2.11 OpenSSL/0.9.8e-fips-rhel5 DAV/2 mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635
do you have only one image per dog or are there more than one?
If you have only one, why not storing the image data directly in the tbldogs-table?
If you have more than one image, I think you have to collect them nested in a loop for each row
I have more that one image per dog. I also have a pedigree per dog, that will need displaying. I though I could just modify the sql adding columns and changing the where clause to fit my needs as they changed.
In the images table I have a column ’use_pict’ storing a enum value (’Y’,’N’) I thought I could just use that column to filter out all the other images.
The important thing is not to stop questioning. Curiosity has its own reason for existing. One cannot help but be in awe when he contemplates the mysteries of eternity, of life, of the marvelous structure of reality. It is enough if one tries merely to comprehend a little of this mystery every day. Never lose a holy curiosity.
-Albert Einstein (1879 - 1955)
Apache/2.2.11 (Unix) mod_ssl/2.2.11 OpenSSL/0.9.8e-fips-rhel5 DAV/2 mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635
LOL Now I am getting a parse error... Not remembering my changes to the query from last night.
my current snippet follows
<?php
$rs='SELECT sireim.filelocation AS sireim_filelocation,
sireim.description AS sireim_desc,
sireim.width AS sireim_width,
sireim.height AS sireim_height,
damim.filelocation AS damim_filelocation,
damim.description AS damim_desc,
damim.width AS damim_width,
damim.height AS damim_height,
tbreeding.dow,
tbreeding.rd,
tbreeding.no_male_puppies,
tbreeding.no_female_puppies,
tbreeding.male_available,
tbreeding.female_available,
sire.callname AS sire_callname,
dam.callname AS dam_callname,
sire.regname AS sire_regname,
dam.regname AS dam_regname
FROM `tblbreeding` tbreeding
LEFT JOIN `tbldogs` sire ON sire.dog_id = tbreeding.sireFK
LEFT JOIN `tbldogs` dam ON dam.dog_id = tbreeding.damFK
LEFT JOIN `tblimages` sireim ON sireim.dogFK = sire.dog_id
LEFT JOIN `tblimages` damim ON damim.dogFK = dam.dog_id
LEFT JOIN `tblimages` usepicim ON usepicim.use_pict = 'Y'
WHERE breedFK = '87'
LIMIT 0 , 1000';
$sql=$modx->db->query($rs);
$resultArray = $modx->db->makeArray($sql);
//var_dump($resultArray);//for debugging
if (count($resultArray)>0){
$output = '';
foreach($resultArray as $item){
//print_r($item);//for debugging
$params['Sire_callname']=$item['sire_callname'];
$params['Dam_callname']=$item['dam_callname'];
$params['Sire_regname']=$item['sire_regname'];
$params['Dam_regname']=$item['dam_regname'];
$params['Whelping date']=$item['dow'];
$params['Release date']=$item['rd'];
$params['Num male']=$item['no_male_puppies'];
$params['Num female']=$item['no_female_puppies'];
$params['Available males']=$item['male_available'];
$params['Available females']=$item['female_available'];
$output.=$modx->parseChunk('Golden Litters', $params, '[+', '+]');
}
}
return $output;
?>
Error is:
Parse error: parse error in E:\wamp\www\Chrys-haefen\manager\includes\document.parser.class.inc.php(770) : eval()'d code on line 25
The important thing is not to stop questioning. Curiosity has its own reason for existing. One cannot help but be in awe when he contemplates the mysteries of eternity, of life, of the marvelous structure of reality. It is enough if one tries merely to comprehend a little of this mystery every day. Never lose a holy curiosity.
-Albert Einstein (1879 - 1955)
Apache/2.2.11 (Unix) mod_ssl/2.2.11 OpenSSL/0.9.8e-fips-rhel5 DAV/2 mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635
