Jump to content

n8w

Members
  • Posts

    123
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

n8w's Achievements

Regular Member

Regular Member (3/5)

0

Reputation

  1. Thanks Barand .. that was a ton of help Now I added a varchar field to my illustrator_table called related_illustrators which I plan to store a string of the related illustrator ids example 123,23454,2345,1232 I want to run a cron job once a day to store these values here is my cron job and I know it's super inefficient .. could you take a look at it to help me optimize it? Any help greatly appreciated $start =""; $querytime=""; $start = microtime(TRUE); ############################# # BEGIN: related illustrators ############################# $sql = 'UPDATE illustrators_table SET related_illustrators=""'; //initialize $retid = mysql_query($sql) or die(mysql_error()); $sql = 'SELECT illustrator_id, COUNT(*) as favorite_count FROM favorites GROUP BY illustrator_id'; $retid = mysql_query($sql) or die(mysql_error()); # check for errors if (!$retid) { die(mysql_error()); } else { while ($row = mysql_fetch_array($retid)) { $illustrator_id = $row[illustrator_id]; $favorite_count = $row[favorite_count]; if($favorite_count>0){ $sql2 = 'SELECT p.illustrator_id, count(*) as favorite_count FROM favorites p INNER JOIN (SELECT user_id FROM favorites WHERE illustrator_id = '.$illustrator_id.') as X using (user_id ) WHERE illustrator_id <> '.$illustrator_id.' GROUP BY illustrator_id ORDER BY favorite_count DESC LIMIT 17'; $retid2 = mysql_query($sql2) or die(mysql_error()); while ($row = mysql_fetch_array($retid2)) { $related_illustrators .=", ". $row[illustrator_id]; } $sql3 = 'UPDATE illustrators_table SET' . ' related_illustrators = "'.$related_illustrators.'"' . ' WHERE' . ' illustrator_id = "'.$illustrator_id.' " LIMIT 1'; //$retid3 = mysql_query($sql3) or die(mysql_error()); } } } $querytime = microtime(TRUE) - $start; echo $querytime;
  2. I am trying to figure out the logic / mysql query I need to figure out "related suggestions" So for example I have these illustrators http://www.illustrationmundo.com/illustrators.php and if you like illustrator A you will probably like illustrator B,D,Z based on a table that contains people's favorite illustrators the illustrator favorite table looks like this illustrator_favorites user_id=the user who is favoriting the illustrator illustrator_id=the illustrator that is being favorited So I assume I need to figure out people that like illustrator 12547 usually also likes illustrators 124,125478 and 15429 I have no idea how to do this .. any help is greatly appreciated
  3. Thanks Awjudd good advice I just figured out the problem ... I was missing () on part of the statement incorrect SELECT * FROM mytable WHERE user_id=1 && c1 LIKE "%jj%" || c2 LIKE "%jj%" ORDER BY c_timestamp DESC correct SELECT * FROM mytable WHERE user_id=1 && (c1 LIKE "%jj%" || c2 LIKE "%jj%" ) ORDER BY c_timestamp DESC
  4. integer (primary key)
  5. I am getting some unexpected results and I think it's because in my sql statement I am mixing up true when I want it to return just the literal match of user_id that is "1" SELECT * FROM mytable WHERE user_id='1' right now I think it's selecting everthing if the user_id is not empty .. hence it interprets user_id='1' as being true How can I write this so it selects everything from table where the user_id literally is the number 1? Thanks
  6. Thanks .. this worked great update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)
  7. I have a column that has values like record 1 = bob,nancy, sally record 2 = bob,nancy record 3 = nancy, sally What I would like to do is replace all instances of nancy with betty so all my records would look like this record 1 = bob,betty, sally record 2 = bob,betty record 3 = betty, sally can someone please point me in the right direction of what my sql statement should look like? Thanks
  8. I want to walk through the mysql_fetch_array twice currently I am just running the same sql statement twice $retid = mysql_query( $sql); $retid2=mysql_query( $sql); Which, I know isn't effecient making two calls to the db when I already have the info What is the best way to walk through the result twice? similar to while ($row = mysql_fetch_array($retid)) { //my code } other stuff while ($row = mysql_fetch_array($retid2)) { //my code }
  9. Hey Biobob, Thanks for replying I think I am calling the wrong tables because I am getting a crazy count sql SELECT favorite.letter_id, favorite.user_id, COUNT( favorite.user_id ) AS the_count FROM favorite LEFT JOIN `user` ON `user`.`user_id` = `favorite`.`user_id` LEFT JOIN `letter` ON `letter`.`user_id` = `letter`.`user_id` WHERE user.user_status = 'active' AND letter.letter_visible =1 GROUP BY user.user_id HAVING COUNT( letter.user_id ) >0 LIMIT 10 results should be in the 100 range letter_id user_id the_count 89 1 40230 31 3 894 140 17 1341 85 23 2235 104 26 1788 147 27 9387 46 30 447 128 31 894 104 34 24138 267 39 447 Do you see where I am going wrong? Thanks!
  10. I have 3 tables favorite -favorite_id -user_id -letter_id user -user_id -user_status letter -letter_id -user_id -letter_visible I would like to count how many favorites a user has SELECT letter_id,user_id, COUNT(user_id) as the_count FROM favorite GROUP BY user_id but now I want to count how many favorites a favorite.user_id has where the user.user_status=active and the letter.letter_visible=1 The tricky part is favorite.user_id is the person who marked the letter as their favorite not the person who created it So I have to find who created it by looking up favorite.letter_id and seeing if their user.user_status=active So in plain english I would like to find How many favorites a user has where the status of the person who created the letter is active and the status of the letter is visible. Thanks for any help!
  11. //in function going_in($text){ $text = trim($text); $text = mysql_real_escape_string($text); return $text; } //out function going_out($text){ $text = stripslashes($text); return $text; }
  12. n8w

    encoding question

    sorry I am novice at this but How do I store it as utf-8 how many characters should I allow for the varchar? I only need to store one character .. but do I need more characters when I utf8_encode it? so when it goes into the database should my functions look like this? putiting into database function bad_chars($text){ $text = trim($text); $text = mysql_real_escape_string($text); $text = utf8_encode($text); return $text; } pulling out of database and displaying on web page function converttohtml($text){ $text = stripslashes($text); $text = utf8_decode($text); return $text; }
  13. n8w

    encoding question

    Thanks .. but do you think that is the best way to go about it? Technically I can do this .. but I am not sure if that is the best approach. I will the context so it makes more sense So i have a site where you can browse different versions of letters so if you go to http://www.letterplayground.com/index.php?letter=a you will see all the a's people have submitted and I am actually storing the letter with a column called letter which is just char(1) but now I realize there is some problem with that approach for example if you want to search for zeros .. the php script see's it as false instead of zero ... so with that problem and foreign language issues my main question is: how should I store this 1 character? should I stor it as a hex value, decimal value, etc Thanks for your time!
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.