Query in and out of loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Query in and out of loop

Post by psurrena »

My code below works but I think it could be done better. When I use $r in the while loop, just as the variable, it goes forever. How can I run the query once and use it both in and out of a loop?

Regards,
Peter

Code: Select all

switch($mode){
	case"cat":
		$cat=$_GET['type'];
										
		$query="SELECT * FROM project LEFT JOIN category USING (category_id) WHERE category_id LIKE'%$cat%' ORDER BY project_name ASC";
		$result=mysql_query($query) OR DIE (mysql_error());
		$r=mysql_fetch_assoc($result);
		echo "<h2>".$r['category_title']."</h2>";
		
		while ($row=mysql_fetch_assoc($result)){
			echo '<a href="work.php?mode=item&id='.$row['project_id'].'">'.$row['project_name']."</a><br />";
		}
		break;
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

The query is being run only once as is.

And when you just use $r, you aren't actually using mysql_fetch_assoc($result) in the loop, you are using the result of mysql_fetch_assoc($result). So, $r doesn't magically change value each time you use it, otherwise, when you tried to access $r['category_title'], you'd actually be getting the second row rather than the first.

Your code is fine. If you really want to change it, then pick either $r or $row, and stick with it. In your current code, you create $r, use it once, and never touch it again. It's a waste of space, IMO. :-p

But if you want to access the data from $r later on, then leave it as is.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Something of note is that the first record is basically being thrown away (it won't appear in the links generated.) There's also no logic for when the query returns zero records.
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

The reason the query was run the first time was to get the section title. Is there another way?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

While we're at it, filter $cat=$_GET['type'], especially since you use it in your query.

And he's saying that if your first record contains a link, you're not using it.
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

Filter? As in, if it's an integer have php make sure it's a number?

Why does it ignore the first record?

Other logic will be added later - first thing first.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

IT doesn't ignore the first record... You do.
You get the first result, and you only get category_title out of it, and you ignore project_id and project_name.

And yeah, you should run a mysql_real_escape_string() on $_GET['type'] or, if it's supposed to be an integer, typecast it as such.
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

got it - Thanks!

So it's bad practice to select * when not using all?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

psurrena wrote:So it's bad practice to select * when not using all?
Always has been.

The less MySQL has to do, the faster it runs.
Post Reply