/    Sign up×
Community /Pin to ProfileBookmark

some "conceptual" help needed…

Copy linkTweet thisAlerts:
Jun 20.2007

Hello,

I am still new to database programming and am stuck with an idea and no solution.

I already have a working user registration/login script which dumps everyones name and id in the “users” table.

Now, I want a table filled with books, their authors, and a description. Essentially, i want each user to be able to select their “favorite books” and have it show up as a “Sam’s Favorite Books” page.

My problem, however, seems to arise by trying to connect the users selected books and to have that correspond with their “book page”.

Any ideas on how to tackle this problem?

Thanks in advance,
Sam

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@chestertbJun 20.2007 — I use a 'link' table.

It's a simpel database table that has just two fields, in your case, the person and the book.

It would look something like this...

21 153

21 297

14 556

21 998

etc

In the above, person 21 is now linked to books 153, 297 and 998, while person 14 is linked to book 556.

Both fields are key fields in the db.

It's then fairly simply to construct a search query for, say, all the books linked to person 21, (SELECT * FROM booklink WHERE person='21') or, all of the people who like book 297 (SELECT * FROM booklink WHERE book='297').
Copy linkTweet thisAlerts:
@tubaplaya76authorJun 20.2007 — My question now is, how do I get the values from the original person ID from the 'user' table and the book data from the 'books' table into my new 'booklink' table?
Copy linkTweet thisAlerts:
@felgallJun 21.2007 — You add the records to that table when a given user selects a specific book by taking their userid and the id of the book that they selected and adding them into the new table. Until they actually make the choices you don't know which users want which books included.
Copy linkTweet thisAlerts:
@chestertbJun 21.2007 — First, let me apologise for the really bad spelling/typing. I seem to be having trouble with the word "simple".

Ok... now to your question...

As you're new to bd programming, I'll go back to basics. If this is too basic, I apologise. My intention is merely to make sure we're on the same page.

First, you're now talking about relational db construction. That means that in each record, there is some unique value by which you can refer to that record. Fortunately, most db engines hapilly create one of these for you. In mySQL, it is the "auto increment" value. Effectively, that's just the record number.

Once you've got that number (or any other unique value), you can link one data table to another.

In my example, because there are many books that can be linked to one person, I've used a table that contains the person record number and the book record number. Of course, the link table will also have it's own auto increment value too.

If I'm creating the links manually and the data set isn't too large, then I usually create three lists. The one on the left would list the people, and the on on the right, the books. In between the two, I would put a list of the linked books.

Click on the left list, and it you shows the books selected by that person.

The html for each entry in that list would look something like this...

<a href=''yourscript.php?pid=".$pid."&act=get'>$bookname</a>

Click on the right list, and you add that book to that person.

The html for the entry in the right list would look something like this...

<a href=''yourscript.php?pid=".$pid."&bid=".$bid."&act=add'>$bookname</a>

Click on the middle list, and you remove that book from that person.

The html for each entry in the middle list would look something list this...

<a href=''yourscript.php?linkid=".$lid."&pid=".$pid."&act=del'>$bookname</a>

Your script is going to perform a different action, depending on the value to "act" (call it anything you like).

for example...
[code=php]//get the incoming values
act = $_REQUEST['act'];
$pid = $_REQUEST['pid'];
$lid = $_REQUEST['lid'];
$bid = $_REQUEST['bid'];

//now perform the act
switch($act)
{
case "del":
//use the $lid number to delete the link record
$query = "DELETE FROM link WHERE lid='$lid'";
$result = mysql_query($query);
break;

case "add":
//use the pid and bid numbers to add a record to the link table
$query = "INSERT INTO link (pid, bid) VALUES ('$pid', '$bid')";
$result = mysql_query($query);
break;
}

//now assemble your list of linked books...

if($pid!="")
{

//get the list of books belonging to that person
//for simplicity, I'm goign to do that without the SQL JOIN function.
//For more info on that, there's a reasonable tutorial at www.w3schools.com
$query = "SELECT * FROM link WHERE pid='$pid'";
$result = mysql_query($query);
while($list=mysql_fetch_array($result))
{
$l++;//records the number of books found
$lid = $list['lid'];
$bid = $list['bid']

//now get the individual book title by performing a query using $bid
//then assemble your html for that line
}
}[/code]
×

Success!

Help @tubaplaya76 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.19,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...