n8w

October 28, 2012

good call that works great thanks!

October 28, 2012

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;

October 27, 2012

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=1 vs user_id=true · May 3, 2012

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

user_id=1 vs user_id=true · May 3, 2012

integer (primary key)

user_id=1 vs user_id=true · May 3, 2012

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

April 8, 2012

Thanks .. this worked great

update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)

April 7, 2012

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

August 16, 2011

thanks so much !

August 15, 2011

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

}

February 27, 2011

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

September 19, 2009

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!

September 19, 2009

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!

September 19, 2009

//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;

}

September 17, 2009

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;
}

September 17, 2009

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!

September 17, 2009

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?

September 2, 2009

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

August 9, 2009

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

July 29, 2009

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;
}

July 28, 2009

thanks soooooooooo much .. this rules!

July 27, 2009

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 ????

July 22, 2009

or if someone know of a good tag cloud api .. that would work

July 21, 2009

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";

June 28, 2009

this is so great! thanks

Sign In

n8w

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by n8w

If You Like This, Then You Will Like This

If You Like This, Then You Will Like This

If You Like This, Then You Will Like This

user_id=1 vs user_id=true

user_id=1 vs user_id=true

user_id=1 vs user_id=true

search and replace using a sql statement

search and replace using a sql statement

while ($row = mysql_fetch_array($retid))

while ($row = mysql_fetch_array($retid))

Freelance php / MySQL optimization

Nested Join question

Nested Join question

going into the database .. going out

encoding question

encoding question

encoding question

registration question ... facebook connect, opensocial, passport, etc

tag cloud question

dealing with multiple languages

[SOLVED] Deleting records from multiple tables?

[SOLVED] Deleting records from multiple tables?

tag cloud question

tag cloud question

[SOLVED] return part of a string

Browse

Activity

Important Information