Petsmacker

I run a site where people can rate articles written by members (user_id). These articles can then be rated by others on a special scale. TABLE: articles - id | user_id | title 1 | 3 | Transformers 2 | 5 | Shrek 3 | 2 | Shrek TABLE: article_ratings - article_id | rating_x | rating_y 1 | 4 | 5 2 | 3 | 2 2 | 4 | 4 3 | 2 | 5 2 | 5 | 3 3 | 4 | 4 Basically what I'm trying to do is get the average of the averages for a specific article. Example: I want to know the average of the average ratings for all the articles on 'Shrek' The Shrek article ID's are 2 and 3 The average rating for Shrek with ID:2 are: rating_x = 4 rating_y = 3 The average rating for Shrek with ID:3 are: rating_x = 2 rating_y = 3 Then average those both together to bring a result of: rating_x = 3 rating_y = 3 ^^^^^^^^^^ And this result just here is the kind of output I want my database to provide. I'm able to get the averages alone but am not able to get the average of the averages. I'm using PHP and SQL if that helps. I'm thinking something along the lines of: SELECT AVG(AVG(article_ratings.rating_x)), AVG(AVG(article_ratings.rating_y )) FROM article_ratings, articles WHERE articles.title='Shrek' AND articles_rating.article_id=articles.id Obviously, two AVG()'s overlapping aren't allowed and my SQL isn't great as you can see so I hope someone can help.

I almost feel like breaking into tears the solution was so easy after tearing my hair out for ages. Thank you very, very much.

$string2="[url=http://www.example.com]Example[/url] and [url=http://www.example.com]Example[/url] and [url=http://www.example.com]Example[/url]"; preg_match_all("/(\[url=)(.+)(\])(.+)(\[\/url\])/", $string2, $urlmatches); echo "URLs :" . count($urlmatches[0]); echo "<br><br><br>"; $string2="[url=http://www.example.com]Example[/url] and [url=http://www.example.com]Example[/url] and [url=http://www.example.com]Example[/url]"; preg_match_all("/(\[url=)(.+)(\])(.+)(\[\/url\])/", $string2, $urlmatches); echo "URLs :" . count($urlmatches[0]); In the above code, the first section will output '1', despite there being 3 URLs, the second one works correctly and outputs '3' but only because each one is on a new line, is there any way to get the first one to work so multiple URLS on the same line will be counted? (By the way, this isn't my code, I'm aware its full of security holes, I've just cut out the rubbish to show the root problem)

I've FINALLY solved the issue. My second-to-last post, I misunderstood what I needed to do. If anyone else is having this problem, you need to add: mysql_query("SET NAMES 'utf8'"); After your mysql connect area like so: mysql_connect("$localhost","$db_user","$db_pass"); @mysql_select_db("$db_name"); mysql_query("SET NAMES 'utf8'"); Now, when you enter special characters in the DB, they'll come out as they should. Enjoy!

My query on the PHP page and the one I use in phpMyAdmin (including the query it shows after its executed) are both completely the same, the problem seems to be exclusive to queries performed on the PHP page.

Can't modify post anymore. Just in case someone was about to suggest it: SET NAMES 'utf8' UPDATE temp2 SET thing='••••' Doesn't work either. I'm going absolutely insane because I can't continue my project until this issue is resolved...

Yeah, they are in the UTF-8 charset and I made my database UTF-8, the table UTF-8, the row UTF-8. So I've had to rule out that issue. I have no idea why for example, bullet points: • come out as • I've output the Query that has been sent to the database and its fine. UPDATE temp2 SET thing='••••' When I do this via phpMyAdmin, it goes through as it should and •••• is in the database. However, when I perform the query on a normal PHP page, thats when it becomes garbled. Does anyone really not have any idea as to why?

I'm having a problem where if I input special characters such as any foreign characters or – (this is an em-dash) ‘ ’ • “ ” They go into the database as something along the lines of “ or another character might come out as •. I output the strings at every point before they go into the database and they seem to be fine so I'm assuming its an SQL problem. Both my META and PHP header tags are set to UTF-8. I also put my database as UTF-8 to see if that'd help but no. I'm still very confused as to the whole characters vs encoding thing so maybe UTF-8 isn't even on the same page as the issue I'm trying to resolve. Any ideas?

Legend. Thats solved the issue. Needless complications...

If I add: $p=str_replace("a", "gfdgfgfdg", $p); to it it works just fine. Nonetheless, I tried adding your bit but no, it does nothing. The characters aren't replaced. Letters, numbers and simple punctuation don't seem to be a problem for str_replace to work but I can't get it to with those characters.

<? $p=$_POST['p']; if ($_POST['p']){ $p=str_replace("“", "\"", $p); $p=str_replace("”", "\"", $p); $p=str_replace("’", "'", $p); $p=str_replace("‘", "'", $p); $p=str_replace("–", "-", $p); $p=str_replace("•", "-", $p); }?> <table width="100%"><tr><td class="left"><div id="gg"><?echo "$p";?></div></td></tr></table><br><br> <form method="POST" name="ff"> <textarea style="width:60%;height:400px;" name="p"></textarea> <br><input type="submit" value="Go"> </form> My code is as simple as that but str_replace isn't changing them. Thats ALL the HTML and PHP on my page I've stripped away any other things that could cause it. Thats all on my page. Why aren't things changing?

I tweaked the code and you gave me, and guess what - we have workidge! Here is the full query for anybody that needs it. SELECT e.user_id, MAX(e.time) FROM watch w JOIN events e ON e.user_id = w.watch_id WHERE w.user_id = 1 GROUP BY e.user_id ORDER BY MAX(e.time) DESC Thank you so much corbin.

Your query gave me this error: 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 'WHERE e.user_id = 1 ORDER BY e.time DESC I placed the WHERE section behind the GROUP BY: SELECT w.user_id, MAX(e.time) FROM watch w JOIN events e ON e.user_id = w.watch_id WHERE w.user_id = 1 GROUP BY e.user_id ORDER BY e.time DESC But it returned 0 rows which is incorrect. Aargh!

Hey thank you, it almost works, I had to fix ORDER BY w.time because it brought up an error. I changed it to e.time. I also added DESC at the end. "SELECT * FROM watch w JOIN events e ON e.user_id = w.user_id WHERE w.user_id = 1 ORDER BY e.time DESC;" -------- Assuming the DB still looks like this: watch - user_id | watch_id 1 | 34 1 | 56 events - user_id | time 56 | 1235278075 75 | 1235235986 34 | 1235280000 34 | 1000050000 -------- Running the revised query you gave me creates results like this: 34 | 1235280000 56 | 1235280000 34 | 1235280000 All the times are the same. -------- I edited the query slightly to see if I could get it working: SELECT * FROM watch w JOIN events e ON e.user_id = w.watch_id WHERE w.user_id = 1 ORDER BY e.time DESC But now its giving me: Results: 34 | 1235280000 56 | 1235278075 34 | 1000050000 So it is only giving me the ID's in watch and they are in order but they're not distinct. I've had numerous goes at getting it to work but when I add DISTINCT, it then ignores the ORDER BY time DESC --- I hope this is clear, I'm so stuck.

I'm trying to retrieve information from one database and use it to order the details of another. First I'll give a basic interpretation of my database situation. (Bold words are table names) watch - user_id | watch_id events - user_id | time What I'm attempting to do, is get the watch_id's from watch and find its first user_id equivalent in events and then subsequently order the results by time desc. For example: watch - 1 | 34 1 | 56 events - 56 | 1235278075 75 | 1235235986 34 | 1235280000 34 | 1000050000 So assuming my User_id is 1, I need to get the user_id's in events that are in my watch_id's in watch and then have it so that in a query, they're ordered by time DESC. In this case, it would give me the results: 34 | 1235280000 56 | 1235278075 I hope I've explained the situation properly. Any help would be greatly appreciated. Thanks