[RESOLVED] What would be the Query.......

REY619

I use this query to insert data to the table.....

  $res = "INSERT INTO `indianw_phpsite`.`ibwf_bookmarks` (`id` ,`userid` ,`topic`) VALUES (NULL , '".$uid."', '".$tpcid."')";

Now the table is in this form.......

id --- userid --- topic
1 --- 8 --- 4561
2 --- 8 --- 7895
3 --- 7 --- 3451
4 --- 5 --- 3415
5 --- 45 --- 1245
6 --- 56 --- 7458
7 --- 78 --- 1235
8 --- 7 --- 1235
. --- . --- .
. --- . --- .
. --- . --- .
. --- . --- .

This is basically a bookmark table, when a User(with a specific userid) bookmarks a topic(which also has a specific id), the data gets stored in the table in that form.... I hope u understand....
Now the problem is I want to apply the limit so that each user should only have 10 bookmarks... It can be found out by finding how many times the user id of that user has appeared in the table.....
For eg. if the person with userid 8 has stored 10 bookmarks, he should not be able to store more bookmarks...... What would be the query to find out this???? I hope u understand....
And also what would be the Query to check no same topic exists with same id twice, or he doesnt bookmark the same topic twice......

Pleae help..... Thanx! 🙁

jib0

First of all, if your id field is an AUTO_INCREMENT field, you can simply write:

$res = sprintf('INSERT INTO `indianw_phpsite`.`ibwf_bookmarks` (`userid` ,`topic`) VALUES (%u, %u);', $uid, $tpcid);

How many bookmars does a user have:

SELECT COUNT(*) AS n FROM ibwf_bookmarks WHERE userid = 8;

For the "no same topic exists with same id twice", you should declare

UNIQUE(userid, topic)

when you create your table. It will prevent a wrong insert.

I hope this helps.

REY619

Thanx for replying...... Now i created the table using phpmyadmin... How would i alter that now, to make userid and topic unique, as in...

UNIQUE(userid, topic)

Thanx..

jib0

ALTER TABLE ibwf_bookmarks ADD UNIQUE(userid, topic);

REY619

Great!! Its preventing the same topic to be bookmarked twice, but shows this -

Duplicate entry '8-3140' for key 2

at the php page, when i try to bookmark the same topic twice..... How can i edit the message so that it says Bookmark already exists etc.
Does it got to do something with mysql_error()
Thanx..

Sorry if bothering u ppl.... 😉

jib0

Well, the UNIQUE constraint is for the database purpose only. It insure you that it will never be a same pair (userid, topic) in your table. It is just as security.

Normally you should check if the 10 bookmarks limit has been reached and display or not the add bookmark button or whatever method you use to add a bookmark.

REY619

Oh Right...... Got it!!!! Thanx!!!! 🙂