Creating Tables From The Database

[dimkon]

Hello. I am a starter in PHP and 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:

1. 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)
   
2. 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)
   
3. Create the above (step 1 and 2) tables and if is necessary we put zero in the non voting item.
   

 

I cannot do that with PHP commands. Can anyone help me, please?

[Larry]

Could you clarify: do you want to create HTML tables or MySQL tables? I assume HTML, but it's not clear. The end solution, by the way, will just be a nice, advanced SQL command, executed from PHP.

[dimkon]

I have to create MySQL tables in order to use these data for some calculations . Thanks for responding.

[Larry]

Well, if the data already exists in other MySQL tables, you DON'T want to create new MySQL tables as that would break the rules of normalization. You could use a VIEW table instead, depending upon your version of MySQL.

[dimkon]

Thank you very much for your advice. I will try to use a view table as you said. I supposed that it won't be so difficult