Perl mysql question - GoFuckYourself.com

archael · 09-22-2004, 02:09 PM

Hey,
Right now I have a script that works well and displays everything I want exactly as I want it.

But since some of my own stuff is inside my sql database, I want to advertise mine first if the search matches.

Basically, the surfer enters a search string and then based on how many words are used, it searches the database and displays all the results accordingly.

What I want is to give priority to my own. Lets say someone searches for "carrots" and "beans". My script will find everything that matches carrots and beans.

If one of my own has that description, I want it to be displayed first while not overwriting the real first results.

Anyone have any idea how to do this?

Thanks in advance,
Jon

blazin · 09-22-2004, 02:41 PM

Cowboy programmers way:

2 searches, 1 includes your stuff, 1 rest of db

merge results

swedguy · 09-22-2004, 03:09 PM

Or if you have a "rank" column or something like that. 1 is you and 2 is everyone else.

SELECT * ....... ORDER BY rank;

Babaganoosh · 09-22-2004, 03:20 PM

Care to show your DB structure? You didn't give enough information to answer your question.

aps · 09-22-2004, 03:22 PM

Quote:

Originally posted by swedguy
Or if you have a "rank" column or something like that. 1 is you and 2 is everyone else.

SELECT * ....... ORDER BY rank;

This would be the correct and simplest way to solve this problem.

archael · 09-22-2004, 04:02 PM

Yes i know what you mean, I thought about it too but I would have to add that rank to about 50,000+ entries in my database.

And I would order my results by descending order and all the rest by descending order as well.

I tried the cowboy's programmer's way but it doesnt seem to query at all if I have 2 $sth->execute in the code.

I'm gonna try playing around with it some more.

Thanks,
Jon

lb_vee · 09-22-2004, 04:08 PM

Quote:

Originally posted by archael
Yes i know what you mean, I thought about it too but I would have to add that rank to about 50,000+ entries in my database.

And I would order my results by descending order and all the rest by descending order as well.

I tried the cowboy's programmer's way but it doesnt seem to query at all if I have 2 $sth->execute in the code.

I'm gonna try playing around with it some more.

Thanks,
Jon

you should rename your $sth to $sth2

so you have one:
$sth->execute(your stuff)
$sth2->execute(other stuff)

if both $sth's are the same, its not gonna work

archael · 09-22-2004, 04:12 PM

Yep thats exactly what I tried.. It just ended up showing absolutely no results. Not even for the $sth that was already working. I'm gonna try it again see if I had any errors anywhere.

But what was weird is that the query that used to work well ended up showing nothing even if it stayed untouched.

All different variables too.

Jon

aps · 09-22-2004, 04:15 PM

Quote:

Originally posted by archael
Yes i know what you mean, I thought about it too but I would have to add that rank to about 50,000+ entries in my database.

And I would order my results by descending order and all the rest by descending order as well.

I tried the cowboy's programmer's way but it doesnt seem to query at all if I have 2 $sth->execute in the code.

I'm gonna try playing around with it some more.

Thanks,
Jon

This does not have to be compliated. You could say that all entries that are your get a rank of 1 and everything else gets a rank of 2. If you know what entries are yours then just add the column and do 2 updates. Done. If you don't know what entries are yours without going through each of the 50,000 then you are kind of screwed anyway

lb_vee · 09-22-2004, 04:18 PM

Quote:

Originally posted by archael
Yep thats exactly what I tried.. It just ended up showing absolutely no results. Not even for the $sth that was already working. I'm gonna try it again see if I had any errors anywhere.

But what was weird is that the query that used to work well ended up showing nothing even if it stayed untouched.

All different variables too.

Jon

Try this:

create subroutine that gets the data you need and takes the one argument you need for your sites data and other sites data.

then do this (assuming you're returning an array):

Code:

my @mySitesData = &mySub('mySite');
my @otherSitesData = &mySub('otherSite');

That should work, because the scope of $sth is now within a subroutine.

archael · 09-22-2004, 04:31 PM

I was just able to get it to work with all your help ;)

The only problem I gotta find now is how to compile both of them without having to create a subroutine.

Now my results are being displayed first but i'll give an example of my problem.

Every 6 results have to have a </tr><tr>. But if I only have 1 result of my own, then it is not all aligned properly.

Right now i basically have both $sth and $sth2. $sth is limited at 144 results and $sth2 (my own) limited at 6.

I could easily get this done by adding "don't display anything if $count not equal to 6" but it would be cool if it would still display a few results perfectly aligned.

If anyone has an example of how to merge 2 results, would be cool to know.

Thanks,
Jon

archael · 09-22-2004, 04:48 PM

ok here's the simplet thing I ended up with up to now.

@array = $sth->fetchrow
@array2 = $sth2->fetchrow (my own)

Now i need some kind of form to add those arrays together like this:

my $thecount = 0;
while(my @results = (@array2+@array)) {
$thecount++;
if ($thecount % 6){
print "This is the end of the first row\n";
}
else {
print "These are results 1 to 5\n";
}
}

What doesnt seem to work is adding the two arrays together, doesn't seem to work with a comma either.

archael · 09-22-2004, 04:50 PM

sorry mistake on previous post. And dont know why the board is adding haha123; either.

my $thecount = 0;
while(my @results = (@array2+@array))
$thecount++;
if (int($thecount % 6))
print "This is the end of the first row\n";
}
else haha123;
print "These are results 1 to 5\n";
}
}

lb_vee · 09-22-2004, 04:59 PM

Quote:

Originally posted by archael
sorry mistake on previous post. And dont know why the board is adding haha123; either.

my $thecount = 0;
while(my @results = (@array2+@array))
$thecount++;
if (int($thecount % 6))
print "This is the end of the first row\n";
}
else haha123;
print "These are results 1 to 5\n";
}
}

Its the open and close braces that cause the haha123 to appear

archael · 09-22-2004, 05:04 PM

ahhhh ok I get it hehe

But anyone know how to add my two arrays together?

lb_vee · 09-22-2004, 05:26 PM

Quote:

Originally posted by archael
ahhhh ok I get it hehe

But anyone know how to add my two arrays together?

if you join both arrays, you're gonna have 1 huge array with no way to tell the difference between your data, and other sites data. Is that what you want??

archael · 09-22-2004, 05:31 PM

if they are compile in order with my results first, yes thats what I want.

But I need it to have a counter so that after compilation of both arrays, every 6 results will hold a </tr><tr>

lb_vee · 09-22-2004, 05:35 PM

Quote:

Originally posted by archael
if they are compile in order with my results first, yes thats what I want.

But I need it to have a counter so that after compilation of both arrays, every 6 results will hold a </tr><tr>

Here's an example of how to get a tr after every 6th item.

Code:

my @stuffList = ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19');
my @otherStuffList = ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19');
push (@stuffList,@otherStuffList);
my $length    = @stuffList;
my $perRow    = 5;
my $counter   = 0;
                                                                                                                         
foreach (@stuffList)
{
  print $_;
                                                                                                                         
                                                                                                                         
  if ($counter < $perRow)
  {
    $counter++;
  }
  else
  {
    print "\n"; ### replace with </tr>
    $counter=0;
  }
                                                                                                                         
}

archael · 09-22-2004, 05:40 PM

alright im gonna give this a try.

But I also meant to ask, i see the $_ and @_ everywhere but have no idea what that means.

Can you explain it?

Thanks,
Jon

abyss_al · 09-22-2004, 05:48 PM

Quote:

Originally posted by a1escorts
if you join both arrays, you're gonna have 1 huge array with no way to tell the difference between your data, and other sites data. Is that what you want??

listen tho this ^ guy!! a1escort is programming GURU

lb_vee · 09-22-2004, 05:49 PM

Quote:

Originally posted by archael
alright im gonna give this a try.

But I also meant to ask, i see the $_ and @_ everywhere but have no idea what that means.

Can you explain it?

Thanks,
Jon

I'll start with @_.

When you define a subroutine, perl by default takes arguments as an array in the @_ array;

example:

Code:

sub bob()
{
 my $firstArg = @_[0];
 my $secondArg=@_[1];
}

The $_ is the current value in a loop pretty much.

Instead of :

Code:

foreach my $thisItem (@array)
{
  print $thisItem;
}

you can do :

Code:

foreach (@array)
{
  print $_; ### same as this item
}

archael · 09-22-2004, 06:02 PM

ahh alright thanks for the explanation ;)

Now im having trouble getting the push method to work.. what it seems to be doing is posting the same result over and over again.

Here is my code up to now:

my @gals = $sth->fetchrow;
my @gals2 = $sth2->fetchrow;
push (@gals2, @gals);

my $counter = 0;

foreach (@gals2)
{
$counter++;
if ($counterhahahaha6)
{
print "</tr><tr>\n";
$counter=0;
}
else
{
print "\n";
}

}

No idea yet why its displaying the same result over and over

lb_vee · 09-22-2004, 06:12 PM

with $sth->fetchrow, you might have to step through each item in a while loop and push the result into your @gals array.

does fetchrow return a hash or an array??

with $sth->fetch, you can build a hash that reflects the data coming out of the database and push eash hash into the array you're going to return.

so you have $result->[ARRAY]->[Hash]

lb_vee · 09-22-2004, 06:18 PM

dude, can I look at your code?

ICQ me 202024544

raymor · 09-23-2004, 09:38 AM

Because in theory there may be a great many results
you shouldn't be reading them all into an array anyway,
and using theose two different subs is a little silly.
Instead I'd suggest a fetchrow_array or fetchrow_hash loop,
displaying each entry as it's found.
I'd also do it with one SELECT statement.
The exact syntax of that statement depends on how "yours"
versus "not yours" is represented in the database.
Let's assume you have a column called "ownerID" and
that your ownerID is 56. So anything with a 56 in the ownerID
column is yours. Then you'd do:
SELECT ... FROM ... ORDER BY ownerID=56 DESC
to randomly order, while making sure yours are on top:
SELECT ... FROM ... ORDER BY ownerID=56 DESC, RAND()

archael · 09-23-2004, 01:20 PM

Yeah i know what you mean, i thought about that too.

Making 2 arrays and putting them together seems to be really slow.

What I would need would be to be able to do like you said while keeping my results and the rest all ordered by their ID, just so the rest doesnt display old galleries first.

09-22-2004, 02:09 PM	#1
archael Registered User Join Date: Jun 2003 Posts: 51	Perl mysql question Hey, Right now I have a script that works well and displays everything I want exactly as I want it. But since some of my own stuff is inside my sql database, I want to advertise mine first if the search matches. Basically, the surfer enters a search string and then based on how many words are used, it searches the database and displays all the results accordingly. What I want is to give priority to my own. Lets say someone searches for "carrots" and "beans". My script will find everything that matches carrots and beans. If one of my own has that description, I want it to be displayed first while not overwriting the real first results. Anyone have any idea how to do this? Thanks in advance, Jon

09-22-2004, 02:41 PM	#2
blazin Confirmed User Join Date: Aug 2002 Posts: 2,781	Cowboy programmers way: 2 searches, 1 includes your stuff, 1 rest of db merge results __________________ I don't endorse a god damn thing......

09-22-2004, 03:20 PM	#4
Babaganoosh ♥♥♥ Likes Hugs ♥♥♥ Industry Role: Join Date: Nov 2001 Location: /home Posts: 15,841	Care to show your DB structure? You didn't give enough information to answer your question. __________________ I like pie.

09-22-2004, 06:12 PM	#23
lb_vee Confirmed User Join Date: May 2004 Posts: 886	with $sth->fetchrow, you might have to step through each item in a while loop and push the result into your @gals array. does fetchrow return a hash or an array?? with $sth->fetch, you can build a hash that reflects the data coming out of the database and push eash hash into the array you're going to return. so you have $result->[ARRAY]->[Hash] Last edited by lb_vee; 09-22-2004 at 06:15 PM..

09-23-2004, 09:38 AM	#25
raymor Confirmed User Join Date: Oct 2002 Posts: 3,745	Because in theory there may be a great many results you shouldn't be reading them all into an array anyway, and using theose two different subs is a little silly. Instead I'd suggest a fetchrow_array or fetchrow_hash loop, displaying each entry as it's found. I'd also do it with one SELECT statement. The exact syntax of that statement depends on how "yours" versus "not yours" is represented in the database. Let's assume you have a column called "ownerID" and that your ownerID is 56. So anything with a 56 in the ownerID column is yours. Then you'd do: SELECT ... FROM ... ORDER BY ownerID=56 DESC to randomly order, while making sure yours are on top: SELECT ... FROM ... ORDER BY ownerID=56 DESC, RAND() __________________ For historical display only. This information is not current: support@bettercgi.com ICQ 7208627 Strongbox - The next generation in site security Throttlebox - The next generation in bandwidth control Clonebox - Backup and disaster recovery on steroids

09-22-2004, 03:09 PM	#3
swedguy Confirmed User Industry Role: Join Date: Jan 2002 Posts: 7,981	Or if you have a "rank" column or something like that. 1 is you and 2 is everyone else. SELECT * ....... ORDER BY rank;

09-22-2004, 04:02 PM	#6
archael Registered User Join Date: Jun 2003 Posts: 51	Yes i know what you mean, I thought about it too but I would have to add that rank to about 50,000+ entries in my database. And I would order my results by descending order and all the rest by descending order as well. I tried the cowboy's programmer's way but it doesnt seem to query at all if I have 2 $sth->execute in the code. I'm gonna try playing around with it some more. Thanks, Jon

09-22-2004, 04:12 PM	#8
archael Registered User Join Date: Jun 2003 Posts: 51	Yep thats exactly what I tried.. It just ended up showing absolutely no results. Not even for the $sth that was already working. I'm gonna try it again see if I had any errors anywhere. But what was weird is that the query that used to work well ended up showing nothing even if it stayed untouched. All different variables too. Jon

09-22-2004, 04:31 PM	#11
archael Registered User Join Date: Jun 2003 Posts: 51	I was just able to get it to work with all your help ;) The only problem I gotta find now is how to compile both of them without having to create a subroutine. Now my results are being displayed first but i'll give an example of my problem. Every 6 results have to have a </tr><tr>. But if I only have 1 result of my own, then it is not all aligned properly. Right now i basically have both $sth and $sth2. $sth is limited at 144 results and $sth2 (my own) limited at 6. I could easily get this done by adding "don't display anything if $count not equal to 6" but it would be cool if it would still display a few results perfectly aligned. If anyone has an example of how to merge 2 results, would be cool to know. Thanks, Jon

09-22-2004, 04:48 PM	#12
archael Registered User Join Date: Jun 2003 Posts: 51	ok here's the simplet thing I ended up with up to now. @array = $sth->fetchrow @array2 = $sth2->fetchrow (my own) Now i need some kind of form to add those arrays together like this: my $thecount = 0; while(my @results = (@array2+@array)) { $thecount++; if ($thecount % 6){ print "This is the end of the first row\n"; } else { print "These are results 1 to 5\n"; } } What doesnt seem to work is adding the two arrays together, doesn't seem to work with a comma either.

09-22-2004, 04:50 PM	#13
archael Registered User Join Date: Jun 2003 Posts: 51	sorry mistake on previous post. And dont know why the board is adding haha123; either. my $thecount = 0; while(my @results = (@array2+@array)) $thecount++; if (int($thecount % 6)) print "This is the end of the first row\n"; } else haha123; print "These are results 1 to 5\n"; } }

09-22-2004, 05:04 PM	#15
archael Registered User Join Date: Jun 2003 Posts: 51	ahhhh ok I get it hehe But anyone know how to add my two arrays together?

09-22-2004, 05:31 PM	#17
archael Registered User Join Date: Jun 2003 Posts: 51	if they are compile in order with my results first, yes thats what I want. But I need it to have a counter so that after compilation of both arrays, every 6 results will hold a </tr><tr>

09-22-2004, 05:40 PM	#19
archael Registered User Join Date: Jun 2003 Posts: 51	alright im gonna give this a try. But I also meant to ask, i see the $_ and @_ everywhere but have no idea what that means. Can you explain it? Thanks, Jon

09-22-2004, 06:02 PM	#22
archael Registered User Join Date: Jun 2003 Posts: 51	ahh alright thanks for the explanation ;) Now im having trouble getting the push method to work.. what it seems to be doing is posting the same result over and over again. Here is my code up to now: my @gals = $sth->fetchrow; my @gals2 = $sth2->fetchrow; push (@gals2, @gals); my $counter = 0; foreach (@gals2) { $counter++; if ($counterhahahaha6) { print "</tr><tr>\n"; $counter=0; } else { print "\n"; } } No idea yet why its displaying the same result over and over

09-22-2004, 06:18 PM	#24
lb_vee Confirmed User Join Date: May 2004 Posts: 886	dude, can I look at your code? ICQ me 202024544

09-23-2004, 01:20 PM	#26
archael Registered User Join Date: Jun 2003 Posts: 51	Yeah i know what you mean, i thought about that too. Making 2 arrays and putting them together seems to be really slow. What I would need would be to be able to do like you said while keeping my results and the rest all ordered by their ID, just so the rest doesnt display old galleries first.