Variables in MySQL Command

[coolant]

I know you can have VALUES in MySQL query's be variables, but is there any way to set a table as a variable?

I have the following code...

$cat = $_POST['cat'];
$date = $_POST['date'];
$title = $_POST['title'];
$joke = $_POST['joke'];

$insert = "INSERT INTO $cat (id, date, title, joke) VALUES ('','$date','$title','$joke')"; 
mysql_query($insert) or die ("Could not add data to the table");

If I change $cat to a table name, it works. Is there any way to have the query recognize the $cat tho?

[mikey]

put it in quotes like the others?

[RoboStac]

Theres no reason at all that shouldn't work, assuming $cat is actually getting the correct table name. all the mysql query is is a string passed to the database, so $insert is just a normal string variable - the mysql server has no idea it's any different. You could try backquoting (`) the table name, as this is allowed syntax for when the table name has spaces etc, but that could give you problems with spaces at the beginning / end of the POST'ed data

You may want to die(mysql_error()) to find out what the exact error is - that would be a *lot* more useful, as I can't see anything wrong with that code.

Are you sure $cat is getting initialised properly ? (eg stick a print statement just after the assignment).

The reason the others are in quotes is because thats SQL syntax - strings to be inserted have to be quoted, or it will take them as an sql keyword, and either generate errors, or not do what you want.

Hope this helped.

Also, on a purely good practice point, I'd question your inclusion of ID in the field list, as it is getting assigned '' each time. I'm assuming this is an autoincrement, for which I believe the correct method is to assign 0, or more preferably not assing anything at all. (What I'm saying is leave it out the fields and values list). If its not an autoincrement, its an empty string field, for which you could set that as a default value and leave it out anyway.

[Tim Dorr Veteran]

Hmm, looks like you have an improperly normallized database. That means it's tables are not laid out correctly. You have a table for each category, but that's a problem. Try this format:

Table: categories

Fields: cat_id, cat_name

Table: jokes

Fields: joke_id, cat_id, date, title, joke

You fill the categories table with your available categories (plus, you can more easily create categories this way). Then you just link each joke their category and you can select them like this:

SELECT * FROM jokes j LEFT JOIN categories c ON c.cat_id = j.cat_id

That will give the jokes with their category names added on. You can also just change your form to SELECT * FROM categories and print ou the list of categories in a <select>. Then on the other end, you just grab the category id from the <select> and use it to insert the joke.

[Thekk]

If I'm correct, your query statement should look like this:

$insert = "INSERT INTO ".$cat." (id, date, title, joke) VALUES ('','".$date."','".$title."','".$joke."')";

Also, make sure that the tablename that you $_POST has the correct combination of upper and lowercase characters...

Greetings,

Thekk