Condensing(joining) multiple queries(I'm wearing my fingers down to nubs)

schwim

Hi there everyone,

I'm trying very hard to leave the comfort of the very basics and journey into more efficient coding. Usually, I'm able to find the necessary methods by googling, but I'm not having much luck with this particular problem. Here's my example.

I'm listing classified ads that a user owns on a profile page. During the database query, I'm pulling all records that matches their user_id. Once I've got that, I perform another query to get the category title for the present record in the loop and in the very same loop, I do a third query to get the title of the parent category for the category that the ad is under. Here's what it ends up looking like:

				$query1="SELECT id,cat_id,title,DATE_format(create_date, '%b %e, %Y, %l:%i%p') as create_date,DATE_format(start_date, '%b %e, %Y, %l:%i%p') as start_date,life_span FROM classifieds_ads WHERE user_id=$user_id";
				$result1=mysql_query($query1) or die (mysql_error());
				while($row=mysql_fetch_array($result3)){
					$id=$row['id'];
					$cat_id=$row['cat_id'];
					$title=$row['title'];
					$create_date=$row['create_date'];
					$start_date=$row['start_date'];
					$life_span=$row['life_span'];

				$query2="SELECT title,parent FROM classifieds_categories WHERE cat_id=$cat_id";
				$result2=mysql_query($query2) or die (mysql_error());
				while($row=mysql_fetch_array($result2)){
					$title=$row['title'];
					$parent=$row['parent'];

					$query3="SELECT title FROM classifieds_categories WHERE cat_id=$parent";
					$result3=mysql_query($query3) or die (mysql_error());
					while($row=mysql_fetch_array($result3)){
						$parent=$row['parent'];

					}
				}
			echo("
			<a href='link.php?id=".$id.">".$title."</a> Created: ".$create_date." in Category: ".$parent_title."~>".$title." <br />
			");
			}

Which leads me to my question; Is there a better way to handle this? All of these nested loops are making me quite dizzy and it seems wasteful to keep pasting the same code over and over. For the life of me, however, I can't find anything through my searches, likely because I don't know what to search for.

Any thoughts on the matter would be greatly appreciated.

thanks,
json

laserlight

schwim wrote:
I'm listing classified ads that a user owns on a profile page. During the database query, I'm pulling all records that matches their user_id. Once I've got that, I perform another query to get the category title for the present record in the loop and in the very same loop, I do a third query to get the title of the parent category for the category that the ad is under.

I suggest that you outline the relationships between the tables. Your description makes it sound like each ad belongs to exactly one category, but it is not so clear for categories. If an ad always belongs to exactly one category, then you certainly do not need a loop to retrieve its category.

Furthermore, it looks like your example code has bugs. For example, the $title variable is overwritten in the inner loop, yet it is printed twice. The $parent_title variable, on the other hand, does not appear to exist and yet is used.

Generally, it should be possible to solve your problem by the use of joins, but I am reluctant to give an example until I have a clearer picture of what exactly are the relationships involved and what exactly you are trying to select.

schwim

Hi there laserlight,

Thanks for the reply and sorry for the confusion. The code is indeed incorrect as I was writing the example in situ and messed up the bottom. The variable in the last loop should be determining the title of the parent category, so

$parent=$row['parent'];

should be:

$parent_title=$row['title'];

This is so I can have the name of the category that the ad is in as well as the title of that category's parent category.

An ad belongs to only one category and each category belongs to only one parent category. "For Sale" has it's own children and "Wanted" has it's own. In the database, each category has a parent row, in which the id of the parent category is placed. If it's a root category, like for sale or wanted, the parent_cat is 0.

My nested queries are to get the ad, the category title and that category's parent title, for the purpose of creating a breadcrumb.

I hope this makes more sense.

thanks,
json

laserlight

Is it possible that an ad's category is a root category?

EDIT:
But that is a minor point that you should be able to handle yourself. This is roughly what you might do:

$query = sprintf("SELECT ads.id, ads.cat_id, ads.title,
        DATE_FORMAT(ads.create_date, '%b %e, %Y, %l:%i%p') AS create_date,
        DATE_FORMAT(ads.start_date, '%b %e, %Y, %l:%i%p') AS start_date,
        ads.life_span, categories.title AS category_title, parents.title AS parent_title
    FROM classifieds_ads AS ads
        INNER JOIN classifieds_categories AS categories ON ads.cat_id=categories.cat_id
        LEFT JOIN classifieds_categories AS parents ON categories.parent=parents.cat_id
    WHERE ads.user_id=%d",
    $user_id);

$result = mysql_query($query)
    or die(mysql_error()); // for debugging only

while ($row = mysql_fetch_assoc($result)) {
    printf('<a href="link.php?id=%d">%s</a> Created: %s in Category: %s ~> %s <br />',
        $row['id'],
        htmlspecialchars($row['title']),
        htmlspecialchars($row['create_date']),
        htmlspecialchars($row['parent_title']),
        htmlspecialchars($row['category_title']));
}

It may contain syntax errors, etc, but hopefully the idea is clearer now.