Jump to content

Archived

This topic is now archived and is closed to further replies.

bob_the _builder

Table joins

Recommended Posts

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

Share this post


Link to post
Share on other sites
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"

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
You can always use the first code I posted in my first reply and take the record deletions out of the image deletion loop.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
NO.

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.