Perl mysql question

[archael]

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]

Cowboy programmers way:

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

merge results

[swedguy]

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

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

[Babaganoosh]

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

[aps]

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]

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]

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]

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]

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]

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):

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

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

[archael]

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]

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]

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]

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]

ahhhh ok I get it hehe

But anyone know how to add my two arrays together?

[lb_vee]

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]

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]

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.

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]

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]

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]

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:

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

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

Instead of :

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

you can do :

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

[archael]

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]

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]

dude, can I look at your code?

ICQ me 202024544

[raymor]

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]

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.