Database Question - Matching

[vibajajo1964]

I'm just wondering if this is possible in Mysql. I want to have a website with a Mysql database containing a million items. I'll call this database the Master List.

Next, I want to have a person be able to come on the website and be able to somehow select from the Master List a number of items they would like to buy. I'll call this the want list. After a person completes their want list, I'd like the want list to go into a new datbase I'll call the Wanted List database.

The last part would be where a person looking to sell an item would be able to search the Wanted List database.

Anybody know a program that already does what I'm looking for or how to get me started?

Thanks for any help,

Vince

 

[feherke]

Hi

Your plan is difficult to follow due to your habit of calling everything a database.

Personally I would start with 3 tables, storing the information like this :

item          item_person_relation                  person
id | name     id | item_id | person_id | relation   id | name
---+-------   ---+---------+-----------+---------   ---+--------
 1 | Apple     1 |       1 |         1 | buy         1 | Arnold
 2 | Banana    2 |       1 |         2 | buy         2 | Bruce
 3 | Cherry    3 |       2 |         2 | buy         3 | Charles
               4 |       1 |         3 | sell

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[vibajajo1964]

Feherke,

Thanks for your reply and sorry, I meant to call them tables, not databases. The first table is the easiest, just entering the list of items in Table 1.

The part where I'd need guidance would be on Table 2, the wantlist. I'm not sure of some things and would appreciate ideas and guidance:

1.) I'm not sure of the method to be used for selecting items and creating a wantlist. I can only think of a checkbox next to every item and wouldn't know how to implement this. Any better ideas?

2.) If I have a huge amount of items, I'm assuming I would need a search box for people to search for items to add to a wantlist. I'm envisioning after an item is searched for, a number of matches would appear with a checkbox next to each one so that an item could be selected to the wantlist.

3.) I could set up Table 2 with the headings similar to those you suggested, however after a person selects their wantlist items, how do I get a persons completed wantlist to transfer to Table 2 along with the Person's Name, email address.

Thanks for your help,

Vince

 

[feherke]

Hi

I can only think of a checkbox next to every item and wouldn't know how to implement this.

That is good if only a logical status is needed. But if is relevant how many of a certain item a person wants, a [tt]text[/tt] [tt]input[/tt] ( or a [tt]numeric[/tt] one, in case of HTML5 ) would be better. But this depends on your business and/or goal.

If I have a huge amount of items, I'm assuming I would need a search box for people to search for items to add to a wantlist.

Certainly you will have to offer some ways to restrict the displayed list. Searching is one of them. Additionally, but not strictly related to the searching you will need paging and sorting too. Beside that, some kind of categorization and/or tagging would be useful.

3.) I could set up Table 2 with the headings similar to those you suggested, however after a person selects their wantlist items, how do I get a persons completed wantlist to transfer to Table 2 along with the Person's Name, email address.

Earlier you wrote, "Table 2, the wantlist". In that case table 2 should not contain other person data then the person's unique identifier from another table.

Given the tables I mentioned in my earlier post, the most frequent operations would look like this :

*** Display a list of items ***
query :
  select
  id,name
  from item
  where $get_filter_condition
  order by $get_sort_criteria
  offset $get_page_number
  limit $get_page_length

loop through results :
  display :
    <input type="checkbox" name="wanted[]" value="$sql_id"> $sql_name

*** Save wanted items ***
loop through form data :
  query :
    replace into item_person_relation (item_id,person_id,relation)
    values ($form_wanted[$i],$session_user,'buy')

*** Display wanted items ***
query :
  select
  i.id,i.name
  from item i
  inner join item_person_relation ipr on ipr.item_id=i.id
  where ipr.person_id=$session_user
  and $get_filter_condition
  order by $get_sort_criteria
  offset $get_page_number
  limit $get_page_length

loop through results :
  display :
    $sql_name <input type="checkbox" name="unwanted[]" value="$sql_id"> Delete ?

*** Remove unwanted items ***
loop through form data :
  query :
    delete from item_person_relation
    where item_id=$form_unwanted[$i]
    and person_id=$session_user
    and relation='buy'

*** List persons wanting a certain item ***
query :
  select
  p.id,p.name
  from person p
  inner join item_person_relation ipr on ipr.person_id=p.id
  where ipr.item_id=$get_item_id
  and ipr.relation='buy'
  and $get_filter_condition
  order by $get_sort_criteria
  offset $get_page_number
  limit $get_page_length

loop through results :
  display :
    <a href="person.info?id=$sql_id">$sql_name</a>

Their proper implementation may depend on the chose language. That would be off-topic here.

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[feherke]

Hi

( or a [tt]numeric[/tt] one, in case of HTML5 )

Oops. I mean [tt]number[/tt].

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]