[SOLVED] Not able to pull info from Left Join

[harkly]

I am not able to pull the info from one table.

 

 

This is my query:

$query="SELECT image.imgid, image.title, image.artid, image.date, artist.artid, artist.fullName FROM image LEFT JOIN artist ON image.artid = artist.artid WHERE image.title LIKE 'Portrait of a Young Woman'";


$result = mysql_query($query) or die(mysql_error());

$count = count(0);

while ($r=mysql_fetch_array($result))

{	

   $title=$r["title"];

   $imgid=$r["imgid"];

   $date=$r["date"];

   $artid=$r["artid"];

   $fullName=$r["fullName"];

   //displays the row

echo $count++; 
echo ". <a href='image.php?imgid=$r[imgid]'><img src=\"image/$imgid\" border=0 ></a>, <i>".$title."</i>, ".$date.", by ".$fullName."<br>";

 

Everything prints out fine but the $fullName and the $artid. I believe I am using the correct Join. Can anyone send me in the correct direction?

 

[corbin]

Looks like there's not a matching dataset in the artist table for that artist id....

[harkly]

There is an artid for the artist, it seems as though it isn't pull any info from the artist table. Is my code incorrect?

[xtopolis]

Corbin was referring to the condition in the statement:

WHERE image.title LIKE 'Portrait of a Young Woman'

 

Is there an artist with an image.title like that?

[corbin]

Also, this probably isn't the problem, but

 

image.artid, image.date, artist.artid

 

 

 

artid and artid will clash.  So, either select only one of them (should be the same value after all) or use an alias.

 

 

Example:

 

image.artid, image.date, artist.artid as artistartid

[harkly]

Maybe I'm confused then on setting up my tables.

 

I am using a table called image and one called artist. And I have the artid as the primary in the artist table and referenced in the image table. I want to be able to pull up an image and then pull the artist name from the artist table. Could I have missed something?

[xtopolis]

I want to be able to pull up an image and then pull the artist name from the artist table.

$query="SELECT a.fullName 
  FROM artist a
  JOIN image i ON(a.artid = i.artid)
WHERE i.imgid = 5";

Get's the author's fullName where the imgid is 5

 

I think that would work, without seeing your structure..

[edit: logical correctness, per corbin]

[corbin]

SELECT image.imgid, image.title, image.artid, image.date, artist.artid, artist.fullName FROM image LEFT JOIN artist ON image.artid = artist.artid WHERE image.title LIKE 'Portrait of a Young Woman'

 

Since you're joining the table artist, you should really have artist.artid = image.artid not the other way around. (Not sure if this way is faster, just more logical to me.)

 

 

Also, LIKE 'Portrait....' is entirely pointless and identical to = since there are no LIKE modifyer thingies.  (%? so on.)

 

 

 

 

 

 

 

You shouldn't really be using a left join since you should always have a parent for the image child.  You should just use a JOIN.

[harkly]

I just tried the JOIN only and nothing shows.

 

I used the LIKE "Portrait ... to simplifly my code,for me at least, until I get the query working then I will move on to passing a variable from a form.

 

Maybe I am on the wrong track, is there another way to do queries from 2 table?

[xtopolis]

Post your structure, and some example data in it.  (both can be dumped to text from mysql).

 

You can use UNION... but really, a JOIN and tables being properly normalized would probably be best here.

[harkly]

I hope this is what you mean by structure, its a bit extensive...

 

artist

 

artid,fullName,firstName,middlename,lastName,otherName,Called,dob_xt,DOB,dod_xt,DOD,nationality,artForm,movid_1,movid_2,movid_3,medid_1,medid_2,medid_3,genid_1,genid_2,genid_3,period,bio,keywords,notes,verified,;

AR3614,Amedeo Modigliani,Amedeo,,Modigliani,,,,1884,,1920,Italian,"Painter, Sculptor",Expressionism,,,,,,,,,,,AR1539,,1,;

AR1026,Yaacov Agam,Yaacov,,Agam,,,,1928,,,Israeli,Painter/Sculptor,Optical Art,,,,,,,,,,,,,1,;

 

 

image

 

 

imgid,artid,title,datext_1,date,medid,size_inch,size_cm,musid,mov_id,genid,perid,bio,keywords,notes,verified;

IMG0001,AR2326,Portrait of Laurent-Nicolas de Joubert ,,1787,Oil on canvas,30 3/4 x 24 in.,78.1 x 61 cm.

IMG0002,AR2326,Portrait of Madame Joubert,,1787,Oil on canvas,30 3/4 x 24 in.,78.1 x 61 cm.,,,,,Commissioned by Marie-Louise Poulletier de Perigny to commemorate her marriage to Laurent-Nicolas de Joubert

 

 

 

What do you mean by noramlized table??

 

[harkly]

In case this is what you meant by structure 

 

 

CREATE TABLE artist (artid varchar(11) NOT NULL PRIMARY KEY,

 

fullName tinytext Not Null,

firstName tinytext NULL,

middleName tinytext NULL,

lastName tinytext NULL,

otherName tinytext NULL,

called tinytext NULL,

dob_xt tinytext NULL,

dob tinytext NULL,

dod_xt tinytext NULL,

dod tinytext,

nationality tinytext,

artform tinytext,

movid_1 varchar(7),

movid_2 varchar(7),

movid_3 varchar(7),

medid_1 varchar(7),

medid_2 varchar(7),

medid_3 varchar(7),

genid_1 varchar(7),

genid_2 varchar(7),

genid_3 varchar(7),

perid varchar(7),

bio mediumtext,

keywords tinytext,

notes text,

verified BOOL);

 

 

 

 

CREATE TABLE image (imgid varchar(11) NOT NULL PRIMARY KEY,

artid varchar(7) NOT NUll,

title tinytext,

datext_1 tinytext,

date tinytext,

medid varchar(7),

size_inch tinytext,

size_cm tinytext,

musid varchar(7),

movid varchar(7),

genid varchar(7),

perid varchar(7),

bio text,

keywords text,

notes text,

verified BOOL);

 

[xtopolis]

Normalization. A VERY IMPORTANT CONCEPT

(phpfreaks topic: http://www.phpfreaks.com/forums/index.php/topic,126097.0.html)

good article from there: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

I started to try your table stuff, but your image inputs didn't match... so I'm not going to.  Sorry.

[harkly]

Thanks, I just read the one article and I think that may be my problem. I need to have Foreign keys, I think. Trying that and running into problems with that.

[xtopolis]

You need to separate your data a bit more I think.  It seems by the column titles that you lumped everything together.  Sorry, try to sort your stuff out a bit more, then post again.

[harkly]

How's this? I took out the fields I'm not using.

 

artist --

artid fullName

AR3614 Amedeo Modigliani

AR3646 Anthonis Mor van Dashorst

 

image --

imgid artid date title

IMG0221 AR3614 1916 Portrait of a Young Woman

IMG0888 AR3614 1907 Head of a Woman with a Hat

IMG0889 AR3614 1908 Head of a Young Woman

IMG0890 AR3614 1908 The Jewess

 

 

I would really like not to use Foreign Keys if possible, turns out thats causing me more problems and not much info on the Internet about it.

[harkly]

Doubled posted info above

[harkly]

Sorry posted again! Going insane with this  :-\

[xtopolis]

Looks much cleaner, however, you're using a reserved word (date) as a column name, which is bad..

 

If you're saying you don't want to have artid in both tables (one being a fk), I would suggest you leave it, if it's how they are linked.  Once you get used to joins, you'll see their function.  I'm not expert, but it seems to help later down the line.

 

Now you could do something like:

SELECT a.fullName 
FROM artist a 
JOIN image i ON(a.artid = i.artid)
WHERE imgid = "IMG0221";

To get the artist of the image with the id, IMG0221..

 

But more than likely you'd get the images by a certain author:

SELECT a.fullName, i.imgid, i.title 
FROM image i
JOIN artist a ON(i.artid=a.artid)
WHERE a.artid = "AR3614";

[harkly]

I changed the "date", didn't even think about that and then I used both your Select statements, neither worked. I also changed the image.artid to image.art_id in case that was the problem as suggested.

 

Can I do 2 separate Select statements and then combine the info somehow?

 

The output that I want is very simple

Image, title of work, date, artist.

 

my current query --- 

$query="SELECT image.imgid, image.title, image.art_id, image.dt, artist.artid, artist.fullName FROM image LEFT JOIN artist ON artist.artid = image.art_id WHERE image.title LIKE '%search%'";


while ($r=mysql_fetch_array($result))

{	

   $title=$r["title"];

   $imgid=$r["imgid"];

   $dt=$r["dt"];

   $art_id=$r["art_id"];  // I tried this both ways art_id & artid

   $fullName=$r["fullName"];

echo ". <a href='image.php?imgid=$r[imgid]'><img src=\"image/$imgid\" border=0 ></a>, <i>".$title."</i>, ".$dt.", by <a href='artist.php?artid=$r[artid]'>".$fullName."</a><br>";

[xtopolis]

So this is what I came up with from before, based on your simple tables:

 

image

CREATE TABLE `image` (
  `imgid` varchar(255) collate latin1_german2_ci NOT NULL,
  `artid` varchar(255) collate latin1_german2_ci NOT NULL,
  `imgYear` int(4) NOT NULL,
  `title` varchar(255) collate latin1_german2_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

-- 
-- Dumping data for table `image`
-- 

INSERT INTO `image` (`imgid`, `artid`, `imgYear`, `title`) VALUES ('IMG0221', 'AR3614', 1916, 'Portrait of a Young Woman');
INSERT INTO `image` (`imgid`, `artid`, `imgYear`, `title`) VALUES ('IMG0888', 'AR3614', 1907, 'Head of a Woman with a Hat');
INSERT INTO `image` (`imgid`, `artid`, `imgYear`, `title`) VALUES ('IMG0889', 'AR3614', 1908, 'Head of a Young Woman');
INSERT INTO `image` (`imgid`, `artid`, `imgYear`, `title`) VALUES ('IMG0890', 'AR3614', 1908, 'The Jewess');

 

artist

CREATE TABLE `artist` (
  `artid` varchar(11) collate latin1_german2_ci NOT NULL,
  `fullName` varchar(255) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`artid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

-- 
-- Dumping data for table `artist`
-- 

INSERT INTO `artist` (`artid`, `fullName`) VALUES ('AR3614', 'Amedeo Modigliani');
INSERT INTO `artist` (`artid`, `fullName`) VALUES ('AR3646', 'Anthonis Mor van Dashorst');

 

your query adjusted to my columns:

SELECT image.imgid, image.title, image.artid, image.imgYear, artist.artid, artist.fullName FROM image LEFT JOIN artist ON artist.artid = image.artid WHERE image.title LIKE '%Woman%'

 

Returns 3 rows:

[pre]

imgid      title                        artid        imgYear      artid        fullName       

IMG0221    Portrait of a Young Woman AR3614        1916          AR3614      Amedeo Modigliani

IMG0888    Head of a Woman with a Hat AR3614        1907          AR3614      Amedeo Modigliani

IMG0889    Head of a Young Woman     AR3614        1908          AR3614      Amedeo Modigliani

[/pre]

[harkly]

Thanks!!!

 

I was able to duplicate your results by clearing out the 2 tables and inserting the data into it differently. So the error has something to do with the way I imported the data into my tables.