Help Simplfying This Query

[joe_C_nice]

Is there anyway I could turn this into one query

$sql = mysql_query( "select * from votes where id='$_GET['item']' ");		


   $sum = mysql_query("SELECT SUM(stars) FROM votes WHERE id='$_GET['item']' ");        
   $sum = mysql_fetch_assoc($sum); 
   $num_rows = mysql_num_rows($sql);

   $rating = $sum['SUM(stars)'] / $num_rows; 	  

   $rating = round($rating);	

[laserlight]

It looks like that all you need the SELECT for is for counting total number of rows.
That can be obtained with a SELECT COUNT()

$query = "SELECT SUM(stars) AS sum_stars, COUNT(*) AS num_rows
	FROM votes WHERE id='{$_GET['item']}'";
$result = mysql_query($query)
	OR die(mysql_error()); //for debugging
$row = mysql_fetch_assoc($result);
$rating = round($row['sum_stars'] / $row['num_rows']);

Oh, and remember to validate $_GET['item'] before you use it.
If not, you risk SQL injection.

[joe_C_nice]

Is it possible for me to attach it to an existing query.

//Here's the existing query
mysql_query("SELECT list.*, inv.* from your_list AS list INNER JOIN inventory AS inv ON list.item_num=inv.item_num AND list.user='".$customer."'");

//what this query does it get all relevant product information for items stored in the your_list table.

[Aurasia]

I've seen people speaking about "validating" $_GET['variable'] ... what does that mean? Why do it and how to do it?

[joe_C_nice]

Any suggestions.

Other than placing this in a while loop.

$sql = mysql_query( "select * from votes where id='$_GET['item']' ");        


   $sum = mysql_query("SELECT SUM(stars) FROM votes WHERE id='$_GET['item']' ");        
   $sum = mysql_fetch_assoc($sum); 
   $num_rows = mysql_num_rows($sql);

   $rating = $sum['SUM(stars)'] / $num_rows;       

   $rating = round($rating);

[Trs2988]

Originally posted by Aurasia
I've seen people speaking about "validating" $_GET['variable'] ... what does that mean? Why do it and how to do it?

$GET (and usually $POST) variables are dangerous, because any user/hacker can come along, change the address bar, and all the sudden they can break into your website. For example, let's suppose you have a form that emails a user their username and password if they forget it. You might have them enter in their email address to identify who it is. You might then have code like this to fetch their username and password:

extract(mysql_fetch_assoc(mysql_query("select username,password from users where email='$_GET[email]'")));

Although it's condensed code, you would expect that code to work correctly. However, what if someone enters this into the email box:

[e-mail]' or 1=1

Now, the query will read:

select username,password from users where email='[e-mail]' or 1=1

That query will return the username and password of the first person in the database (most likely an admin) and email it to whatever email the hacker would like.

SO, now that you know why it's necessary, how do you validate it? Well, it really depends on what type of variable you're EXPECTING it to be.

• If the variable is supposed to be a number (like an ID or a quantity), you can use is_numeric() to make sure it's a number.

• If it's something like a select box, then you should make sure that they have choosen one of the possible selections (by comparing it to all of the choices), and exiting if it's not.

• If it's something complex like an email address, then you can use ereg() to make sure there's no quotes or anything.

• Finally, if it's a comments field or maybe something that would be posted on a forum, you can use htmlspecialchars() to convert quotes or other special characters into characters that won't interfere with your queries.

Originally posted by
joe_C_nice
Any suggestions.

Other than placing this in a while loop.

If I were you, I'd would use what laserlight said. You don't want to combine too many queries, just because you can end up with extremely complicated queries that have strange bugs or, better yet, cause you to forget what they do and then have the impossible trouble of figuring it out again. Then again, you may have your own reasons for just wanting to combine it into one.