getting variable into statement

[freelance84]

The following line thoughs up an error:

 

$query = 'PREPARE statement FROM "INSERT INTO $table VALUES(?,?,?,?,?,?,?)"';
$result = mysql_query($query);
if(!$result) die(mysql_error());

 

The error returned is:

Table 'predef.$table' doesn't exist

 

For some reason it seems to be taking the database name and joining it the with variable name. How do I get the variable and not the variable name into the statement?

 

I've tried using single quotes, double quotes and back ticks:

Single quotes returns a parse error

 

Double quotes returns:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$table" VALUES(?,?,?,?,?,?,?)"' at line 1

 

Back ticks returns:

Table 'predef.$table' doesn't exist

 

 

[freelance84]

This is the entire prepared statement section that throws a wobbler

$table = $username."_".$newClass; //this creates the correct table name

 

$surname = get_post('surname');

$forename1 = get_post('forename1');

$forename2 = get_post('forename2');

$forename3 = get_post('forename3');

$title = get_post('title');

$gender = get_post('gender');

$chosenFname= get_post('chosenFname');

 

$query = 'PREPARE statement FROM "INSERT INTO "$table" VALUES(?,?,?,?,?,?,?)"';

$result = mysql_query($query);

if(!$result) die(mysql_error());

 

$query = 'SET @surname = "$surname",' .

'@forename1 = "$forename1",' .

        '@forename2    = "$forename2",' .

'@forename3    = "$forename3",' .

'@title = "$title",' .

'@gender         = "$gender",' .

'@chosenFname= "$chosenFname",';

 

mysql_query($query);

 

$query = 'EXECUTE statement USING @surname,@forename1,@forename2,@forename3,@title,@gender,@chosenFname';

mysql_query($query);

 

$query = 'DEALLOCATE PREPARE statement';

mysql_query($query);

[freelance84]

Does anyone have any ideas? really quite stuck here. When I change the $table to the actual table name it will go past this line.

[Pikachu2000]

Have you echoed your query string to make sure it contains the values you'd expect it to contain?

[freelance84]

Yup

 

they all worked fine.

 

I've decided to go back to the regular way of insertion for now and come back to this later. I have no idea why this doesn't work. 

[PFMaBiSmAd]

The overall (initial and final) quotes on the string being assigned to $query must be double-quotes to get php variables inside the string to be replaced with their actual runtime value.

 

Is there some reason you are not using the client-side prepared statement support as it is many times more efficient than the server-side prepared statements you are trying to use.

 

Also, don't dynamically produce tables for each different user and class. That will result in a data management nightmare.

[freelance84]

ah right, thank you 

 

The reason I am using server side at the moment is simply because the book i am learning from uses serverside. After I have really got a firm understanding of the PHP involved I will move on to try and use client side javascript.

 

Could you expand perhaps a little on the tables?

This is how I had planned to create the structure for database.

 

members table (contains username/password...etc)

each member then has ability to create up to 5 tables which will be prefaced with their username and an underscore then the name they choose.

e.g:

user, smithy856

smith856_gym705

 

If the member has 5 tables already they must 1st remove one before they can create another.

 

 

Are you saying it might be best to only allow one table per username?

[PFMaBiSmAd]

The client-side I was referring to was the client that connects to the msyql server (which is php in this case.) It has nothing to do with javascript, which is client-side when discussing web servers and http requests, not database servers and queries.

 

It's best to store same-meaning/same-structure data in one table. If the only thing that is different between data is a value or a category it belongs under or a user it belongs to, then it is same-meaning/same-structure data. In general, you would have a user id column in your data table that identifies which rows belong to each user, not a separate table for each user and where you are using up to 5 different tables for each user, you would simply have up to 5 rows per user in the single data table.

[freelance84]

r, not a separate table for each user and where you are using up to 5 different tables for each user, you would simply have up to 5 rows per user in the single data table.

 

But each users table would have 16 fields and anything upto 40/50 rows.

If I put all the users into one table this could potentially result in each user having 250rows each in the table. If the site gained 5000users that would be 1,250,000 rows.

 

I could create one table per user thus each table could contain upto 250rows each. Therefore each table would be more manageable and when a username was deleted their table would be deleted also?

[PFMaBiSmAd]

Here is a real life example. This forum currently has - 1,043,925 Posts in 198,897 Topics by 91,882 Members. Do you believe that each of the 91k members of this forum has a separate table OR that all the posts by all the members are stored in one table using a member id to identify who wrote each post?

[freelance84]

That's one big table!

 

OK then I'll go along those lines and create one table for user input along with ID's. Thanks for the help PFMaBiSmAd