complex query

[patchido]

Well, as i said in my previous post, im pretty new into php, mysql, i was searching into how to make queries but i can't find where to learn doing real complex ones.

 

This is how it goes,

 

-i've got an array full of id's

 

-inside my table i wan't to get all the values where folder_id == to any id inside my array(i guess a loop would do this)

 

-and i wan't to get only the first 20 values of the columns (folder_id and photo) for every folder_id

 

 

any suggestions?

 

thanks

[Psycho]

The answer to your first question is very simple. Just use the IN operator. E.g.

SELECT * FROM table WHERE id IN (2,4,6,23,34)

 

If you have an array in PHP, then just use the implode() function - assuming these are INT values that you have verified as being ints. If these are strings, then you would want to run the array through a process to use mysql_real_escape_string(0 and enclose the values in singe quote marks first.

 

NEVER run queries in loops.

 

As for

and i wan't to get only the first 20 values of the columns (folder_id and photo) for every folder_id

I'm not sure I follow. What is meant by "values". Are you saying that for each ID there are many records and you only want the first 20 records for each ID? If yes, take a look at this article: http://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-group

[patchido]

The answer to your first question is very simple. Just use the IN operator. E.g.

SELECT * FROM table WHERE id IN (2,4,6,23,34)

 

If you have an array in PHP, then just use the implode() function - assuming these are INT values that you have verified as being ints. If these are strings, then you would want to run the array through a process to use mysql_real_escape_string(0 and enclose the values in singe quote marks first.

 

NEVER run queries in loops.

 

As for

and i wan't to get only the first 20 values of the columns (folder_id and photo) for every folder_id

I'm not sure I follow. What is meant by "values". Are you saying that for each ID there are many records and you only want the first 20 records for each ID? If yes, take a look at this article: http://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-group

 

yes, i have an array with the id's i need, they are gathered through a query from an id column so im sure it's an id..

 

what i meant with values is.(it is for a photo gallery)

 

i have a table with album names, so i gather the id from this table, after this, i want to use that array to search in my other table for up to 20 entries for each album name(id) in my table where all my pictures are in(regardless of there album-there is a clumn named"folder_id"that links them together)

[patchido]

NEVER run queries in loops.

Is there another way??

 

I'm not sure I follow. What is meant by "values". Are you saying that for each ID there are many records and you only want the first 20 records for each ID? If yes, take a look at this article: http://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-group

 

read through it, and it appears to be kindof, what i want, but if i didn't misread that one has limitations into not being able to select less than the number, i want the entries to be from 0-20 entries for every available folder

 

PD: that code is way too advanced for me, i didn't understand a thing on how it is done xD

[Psycho]

NEVER run queries in loops.

Is there another way??

 

Yes of course.

 

yes, i have an array with the id's i need, they are gathered through a query from an id column so im sure it's an id..

 

what i meant with values is.(it is for a photo gallery)

 

i have a table with album names, so i gather the id from this table, after this, i want to use that array to search in my other table for up to 20 entries for each album name(id) in my table where all my pictures are in(regardless of there album-there is a clumn named"folder_id"that links them together)

You need to learn to do JOINs. You should not run a query to get a list of IDs to then do a subsequent query. You first stated you don't know where to go to learn to do complex queries. Well, doing a JOIN is not a complex query. Do some Googling to find a tutorial or two and read them.

 

Plus, I already linked you to a page to explain the complex part of what you ask. But, I think you need some more intermediary understanding.

[patchido]

thanks, ill look into the JOINs, and for the record, i am deleting the limit 20, i have a better idea, so my question is, how do i get the names from the joined tables? i mean there will be alot of names repeated isn't it?

 

SELECT  `fotos folder`.`id` ,  `fotos folder`.`nombre` ,  `fotos`.`foto` 
FROM  `fotos folder` 
JOIN  `fotos` ON  `fotos folder`.`id` =  `fotos`.`folder_id` 

 

i get this

 

id nombre                  foto

1 Junta con Papas images/JuntaConPapas/pic20.jpg

1 Junta con Papas images/JuntaConPapas/pic1.jpg

1 Junta con Papas images/JuntaConPapas/pic7.jpg

2 Box de Ciegos  images/BoxCiegos/foto_1.jpg

2 Box de Ciegos  images/BoxCiegos/foto_3.jpg

 

 

so, from here i want to send the data to a flash application, but it wont accept arrays so i want to make an array from `nombre` how can i do this without reapiting?? i want to echo this 

Nombre=Junta con Papas|Box de Ciegos&Fotos=1|images/JuntaConPapas/pic20.jpg(||)1|images/JuntaConPapas/pic1.jpg(||)1|images/JuntaConPapas/pic7.jpg(||)2|images/BoxCiegos/foto_1.jpg(||)2|images/BoxCiegos/foto_3.jpg

[Psycho]

here i want to send the data to a flash application, but it wont accept arrays so i want to make an array from `nombre` how can i do this without reapiting?? i want to echo this 

Nombre=Junta con Papas|Box de Ciegos&Fotos=1|images/JuntaConPapas/pic20.jpg(||)1|images/JuntaConPapas/pic1.jpg(||)1|images/JuntaConPapas/pic7.jpg(||)2|images/BoxCiegos/foto_1.jpg(||)2|images/BoxCiegos/foto_3.jpg

 

OK, I think I understand the format. And this *should* get what you want, but I didn't test it.

$nombres = array();
$fotos = array()
while($row = mysql_fetch_assoc($result))
{
    //Add name to array if not already there
    if(!in_array($row['nombre'], $nombres))
    {
        $nombres[] = $row['nombre'];
    }

    //Detemine the 'index' for the output
    $index = 1 + array_search($row['nombre'], $nombres);

    //Add photo to array
    $fotos[] = "{$index}|{$row['foto']}";
)

//Create flash output string
$flashStr  = "Nombre=" . implode('|', $nombres);
$flashStr .= "&Fotos=" . implode('(||)', $fotos);

echo $flashStr;

[patchido]

had some mistakes, but fixed them, and yes! it's actually what i needed, thanks a lot, just for learning issues could you describe me what implode does? i don't understand the API, and, in_array is efficent? isn't it slow if my array was too big?

 

 

thanks a lot

 

 

[Psycho]

just for learning issues could you describe me what implode does? i don't understand the API

There is a manual: http://us2.php.net/manual/en/function.implode.php

It will do a much better job of explaining than I could

 

and, in_array is efficent? isn't it slow if my array was too big?

Perhaps. What do you consider "big"? What kind of performance issues are you concerned with. I provided a workable solution based on the information you provided. I built it so it wouldn't matter what order the data was processed. If you do order the results on the 'nombre' value you could do this

$nombres = array();
$fotos = array()
$lastNombre = false;
while($row = mysql_fetch_assoc($result))
{
    //Add name to array if not already there
    if($lastNombre != $row['nombre'])
    {
        $nombres[] = $row['nombre'];
        $index = count($nombres) + 1;
    }
    //Add photo to array
    $fotos[] = "{$index}|{$row['foto']}";
)

//Create flash output string
$flashStr  = "Nombre=" . implode('|', $nombres);
$flashStr .= "&Fotos=" . implode('(||)', $fotos);

echo $flashStr;