retrieving multiple rows advice

[julian_m]

let's say I've an array of articles's id

$arr_art_id=(1, 551, 2015, 6 .......n )

what option is better (1 or 2), in order to achieve better performance?

(pseudo code)
1)
for i=0 to array count{
select * from articles where articles.id=$ar r_art_id[i]
mysqlquery($sql )
//do whatever i have to do
}


2)

$or ='';
for i=0 to array count{
$sql.= $or . 'articles.id='. $arr_art_id[i];
$or ='or';
}

$sql = 'select * from articles where' .$sql

mysqlquery($sql )
//do whatever i have to do

tia

regards - julian maisano

[Bob Stearns]

Re: retrieving multiple rows advice

julian_m wrote:[color=blue]
> let's say I've an array of articles's id
>
> $arr_art_id=(1, 551, 2015, 6 .......n )
>
> what option is better (1 or 2), in order to achieve better performance?
>
> (pseudo code)
> 1)
> for i=0 to array count{
> select * from articles where articles.id=$ar r_art_id[i]
> mysqlquery($sql )
> //do whatever i have to do
> }
>
>
> 2)
>
> $or ='';
> for i=0 to array count{
> $sql.= $or . 'articles.id='. $arr_art_id[i];
> $or ='or';
> }
>
> $sql = 'select * from articles where' .$sql
>
>
> //do whatever i have to do
>
> tia
>
> regards - julian maisano
>[/color]
Better than either:

$sql = 'select * from articles where articles.id in ($arr_art_id)';
mysqlquery($sql );
//do whatever i have to do

[Zoe Blade]

Re: retrieving multiple rows advice

On Tue, 21 Nov 2005, julian_m wrote:
[color=blue]
> let's say I've an array of articles's id
>
> $arr_art_id=(1, 551, 2015, 6 .......n )
>
> what option is better (1 or 2), in order to achieve better performance?
>
> (pseudo code)
> 1)
> for i=0 to array count{
> select * from articles where articles.id=$ar r_art_id[i]
> mysqlquery($sql )
> //do whatever i have to do
> }
>
>
> 2)
>
> $or ='';
> for i=0 to array count{
> $sql.= $or . 'articles.id='. $arr_art_id[i];
> $or ='or';
> }
>
> $sql = 'select * from articles where' .$sql
>
> mysqlquery($sql )
> //do whatever i have to do
>
> tia
>
> regards - julian maisano[/color]

Please for give me, regulars, if I'm out of line or out of my league for a
first post, but I thought I'd help out a bit now that I write PHP for a
living. (Whether I'm any good at it is another story, but at least I've
improved.)

If it's better performance your'e after, Julian, try something like this:

$sql = "SELECT title, body, author FROM articles ORDER BY date ASC";
mysqlquery($sql );
$results = mysql_num_rows( $sql);
for ($i=0; $i < $results; $i++)
{
$rows .= mysql_fetch_arr ay($sql);
}

foreach($rows as $row)
{
// Do things
}

Selecting only the columns you want in your SQL statement saves a little
bit of overhead, and you might as well get the database to put them in the
order you want them in. Then you put each row into an array, so it becomes
a two-dimensional array that looks just like the results table.

Then you can use the handy foreach loop to do whatever you want with the
output, one line at a time, without having to do any more queries.

My code is probably slightly off as it's been a while since I've written
procedural PHP code (my boss made me switch to object oriented, which has
sped us up when it comes to writing the projects but is probably slower at
executing the actual code, although not noticably so). But I'd recommend
naming columns, making a two-dimensional array, and using foreach on it,
for what it's worth.

Oh, and making sure you don't use a function as the middle argument in a
"for" loop helps. While it's less code, the line "for ($i=0; $i <
mysql_num_rows( $sql); $i++)" would make PHP call up that function every
time it executed the code in the loop.

Does that help?

Zoe.

[Steve]

Re: retrieving multiple rows advice

X-No-Archive: yes

[color=blue]
> Please for give me, regulars, if I'm out of line or out of my league for a
> first post, but I thought I'd help out a bit now that I write PHP for a
> living.[/color]

Sorry, Zoe, I think you missed the issue that the OP was trying to
resolve. Your advice is good as far as improving general performance is
concerned but your solution doesn't address how to restrict the SQL
query to ids listed in the array (first few lines of the OP):
[color=blue]
> let's say I've an array of articles's id[/color]
[color=blue]
> $arr_art_id=(1, 551, 2015, 6 .......n )[/color]

(Don't let that put you off contributing.)

---
Steve

[julian_m]

Re: retrieving multiple rows advice


Steve wrote:[color=blue]
> X-No-Archive: yes
>
>[color=green]
> > Please for give me, regulars, if I'm out of line or out of my league for a
> > first post, but I thought I'd help out a bit now that I write PHP for a
> > living.[/color]
>
> Sorry, Zoe, I think you missed the issue that the OP was trying to
> resolve. Your advice is good as far as improving general performance is
> concerned but your solution doesn't address how to restrict the SQL
> query to ids listed in the array (first few lines of the OP):[color=green]
> > let's say I've an array of articles's id[/color]
>[color=green]
> > $arr_art_id=(1, 551, 2015, 6 .......n )[/color]
>
> (Don't let that put you off contributing.)[/color]

indeed

regards - julian

[julian_m]

Re: retrieving multiple rows advice


Bob Stearns wrote:
[color=blue][color=green]
> >[/color]
> Better than either:
>
> $sql = 'select * from articles where articles.id in ($arr_art_id)';
> mysqlquery($sql );
> //do whatever i have to do[/color]

As you can imagine, I didn't know anything about "in".....

thanks a lot

regards - julian

[Chuck Anderson]

Re: retrieving multiple rows advice

julian_m wrote:
[color=blue]
>Bob Stearns wrote:
>
>
>[color=green]
>>Better than either:
>>
>>$sql = 'select * from articles where articles.id in ($arr_art_id)';
>>mysqlquery($s ql);
>> //do whatever i have to do
>>
>>[/color]
>
>As you can imagine, I didn't know anything about "in".....
>
>thanks a lot
>
>
>[/color]
I've used IN for subqueries, but I'd never heard of using it with an
array. Can you really use the array just like that? After trying to find
more info on this, all I have seen indicates that you have to implode
the array into a comma separated list.

Si o no?

--
*************** **************
Chuck Anderson • Boulder, CO

http://www.CycleTourist.com

Integrity is obvious.
The lack of it is common.
*************** **************

[julian_m]

Re: retrieving multiple rows advice


Chuck Anderson wrote:[color=blue]
> julian_m wrote:
>[color=green]
> >Bob Stearns wrote:
> >
> >
> >[color=darkred]
> >>Better than either:
> >>
> >>$sql = 'select * from articles where articles.id in ($arr_art_id)';
> >>mysqlquery($s ql);
> >> //do whatever i have to do
> >>
> >>[/color]
> >
> >As you can imagine, I didn't know anything about "in".....
> >
> >thanks a lot
> >
> >
> >[/color]
> I've used IN for subqueries, but I'd never heard of using it with an
> array. Can you really use the array just like that? After trying to find
> more info on this, all I have seen indicates that you have to implode
> the array into a comma separated list.
>
> Si o no?[/color]

Efectivamente, tiene usted absoluta razón ; )

It doesn't work just including the array into the sql. I think that Bob
Stearns wrote a sort of pseudo-code (just as i did) to "show the road"
....

[color=blue]
> *************** **************
> Chuck Anderson · Boulder, CO
> http://www.CycleTourist.com
> Integrity is obvious.
> The lack of it is common.
> *************** **************[/color]

By the way, a few days ago I saw in the news a guy who is travelling on
his bike all around the world. If you are interested, or even if you
want to improve your spanish, you can give it a look

https://www.acercandoelmundo.com

regards - julian

[Chuck Anderson]

OT: Re: retrieving multiple rows advice

julian_m wrote:
[color=blue]
>Chuck Anderson wrote:
>
>[color=green]
>>julian_m wrote:
>>
>>[color=darkred]
>>>Bob Stearns wrote:
>>>
>>>
>>>>Better than either:
>>>>
>>>>$sql = 'select * from articles where articles.id in ($arr_art_id)';
>>>>mysqlquery( $sql);
>>>> //do whatever i have to do
>>>>
>>>>
>>>>
>>>>
>>>As you can imagine, I didn't know anything about "in".....
>>>[/color]
>>I've used IN for subqueries, but I'd never heard of using it with an
>>array. Can you really use the array just like that? After trying to find
>>more info on this, all I have seen indicates that you have to implode
>>the array into a comma separated list.
>>
>>Si o no?
>>
>>[/color]
>
>Efectivament e, tiene usted absoluta razón ; )
>
>It doesn't work just including the array into the sql. I think that Bob
>Stearns wrote a sort of pseudo-code (just as i did) to "show the road"
>...
>
>[/color]
Okay. I wanted to be sure I understood how to use this properly.
[color=blue]
>By the way, a few days ago I saw in the news a guy who is travelling on
>his bike all around the world. If you are interested, or even if you
>want to improve your spanish, you can give it a look
>
>www.acercandoelmundo.com
>
>[/color]
Thanks. .... I'm always getting myself in too deep this way. As I like
to say, I know just enough Spanish (German, too) to get myself into
"trouble." Just enough to make someone think that I know what I'm
talking about :-)

Anyway, I don't know enough Spanish to be able to read that site by
myself, but I *can* see that this is a very ambitious project. And, it
is a couple (man and woman) riding a tandem bicycle. They plan to stay
on the road for 10 years.

Re: language. I use "si o no" as a "universal phrase" ..... much like
the German kaput, or Italian ciao. I think those are all understood in
any country.


--
*************** **************
Chuck Anderson • Boulder, CO

http://www.CycleTourist.com

Integrity is obvious.
The lack of it is common.
*************** **************