n8w

good call that works great thanks!

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;

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

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

integer (primary key)

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

Thanks .. this worked great update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)

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

thanks so much !

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 }