SQL Select Problem...

darthmahon

Hey,

Ok I have two tables that I want to get information from in the same query.

The field information I want from the two tables is: Name, Location and Type.

The tables are called PICTURES and PEOPLE. There is a field in the PICTURES table called subid that relates to the ID field of the PEOPLE table. The ID field identifies which PERSON the PICTURE relates to, you know what I mean?

Anyway, I know the following won't work but I need a query something like this:

Code:

SELECT name, location, type FROM PICTURES AND PEOPLE WHERE id=$subid

Any ideas?

Cheers,
Chris

Norman_Graham

Hi Chris

The syntax is:

SELECT pictures.name, pictures.location, pictures.type, pictures.id, people.subid FROM pictures, people WHERE picture.id=$subid AND people.subid=$subid;

Of course, if performance is not really an issue, you can also just do SELECT * FROM pictures, people WHERE ...

HTH

Norm

darthmahon

Did something similar after a bit of reading, thanks for your help 🙂

Only problem is now, it outputs each result($row) twice...

There are only two records in the PICTURES table and it outputs both twice.

This is my code:


                <?

// form not yet submitted

// generate and execute query

$query = "SELECT p.name, pic.location, pic.type, pic.subid FROM PEOPLE p, PICTURES pic WHERE pic.type='$formattype'";

$result = mysql_query($query) or die ("Error in query: $query. " .

mysql_error());

// display images

if (!$submit)

{


 // if a result is returned

 if (mysql_num_rows($result) > 0)

 {

?>
              </p>
              <table width="100%" border="0" cellpadding="5" cellspacing="0" class="Normal">
                <?

 // iterate through resultset

 // print info

 while($row = mysql_fetch_object($result))

 {

 ?>
                <tr> 
                  <td width="150" align="center"><img src="<? echo $row->location; ?>" width="100" height="100"></td>
                  <td valign="top" class="Normal"><p>Type: <? echo $row->type; ?> Person: <? echo $row->name; ?></p>
                    <p><a href="d">Delete This Image</a></p></td>
                </tr>
                <?

 }

 ?>
              </table>
              <?

 }

 // no result returned

 else

 {

  echo "Either you have not selected a type or that record could not be located in the database.";

 }

} ?>

Any ideas on how to get it to output each row once?

Norman_Graham

Hi Chris

Your query is very strange indeed - you have no condition for the people table for a start. What are the names of the tables? You've got picture, pic and just p, plus multiple combinations of these. Which is it?

You want this:

SELECT * FROM pictures, people WHERE picture.myfield = '$myvariable' and people.myfield ='$myvariable';

HTH

Norm

darthmahon

Ok I have two tables, below are the field names for each:

PEOPLE table

id, name

PICTURES table

id, filename, location, type, peopleid, bin

Now, here is the data I currently have in these tables, IGNORE the brackets():

PEOPLE table data

1, Nigel
2, David

PICTURES table data
1 (id), nigel.jpg (filename), /final/images/nigel.jpg (location), newsitem (type), 1 (peopleid - relates to id in PEOPLE table), 0 (bin)

Now I only want to gather the records in the PICTURES table that have their "type", for example, as newsitem and I want to have the following data outputted:

Image (show the image by using the location field)
Picture Name (show the name of the picture)
Name (show the name the image is related to, i.e nigel)

And keep doing that for each result that has it's type as newsitem.

That make any more sense?

Cheers,
Chris

vallette

Try something like this:

SELECT Pictures.image,Pictures.name AS picturename,People.name
FROM Pictures
INNER JOIN People ON Pictures.peopleid=People.id
WHERE Pictures.type='newsitem'