Jump to content


Photo

Table joins


  • Please log in to reply
29 replies to this topic

#1 bob_the _builder

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

Posted 02 September 2006 - 01:13 AM

Hi,

First my table structures are:

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;


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 subcat_id

Any ideas on the correct structure?

Thanks


#2 Barand

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

Posted 02 September 2006 - 01:02 PM

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

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'

For your first question, is that "one image from a random subcat" or "a random image from each subcat"
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

#3 bob_the _builder

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

Posted 02 September 2006 - 09:00 PM

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:

$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']."'");

I get an error:

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


Also tried:

$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']."'");

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

Thanks

#4 Barand

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

Posted 02 September 2006 - 10:44 PM

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
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

#5 bob_the _builder

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

Posted 02 September 2006 - 11:11 PM

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:

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']."'");

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

#6 Barand

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

Posted 02 September 2006 - 11:35 PM

This will get the images to unlink
The code I gave will delete the table rows from the subcat and images tables
$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]);
}

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

#7 bob_the _builder

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

Posted 02 September 2006 - 11:56 PM

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

#8 Barand

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

Posted 03 September 2006 - 12:01 AM

I count 3 queries, 1 SELECT and 2 DELETE's
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

#9 Barand

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

Posted 03 September 2006 - 12:28 AM

try
$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'");

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

#10 bob_the _builder

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

Posted 03 September 2006 - 01:26 AM

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:

$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());

Is that nearer to how it should be?

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


Thanks

#11 bob_the _builder

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

Posted 03 September 2006 - 10:45 AM

Does the trick using:

$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());
}

Im not familiar with:

while (list($photo, $subcat, $maincat) = @mysql_fetch_row($sql)) {


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

Thanks

#12 Barand

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

Posted 03 September 2006 - 10:56 AM

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.
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

#13 Barand

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

Posted 03 September 2006 - 11:05 AM

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

For your random image
<?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());
?>

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

#14 bob_the _builder

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

Posted 03 September 2006 - 08:09 PM

Thanks

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.


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

#15 Barand

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

Posted 03 September 2006 - 08:16 PM

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.
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

#16 bob_the _builder

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

Posted 04 September 2006 - 12:46 AM

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

#17 Barand

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

Posted 04 September 2006 - 06:21 AM

You can always use the first code I posted in my first reply and take the record deletions out of the image deletion loop.
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

#18 bob_the _builder

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

Posted 04 September 2006 - 07:21 AM

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:

$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());


#19 Barand

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

Posted 04 September 2006 - 03:17 PM

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

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'



This was my original code
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

#20 bob_the _builder

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

Posted 04 September 2006 - 09:51 PM

Hi,

I guess you mean something like:

		$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());

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users