image/database problem

d34db0lts

hello,

i'm doing this website where the users can posts news and upload images. i thought that it would be best to put all images in one table and all "content" in another. table images has the primary key of table content. i want to be able to find out which posts do not have any images associated with them.

these are my tables

table content
contentID
txtTitle
txtAuthor
txtBlog

table images
imageID
contentID
txtPath

this is the query i'm working with:
SELECT content.contentID, content.txtTitle, content.txtAuthor
FROM content, image
WHERE content.contentID != image.contentID
ORDER BY content.contentID DESC

the problem with this is that it shows every record twice (actually, it now went up to three times).

i got that query because:
SELECT content.contentID, content.txtTitle, content.txtAuthor
FROM content, image
WHERE content.contentID = image.contentID
ORDER BY content.contentID DESC

gives me all the records that have an image associated with them, i figured that by adding a ! it would work, and it did, and then i did something and it stopped working. i didn't really do anything to affect this code, all i did was add more records and then it got screwed up. i've tried using NOT and ISNULL, IS NULL, IS NOT NULL and i'm about to give up.

so please, help me!

bastien

just be smarter about how you display the content. if you are getting the images correctly, then just use php to check if you have written out the user info already, if yes then don't write anything else except the image, esle show the user data.

Something like:

$username = '';

while ($rows = mysql_fetch_array($result))
{
  if ($rows['username'] != $username) 
  {
   echo $rows['username'];
   $username = '$rows['username'];
  }

  echo $rows['imagename'];
}

d34db0lts

Originally posted by bastien
just be smarter about how you display the content. if you are getting the images correctly, then just use php to check if you have written out the user info already, if yes then don't write anything else except the image, esle show the user data.

Something like:
$username = '';

while ($rows = mysql_fetch_array($result))
{
  if ($rows['username'] != $username) 
  {
   echo $rows['username'];
   $username = '$rows['username'];
  }

  echo $rows['imagename'];
}
[/B]

i'm not actually displaying any images. what i'm doing is trying to show the user (which will only be the admin, since the admin will be the only one allowed to add posts) that some posts do not have any images associated with them. i'm trying to find out how many records in my table content do not have a matching id in table image. as of right now 3 out of 10 records in the content table have images. so that means there are three records in table images that have a foreign key that matches the primary key in the content table. so i want a query that will show me the other seven records...which do not have any images associated with them. i should have explained that better, sorry.

this is how i want my page to look like

total posts (10)

3 posts have images
author1 | title1
author2 | title2
author1 | title3

i need a query that will show the me the numbers like the 3 and 7 and will also show me the author and the title. does this make sense? please help me!

bastien

that is different.

try:

$sql="select * from content,images  where content.contentID not in images.contentID";