selecting and updating a table where "column name" = $variable

peanutgallery83

Okay, I'm really stuck here. I'm trying to make it so that when users click on a date on a calendar (which is working perfectly), it will add their id (established upon site registration) into a table called "Availability" under the date for which they clicked.

Example: User #17 clicks on March 20th on the calendar. This script should then save the value '17' into the table in the column '3-20' (for march 20th), in the row where the id matches their id.

As you can see in the script, I have stored the month and day as session variables in the calendar script. I am then combining these into one variable "$availdate". That variable is then getting used to find the matching column in the table.

The script is giving me the following error:
"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 '3-29 ='17' WHERE id ='17'' at line 2"
Thank you in advance for any help!

 
<?
// start the session 
session_start(); 
header("Cache-control: private"); //IE 6 Fix 

$username = $_SESSION['username'];
$referee_name = $_SESSION['referee_name'];
# to avoid conflict with the session 
$refid = $_SESSION['id']; 

// Register session key with the value 
   $_SESSION['day'] = $day; 
   $_SESSION['month'] = $month;

   $availdate = "$month-$day";

//check for validity of user


//set up table and database names
$db_name ="refphoto_testdb";
$table_name ="availability";

//connect to server and select database
$connection = @mysql_connect("localhost","refphoto","08291983") or die(mysql_error());
$db = @mysql_select_db($db_name,$connection) or die(mysql_error());

//build and issue query 
$sql ="UPDATE $table_name SET 
    ".$availdate." ='$refid'
    WHERE id ='$_SESSION[id]'";

$result = @mysql_query($sql,$connection) or die(mysql_error());
?>

devinemke

your query syntax is wrong, i assume the date field is called "availdate":

$sql = "UPDATE $table_name SET availdate = '$availdate' WHERE id = '" . $_SESSION['id'] . "'";

BTW: you should really be using the DATE field type to store dates. your code suggests that you are using a string type (VARCHAR).

peanutgallery83

No, there is no single column called "availdate"

What I have is a table with lots of columns:

id | 3-1 | 3-2 | 3-3| 3-4| etc etc for each day of the year.

I need to set it up so that when the user clicks on a date in the calendar, say March 20, it will look up column 3-20, and save their id into that column.

Thus, in this code, I need to make it so that it will find that column in the table and save the id. Make sense?

//build and issue query
$sql = "UPDATE $table_name SET 
		" .$availdate. " = '$refid' 
		WHERE id = '" . $_SESSION['id'] . "'";

devinemke

i suggest you re-think your data structure here. why have a table with 365 columns for all those dates when you can have a single DATE field?

table: availability
availability_id (INT) primary key
user_id (INT) relates to another table i'm guessing
date (DATE)

if User #17 is available on March 20 then simply add a record with that user_id and that date. if they are also available on June 3 then add another record for that date.

make sense?

peanutgallery83

Welllll....the reasoning for doing it that way, is that I will be generating a drop down menu that will show only people whose names (id #'s) appear for a particular date.

What will happen, is that in a separate part of the program, and through another table which contains a schedule....when a date appears on the schedule, it will only display the persons who have indicated they are available for that date. I figured that doing it with a 365 column table would be the easiest way to do it.

Make sense?

😕

devinemke

Originally posted by peanutgallery83
Welllll....the reasoning for doing it that way, is that I will be generating a drop down menu that will show only people whose names (id #'s) appear for a particular date.

What will happen, is that in a separate part of the program, and through another table which contains a schedule....when a date appears on the schedule, it will only display the persons who have indicated they are available for that date. I figured that doing it with a 365 column table would be the easiest way to do it.

so, using the table stucture i suggested, your could simply do:

SELECT user_id FROM availability WHERE date = '$date'

where $date is the particular date your are looking for.

TheDefender

Your thought process makes some sense, but when you step back and evaluate the data you are wanting to use and manipulate, no it doesn't make much sense. What happens to your table if you are dealing with a leap year or a new year altogether? Your way means that with every new year, it means building a whole new table. Not very efficient at all and doesn't follow the guidelines of table normalisation at all. I agree with devinemke, and suggest you reconsider your data structure before proceeding further with the project. You will appreciate it in the long run, even if it means more work for you right now.

peanutgallery83

Okay I gotcha now. I am new to this PHP thing and as such get stuck in the "what seems the easiest to do" mode sometimes, even though it might come back and haunt me later.

Thank you very much for the advice....I think I will try your method instead....since I haven't gotten too far in on that part of the project, it won't be difficult to change that up.

Thanks again!

Cheers.

Landis🙂