Split Question ...

[Avihai]

Hello,

I have this query that I use in a php script:

$q = mysql_query("select id,images from ".$content_table." where sectionid=".$section." and images<>'' order by RAND() limit ".$imgnr . "");


This query pulls images and their id from a mySQL db. I use it in an Ajax script to rotate the images without refreshing the page.

The thing is that I will have ~150 images and I want to avoid duplications... so I thought of creating three queries with a split: $q would call the first 50 pics $qb would call 50-100 and $qc would call 100-150.

I need help in the splitting thing :-) I really have no idea how to do this, does any one have a clue?

Blessings,
A.O

[chazzy]

I'm not sure how I see how breaking things up in to groups of 50 avoids duplications.

Either way, from the database end your best bet, since you're ordering by random order, is to pull everything down. Couldn't you maybe, instead of using ajax, just dynamically create the javascript at run time?

[Avihai]

Thanks for the answer ...

If I will create the JS at runtime the images will rotate only on page load
and I want to avoid it. I use a script from jQuery to create a slide show effect with the result of the sql query.

The thing is that I have 3 place holders that should contain 50 images in each one.

In my simple words it would sound like:

$q = take images from 0-50
$qb = take images from 50-100
$qc = take images from 100-150

Then I would randomly place images from the query result.

[chazzy]

I don't know what you mean by "If I will create the JS at runtime the images will rotate only on page load"

I see no reason why that would be true.

[Avihai]

I don't know what you mean by "If I will create the JS at runtime the images will rotate only on page load"

I see no reason why that would be true.

Well, let's just say that I want to create 3 different sql queries that pulls images from the db ...

q1: from id 1 to 50 where section id=4
q2: from id 50 to 100 where section id=4
q3: from id 100 to 150 where section id=4

how to I do that (the from to thing) ?

Thanks :-)

[chazzy]

Ok - but my point is that this won't work from both performance and maintenance perspective. What happens when the 151st image gets added? Either way, you're not accounting for duplicates by breaking it in to 3 queries.


Let's say, instead, you just queried the whole thing


$result_query = mysql_query("select * from content table where sectionid=4 order by rand()") or die(mysql_error());
$i=0;
while($row = mysql_fetch_array($result_query))
{
echo 'var itemArr['.$i.'] = some javascript code that contains the image info.';
$i++;
}

[Avihai]

Well it is getting complicated later on ... because I also pull out text along the images.

Check the attached file ...10268

[chazzy]

so then make proper javascript arrays for the data. or use objects.

this might help you w/ the multidimensional arrays.

http://www.devx.com/tips/Tip/12455

[Avihai]

Thanks for the tip and the link :-)

I have solved the issue by using limit 0,50 for the first query 50,100 for the second one and 100,150 for the third one.

It actually works :-)

Thanks again :-)

[chazzy]

I can guarantee you that using the limit keyword will not work.

the rand() function recalculates on each request.

[chazzy]

Also, looking at it again, if you did use the following LIMITs:

LIMIT 0,50
LIMIT 50,100
LIMIT 100,150

You're going to get more than 50 results in the last 2 queries.

limit's syntax is
LIMIT [OFFSET ROWCOUNT][ROWCOUNT]

So if you really did put in 0,50,100 as the offset and 50,100,150, the first query will return results 0-50, the second 50-150 and the third 100-250.