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
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.
you must make ids unique, you have same id for different rows, therefore you can't get both rows at the same time.
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.
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.
try union all
(SELECT name,id FROM tblname WHERE id IN (1,2,7,6)) UNION ALL (SELECT name,id FROM tblname WHERE id IN (1))
As you say id is primary key you cannot insert duplicate entries for that field hence the second insert with id 1 might have failed
SELECT name,id FROM tblname
it will not display the second entry with id 1
because primary key should be unique.
So what your are getting is what you have in Database.
You Try Out This Without Changes In Table
SELECT name,id FROM tblname WHERE id IN (1,2,7,6,1) GROUP BY id