Shorter way to do this, database tables

[Mutley]

I have 2 tables, one with these fields:

 

(Table name: loadout)

user_id | prod_it

 

Then the other with:

 

(Table name: products)

prod_id | name | description

 

What I want to do is list all the products names for a user. The user table only has the prod_id in it, not the name, so how do I simply take the name from the other table?

 

At the moment I do a SELECT query to get the prod_id from the user, then do an individual SELECT query for every single product name using the prod_id I got earlier, as you can imagine, it's a lot of code this way!

[Barand]

SELECT u.user_id, p.name

FROM loadout u INNER JOIN products p

    ON u.prod_id = p.prod_id

[DeathStar]

just wanted to type that Barand :\

[Mutley]

How would I use that in PHP? Via phpmyadmin I get:

 

Unknown column 'u.prod_id' in 'on clause'

[DeathStar]

Its for use in php:

$list = mysql_query("SELECT u.user_id, p.name
FROM loadout u INNER JOIN products p
    ON u.prod_id = p.prod_id");
if (!$list){
print "No info!";}
else{
echo "$list";}

It wont work in phpmyadmin..

[Barand]

How would I use that in PHP? Via phpmyadmin I get:

 

Unknown column 'u.prod_id' in 'on clause'

 

Check column name spellings

[Mutley]

Ahh, sorry, I got it wrong.

 

What I have is:

Table: loadout

user_id | slot1 | slot2 | slot3 | slot4 | slot5

 

The "slots" have the product id's in.

 

Then,

Table: products

prod_id | name

 

So I want to create a list for that user, by getting the name of the products from the prod_id in the "slot" fields.

[Barand]

What you should have is this (normalized data) ,

with a separate row in loadout for each user's slot

[pre]

user            loadout              product

----------      -----------          ------------

user_id  <-+    id            +----> prod_id

name        +--- user_id        |      name

                slot      -----+      description

[/pre]               

Otherwise you just create more work for yourself (and those willing to help)

[Mutley]

This is slightly complicated to what I'm used to.

 

So do I need to create a new column in loadout for something? I don't understand what your diagram is explaining.

 

Thanks a lot for your help so far.

[DeathStar]

have'ing simpler query/database.

ex:

if your db is liek that diagram it would be easy to get info out of tables.

[Mutley]

That's what it is, except in "loadout" I don't have the "id" column.

[Barand]

Instead of

[pre]

user_id | Slot1 | Slot2 | Slot3 | Slot4 | Slot5 |

--------+-------+-------+-------+-------+-------+

  101  |  1  |  5  |  20  |  26  |  53  |

  102  |  6  |  29  |  42  |  108  |    1  |

[/pre]

 

it should be

[pre]

id  |  user_id | slot  |

-----+----------+-------+

1  |  101    |  1  |

2  |  101    |  5  |

3  |  101    |  20  |

4  |  101    |  26  |

5  |  101    |  53  |

6  |  102    |  6  |

7  |  102    |  29  |

8  |  102    |  42  |

9  |  102    | 108  |

10  |  102    |  1  |

[/pre]

[Mutley]

Aha, thanks, fully understand now. All my other tables are already in that format.

 

I'll reply once everything is changed and working with the new table layout.

 

EDIT: If I do it that way though, how do I know what the different slots are?

[Barand]

If the slot number is significant, an alternative is this,

in which case the primary key would be (user_id, slot)

[pre]

user_id |slot |prod_id|

--------+-----+-------+

101    | 1  |  1  |

101    | 2  |  5  |

101    | 3  |  20  |

101    | 4  |  26  |

101    | 5  |  53  |

102    | 1  |  6  |

102    | 2  |  29  |

102    | 3  |  42  |

102    | 4  | 108  |

102    | 5  |  1  |

[/pre]

 

[Mutley]

Here is an example of a query I'm using with the table I have now for loadout:

 

<?php
		$result2 = mysql_query("SELECT * FROM loadout WHERE user_id = '$login' LIMIT 1");
		while($row2 = mysql_fetch_array( $result2 ))
		{
	$l1_slot1 = $row2['slot1'];
	$l1_slot2 = $row2['slot2'];
	$l1_slot3 = $row2['slot3'];
	$l1_slot4 = $row2['slot4'];
	$l1_slot5 = $row2['slot5'];
		}

?>

 

So how would this change? If I used your method, wouldn't I have to do a loop query and attach variables?

[Barand]

Assuming this version of the table

[pre]

user_id |slot |prod_id|

--------+-----+-------+

101    | 1  |  1  |

101    | 2  |  5  |

101    | 3  |  20  |

101    | 4  |  26  |

101    | 5  |  53  |

102    | 1  |  6  |

102    | 2  |  29  |

102    | 3  |  42  |

102    | 4  | 108  |

102    | 5  |  1  |

[/pre]

<?php 
    $sql = "SELECT u.user_id, u.slot, p.name
            FROM loadout u
                INNER JOIN products p ON u.prod_id = p.prod_id
            ORDER BY u.user_id, u.slot";
            
    $res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
    while (list($user, $slot, $prod) = mysql_fetch_row($res)) {
        echo "$user $slot $prod<br>";
    }
?>

[Mutley]

It works, thanks a lot!

 

Can you explain what the "u" and "p" mean in the query? Like u.user_id and u.slot?

[Barand]

Table aliases.

 

Saves having repeat the whole table name each time as in

 

$sql = "SELECT loadout.user_id, loadout.slot, products.name
            FROM loadout 
                INNER JOIN products  ON loadout.prod_id = products.prod_id
            ORDER BY loadout.user_id, loadout.slot";

 

and IMO makes it more readable