Table joins

[bob_the _builder]

Hi,

First my table structures are:

[code]CREATE TABLE gallery_maincat (
  maincat_id int(20) NOT NULL auto_increment,
  maincat_name varchar(50) NOT NULL default '',
  PRIMARY KEY (maincat_id)
) TYPE=MyISAM;

CREATE TABLE gallery_subcat (
  subcat_id int(20) NOT NULL auto_increment,
  maincat_id int(20) NOT NULL,
  subcat_name varchar(50) NOT NULL default '',
  PRIMARY KEY (subcat_id)
) TYPE=MyISAM;

CREATE TABLE gallery_images (
  photo_id int(20) NOT NULL auto_increment,
  subcat_id int(20) NOT NULL,
  photo_filename varchar(25) default '',
  photo_caption text,
  PRIMARY KEY (photo_id)
) TYPE=MyISAM;[/code]


Im having issue trying to write 2 table joins .. 1st is to show a random image for the main categories that corospond to the subcategories.

The 2nd is to delete all images and related name to the main category and subcategory, the only information posted is the maincat_id, so a table join is needed to match the maincat_id to the sub[color=black][/color]cat_id

Any ideas on the correct structure?

Thanks

[Barand]

This _should_ delete subcategory records and related images for a given $maincat

[code]
DELETE 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'[/code]

For your first question, is that "one image from a random subcat" or "a random image from each subcat"

[bob_the _builder]

Hi,

Its just a random image from any subcat related to each maincat in the loop.

Here is what I have for your posted code:

[code=php:0]$sql = mysql_query("DELETE 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 = '".$_GET['maincat']."'") or die(mysql_error());
while ($row = mysql_fetch_array($sql)) {

unlink($images_dir . '/' . $row["photo_filename"]);
  unlink($images_dir . '/tb_' . $row["photo_filename"]);
}
    $sql = mysql_query("DELETE FROM gallery_subcat WHERE maincat_id='".$_GET['maincat_id']."'");
$sql = mysql_query("DELETE FROM gallery_maincat WHERE maincat_id='".$_GET['maincat_id']."'");[/code]

I get an error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource


Also tried:

[code=php:0]$sql = mysql_query("DELETE from gallery_images
WHERE subcat_id IN ( SELECT maincat_id
                        FROM gallery_subcat
                      WHERE maincat_id=".$_GET['maincat_id'].")") or die(mysql_error());
while ($row = mysql_fetch_array($sql)) {

unlink($images_dir . '/' . $row["photo_filename"]);
  unlink($images_dir . '/tb_' . $row["photo_filename"]);
}
    $sql = mysql_query("DELETE FROM gallery_subcat WHERE maincat_id='".$_GET['maincat_id']."'");
$sql = mysql_query("DELETE FROM gallery_maincat WHERE maincat_id='".$_GET['maincat_id']."'");[/code]

Thats deletes everything but the "gallery_images" records and doesnt unlink the images.

Thanks

[Barand]

A SQL query can't delete files from the server, only records from tables. As you were asking about deletions and joins I assumed you wanted to delete the rows from the subcat and image tables that were related the the main category.

A delete query doesn't return a record set which is why you get the invalid resource error

[bob_the _builder]

Hi,

Yep thats what I am trying to do, but also get the photo_filename field from the gallery_images table and use it with the unlink functions you see in the script.

Here I have managed to remove subgallery and all its images:

[code=php:0] if (array_key_exists("confirm",$_REQUEST)) {

$sql = mysql_query("SELECT photo_filename FROM gallery_images WHERE subcat_id='".$_GET['subcat_id']."'");
while ($row = @mysql_fetch_array($sql)) {
  unlink($images_dir . '/' . $row[0]);
  unlink($images_dir . '/tb_' . $row[0]);
}
$sql = mysql_query("DELETE FROM gallery_images WHERE subcat_id='".$_GET['subcat_id']."'");
    $sql = mysql_query("DELETE FROM gallery_subcat WHERE subcat_id='".$_GET['subcat_id']."'");[/code]

Which works great, but when it comes to deleting a maincat, all its subcats and images im stuck, as the link to delete maincat only holds the value $row['maincat_id'] which doesnt relate to the gallery_images table at all .. hence the join or what ever is needed to create the link?

Im stuck .. have no idea how to make it work :(

Thanks

[Barand]

This will get the images to unlink
The code I gave will delete the table rows from the subcat and images tables
[code]
$sql = mysql_query("SELECT 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='".$_GET['maincat_id']."'");
while ($row = @mysql_fetch_array($sql)) {
    unlink($images_dir . '/' . $row[0]);
    unlink($images_dir . '/tb_' . $row[0]);
}
[/code]

[bob_the _builder]

Thanks, that removes the images from the server .. Is there away to delete the gallery_images data and unlink the related images in a single query like I have with the subcat function?

Thanks

[Barand]

I count 3 queries, 1 SELECT and 2 DELETE's

[Barand]

try
[code]
$sql = mysql_query("SELECT i.photo_filename,  i.subcat_id, m.maincat_id
FROM gallery_images i INNER JOIN gallery_subcat s
    ON s.subcat_id = i.subcat_id
INNER JOIN gallery_maincat m
    ON m.maincat = s.maincat
WHERE m.maincat_id='".$_GET['maincat_id']."'");
while (list($photo, $subcat, $maincat) = @mysql_fetch_row($sql)) {
    unlink($images_dir . '/' . $photo);
    unlink($images_dir . '/tb_' . $photo);
}
mysql_query ("DELETE FROM gallery_image WHERE subcat_id = '$subcat'");
mysql_query ("DELETE FROM gallery_subcat WHERE subcat_id = '$subcat'");
mysql_query ("DELETE FROM gallery_maincat WHERE maincat_id = '$maincat'");
[/code]

[bob_the _builder]

Hi,

That only seems to unlink the images, but leaves all mysql records in their tables .. Have tried a few changes and there doesnt seem to be any more errors. But still not removing any or the records in mysql at all:

[code=php:0]$sql = mysql_query("SELECT i.photo_filename,  i.subcat_id, m.maincat_id
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='".$_GET['maincat_id']."'") or die(mysql_error());
while (list($photo_filename, $subcat_id, $maincat_id) = @mysql_fetch_row($sql)) {
  @unlink($images_dir . '/' . $photo);
    @unlink($images_dir . '/tb_' . $photo);
}
mysql_query ("DELETE FROM gallery_images WHERE subcat_id = '$subcat_id'") or die(mysql_error());
mysql_query ("DELETE FROM gallery_subcat WHERE subcat_id = '$subcat_id'") or die(mysql_error());
mysql_query ("DELETE FROM gallery_maincat WHERE maincat_id = '$maincat_id'") or die(mysql_error());[/code]

Is that nearer to how it should be?

All these WHERE maincat_id = '$maincat_id had no _id at the end.


Thanks

[bob_the _builder]

Does the trick using:

[code=php:0]$sql = mysql_query("SELECT i.photo_filename, i.subcat_id, m.maincat_id
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='".$_GET['maincat_id']."'") or die(mysql_error());
while($row = mysql_fetch_array($sql)) {
    @unlink($images_dir . '/' . $row['photo_filename']);
    @unlink($images_dir . '/tb_' . $row['photo_filename']);

mysql_query ("DELETE FROM gallery_images WHERE subcat_id = '".$row['subcat_id']."'") or die(mysql_error());
mysql_query ("DELETE FROM gallery_subcat WHERE subcat_id = '".$row['subcat_id']."'") or die(mysql_error());
mysql_query ("DELETE FROM gallery_maincat WHERE maincat_id = '".$row['maincat_id']."'") or die(mysql_error());
}[/code]

Im not familiar with:

[code=php:0]while (list($photo, $subcat, $maincat) = @mysql_fetch_row($sql)) {[/code]


Would a similar structure be used to grab a random image from any subcat related to each maincat in the loop

Thanks

[Barand]

I was trying to avoid the redundant delete queries. I guess the $subcat and $maincat values were being cleared once it hit the end of the result set.

[Barand]

BTW, Don't use user-supplied values directly in a query.

For your random image
[code]<?php

$maincat = get_magic_quotes_gpc() ? $_GET['maincat_id'] : addslashes($_GET['maincat_id']) ;

$sql = mysql_query("SELECT i.photo_filename, s.subcat_name, m.maincat_name
    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'
        ORDER BY RAND()
        LIMIT 1") or die(mysql_error());
?> [/code]

[bob_the _builder]

Thanks

[quote author=Barand link=topic=106575.msg427052#msg427052 date=1157280985]
I was trying to avoid the redundant delete queries. I guess the $subcat and $maincat values were being cleared once it hit the end of the result set.
[/quote]

Does that mean your structure is a better way to do it?

With the random image, there is no user data being passed, its just a query that shows all tha categories and shows a random image from the subcats that relate to each individual main cat.

Thanks

[Barand]

My structure doesn't work as the values of the $subcat are lost when it hits the end of the results.

With yours, the first time the queries are called the records are deleted, so calls made on the next image record try to delete already-deleted records. It doesn't really matter, it just slows things down.

You could set a variable

$run_once = 1;

before the loop, then inside the loop have

if ($run_once) {
    // put DELETE queries here

    $run_once = 0;
}

Now the queries to delete the records only run once.

I'll give a bit more thought to the random image problem.

[bob_the _builder]

Hi,

I have noticed a flaw in the delete maincat code:

It only works if a subcat has images within it, if there are 2 subcats and only 1 has images it will remove the maincat, the subcat with images + the images but leaves the 2nd maincat which has no images behind in the database.

Cheers

[Barand]

You can always use the first code I posted in my first reply and take the record deletions out of the image deletion loop.

[bob_the _builder]

I couldnt get the original code you posted to work .. Here is what I have from your original code, it unlinks the images, but doesnt delete any mysql records, doesnt seem to bring back any errors neither. I had to alter some of the field names from your original to suit the databse:

[code=php:0]$sql = mysql_query("SELECT i.photo_filename,  i.subcat_id, m.maincat_id
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='".$_GET['maincat_id']."'") or die(mysql_error());
while (list($photo_filename, $subcat_id, $maincat_id) = @mysql_fetch_row($sql)) {
    unlink($images_dir . '/' . $photo_filename);
    unlink($images_dir . '/tb_' . $photo_filename);
}

mysql_query ("DELETE FROM gallery_images WHERE subcat_id = '$subcat_id'") or die(mysql_error());
mysql_query ("DELETE FROM gallery_subcat WHERE subcat_id = '$subcat_id'") or die(mysql_error());
mysql_query ("DELETE FROM gallery_maincat WHERE maincat_id = '$maincat_id'") or die(mysql_error());[/code]

[Barand]

[quote author=Barand link=topic=106575.msg426485#msg426485 date=1157202172]
This _should_ delete subcategory records and related images for a given $maincat

[code]
DELETE 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'[/code]


[/quote]

This was my original code

[bob_the _builder]

Hi,

I guess you mean something like:

[code=php:0]
$sql = mysql_query("DELETE gallery_images, gallery_subcat
FROM gallery_images, gallery_subcat, 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 = '".$_GET['maincat_id']."'") or die(mysql_error());
while($row = mysql_fetch_array($sql)) {
    unlink($images_dir . '/' . $row['photo_filename']);
    unlink($images_dir . '/tb_' . $row['photo_filename']);
$subcat_id = $row['subcat_id'];
$maincat_id = $row['maincat_id'];
}
mysql_query ("DELETE FROM gallery_images WHERE subcat_id = '".$subcat_id."'") or die(mysql_error());
mysql_query ("DELETE FROM gallery_subcat WHERE subcat_id = '".$subcat_id."'") or die(mysql_error());
mysql_query ("DELETE FROM gallery_maincat WHERE maincat_id = '".$_GET['maincat_id']."'") or die(mysql_error());[/code]

Same issue, doesnt delete any subcats that dont contain images from the table  ???

Also gives and error:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ...


Thanks

[Barand]

NO.

Use the select query to delete the images then call the delete query to delete the records.

[bob_the _builder]

Hi,

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


Thanks

[Barand]

try
[code]<?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' ");
?>[/code]

[bob_the _builder]

Ahh I see, back to 2 querys.

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


Thanks

[Barand]

[quote author=bob_the _builder link=topic=106575.msg428073#msg428073 date=1157409103]
Ahh I see, back to 2 querys.
[/quote]

You had 4 before - 1 select and 3 deletes

Try changing delete query to

[code]
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' ");
[/code]