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

Its especially confusing as NEITHER of the divs are 960px. One suggestion: Add <head></head> tags and all the necessary tags within. I'm not sure why that would help but its worth a try.

Not sure what many-to-many means exactly so I'll just explain the general setup. I'm surprised how such a simple request seems to be so difficult to answer, not just on this forum. Part of my site is about 'rating' other peoples content that they've created. So table1 is the 'Ratings' table. Because lots of people will rate the same thing, lots of rows are created for each person that posts a rating. The thing they're actually rating is in table2. Each user can have multiple things to be rated, all contained in multiple rows in table2. If the 'ready' field is 1 on any of these things, the number of ratings for it should be 'count()'ed. The things in table2 where the 'ready' is 0 should be ignored. Thats basically it, any ideas?

Cos only the rows from table1 where ready is equal to 1 in table2 are needed, the information is in two different tables. Just doing a count() of table2 doesn't do the job.

I have two tables. table1 has a field called user_id table2 has a field also called user_id and another one called ready What I'm trying to do is count how many rows there are in table1 where the user_id's in table1 and table2 are the same AND where ready in table2 in those selected rows is equal to 1. This is one of the SQL queries I've tried so far but to no avail. SELECT count(*) FROM table1 INNER JOIN table2 ON table1.user_id=table2.user_id AND table2.ready=1 This one vaguely works but with the problem that all queries are multiplied by the number of rows in table1. Any way I can get this right?

Hi there, I think this is quite a simple one and a tutorial to a webpage that addresses it will suffice - all I can find so far are pages dedicated to drawing the images yourselves or using blank backgrounds. Heres an image description of what I want to do. Essentially, based on different variables, for marker.gif to appear in different areas of the grid. My assumption then is that the grid.gif has to be a background image during the PHP processing whilst marker.gif is the dynamically moved image. Anyway, hope one of you guys can help me sort this one out.

Magic, it works. If theres any other way of shortening it please let me know. I used to only have it like this: if (($_GET['do']=='read') && (ereg("^[0-9]+$", $_GET['id']))){ And it was all fine, but then I asked to have diplay_errors on and my site went slightly mental. But if what you suggested is the best, I shall go with it, cheers!

I'm trying to get it so that it only allows a certain section of the page to be shown when: mysite.com/page.php?do=read&id=4 When id is an integer.

How can this be written properly? if ((isset($_GET['do'])=='read') && (isset(ereg("^[0-9]+$", $_GET['id'])))){ The error it gives: Parse error: syntax error, unexpected T_STRING, expecting T_VARIABLE or '$' in /home/tbhgweb/public_html/mail.php on line 133 Its the ereg part it doesn't like.

Sorry, I should elaborate. The problem is putting a variable inside an include function, not generating the variable itself. Everytime I try, the include file doesn't show.

Very simple question with what I hope will be a simple answer. Here's what I want to do simplified: <?php $x_id=4; include (mypage.php?var=$x_id); ?> Is this possible? I need the x_id variable to be dynamic depending on circumstances. Cheers!