SELECT a few rows out of MYSQL

I need to select category ids from my sql database. I have a variable $product_id and for each product id there are three rows in a table that i need to select using PHP开发者_Python百科.

If I do "SELECT * FROM table_name WHERE product_id='$prodid'"; I only get the one on the top.

How can I select all three category_ids which contain the same product_id?

I suppose you are using PHP's mysql functions, is this correct? I am figuring that your query is actually returning all three rows but you aren't fetching all of them.

 $sql = "SELECT * FROM table_name WHERE product_id='$prodid'";
 $r = mysql_query($sql, $conn); //where $conn is your connection

 $x = mysql_fetch_SOMETHING($r); //where something is array, assoc, object, etc.

The fetch function gives only one row at a time. You say you need three so it needs to be executed three times.

 $x[0] = mysql_fetch_assoc($r);
 $x[1] = mysql_fetch_assoc($r);
 $x[2] = mysql_fetch_assoc($r);

OR this would be better

while($curRow = mysql_fetch_assoc($r)) //this returns false when its out of rows, returns false
{
     $categoryIds[] = $curRow['category_id'];
}

If this doesn't do it then your query is actually returning only one row and we need to see your tables/fields and maybe sample data.

SQL seems to be correct, but Why do you store product_id in categories table? if it's one-to-many relation it would be better to store only category_id in products table.

The SQL query is correct for what you want to do. It will select all the records in table_name with the field product_id = $prodid (not only 1 or 3 but any that matches the variable)

To select a few records you should use the LIMIT keyword

You should look inside your table structure and the variable $prodid to find problems.