bookmark table design

[jodunno]

Hello SQL gurus,

details of system: xampp installation with MariaDB v10.1.30 with PHP v7.2.1

I have set up my website file system to include a bookmarking system. The foundation is intact now i need to set up the database to store the values of the bookmarks. click on my bookmark icon which will submit a bookmark profile block for storage in the database (a serialized array of necessary values). I want to limit the bookmarks to 100 entries per user.

i don't know exactly how to build this table and retrieve the data. is anyone able to offer design tips? i figure that i should make a database named bookmarks with a table for each user? then add an id row and a bookmark title (for duplicate detection) and the serialized profile for redisplay. I guess one can select all data in the table but how should i store each result for analysis and redisplay of the profile? i am not an sql guru. i currently only use simples select statements.

I hope that someone can offer some tips.

Thank you!

[Barand]

28 minutes ago, jodunno said:

i figure that i should make a database named bookmarks with a table for each user?

No. One table named "bookmark" and store the user's id in each bookmark record. If your site currently uses a database, just put the extra table in that.

You can check how many each user has with a simple

SELECT COUNT(*) as total FROM bookmark WHERE userid = ?;

 

30 minutes ago, jodunno said:

serialized array of necessary values

How that is stored depends on what those "necessary values" are. If the arrays always contains the same keys I'd create a column in the bookmark table for each key. If the keys vary from bookmark to bookmark then store as a json encoded array (MySQL 5.7+ supports json columns)

If "bookmark_title" should be unique, enforce it in the table by placing a UNIQUE constraint on that column.

[jodunno]

Hi Barand,

Thank you for the tips. You are very kind to take time to help a newb. I appreciate your post very much!

I've made notes from your post. I will use a table like you mentioned. I am not really new to php/sql but i am obviously not a programmer. I suck, actually. I have alot of trouble with database design. I have not mastered this concept. I made a login system three years ago for the first time in my programming tenure. So i have a dtabase of users. I will just add this table to this database like you mentioned.

Thank you for the count() function tip. I didn't realize that sql has this function. Very nice, indeed! and very helpful post from you.

i can explain a bit more while sticking to the bookmark db problem/topic: i actually built my own routing system which does not use query strings. I use forms and session variables to load pages through a single index file. So the "link" is a partial path to the php file system to load the correct "page" in a single page template. Somewhat spaghetti code but i do not like frameworks. I am happy for now. I will get better over time. Anyway, the bookmark will be a collection of known data from the template page:

page link as a db title to detect duplicates: path/to/data (path is always the root. bookmarks are only used within these pages. thus path is like c:/)

css icon display: i use icons to represent the links to pages and this is either a round icon or rectangular icon (which holds a photo).

icon photo link: each icon has a unique photo, so i need this data to get the correct icon for the bookmark.

language: i use a multi language file system so this is important to display the correct icon title by language. so here en-us, en-uk, de-de etc.

icon title: all ready present on the page as a variable ($pageTitle)

all of the above is my bookmark profile. now i just need to store each bookmark <101 into a database. Then figure out how to read these entries to display them on your bookmarks page (i guess a loop to read each entry and store each entry into an array?) I figure that serialized profiles will make this an easier task. How to know duplicate entries? i figure that a bookmark title (the page link) can be used. Thus, i can easily detect if this page is all ready bookmarked or not.

I will give it my best try and post any problems here. Remember, i am not a guru/programmer. Thus, i may need alot of time to get this set up. Your tips will help me.

Thank you!

[jodunno]

By the way, i have to read about json encoded arrays. is this serialized like php serialize()? i'm not familiar with this so i will research json encode/decode. Thanks for he tip!

[Barand]

I don't think you need bother with the json. Just define your table as something like this

CREATE TABLE `bookmark` (
  `bookmark_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `page_link` varchar(50) DEFAULT NULL,
  `css_icon` varchar(50) DEFAULT NULL,
  `icon_photo_link` varchar(50) DEFAULT NULL,
  `language` varchar(10) DEFAULT NULL,
  `icon_title` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`bookmark_id`),
  UNIQUE KEY `unq_page_link` (`page_link`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

[jodunno]

Hi Barand,

Amazing code and you only read a description of my bookmark profile. You are a 'helluva' coder. Your expertise and mastery shows in your replies.

I don't really need to change anything that you have posted other than names but i didn't post to get free code. I am trying to learn from your example. I'm reading about sql now so that i can think better about these problems and approprite solutions. I'd like to come to the same conclusions as you  oneday. I really learn alot from you and i thank you for that.

Meantime, i've changed the last login code and it works well. I was actuly just inserting your login into lastlogin then inserting the current login into current login. I guess it is easier to say that login becomes your last login before i update the login. I guess i was thinking wrong here. Your idea is better.

I don't have time to add the book mark code today. I have alot of things to do and i am behind schedule. I'll read more about sql before i go to bed, then tomorrow i will tackle this topic. I finished adding the bookmark profile to each page, so all i have to do now is submit it to the dbase. This code example is a great start! I also have to read the data from the db before i can display the bookmarks. I do not have so much coding experience as you do, so i am a bit slow. I'll update the post when i can finish this feature. I'll let you see the final code here so you can offer an opinion if you want to do so.

Thank you, Barand, i have learned alot about sql today. You are steeringme in the right direction!

 

[jodunno]

On 5/9/2020 at 6:44 PM, jodunno said:

I do not have so much coding experience as you do, so i am a bit slow. I'll update the post when i can finish this feature.

Hello again,

I've finally added my bookmarking system and it is working! However, the unique key constraint had to be removed because it is applied to the single table. The unique constraint could only work with a table for each user. I had to add a pre-insert query to check for a duplicate entry (a loop with if column = attempted link). Other than the unique constraint error, al is good. Thanks again!

 

edit: what i mean by unique constraint is that my site crashed with a duplicate entry error (1062) when i added the same link from a different account. I failed to realize too that the unique constraint will allow the link to be entered for only one user in the table.

Best wishes.

Edited September 17, 2020 by jodunno
clarification

[gizmola]

Actually, I think that Barand just made a small mistake, which is certainly very unusual for him    The unique constraint should be:

UNIQUE KEY `unq_page_link` (`user_id`, `page_link`),

That will enforce uniqueness on a user/link basis, which is what I assume you are going for.  This is a better solution in that you are using the db to enforce your integrity rule rather than relying on the front end code to do it for you.

With that said, code around database queries needs to be written in a way that you catch and deal with database errors appropriately. Typically that is done with a try..catch block.  I can't give you specifics without knowing in advance what db api you are using (mysqli vs pdo) .

Here's an example out of the "(The only proper) PDO tutorial" which is highly recommended reading for anyone learning the PDO api:

try {
    $pdo->prepare("INSERT INTO users VALUES (NULL,?,?,?,?)")->execute($data);
} catch (PDOException $e) {
    $existingkey = "Integrity constraint violation: 1062 Duplicate entry";
    if (strpos($e->getMessage(), $existingkey) !== FALSE) {

        // Take some action if there is a key constraint violation, i.e. duplicate name
    } else {
        throw $e;
    }
}

Assuming you are using PDO this could be adjusted to fit your problem as it is essentially the same issue you would face in dealing with the unique constraint violation to prevent a user from adding the same link multiple times.

If you are using mysqli, then you should certainly read this.   A general discussion of php error handling strategies is good to read.

The important thing to note about this, is that you don't want to generalize error handling to try .. catch blocks around all your queries.  This is a specific issue, having to do with something you expect to happen frequently.  Lots of other database exceptions can happen, and you want to have a generalized exception handling solution for those.

[benanamen]

3 hours ago, gizmola said:

Typically that is done with a try..catch block.

Typically yes, that's what you see out on the net, but I would have to disagree with this method. IMO there is really only two common cases to use Try/Catch, that being the DB Connection and handling a duplicate constraint error. There is no need to litter the code base with Try/Catch blocks. (Yes, I was guilty of that until I got spanked by @Jacques1 and learned better)

What "should" be done is set the PDO::ATTR_ERRMODE   => PDO::ERRMODE_EXCEPTION and let the exceptions bubble up and let PHP handle it, which it does very well. set_exception_handler can also be used if you want a custom handler for exceptions.

[gizmola]

 

1 hour ago, benanamen said:

IMO there is really only two common cases to use Try/Catch, that being the DB Connection and handling a duplicate constraint error.

Transactions are another one.  I don't really disagree with what you wrote, and in fact I linked to information that agrees with you.  The problem with anything concrete is that we don't know if jodunno is using PDO or MySqli.

[jodunno]

Hello gizmola and benanamen,

Thank you for the informative replies. I did not know that one can "enforce uniqueness on a user/link basis". I am nescient about sql. I will have to drop my table and rebuild it now. I don't mind an extra query if there is no other way but clearly you know of a better way. I will drop the table and add the new unique constraint. Thank you for this valuable info!!! I should've expressed my desires clearly. My apologies for any vagueness in this thread. Your assumptions are correct about unique links per user. I just want to allow bookmarks but not duplicate entries for each user. I am excited to learn how to do this properly. I doff my hat to you! Thank you.

I use pdo. I just started studying php two years ago in my spare time. I never learned mysqli. I made my first login script two years ago using pdo. Thus, i only know pdo.

my code is slightly different than the following but only by variable names. I clean it up for posting here because some people just insist on doing things their way. such as, naming db variable $conn, $stmt etc. I use my own names.

<?php declare (strict_types = 1);
$dbhost = '127.0.0.1';
$dbname = 'test';
$dbuser = 'root';
$dbpass = '';
$dbattr = array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);

try { $connection = new PDO("mysql:host=$dbhost; dbname=$dbname; charset=utf8mb4", $dbuser, $dbpass, $dbattr); }
catch (PDOException $e) { error_log($e->getMessage()); }

    $query = 'SELECT count(*) as total FROM bookmarks WHERE uid = :uid';
    $stmt = $connection->prepare($query);
    $stmt->execute(array(':uid' => $uid));
    $result = $stmt->fetch();
    $Total = $result['total'];

    if ($Total < 100) { 
        $query = 'SELECT link FROM bookmarks WHERE uid = :uid';
        $stmt = $connection->prepare($query);
        $stmt->execute(array(':uid' => $uid));
        while ($column = $stmt->fetch()) {
            if ($column['bm_path'] === $bmPath) { $duplicate = true; break(2); }
        }
            $query = 'INSERT into bookmarks (uid, type, link, icon, label, date) VALUES (:uid, :type, :link, :icon, :label, NOW())';
            $stmt = $connection->prepare($query);
            $stmt->execute(array(':uid' => $uid, et cetera...));
    }

I wonder if you have any suggestions about making my code better. Please remember that i can now remove the SELECT link query because you showed me how to enforce a unique link per user. Honestly, i am very happy about this info.

Best wishes.

[gizmola]

I will try and come back and take a look at your code, but I wanted to say that using PDO is great.  I would say that the staff is universally in favor of using PDO, so no worries about learning mysqli.

[jodunno]

Hi gizmola,

I typically do not understand certain mysqli code and i usually ignore it. I am happy with pdo and pdo is usually suggested by experts, like you, anyway.

Meantime, i should also explain my code and repost it. You may notice a break(2) and wonder why it exists, for example. My code acually contains a switch based upon a form post. I keep all of my bookmark db code in one file (bookmarksdb). I have a page to display all bookmarks (case 'select'), a page method to add a bookmark on bookmarkable pages (case 'insert') and a page to delete bookmarks from your bookmarks list (case 'delete'). I also use a counter case which simply returns the count ($result['total']). The counter (case 'count') is used as a small circle that holds the count of bookmarks to be displayed at the top right div of the bookmarks page icon. I use it like a phone apps new messages bubble beside app icons. Thus, the icon count bubble will inform you how many bookmarks you have to avoid a need to view the bookmarks page just for this info.

<?php declare (strict_types = 1);
$dbhost = '127.0.0.1';
$dbname = 'test';
$dbuser = 'root';
$dbpass = '';
$dbattr = array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);

try { $connection = new PDO("mysql:host=$dbhost; dbname=$dbname; charset=utf8mb4", $dbuser, $dbpass, $dbattr); }
catch (PDOException $e) { error_log($e->getMessage()); }

    $query = 'SELECT count(*) as total FROM bookmarks WHERE uid = :uid';
    $stmt = $connection->prepare($query);
    $stmt->execute(array(':uid' => $uid));
    $result = $stmt->fetch();

switch ($action) { //$action is known by session array key which holds the csrf token of the form field posted
  case 'delete':
    //code to delete a bookmark, which relies upon !empty($result['total']) 
    break;
  case 'insert':
    if ($result['total'] < 100) { 
        $query = 'SELECT link FROM bookmarks WHERE uid = :uid';
        $stmt = $connection->prepare($query);
        $stmt->execute(array(':uid' => $uid));
        while ($column = $stmt->fetch()) {
            if ($column['link'] === $link) { $duplicate = true; break(2); }
        }
            $query = 'INSERT into bookmarks (uid, type, link, icon, label, date) VALUES (:uid, :type, :link, :icon, :label, NOW())';
            $stmt = $connection->prepare($query);
            $stmt->execute(array(':uid' => $uid, et cetera...));
    } break;

}

I still don't know how to handle errors from the other queries. I don't want my site to break and a white page to be shown with error data. I prefer that my site remain visible and i display the error and not the software. I have no idea how to do this yet.

anyway, Thank you for the info in this thread. I appreciate everyone taking time to help me. Like i said before, i am not delusional about my status here. I am not a programmer at all. I am street smart for sure and able to code despite no education in this field. I sometimes do things correctly and many times just get something to work. No matter what, i always appreciate professionals offering inside advice. I sometimes wish hackers would dump source code to big tech company websites so i can truly learn how the pros do it. I enjoy coding when i can do it correctly but i lack so much info about this industry. I often have to employ street smarts to bring a concept to life. I have no programming friends so i never receive inside intel about 'how the pros code common ideas'.

ok, too much babbling and i far too much work to do today. Off i go...

Best wishes.