Can I use a php session username to select a mysql table?

[smiffy47]

In my site I have a situation where  

$_SESSION['MM_Username']

    will be the same name as a prepared MYSQL table.

(There will be a limited number of Users and tables)

Instead of writing a long series of "else if" to cope with each user on each page, I wish to employ the   $_SESSION['MM_Username']   to point to the correct MSQL table.

Take for example

<?php  $query_rstPalabs = "SELECT * FROM patricia WHERE yesno >= 1";  ?>

How can I substitute the    $_SESSION['MM_Username']   for "patricia"  here in a way that the SQL would select the table "patricia"?

Please be aware that I am not a professional designer, and flounder in the shallows of PHP/MSQL.  I use DW2004MX to help.
Thank you.

[sudarshan.t · Accepted solution]

Smiffy47,

Assign a variable to $_SESSION['MM_Username']. Call the variable within your query:

Example:

$user = $_SESSION['MM_Username'];

<?php  $query_rstPalabs = "SELECT * FROM $user WHERE yesno >= 1";  ?>

Be sure to use session_start in the beginning of your page so that you're allowed to call session variables within your application.

<?php session_start(); ?>

This should be added before your DOCTYPE declaration (Very beginning of your page).

-ST

[smiffy47]

Thanks Sudarshan.

Very helpful.

[bregent]

Why do you have tables named after users?  Something seems wrong. Why not use a single user table? Is each table a completly different design?

[smiffy47]

Thank you for your interest.  The website allows users to add and remove spanish words and phrases that they wish to learn, and the english equivalents.

In it I have one table which deals with usernames and passwords.  The structure of the other tables are identical to each other and simply contain the examples that the users have added. Each set of examples is personally chosen, and so the contents of each table is unique. From my inexperienced perpective it seemed easiest to give each user a seperate table, although I am certainly interested to hear your suggestions for simplification/improvement.

I imagine that the site might well just be used by myself and a few fellow students of spanish, and so the setting up of the tables is no great chore, but I would be interested to know how it could work on a single table if it were to be offered to a larger group of users.

[bregent]

Hi smiffy. The correct way would be to use a single table that contains a foreign key to the user table. The user would inert into and read only from rows with their user id. This is a much simpler design than using multiple tables, and will make your queries going forward MUCH easier to write and maintain. You never want to use multiple tables that store the same type of data.

[smiffy47]

Thanks for that, bregent.

I'll get out my PHPMYSQL book and see if I can set it up as you suggest.