1

I have some doubt. Actually i need to fetch data as per column value by joining two table using PHP and MySQL .I am explaining my tables below.

db_specials:

id        special                  type

1        Food and Beverage          1

2        Liquor                     2

db_basic:

 id      special_id     name

 1         2            aaa

 2         1            Raj

 3         2            Ram

 4         2            Jay

 5         1            Nikesh

Here i need to fetch all data from the tables db_basic those are associated with Liquor from first table. I am explaining my code below.

$res=mysqli_query($connect,"select b.id,b.special_id,b.name,s.type from db_basic as b inner join db_specials as s on b.special_id=s.id where b.special_id=2 order by b.id desc");

while($row=mysqli_fetch_array($res)){
   $data[]=$row;
}

I am getting also the proper data. Here problem is db_specials also has the delete functionality on front end with insert. Suppose user deleted the row which has id=2 in db_specials table and insert again,in this case the id will change 2 to 3. So the query also needs to change accordingly. Here i need to know what should be the best logic so that each time user will not change the query if any specials is deleted and inserted again. Please help me.

Improve this question
6
  • Try to learn Referential Integrity Commented Oct 31, 2016 at 6:08
  • If a row from db_special table gets deleted and reinserted afterwards will the special field contain the same string? Commented Oct 31, 2016 at 6:09
  • @VikasUmrao : Can you send any reference link to easily learn. Commented Oct 31, 2016 at 6:09
  • @1000111 : The id field is auto incremented. Commented Oct 31, 2016 at 6:10
  • I asked for whether the new row will contain the same string as previous. And also let us know if a particular row from db_special table gets deleted what's the effect in db_basic table? The corresponding rows in db_basic table also get deleted? Commented Oct 31, 2016 at 6:12

2 Answers 2

Reset to default
0

Instead of id column, you should rely on the type column of db_specials table, since it's not going to be auto incremented for each INSERT operation. So your query should be like this:

select b.id,b.special_id,b.name,s.type 
from db_basic as b 
inner join db_specials as s 
on b.special_id=s.type 
where b.special_id=2 
order by b.id desc

Also, I recommend that you should change the special_id column name of db_basic table to type, like this:

+------+------+------+
|  id  | type | name |
+------+------+------+
|      |      |      |

This way, it would be easier for you to construct the query, like this:

select b.id,b.type,b.name
from db_basic as b 
inner join db_specials as s 
on b.type=s.type 
where b.type=2 
order by b.id desc
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@ Rajdeep : I was also thinking about type. You mean user has to insert type as 2 each time if it need to delete and again insert.
@subhra Yes, that's correct because id column is auto incremented but type column is not. While inserting the row again, you can manually insert 2 as a type value. I've further updated my answer.
0

if user deleted 2,Liquor,2 in db_specials and reinsert, you cannot control what the user input in the front end.
User might be insert Food,2

Instead of join the type column of db_specials with db_basic, you should put a checking in the front end, when user click the delete button, prompt a message before delete 2,Liquor,2 from the table db_specials, if there is any special_id=2 in table db_basic the corresponding rows will deleted as well.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.