Using To While Loops Or Better Way?

[brown2005]

    while($domains_result = mysql_fetch_array($domains_sql)){

	 $domains_id = $domains_result['domains_id'];    
	 $domains_url = $domains_result['domains_url'];
	 $domains_name = preg_replace('#^https?://www.#', '', $domains_url);

	 $keywords_sql = mysql_query("SELECT * FROM domains_keywords,keywords 
			 WHERE domains_keywords_keyword=keywords_id
			 AND domains_keywords_website='$domains_id'
			 ORDER BY keywords_word ASC");

	 while($keywords_result = mysql_fetch_array($keywords_sql)){

	   $keywords_id = $keywords_result['keywords_id'];    
	  $keywords_word = $keywords_result['keywords_word'];

	  echo' <a href="'.$keywords_id.'">'.$keywords_word.'</a>';

	 }

    }

 

is this the correct way of doing the above code? or is there a better way?

[Andy123]

You didn't provide so much information to work with, but I'm going to give it a shot anyways. Instead of executing one query and then for each row, run another query, you could join the two into a single query like this:

 

SELECT keywords_id, keywords_word FROM keywords WHERE keywords_id IN (SELECT keyword_id FROM domains)

 

So your first query is in the IN() part of the above query. Surely you will have to modify it to match your exact table structure.

 

Hope it works!

[jcbones]

What is your first query. I'm sure you could get a join written for you that would be better, and lead to less confusion, or collision.

[brown2005]

well what it does, is the first query selects a website. then the following selects all the keywords for that website, so youd get:

 

website

1. keyword 1
   
2. keyword 2
   
3. keyword 3
   

[Psycho]

well what it does, is the first query selects a website. then the following selects all the keywords for that website, so youd get:

 

website

1. keyword 1
    
   
2. keyword 2
    
   
3. keyword 3
   

As the other two already stated, using a JOIN to create ONE query to get all the records you need is the correct solution. Having queries in loops is one of the worst things you can do with respect to performance. If you provide the first query we can help with how to create the query and how to process it.

[brown2005]

$domains_sql = mysql_query("SELECT domains_id,domains_url FROM domains ORDER BY RAND() LIMIT 1;");

 

that is the first query

Edited November 25, 2012 by brown2005

[brown2005]

$domains_sql = mysql_query("SELECT domains_id,domains_url FROM domains ORDER BY RAND() LIMIT 1;");

while($domains_result = mysql_fetch_array($domains_sql)){

                $domains_id = $domains_result['domains_id'];   
                $domains_url = $domains_result['domains_url'];
                $domains_name = preg_replace('#^https?://www.#', '', $domains_url);

                $keywords_sql = mysql_query("SELECT * FROM domains_keywords,keywords 
                                WHERE domains_keywords_keyword=keywords_id
                                AND domains_keywords_website='$domains_id'
                                ORDER BY keywords_word ASC");

                while($keywords_result = mysql_fetch_array($keywords_sql)){

                  $keywords_id = $keywords_result['keywords_id'];      
                 $keywords_word = $keywords_result['keywords_word'];

                 echo' <a href="'.$keywords_id.'">'.$keywords_word.'</a>';

                }

               }

anyone like to help me write this code better please? thanks in advance

[Barand]

Not tested

 

SELECT d.domains_id, d.domains_url, k.keywords_id, k.keywords_word
FROM
   (
   SELECT domains_id,domains_url
   FROM domains
   ORDER BY RAND()
   LIMIT 1
   ) as d
INNER JOIN domains_keywords dk
   ON dk.domains_keywords_website = d.domains_id
INNER JOIN keywords k
   ON dk.domains_keywords_keyword = k.keywords_id

[brown2005]

Not tested

 

SELECT d.domains_id, d.domains_url, k.keywords_id, k.keywords_word
FROM
(
SELECT domains_id,domains_url
FROM domains
ORDER BY RAND()
LIMIT 1
) as d
INNER JOIN domains_keywords dk
ON dk.domains_keywords_website = d.domains_id
INNER JOIN keywords k
ON dk.domains_keywords_keyword = k.keywords_id

 

thank you very much, this worked giving me the example below, for a website with two keywords:

 

website

keyword

website

keyword

 

so i looked back through my old listings and got some code to display it like

 

website

keyword

keyword

 

$domains_sql = mysql_query("SELECT domains_id, domains_url, keywords_id, keywords_word
   FROM
           (
           SELECT domains_id,domains_url
           FROM domains
           ORDER BY RAND()
           LIMIT 1
           ) as d
   INNER JOIN domains_keywords
           ON domains_keywords_website = domains_id
   INNER JOIN keywords
           ON domains_keywords_keyword = keywords_id
	  ORDER BY keywords_word ASC") or die (mysql_error());


$num = mysql_num_rows($domains_sql);

$current_price = "";


for($i=0;$i<$num;$i++) {

       $row = mysql_fetch_array($domains_sql);

       if($row['domains_url'] != $current_price) {

        echo $row['domains_url']."<br>";

        $current_price = $row['domains_url'];

       }

 echo $row['keywords_word']."<br>";

}

 

and also added the ORDER BY keywords_word ASC and it does what I want, presuming the code I added is the best way this task is all complete so thank you very much

[brown2005]

$domains_sql = mysql_query("SELECT domains_id, domains_url, keywords_id, keywords_word, domains_comments_comment

 FROM
 (
 SELECT domains_id,domains_url
 FROM domains
 ORDER BY RAND()
 LIMIT 1
 ) as d

 INNER JOIN domains_comments
 ON domains_comments_domain = domains_id

 INNER JOIN domains_keywords
 ON domains_keywords_website = domains_id

 INNER JOIN keywords
 ON domains_keywords_keyword = keywords_id
	 ORDER BY keywords_word ASC") or die (mysql_error());


$num = mysql_num_rows($domains_sql);

$current_price = "";


for($i=0;$i<$num;$i++){


$domains_result = mysql_fetch_array($domains_sql);

$domains_id = $domains_result['domains_id'];
$domains_url = $domains_result['domains_url'];
$domains_name = preg_replace('#^https?://www.#', '', $domains_url);				

$keywords_id = $domains_result['keywords_id'];
$keywords_word = $domains_result['keywords_word'];

$domains_comments = $domains_result['domains_comments_comment'];

if($domains_url != $current_price) {

echo $domains_name."<br /><br />";

$current_price = $domains_url;

}

echo $keywords_word."<br />";
echo $domains_comments."<br />";
}

 

prints out:

 

MS Office
domain 1
MS Office
domain 1 - part 1
MySQL
domain 1
MySQL
domain 1 - part 1
PHP
domain 1
PHP
domain 1 - part 1
Visual Basic
domain 1
Visual Basic
domain 1 - part 1

 

 

and i need it to be:

(info from keywords)



MS Office
MySQL
PHP
Visual Basic

(info from comments)

domain 1
domain 1 - part 1

Edited November 26, 2012 by brown2005

[Psycho]

You are making it harder than it needs to be. Just do the one query and process the records in a while() loop. Then if you have certain fields that can be duplicated that you want printed one time, create a flag variable in the loop to check when it changes. Here is a basic example based upon what you have now

 

$keyword_id = false;
while($row = mysql_fetch_assoc($domains_sql))
{
   //Check if this keyword ID is different from the last
   if($keyword_id !== $row['keywords_id'])
   {
    //Display keyword as header
    echo "<br><b>Keyword: {$row['keywords_id']}</b><br>\n";
   }
   //Display domain
   $domains_name = preg_replace('#^https?://www.#', '', $row['domains_url']);   
   echo "$domains_name<br>\n";
}

[brown2005]

ill explain it a little bit more. i have a number of tables.

 

domains (main table)

 

domains_comments (sub table - linked dy domains_comments_id=domains_id)

domains_ratings (sub table - linked dy domains_ratings_id=domains_id)

domains_keywords (sub table - linked dy domains_keywords_id=domains_id)

 

so what I would normally do is

 

 

$domains_sql = mysql_query("");

while($domains_row = mysql_fetch_array($domains_sql)){


$domains_comments_sql = mysql_query("");

while($domains_comments_row = mysql_fetch_array($domains_comments_sql)){


}



$domains_ratings_sql = mysql_query("");

while($domains_ratings_row = mysql_fetch_array($domains_ratings_sql)){


}

}

I have been told to do just the one query instead of this?

Edited November 28, 2012 by brown2005

[Christian F.]

Yes, one query using JOINs will fix that. Then reference Psycho's post above, for the principle on how to detect and print out different headings for sectioned data.

[Barand]

try

<?php
$domains_sql = mysql_query("SELECT domains_id, domains_url,
   GROUP_CONCAT(keywords_word ORDER BY keywords_word SEPARATOR '<br>') as keywords
   GROUP_CONCAT(domains_comments_comment SEPARATOR '<br>') as comments	    
FROM
   (
   SELECT domains_id,domains_url
   FROM domains
   ORDER BY RAND()
   LIMIT 1
   ) as d

   INNER JOIN domains_comments
   ON domains_comments_domain = domains_id

   INNER JOIN domains_keywords
   ON domains_keywords_website = domains_id

   INNER JOIN keywords
   ON domains_keywords_keyword = keywords_id

GROUP BY domains_id") or die (mysql_error());

while (list($id, $url, $keywords, $comments) = mysql_fetch_row($domains_sql)) {
   echo "$url<br /><br />$keywords<br />$comments<br />";
}

?>