Show multiple rows in MySQL
Question by ramesh
I had a table like this
id | name
------------------
1 | SAM1
2 | SAM2
1 | SAM1
3 | SAM3
7 | SAM7
6 | SAM6
I need to show the results using this query
SELECT name,id FROM tblnameWHERE id IN (1,2,7,6,1)
and getting the following result
id | name
------------------
1 | SAM1
2 | SAM2
7 | SAM7
6 | SAM6
My problem is this skipped last id , ie 1 . I need something like this
id | name
------------------
1 | SAM1
2 | SAM2
7 | SAM7
6 | SAM6
1 | SAM1
With out using the loop query ( like follows ) any other method for doing this ?
$ids=array(1,2,7,6,1);
$i=0;
foreach($ids as $id){
$sql=mysql_query("SELECT * FROM tblname WHERE id=$id");
// Store value to array
}
Please help
Answer by ShinTakezou
The query
SELECT name,id FROM tblname WHERE id IN (1,2,7,6);
should show duplicate rows; e.g. if there are really in the table two distinct rows with the very same id, then the query will show them (since there’s no DISTINCT keyword).
Instead, if you want to create duplicate lines starting from a table containing single lines, you have to join your table with a table having repeated 1 (in your case); another way could be to use union like this:
SELECT name, id FROM tblname WHERE id IN (1,2,7,6)
UNION ALL
SELECT name, id FROM tblname WHERE id = 1;
Edit
Since your id is a primary key, it will be unique, hence the “problem” you’re experiencing. If you want to allow duplicate rows on insert, remove the primary key. If you need it, consider the possible solutions suggested above.
Answer by Starx
What you are attempting is wrong.
Both the fields have same id and same value as well. You said id is your primary key on your table.
A primary key cannot be duplicated among the rows. That is the whole point of having a primary key
You mustn’t have declared the id field as primary key.
- Remove the bottom row
-
Add primary key to the field , run this query
ALTER TABLE `tablename` ADD PRIMARY KEY(id) AUTO_INCREMENT
Now from this point ahead, you will have unique id for all the records you have and you will have no problem on selecting the rows.
