Can I query the whole DB Table, embedded in a query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Can I query the whole DB Table, embedded in a query?

Post by simonmlewis »

Sorry for the subject, not sure how to summarise this question.

I have a long page that lists all Categories.
It then checks if any other category within the database has the same titletag code as that one, and then another query for metadescription, and then another for metakeywords.

It also asks if there are empty fields for titletag, metakeywords or metadescription.

Finally if any of the above are true, it tells the administrator what the error is: "Missing MetaKeywords".

Here is the code. What I need now is to change this so that all those with errors appear the top, because once it comes to products, the list will be VERY long, and be better to see them all twindle down to no errors at the top.

Code: Select all

$result = mysql_query ("SELECT id, categories, titletag, metadescription, metakeywords FROM categories ORDER BY categories");
echo "<div class='sectionhead'>Categories</div>
<table class='table' cellspacing='0' cellpadding='3' width='100%'>";
  while ($row = mysql_fetch_object($result))
     {
     $error = null;
     $resulterror = mysql_query ("SELECT id FROM categories WHERE (titletag IS NULL OR titletag = '' OR metadescription IS NULL OR metadescription = '' OR metakeywords IS NULL OR metakeywords = '') AND id = '$row->id'") or die(mysql_error());
$num_error = mysql_num_rows($resulterror);

if ($num_error != 0)
{
echo "<tr valign='top' bgcolor='#FFFF99' onMouseOver=\"this.bgColor='#FFFF99';\" onMouseOut=\"this.bgColor='#FFFF99';\">";
}
else
{
echo "<tr valign='top'  onMouseOver=\"this.bgColor='#f6f6f6';\" onMouseOut=\"this.bgColor='#FFFFFF';\">";
}
 
echo "<td><a rel='facebox' href='#editmeta$row->id' style='text-decoration: none'>$row->categories</a></td><td><a rel='facebox' href='#editmeta$row->id' style='text-decoration: none'>";
     
     $result2 = mysql_query ("SELECT titletag, categories FROM categories WHERE titletag = '$row->titletag' AND titletag != '' AND titletag IS NOT NULL AND id <> '$row->id'");
$num_dup = mysql_num_rows($result2);
if ($num_dup != 0) { 
$error = "yesduptit";
  while ($row2 = mysql_fetch_object($result2))
     {
     echo "<font color='#ff0000'>Titletag Duplicated with $row2->categories</font><br/>";
     }
     mysql_free_result($result2);
     } 
$num_dup == 0;

     $result2 = mysql_query ("SELECT metadescription, categories FROM categories WHERE metadescription = '$row->metadescription' AND metadescription != '' AND metadescription IS NOT NULL AND id <> '$row->id'");
$num_dup = mysql_num_rows($result2);
  while ($row2 = mysql_fetch_object($result2))
     {
     $error = "yesdupdesc";
     echo "<font color='#ff0000'>Meta Description Duplicated with $row2->categories</font><br/>";
     }
     mysql_free_result($result2);
     


     $result2 = mysql_query ("SELECT metakeywords, categories FROM categories WHERE metakeywords = '$row->metakeywords' AND metakeywords != '' AND metakeywords IS NOT NULL AND id <> '$row->id'");
$num_dup = mysql_num_rows($result2);
  while ($row2 = mysql_fetch_object($result2))
     {
     $error = "yesdupkey";
     echo "<font color='#ff0000'>Meta Keywords Duplicated with $row2->categories</font><br/>";
     }
     
     if ($row->titletag == NULL) { echo " - TitleTag missing";
     $error = "yestitle";
     }
 
     if ($row->metadescription == NULL) { echo " - MetaDescription missing";
     $error = "yesdesc";
     }

     if ($row->metakeywords == NULL) { echo " - metakeywords missing";
     $error = "yeskey";
     }

     
     echo "</a>";
     
      if (!isset($error)) { echo "<img src='/images/icon_tick.gif' alt='No Errors or Duplicates' title='No Errors or Duplicates' />";}
     
     echo "<div id='editmeta$row->id' style='display: none;width:300px;height:200px;overflow:auto; font-size: 11.2px'>
          <div class='head'>Edit Meta Tags</div>
          <form method='post' action='/a_metas'>
          <input type='hidden' name='category' value='$row->categories'>
        <input type='hidden' name='update' value='updatecat'>
        <input type='hidden' name='id' value='$row->id'>
       Title Tag:<br/>
         <input type='text' name='titletag' value='$row->titletag' style='width: 450px'><br/><br/>
       Meta Keywords:<br/><textarea name='metakeywords' style='font-family: arial; font-size: 11.2px; width:450px' rows='5'>$row->metakeywords</textarea><br/><br/>
       
       Meta Description:<br/><textarea name='metadescription' style='font-family: arial; font-size: 11.2px; width:450px' rows='5'>$row->metadescription</textarea><br/><br/>
       <input type='submit' value='Submit'></form>
          </div></td></tr>";
     }
     
         
$num_error = 0;
I was thinking of an embedded query, but:
a) how would you know what the error is
b) how would you query the rest of the table, based on the result that you find
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can I query the whole DB Table, embedded in a query?

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT id, categories, titletag, metadescription, metakeywords FROM categories WHERE (titletag ='' OR metakeywords = '' OR metadescription = '' OR titletag = titletag OR metakeywords = metakeywords OR metadescription = metadescription ORDER BY titletag='' DESC, metakeywords='', metadescription=''");
I doubt this would work but is it on the right track?
If it is, or there is a slight fix to it, how do I then identify which errors it finds. Some kind of "AS error='duplicate keywords'" type of thing?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Can I query the whole DB Table, embedded in a query?

Post by Celauran »

What is it you're trying to do here? Identify fields with duplicated data? Why not query that field directly?

Code: Select all

SELECT titletag
FROM categories
GROUP BY title tag
HAVING COUNT(*) > 1
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can I query the whole DB Table, embedded in a query?

Post by simonmlewis »

I'm trying to show all categories on screen. And then to show if there are duplicates, or if there are missing entries.
And if there are either, then it tells the user what's missing - ie Keywords, Description, Titletag.

My current page does all that, but not in order of "errors". So you have to scan down the page to spot them.

I want to be able to put all errors at the top. OR... *only* show those with errors.

Your example would work, but would only work per error... so that one would show if titletag is duplicated.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Can I query the whole DB Table, embedded in a query?

Post by Celauran »

Right. The idea was mostly to cut down on the number of queries you're running. Get a list of duplicate X, then display those wherever you like. Misunderstood what you were asking, I guess.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can I query the whole DB Table, embedded in a query?

Post by simonmlewis »

So is it not possible to do what I want, and have it in an order, and be told of each error... within one query?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply