PROBLEM WITH A PHP SCRIPT!please HELP!

  • mihalea
  • Born
  • Born
  • mihalea
  • Posts: 2

Post 3+ Months Ago

Hi everybody!
I have a problem with a php script. What I want to do?
I want to select from the table 'categories' with the columns: 'id', 'name' (wich means the name of the category) and 'main_cat' (represents the category where other categories is a under, basically the categories that have a main_cat are sub-categories).
I like to select from the table the entries that has as a 'main_cat' an entry that doesn't exists as a category, that doesn't exists an entry with the column 'name' as the column 'main_cat'.

so here is the script:

PHP Code: [ Select ]
<?php
$getcats1=mysql_query("SELECT DISTINCT name FROM categories");
while($cats1=mysql_fetch_array($getcats1)){
 $cat1=$cats1['name'];
 
 $getinvalidcats=mysql_query("SELECT id, name FROM categories WHERE main_cat<>'".$cat1."'");
 while($invalidcats=mysql_fetch_array($getinvalidcats)) {
  $invalidcats_id=$invalidcats['id'];
  $invalidcats_name=$invalidcats['name'];
 
  print"<span class=normal>$invalidcats_name</span>&nbsp;&nbsp;&nbsp;&nbsp;(<a class=link href=deletecategory.php?id=$invalidcats_id>delete</a>)<br>";
 
}
}
?>
  1. <?php
  2. $getcats1=mysql_query("SELECT DISTINCT name FROM categories");
  3. while($cats1=mysql_fetch_array($getcats1)){
  4.  $cat1=$cats1['name'];
  5.  
  6.  $getinvalidcats=mysql_query("SELECT id, name FROM categories WHERE main_cat<>'".$cat1."'");
  7.  while($invalidcats=mysql_fetch_array($getinvalidcats)) {
  8.   $invalidcats_id=$invalidcats['id'];
  9.   $invalidcats_name=$invalidcats['name'];
  10.  
  11.   print"<span class=normal>$invalidcats_name</span>&nbsp;&nbsp;&nbsp;&nbsp;(<a class=link href=deletecategory.php?id=$invalidcats_id>delete</a>)<br>";
  12.  
  13. }
  14. }
  15. ?>



I am desperately asking for your help! :)

Thanks guys!
Moderator Remark: code tags
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

So you have a table full of categories, main_cat could contain the name value of one of the rows in the table. You want to select all the categories that have an main_cat value that doesnt exist in the categories table. Correct? Simply but, select all rows where the catagories main catagory doesnt not exist.
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Try something like this: (This is untested)
PHP Code: [ Select ]
$result = mysql_query("SELECT * FROM catagories");
 
while($row = mysql_fetch_array($result)) {
   $arr['name'][] = $row['name'];
   $arr['main_cat'][] = $row['main_cat']
}
 
print_r(array_diff($arr['name'],$arr['main_cat']));
 
  1. $result = mysql_query("SELECT * FROM catagories");
  2.  
  3. while($row = mysql_fetch_array($result)) {
  4.    $arr['name'][] = $row['name'];
  5.    $arr['main_cat'][] = $row['main_cat']
  6. }
  7.  
  8. print_r(array_diff($arr['name'],$arr['main_cat']));
  9.  
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

I think your table is setup wrong, or you're querying the table wrong to get $cat1.

By the looks of things, "main_cat" would refer to the "id" of a parent category in a normal table. However, it looks like you're using the "name" column to associate categories with eachother.

--

Assuming the table actually does associate sub-categories using the name column, you don't need to use that first query and the subsequent loop if your version of MySQL supports sub-queries.

SQL Code: [ Select ]
SELECT id, name
   FROM categories
   WHERE main_cat
      NOT IN (SELECT DISTINCT name FROM categories)
  1. SELECT id, name
  2.    FROM categories
  3.    WHERE main_cat
  4.       NOT IN (SELECT DISTINCT name FROM categories)

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 132 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.