mysql query: selecting multiple records problem

[jamie]

hi,

i have one table which contains all of our clients, each with a ID, url, title and description.

i am trying to get my head around how to make sure the correct client links are displayed on the correct pages, and thought about storing the IDs of the clients in an array, and selecting these IDs from the database

e.g.:

$arr_shops = array(3,4,5,9,23) // array of all client IDs which should be displayed on this particular page

SELECT url, title, description
FROM my_table
WHERE ID = ..... // and here is where i come unstuck...

i can't think of any way to select all the clients which match the IDs in the array, in just one db query.

obviously i don't want to loop through the array and do 5 separate queries.

1) WHERE ID = arr_shops[0] AND ID = arr_shops[1],.... etc doesn't work because no shop can have more than one ID

2) OR doesn't work either, as it simply selects the first client 5 times!

anyone any clues? is much appreciated!

cheers

p.s. unfortunately i can't simply put an extra field in the database to identify which category each client should be in, because many clients are displayed in more than one section

[jatar_k]

I'll throw out a couple of ideas and we'll see what we cab figure out.

You could add two more tables though

clients
- client_id
- url
- title
- description

categories
- cat_id
- catname

clientcats
- client_id
- cat_id

then you can find out what clients are in what categories by looking at your relationship table (clientcats).

as far as just fixing the query goes you can use IN

$arr_shops = array(3,4,5,9,23)
$sql = "SELECT url, title, description FROM my_table WHERE ID IN (";
foreach ($arr_shops as $value) {
$sql .= $value . ",";
}
$sql = substr($sql, 0, -1); //get the last comma off
$sql .= ")";

echo $sql;

it should look like

SELECT url, title, description FROM my_table WHERE ID IN (3,4,5,9,23)

[jamie]

jatar_k thank you very much :)

just been experimenting (and reading up on joins) and have come up with the following:

SELECT ID, name
FROM clients
LEFT JOIN clientcats
ON clients.client_id = clientcats.client_id
LEFT JOIN categories
ON categories.cat_id = clientcats.cat_id
WHERE categories.catname = $_SERVER['SCRIPT_URL']

which works like a dream! again thanks for putting me on the right track.

the only problem i have now is how to order the results in a particular way.

normally we display all clients in descending order according to date of signing up. but occasionally we change the order to benefit a certain client or because of financial incentives, etc, etc ;)

at the moment my query returns all clients in the order in which they are entered into the table. short of adding another field to the clients table which contained a manually set field for sorting them (e.g. clients.displayorder) which would be a bit of a headache to maintain across different pages...

...the only other way i can think of is to load the results into an array and then match the printing out of this array to an $arr_clients_order which i would set for each page manually to determine the order of display

e.g.

$arr_clients_order = array(4,1,6);

$arr_clients_results = array(
0 => array(
'ID' => 1,
'name' => 'Butcher',
'description' => 'Makes great sausages'
),
1 => array(
'ID' => 4,
'name' => 'Baker',
'description' => 'Delicious cakes and tarts'
),
2 => array(
'ID' => 6,
'name' => 'Candlestickmaker',
'description' => 'Lights up the town'
));

for ($x = 0, $a = sizeof($arr_clients_order); $x < $a; $x++) {
for ($j = 0, $i = sizeof($arr_clients_results); $j < $i; $j++) {
if ($arr_clients_order[$x] == $arr_clients_results[$j]['ID']) {
echo $arr_clients_results[$j]['name'];
echo '<br>';
echo $arr_clients_results[$j]['description'];
echo '<br><br>';
}
}
}

this works, and prints out baker, butcher, candlestickmaker, but with a page of 10 clients, it could be a 100 fold loop!

i am sure there must be an easier way?

cheers

[killroy]

Erm, why not use ORDER BY?

SELECT ID, name
FROM clients
LEFT JOIN clientcats
ON clients.client_id = clientcats.client_id
LEFT JOIN categories
ON categories.cat_id = clientcats.cat_id
WHERE categories.catname = $_SERVER['SCRIPT_URL']
ORDER BY signupdate;

Or

SELECT ID, name
FROM clients
LEFT JOIN clientcats ON clients.client_id = clientcats.client_id
LEFT JOIN categories ON categories.cat_id = clientcats.cat_id
WHERE categories.catname = $_SERVER['SCRIPT_URL']
ORDER BY name;

Also the OR should work with distinct:

SELECT DISTINCT url,title, description
FROM my_table
WHERE ID=3 OR ID=4 OR ID=5 OR ID=9 OR ID=23

Although I don't see why it should throw up duplicates without the ID, maybe an indexing problem.

Anyways, the cleanest single table way is with IN, but a normalized multi table design is of course much better.

SN

[jamie]

hi killroy,

thanks for the feedback, but it's not quite on the right track :)

(thanks for explaining how the OR works though).

the problem is i _can't_ ORDER BY signup date, because as i said, we manipulate the order of display in the case of certain clients. e.g. one of them pays us double and goes to the top of the page, regardless of sign up date; or one of them is doing very badly for a while so we move them up a few places.

i could of course add another db_field:

...add another field to the clients table which contained a manually set field for sorting them (e.g. clients.displayorder)

so i could then "ORDER BY clients.displayorder"

but it would be very difficult to manually update, as some clients are displayed on more than one page.

so as far as my sql knowledge goes, i can see no obvious way of ordering the clients in the sql query. so i have to do it with php? hence the nested loops; but that looks very inefficient.

much appreciate the help!

[coopster]

...because as i said, we manipulate the order of display in the case of certain clients. e.g. one of them pays us double and goes to the top of the page, regardless of sign up date; or one of them is doing very badly for a while so we move them up a few places.

You've pretty much nailed your dilemma down in this one statement. There is a lot of human-decision-making going on in the sort here. You've established an internal weight-factor that is difficult to program initially, let alone maintain in the long haul. Therefore, your conclusion is: Do I try to maintain this dynamically using a database OR dynamically using code? You said it yourself...

...i can see no obvious way of ordering the clients in the sql query. so i have to do it with php?

Personally, I would create a new table and add that to my query statement:

clientsort
- client_id
- client_sort_definition
- client_sort_order

SELECT ID, name
FROM clients
LEFT JOIN clientcats
ON clients.client_id = clientcats.client_id
LEFT JOIN categories
ON categories.cat_id = clientcats.cat_id
LEFT JOIN clientsort
ON clientsort.client_id = clientcats.client_id
WHERE categories.catname = $_SERVER['SCRIPT_URL']
AND clientsort.client_sort_definition = '$my_page_sort'
ORDER BY client_sort_order

Of course, this will require an additional maintenance program to maintain sort orders on each of your page/sort definitions.

[jamie]

i see what you mean coopster. that's another table to maintain with at least 1 record for each client....

.. getting tied up in knots here ;)

however, once it is set up, we don't make many changes, so it should be ok to maintain.

i'll test it for a bit and see how it goes.

many thanks for your input :)

[killroy]

I run a medium sized yellow pages directory and solved a similar problem. Entries were in multiple category listings and had rankings according to their contracts.

so you had relationships like:

entry -> entry-cat1-link-table -> cat1
entry -> entry-cat2-link-table -> cat2

The rannking was stored in the middle tables. So each listign had itS' own ranking.

It'll be easy to decide what to do, simply look at how and where you're storign this ranking at the moment.

SN