Jump to content


  • Posts

  • Joined

  • Last visited

Everything posted by n8w

  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!
  14. I have a column in my database table where I just I just need to store 1 letter/character example "a","8","c",etc I set up the column as char(1) but now I realize I have a problem storing characters from foreign languages so I think I need to store the character as hex value example ú would be c3ba is this the best solution?
  15. I am creating a new site where I need to identify users .. but I want to avoid giving users one more user/name password to remember .. I am considering FACEBOOK CONNECT, OPEN SOCIAL, etc .. any suggestions? note to moderator: I thought this would be the most appropriate forum for my question .. if not .. please move it to the one you think it should be in. Thanks
  16. n8w

    tag cloud question

    I want to count how many times words exist in various entires ... so for example if I had these three entries: #1 - health, sun, dog, cat #2 - health, sun, cloud, rain #3 - health, apple, ice cream, cloud I would like the the script to return the number of times each word is used (3) health (2) sun (2) cloud (1) dog (1) cat (1) rain etc
  17. what is the best way to store data in my database if people might be posting info in multiple languages (English, Spanish, etc)? should my in and out fucntion look something like this? //in- putting data into the database function bad_chars($text){ $text = trim($text); $text = utf8_encode ($text) $text = mysql_real_escape_string($text); return $text; } //out - pulling data out of the database function converttohtml($text){ $text = stripslashes($text); $text = utf8_decode ($text) return $text; }
  18. thanks soooooooooo much .. this rules!
  19. I would like to delete records from multiple tables... I have this tutorial http://www.electrictoolbox.com/article/mysql/cross-table-delete/ .. but still can't figure out the correct syntax for my scenario I want to delete all records with user_id=154 from tables users, illustrators, comments *note: each table has a user_id column how should I write that? DELETE users.*, illustrators.*, comments.* FROM users u, illustrators i, comments c WHERE ????
  20. n8w

    tag cloud question

    or if someone know of a good tag cloud api .. that would work
  21. I would like to create a tag cloud .. but not from a defined array .... but from a column in a database with many entries within a date range ... it could have 100s of entries my table has the following columns date, column1, column2, rating I basically want to find out which are the most common words in column 1 and column 2 a row with date might look like this record 1 date:2009-07-16 09:10:33Z column1:red, blue, green column2: hot, sunny rating:3 record 2 date:2009-07-15 09:00:13Z column1:red, blue column2: cold, wet rating:1 what is the best approach to doing this? Here is a tutorial I found on tag clouds .. but it's based off a defined array .. I want to base my off my columns that might have hundred of tags and duplicates .. etc function createTagCloud($tags) { //I pass through an array of tags $i=0; foreach($tags as $tag) { $id = $tag['id']; //the tag id, passed through $name = $tag['tag']; //the tag name, also passed through in the array //using the mysql count command to sum up the tutorials tagged with that id $sql = "SELECT COUNT(*) AS totalnum FROM tutorials WHERE tags LIKE '%".$id."%' AND published = 1";
  22. I want to dynamically search a string. So if $id=339; I would like it to return the number after the ":" which would be "3" What is the best way to do this? $id=339; $str="338:1, 339:3 ,340:15";
  • 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.