missing something

acrayne

Hi there.
I am sure I am missing something that is proably simple. This is what I have I have six tables, four of these tables have a common field "invID" which is the primary key for my main table "port_inventory" I have a many to one setup. The other two tables hold frequent info that is used in the main port_inventory table, such as port_inventory.artistID is the same as port_artist.artistID when that id is choosen in the main table a specific artist is shown, the same goes for port_medium.mediumID is the same as port_medium.mediumID.

I am querrying all six tables and getting the correct info (so far, I only have three entries that I am testing with). My problem comes in when I querry I only recieve the info for the last record entered. I would like to display all entries according to the invID. Here is the querry I am using.

$sql = "SELECT * FROM port_inventory,port_images,port_text,port_keywords,port_artist,port_medium WHERE port_inventory.invID=port_images.invID
and port_inventory.invID=port_text.invID
and port_inventory.invID=port_keywords.invID=$id
and port_artist.artistID=port_inventory.artistID
and port_medium.mediumID=port_inventory.mediumID";

$result = mysql_query($sql);

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

I am sure I am missing something simple, but I havr been staring at the screen to long.
Thanks
Aaron

kevinsequeira

try changing
and port_inventory.invID=port_keywords.invID=$id

and port_inventory.invID=port_keywords.invID AND port_inventory.invID= $id

try running the whole query in the databse directly..

any luck ?

reg
kevin

acrayne

That did it.
Why was that happening? Was it becuase I didnt declare which was the main id?

Thanks again.

kevinsequeira

because you cant have a AND condition comparing 3 values this way

and port_inventory.invID=port_keywords.invID=$id

you have to break it up into two AND statements

reg
kevin

acrayne

Everything is working well, except I that I cannont echo / or print the id to the page. I think the problem has to do with the way the invID field is joined (which probably isnt the right term)?

I have included the updated query and the first line of my array. After I finish the array and try try and to print /echo $id I get nothing.

$sql = "SELECT * FROM port_inventory,port_images,port_text,port_keywords,port_artist,port_medium
WHERE port_inventory.invID=port_images.invID and
port_inventory.invID=port_text.invID
and port_inventory.invID=port_keywords.invID AND port_inventory.invID= $id
and port_artist.artistID=port_inventory.artistID
and port_medium.mediumID=port_inventory.mediumID";

$result = mysql_query($sql);
while ($access = mysql_fetch_array($result)) {
$id = $acess["invID"];

kevinsequeira

$id = $acess["invID"];

ure missing a c in access, should be

$id = $access["invID"];

reg
kevin

acrayne

Thanks, I didnt even see that. I fixed that but it brought another problem to the surface. Now I the entire page is being returned blank.

I am beginning to think that there may be somthing wrong with the way I am trying to relate the id's. Because as soon as the array knows that
$id = $access["invID"]; it returns blank. Is the array not know which invID to look at?

OR do I need to work on a new querry?

kevinsequeira

post ure entire code, and i will take a look

reg
kevin

kevinsequeira

u could be right it doesnt know which invID to refer to, try change your query to
select port_inventory.invID ..... instead of select *

print_r($access) in the while loop, so u can get the array key at which invID is present and refer to as $access[0] etc

reg
kevin

acrayne

Here is the querry and the array. The rest of the page is straight forward html, with the variables from the array echoed out.

$sql = "SELECT * FROM port_inventory,port_images,port_text,port_keywords,port_artist,port_medium
WHERE port_inventory.invID=port_images.invID
and port_inventory.invID=port_text.invID
and port_inventory.invID=port_keywords.invID AND port_inventory.invID= $id
and port_artist.artistID=port_inventory.artistID
and port_medium.mediumID=port_inventory.mediumID";


$result = mysql_query($sql);
while ($access = mysql_fetch_array($result)) {
$id = $access["invID"];
$artistID = $access["artistID"];
$title = $access["title"];
$mediumID = $access["mediumID"];
$year = $access["year"];
$date = $access["date"];
$sizeH = $access["sizeH"];
$sizeW = $access["sizeW"];
$img_full = $access["img_full"];
$img_large = $access["img_large"];
$img_medium = $access["img_medium"];
$img_small = $access["img_small"];
$text=$access["text"];
$kwd_one=$access["one"];
$kwd_two=$access["two"];
$kwd_three=$access["three"];
$kwd_four=$access["four"];
$artist=$access["artist"];
$medium=$access["medium"];
}

*edit, just saw your other post, will give it a try.

acrayne

Originally posted by kevinsequeira
u could be right it doesnt know which invID to refer to, try change your query to
select port_inventory.invID ..... instead of select *

OR

print_r($access) in the while loop, so u can get the array key at which invID is present and refer to as $access[0] etc

I gave the print_r a try, it returns 30 records, one for each field in all of the tables queried. I think I am seeing where the problem might be. At the 10th array, one of the files from a table is being labled as "id", becuase the field name in the table is "id". This is also true for a couple of the other tables. The fields that are labled "id" have nothing in common with the other fields, it is the key and auto_increment field for those tables. The common id amoung most of the tables is "invID", which only auto_increments in one table. I think I need to go back and rethink some of my field/id names. For some reason I have a hunch that my problem lies there.
Does this sound like it might be the problem?

acrayne

Ok, I think I got it, I went back to the drawing board and completly re-wrote the querry. So far I am getting the correct info, and the right id variables are being carried from one page to the next. I am sure this is kinda bulky and can be trimmed down, fewer lines and a little more efficiency, and I would love to hear them. Here is the revised code,

$sql = "SELECT A.invID as invID, A.title as title, A.year as year, A.date as date, A.sizeH as sizeH,
A.sizeW as sizeW, B.img as img, C.txt_title as txt_title,
C.text as text, D.one as one, D.two as two, D.three as three, D.four as four, E.artist as artist, F.medium as medium
FROM port_inventory as A, port_images as B, port_text as C, port_keywords as D, port_artist as E, port_medium as F
WHERE A.invID=B.invID
AND A.invID=C.invID
AND A.invID=D.invID AND A.invID = $id
AND A.artistID=E.artistID
AND A.mediumID=F.mediumID";

$result = mysql_query($sql);
while ($access = mysql_fetch_array($result)) {
$id = $access["invID"];
$artistID = $access["artistID"];
$title = $access["title"];
$mediumID = $access["mediumID"];
$year = $access["year"];
$date = $access["date"];
$sizeH = $access["sizeH"];
$sizeW = $access["sizeW"];
$img = $access["img"];
$txt_title=$access["txt_title"];
$text=$access["text"];
$kwd_one=$access["one"];
$kwd_two=$access["two"];
$kwd_three=$access["three"];
$kwd_four=$access["four"];
$artist=$access["artist"];
$medium=$access["medium"];
}