Jump to content



Photo

Creating Tables From The Database


  • Please log in to reply
4 replies to this topic

#1 dimkon

dimkon

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 25 March 2012 - 3:27 PM

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:
  • 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.

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

#2 Larry

Larry

    Administrator/Writer

  • Administrators
  • 3,999 posts
  • LocationState College, PA (USA)

Posted 26 March 2012 - 3:40 PM

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.
  • 0

#3 dimkon

dimkon

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 27 March 2012 - 9:52 AM

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

#4 Larry

Larry

    Administrator/Writer

  • Administrators
  • 3,999 posts
  • LocationState College, PA (USA)

Posted 28 March 2012 - 7:08 AM

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.
  • 0

#5 dimkon

dimkon

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 28 March 2012 - 10:47 AM

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
  • 0