Page 1 of 1
Query in and out of loop
Posted: Wed May 30, 2007 1:52 pm
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;
Posted: Wed May 30, 2007 1:59 pm
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.
Posted: Wed May 30, 2007 2:04 pm
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.
Posted: Wed May 30, 2007 2:07 pm
by psurrena
The reason the query was run the first time was to get the section title. Is there another way?
Posted: Wed May 30, 2007 2:08 pm
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.
Posted: Wed May 30, 2007 2:13 pm
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.
Posted: Wed May 30, 2007 2:19 pm
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.
Posted: Wed May 30, 2007 2:22 pm
by psurrena
got it - Thanks!
So it's bad practice to select * when not using all?
Posted: Wed May 30, 2007 2:29 pm
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.