-
Posts
123 -
Joined
-
Last visited
Posts posted by n8w
-
-
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
- user_id=the user who is favoriting the illustrator
-
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
}
-
I am looking for a programmer who can optimize my MySQL database and make changes to a couple php scripts so they preform better and take a load off the server .. caching, etc
Please contact me if you are interested with your expertise / rates
Nate Williams
iliketorelax@gmail.com
-
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!
-
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!
-
//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;
}
-
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; }
-
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!
-
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?
-
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
-
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
-
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; }
-
thanks soooooooooo much .. this rules!
-
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 ????
-
or if someone know of a good tag cloud api .. that would work
-
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";
-
this is so great! thanks
If You Like This, Then You Will Like This
in MySQL Help
Posted
good call that works great thanks!