0

Okay so I am making a marketplace type of website like olx etc.. users can post different types of posts meaning the fields change so I made an extra table called posts_attributes which those changing fields get saved in.

My current problem is when i want to display the posts attributes i want to display a title next to it, for example, lets say the user posted a car for sale now in the brand[input] field he entered volvo and the text volvo will get saved in the posts_attributes table when I want to display it i don't want to display just volvo I want to display Brand: volvo, but if a user is selling a house he will be asked the amount to bedrooms which he then will enter 2 or 3 etc.. then that will also be saved in posts_attributes. which again should be displayed as Bedrooms: 3.

How would I be able to do this with my current database schema or another type of schema?

How posts gets saved in db[post_ad.php]

please ignore all the variables just assume they are all text except for $ad_body that is an array

                date_default_timezone_set("Asia/Yangon");
                $date = date("Y-m-d h:i:s");
                $stmt = $conn_posts-> prepare("INSERT INTO `posts`(`ad_title`, `ad_sub_cat`, `ad_price`, `used_new`, `for_r_s`,`ad_location`,`ad_poster`, `ad_des`, `ad_date`) VALUES (?,?,?,?,?,?,?,?,?)"); //query
                $stmt->bind_param("sssssssss", $ad_title, $ad_sub_cat,$ad_price,$ad_used_new,$ad_for_r_s,$ad_loc,$_SESSION['user_ID'],$ad_des, $date); // bind_param / insert info
                $stmt->execute(); // execute
                $stmt->store_result(); // store results
                $last_id = $conn_posts->insert_id; //get id of post
                if($stmt->affected_rows == 1){
                    foreach($ad_body as $data){
                        $stmt_atr = $conn_posts-> prepare("INSERT INTO `posts_attributes`(`post_id`,`post_attribute`) VALUES (?,?)"); //query
                        $stmt_atr->bind_param("ss",$last_id,$data); // bind_param / insert info
                        $stmt_atr->execute(); // execute
                        $stmt_atr->store_result(); // store results
                    }
                    if($stmt_atr->affected_rows >= 1){
                        header('location:ad?id='.$last_id.'');
                        exit();
                    }else{
                        $errors[] = "Sorry, there was an error uploading your ad. D:/paf102";
                    }
                }else{
                    $errors[] = "Sorry, there was an error uploading your ad. D:/paa102";
                }

This is my Database Schema built with Lucidchart

enter image description here

I know there is a lot of PHP but I just recently saw this site and maybe it might work better for my database questions. i am sorry if this question is not in relation to the rules and please ask or tell me anything and i will immediately supply info or fix my question.

1 Answer 1

0

What database system are you using?...please update your question's tags accordingly.

As far as schema design, if your posts_attributes table is where you're storing the "Volvo" and "3" values, then you should have a field called attribute_value that stores it and the post_attribute field goes away. You can then add an attribute_title field too, which can store things like "Brand" and "Bedrooms".

Though you should have a normalized table of the unique list of attribute_titles and only store the id of the title itself in your posts_attributes table. In which case, the field in your posts_attributes table will actually be called attribute_title_id.

8
  • I was thinking the same thing and I already started but how would I save the attribute_title_id with the attribute_value because as you can see from my ad_post.php page it gets added with a for each which is an array of the fields that the users added like Brand and amount of doors and color etc...
    – DB_cont
    Oct 23, 2021 at 12:45
  • @DB_cont Does your User define the attribute_title or are they forced to choose it from an existing place in the application?
    – J.D.
    Oct 23, 2021 at 13:51
  • it is a user define input here is the html <input name="body[]" placeholder="Brand"></input> and if it is in the house category it will be <input name="body[]" placeholder="Bedrooms"></input>
    – DB_cont
    Oct 23, 2021 at 14:57
  • I made it like this because now i can just check the category id and change the form based on which main category was selected with a simple file include meaning the form will change.
    – DB_cont
    Oct 23, 2021 at 14:57
  • @DB_cont ok so the only place the attribute_title lives currently is in your HTML code?
    – J.D.
    Oct 23, 2021 at 21:09

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.