Jump to content


Photo

Table joins


  • Please log in to reply
29 replies to this topic

#21 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 04 September 2006 - 09:53 PM

NO.

Use the select query to delete the images then call the delete query to delete the records.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#22 bob_the _builder

bob_the _builder
  • Members
  • PipPipPip
  • Advanced Member
  • 207 posts

Posted 04 September 2006 - 10:14 PM

Hi,

Not really sure what you mean by call on the delete query?


Thanks

#23 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 04 September 2006 - 10:22 PM

try
<?php
$maincat = $_GET['maincat_id'];
 
$sql = mysql_query("SELECT i.photo_filename
    FROM gallery_images i 
    INNER JOIN gallery_subcat s 
        ON s.subcat_id = i.subcat_id
    INNER JOIN gallery_maincat m
        ON m.maincat_id = s.maincat_id
    WHERE m.maincat_id='$maincat' ") or die(mysql_error());
    
while (list($photo_filename) = @mysql_fetch_row($sql)) {
    unlink($images_dir . '/' . $photo_filename);
    unlink($images_dir . '/tb_' . $photo_filename);
}

mysql_query("DELETE gallery_maincat, gallery_subcat, gallery_images
    FROM gallery_subcat, gallery_images, gallery_maincat
    WHERE gallery_maincat.maincat_id = gallery_subcat.maincat_id
    AND gallery_images.subcat_id = gallery_subcat.subcat_id
    AND gallery_maincat.maincat_id = '$maincat' ");
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#24 bob_the _builder

bob_the _builder
  • Members
  • PipPipPip
  • Advanced Member
  • 207 posts

Posted 04 September 2006 - 10:31 PM

Ahh I see, back to 2 querys.

It still doesnt delete subcats under the maincat being deleted that doesnt contain any images.


Thanks

#25 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 04 September 2006 - 10:41 PM

Ahh I see, back to 2 querys.


You had 4 before - 1 select and 3 deletes

Try changing delete query to

mysql_query("DELETE gallery_maincat, gallery_subcat, gallery_images
    FROM gallery_maincat 
    INNER JOIN gallery_subcat
        ON gallery_maincat.maincat_id = gallery_subcat.maincat_id
    LEFT JOIN gallery_images
        ON gallery_images.subcat_id = gallery_subcat.subcat_id
    WHERE gallery_maincat.maincat_id = '$maincat' ");

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#26 bob_the _builder

bob_the _builder
  • Members
  • PipPipPip
  • Advanced Member
  • 207 posts

Posted 04 September 2006 - 11:08 PM

Hi,

Thanks that seems to work, I guess it has to be 2 queries?

I dont really understand when you should use left joins or inner joins etc   :(

A query to show a random image for each maincat from a corrosponding random subcat .. I guess thats hard as there is no post data to make the select from?

Also abit off mysql, should you also clean any data sent across the url like &maincat_id=$_POST['maincat_id'], I guess code can be added to the url to cause damage to the site?

also is (list($photo_filename) away around creating a variable $photo_filename = $row['photo_filename'];?


Thanks

#27 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 04 September 2006 - 11:16 PM

A left join B

- select all recs from A even if there is no matching rec in B


A inner join B

- select only those recs from A and B that match on the join column{s)


Clean data

Never put data straight into a query that originates form the client. That means any data in GET, POST or COOKIE. Always clean it - as a minimum addslashes, if magic_quotes_gpc is off (if it's on it does it for you). You might also want to strip_tags() to prevent javascript and html code from being entered.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#28 bob_the _builder

bob_the _builder
  • Members
  • PipPipPip
  • Advanced Member
  • 207 posts

Posted 05 September 2006 - 11:13 PM

Thanks,

That makes the joins bit more clearer .. Still no luck with the random maincat image. Seems it keeps showing images for maincats that arnt even in a related subcat.

Any ideas?


Cheers

#29 bob_the _builder

bob_the _builder
  • Members
  • PipPipPip
  • Advanced Member
  • 207 posts

Posted 11 September 2006 - 07:47 AM

Hi,

I got it using:

$preview = mysql_query("SELECT i.photo_filename 
        FROM gallery_images i 
        INNER JOIN gallery_subcat s 
        ON i.subcat_id = s.subcat_id 
        INNER JOIN gallery_maincat m 
        ON m.maincat_id = s.maincat_id 
        WHERE m.maincat_id = {$row['maincat_id']}
		ORDER BY RAND()") or die(mysql_error());

had a query prior the the $preview one for he pagination .. so was able to use $row['maincat_id']

$sql = mysql_query("SELECT * FROM gallery_maincat ORDER BY maincat_id DESC LIMIT $from, $max_results");

Is this a reasonable way to go about it?


Thanks

#30 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 11 September 2006 - 02:42 PM

As you want a single image per maincat, add LIMIT 1 to end of the $preview query
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users