is this a messy query?

[fife]

Hi

 

Right here is what I'm trying to do.  I have in my members table 5 fields called fav_1 to fav_5.  These fields are just category ID's.  In my group table I have also got a catID (cat) field.  Im trying to make a query which will match at random the group cat to any of the members top 5 where the area first and county second are the same and limited by 5.  I also want it to order it by fav_1 and down the scale.  I have wrote the query but it just looks really messy.  Is there a nicer way to do this?

 

$qAdds = "SELECT name, cover, cat, subcat, thumbsup, FROM `groups` WHERE `cat` = ".$User['fav_1']." OR `cat` = ".$User['fav_2']." OR `cat` = ".$User['fav_3']." OR `cat` = ".$User['fav_4']." OR `cat` = ".$User['fav_5']." AND `area` = ".$User['area']." AND county = ".$User['county']." ORDER BY rand() LIMIT 5";

[mhodge_txs]

Im confused by your question. If the statement you currently have producing the result set you want and are just merely asking if there is a better way to make it neater, of are you asking how to fix the logic in the statement (because it seems to me that the logic in your question does not match the logic in your statement).

 

Have you used the IN operator before ? If not check it out

http://www.w3schools.com/sql/sql_in.asp

[fife]

Im just asking if there is a neater way to write the statement.  say this or this or this or this and this and this just seems really long winded.  Is it possible to write it more cleanly or have I written it the best way you can?

[mhodge_txs]

Did you take a look at the IN operator ? that will help clean up the statement quite considerably. Other then that I dont have another suggestion.

 

[fife]

//is this correct

$qAdds = "SELECT name, cover, cat, subcat, thumbsup, FROM `clubs` WHERE `cat` IN = ('".$User['fav_1']."', '".$User['fav_2']."', '".$User['fav_3']."', '".$User['fav_4']."', '".$User['fav_5']."') AND `area` = ".$User['area']." AND county = ".$User['county']." ORDER BY rand() LIMIT 5";

//??

[mhodge_txs]

You have "IN = (" it should just be "IN("

 

How about this.

 

$sql = sprintf ("SELECT name, cover, cat, subcat, thumbsup, FROM clubs 
					WHERE cat IN('%s', '%s', '%s', '%s', '%s') 
					AND area = '%s' 
					AND county = '%s' 
					ORDER BY rand()
					LIMIT 5",
					$User['fav_1'],$User['fav_2'],$User['fav_3'],$User['fav_4'],$User['fav_5'],
					$User['area'],
					$User['county']);

[fife]

that looks much better.  I have never seen it written '%$' before.  thats really good thank you.  I will try it now and let you know how it goes.

[fife]

ok I used the code given and it seem to not be doing anything.  I dont think it is wrong because it does not throw any errors but here is what I have.  It should come back with one name....

 

$Adds =  mysql_query ("SELECT name, cover, cat, subcat, area, county, thumbsup, FROM clubs 
					WHERE cat IN('%s', '%s', '%s', '%s', '%s') 
					AND area = '%s' 
					OR county = '%s' 
					ORDER BY rand()
					LIMIT 5",
					$User['fav_1'],$User['fav_2'],$User['fav_3'],$User['fav_4'],$User['fav_5'],
					$User['area'],
					$User['county']);

		while($CP = mysql_fetch_assoc($Adds)){ 
					echo "{$CP['name']}<br/>";  }}

[Muddy_Funster]

You have replaced the sprintf() with mysql_query(), thus your use of '%s' is being taken litteraly by the SQL. 

Don't place raw strings inside of mysql_query().  assign the string to a variable and then sanitise it fist, placing the sanitised variable inside the mysql_query(), this also has the benefit of allowing you to echo out your actual query string as it will be when it is sent to the database.

[chaseman]

$dbc = mysqli_conncet(your database connection info);
$query = sprintf('your query string');

$query_run = mysqli_query ($dbc, $query); // The final query request

[Muddy_Funster]

$dbc = mysqli_conncet(your database connection info);
$query = sprintf('your query string');

$query_run = mysqli_query ($dbc, $query); // The final query request

except that the OP is using the mysql library, not the mysqli one....

[chaseman]

Yeah I noticed that, mysql improved just flows my boat better

[fife]

Ok.  I have made the changes and read up on the new commands you have sent me.  I re-wrote the query to match but still it echo's nothing.  I even tried just to echo $adverts but it seems to be empty.  Which would suggest the query is wrong.  I tried mysqli_query and mysql_query but nothing.  Let me explain again what I want coz maybe im going about this the wrong way.

 

// ok so I want to find all the clubs which cat of the club is equal  to the users top 5 favourite categorys.  Also in the query the returned clubs must be in either the same town or county .



$Adds =  sprintf("SELECT `name`, `cover`, `cat`, `subcat`, `area`, `county`, `thumbsup`, FROM `clubs` 
					WHERE `cat` IN('%s', '%s', '%s', '%s', '%s') 
					OR `area` = '%s' 
					OR `county` = '%s' 
					ORDER BY rand()
					LIMIT 5",
					$User['fav_1'],$User['fav_2'],$User['fav_3'],$User['fav_4'],$User['fav_5'],
					$User['area'],
					$User['county']);

					$adverts = mysqli_query($Adds) or die(mysql_error());

                                	

// I then just want to display the names of these clubs for now.  At the minute with the user im using it should display one club!  




		while($CP = mysql_fetch_assoc($adverts)){ 
					echo "{$CP['name']}<br/>";  }



// that query seems to echo nothing but I know it works because if I write........

$query = mysql_query("SELECT * FROM `clubs` WHERE `cat` = ".$User['fav_1']." ORDER BY rand() LIMIT 5")

while ($result = mysql_fetch_assoc($query)) {

echo "{$result['name']}<br/>";
}

//then my query comes back with a name.  I have check that my town and county between the club and the user are the same. So does anybody have any idea whats wrong or maybe another way to write the query to incorporate the above?  

[fife]

ok I now have an error to work with.  can anyone make sense of this?

 

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `clubs` WHERE `cat` IN('22', '20', '17', '26', '25') OR `area' at line 1

//for this query

$Adds =  sprintf("SELECT `name`, `cover`, `cat`, `subcat`, `area`, `county`, `thumbsup`, FROM `clubs` 
					WHERE `cat` IN('%s', '%s', '%s', '%s', '%s') 
					OR `area` = '%s' 
					OR `county` = '%s' 
					ORDER BY rand()
					LIMIT 5",
					$User['fav_1'],$User['fav_2'],$User['fav_3'],$User['fav_4'],$User['fav_5'],
					$User['area'],
					$User['county']);

					$adverts = mysql_query($Adds) or die(mysql_error());

		while($CP = mysql_fetch_assoc($adverts)){ 
					echo "{$CP['name']}<br/>";  }

[Muddy_Funster]

the comma just before FROM

[fife]

Thank you very much the query works great now!

[Muddy_Funster]

Yeah I noticed that, mysql improved just flows my boat better

fair comment, but without knowing whether the mysqli library is enabled on someone elses server or not, it's probably better to stick with what flows their boat, rather than yours

[chaseman]

Yeah I noticed that, mysql improved just flows my boat better

fair comment, but without knowing whether the mysqli library is enabled on someone elses server or not, it's probably better to stick with what flows their boat, rather than yours

True that. ... I'm so egoistical  :'(

[Muddy_Funster]

Yeah I noticed that, mysql improved just flows my boat better

fair comment, but without knowing whether the mysqli library is enabled on someone elses server or not, it's probably better to stick with what flows their boat, rather than yours

True that. ... I'm so egoistical  :'(

lol