Jump to content

Table joins


bob_the _builder

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
Link to comment
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"
Link to comment
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
Link to comment
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
Link to comment
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
Link to comment
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]
Link to comment
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]
Link to comment
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
Link to comment
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
Link to comment
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]
Link to comment
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
Link to comment
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.
Link to comment
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]
Link to comment
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
Link to comment
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
Link to comment
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]
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.