Page 1 of 1

Create MySQL tables

Posted: Tue Mar 27, 2012 10:01 am
by kondim
Hello. I have a huge (for me ) problem. I would be so grateful if someone helps me.

I (try to ) work in PHP (version 5.3.1) and in MySQL (version 5.1.41).
Firstly, I connected successfully with the Database with these commands

$link=mysql_connect('localhost', 'root', '');
@mysql_select_db("joomla.voting", $link);

I want to create tables from the table joomla.voting of the Database. The joomla.voting table is like the following

Item | user | score
-------------------------------
120 | 1 | 5
120 | 2 | 1
121 | 1 | 4
121 | 3 | 2
…. …. ……

In this example we have two items (120,121), 3 users(1,2,3) and their votes(scores).
I want to create tables for each user with the items’ votes like that:
Table1: [5,4]
Table2: [1,0]
Table3:[0,2]
Where table1 is the table for user 1, table2 is for user 2, etc.
The first position of the table is the first item’ s vote (in the table 1, the number 5 is the vote for item 120)
The second position of the table is the second item’ s vote (in the table 1, the number 4 is the vote for item 121).
If the user hasn’t voted yet an item, I want to put zero (0) in this position (as in the example of the table2 where the user hasn’t voted the item 121).

My (theoretical) solution is:

Count unique the items from the table joomla.voting ( if it is necessary, we sort them because we want the first item to be in the first position of the table(that we want to create), etc)
Count the users from the table joomla.voting( if it is necessary, we sort them because we want the first table (that we want to create) to be for the first user , etc)
Create the above (step 1 and 2) tables and if is necessary we put zero in the non voting item.

Can anyone help me, please?

Re: Create MySQL tables

Posted: Tue Mar 27, 2012 1:06 pm
by requinix
First and foremost, what kind of "table" are you talking about?

Re: Create MySQL tables

Posted: Tue Mar 27, 2012 3:24 pm
by kondim
I want to create and use database tables in MySQl. Then i want to use that information in order to use an recommendation algorithm. Thanks for responding

Re: Create MySQL tables

Posted: Tue Mar 27, 2012 4:00 pm
by requinix
Ah, I was afraid of that.

Don't. There is absolutely no reason you should be creating tables. Whatever your problem is, there's a better solution.
So why do you think you need separate tables for each user?

Re: Create MySQL tables

Posted: Wed Mar 28, 2012 10:38 am
by kondim
I think that i need separates tables because i think that I will manage the data easier. From what you say I understand that it is very difficult and I have to find another solutions. Perhaps will it be easier to retrieve data from the existing tables?

Re: Create MySQL tables

Posted: Wed Mar 28, 2012 2:01 pm
by requinix
kondim wrote:Perhaps will it be easier to retrieve data from the existing tables?
Precisely. Using multiple tables sounds like a reasonable idea but trust me, it will only make things harder.