convert text input to sql column

[justintoo1]

Hello all,

 

I need help converting text input from a form to a column from a table.

 

this is my form I ask for a day of the week and area.

 

<form action="processform.php" method="post">
<table>
<tr>
  <td>What day of the week ?</td>
  <td><input type="text" name="day"></td>
</tr>
<tr>
  <td>What area ?</td>
  <td><input type="text" name="area"></td>
</tr>
  <td></td>
  <td><input type="submit" name="submit" value="Search employees"></td>
</tr>
</table>

 

Now when I get the "day" i want to change that to $day.

Then I want to use that $day and search my table 'avail'.

Like 'avail.avail_$day'

The table avail has the columns 'avail_mon' through 'avail_sun'.

 

Is it a simple as assigining the users input to a variable ($day) and comparing like I thought. Or is this much mroe complicated.

 

I will put the code in my processform.php, and I will continue working on it.

 

Thanks for any help - justin

[megaresp]

I may have misunderstood what you're asking here, but I think what you want is something like this...

 

$day=$_POST['day'];

$query="select [whatever columns] from [table name] where avail_mon='$day' or avail_tue='$day' or [and so on]";

 

I suspect you may be looking for a less verbose way of querying your table. If it were me, I'd find a better way to store available days. In particular, I'd want this to be a single field. For example, I sometimes create text fields and use [] as separators for the individual data. For example:

 

[mon][tue][wed][thu][fri][sat][sun]

 

I might then use the following to show when the user was and wasn't available:

 

[monX][tue][wedX][thu][fri][sat][sun]

 

A query can then inspect a single field to see if the user is available on Monday by searching for [mon]. In the above example, it wouldn't find [mon] (because it's [monX]), thus telling you the user wasn't available that day.

 

Of course, if you need to know all available days or all unavailable days, you'd read the entire field into a variable, explode it, and write a PHP function or method.

 

I suspect there are better ways than this to achieve your result. For example, you use a tinyint and inspect individual bits to see if the user was or wasn't available. But why bother given today's server power?

 

Anyway, I hope this gets you started on a more sophisticated solution.

[justintoo1]

hello again,

 

thanks for the input.

 

this is how my avail table is set up now.

 

mysql> desc avail;
+-----------+------------+------+-----+---------+----------------+
| Field     | Type       | Null | Key | Default | Extra          |
+-----------+------------+------+-----+---------+----------------+
| avail_id  | int(11)    | NO   | PRI | NULL    | auto_increment | 
| emp_id    | int(3)     | NO   |     | NULL    |                | 
| avail_mon | varchar(3) | NO   |     | NULL    |                | 
| avail_tue | varchar(3) | NO   |     | NULL    |                | 
| avail_wed | varchar(3) | NO   |     | NULL    |                | 
| avail_thu | varchar(3) | NO   |     | NULL    |                | 
| avail_fri | varchar(3) | NO   |     | NULL    |                | 
| avail_sat | varchar(3) | NO   |     | NULL    |                | 
| avail_sun | varchar(3) | NO   |     | NULL    |                | 
+-----------+------------+------+-----+---------+----------------+

 

If i go into the terminal and manually type in my query I get result I would expect. like so

Thsi query displays employee id and name.
It joines 3 tables emp-avail-area to fidn someone
who is available on tues(y), and knows bakery.

mysql> select emp.emp_name, avail.emp_id
     from emp
     inner join avail
     on emp.emp_id=avail.emp_id
     inner join area
     on avail.emp_id=area.emp_id
    where avail.avail_tue='y'
     and
     area.area_bake='y';
+----------+--------+
| emp_name | emp_id |
+----------+--------+
| Wanna B  |     99 | 
+----------+--------+
1 row in set (1.02 sec)

 

So that way If i need to fill a shift for bakery on a tuesday, I want to run thru my list of emp's and find those that are available on Tues (y) and are trianed to work the bakery (y);

 

I can see what you mean by finding a better way to store avail days. This is what I have now..

 

emp table

mysql> select * from emp;
+--------+-----------+
| emp_id | emp_name  |
+--------+-----------+
|    461 | Justin B  | 
|    112 | Madeup T  | 
|     45 | Faker S   | 
|     99 | Wanna B   | 
|    105 | Frank R   | 
|    215 | Fresh D   | 
|    223 | Will F    | 
|    250 | Nomort Y  | 
|    303 | Randy Q   | 
|    319 | Kathy L   | 
|    384 | Shelly A  | 
|    400 | Linda N   | 
|    453 | Candy C   | 
|    470 | Richard F | 
+--------+-----------+

 

....and this is my avail table :

select * from avail;
+----------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| avail_id | emp_id | avail_mon | avail_tue | avail_wed | avail_thu | avail_fri | avail_sat | avail_sun |
+----------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|        1 |    461 | y         | y         | n         | y         | y         | y         | n         | 
|        2 |    112 | y         | n         | y         | n         | y         | n         | y         | 
|        3 |     45 | n         | n         | n         | y         | y         | y         | y         | 
|        4 |     99 | y         | y         | n         | n         | y         | n         | y         | 
|        5 |    105 | y         | y         | y         | y         | y         | n         | n         | 
|        6 |    215 | n         | n         | y         | y         | n         | n         | y         | 
|        7 |    223 | n         | n         | y         | n         | n         | y         | y         | 
|        8 |    250 | y         | n         | n         | y         | n         | y         | y         | 
|        9 |    303 | y         | y         | y         | n         | n         | n         | n         | 
|       10 |    470 | n         | n         | y         | y         | n         | n         | y         | 
|       11 |    453 | y         | y         | y         | n         | n         | y         | y         | 
|       12 |    400 | n         | n         | y         | y         | y         | n         | y         | 
|       13 |    384 | y         | y         | y         | n         | n         | n         | n         | 
|       14 |    319 | y         | y         | y         | n         | n         | y         | n         | 
+----------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

 

[justintoo1]

hello again, I for got to display my area table too.

 

here 'area' table :

mysql> select * from area;
+---------+--------+-----------+-----------+-----------+-----------+----------+
| area_id | emp_id | area_bake | area_dine | area_dish | area_line | area_reg |
+---------+--------+-----------+-----------+-----------+-----------+----------+
|       1 |    461 | n         | n         | y         | y         | n        | 
|       2 |    112 | y         | y         | n         | n         | n        | 
|       3 |     45 | y         | n         | n         | y         | y        | 
|       4 |     99 | y         | n         | n         | y         | y        | 
|       5 |    384 | y         | y         | y         | y         | y        | 
|       6 |    319 | n         | y         | y         | n         | n        | 
|       7 |    400 | n         | n         | y         | y         | n        | 
|       8 |    453 | n         | n         | y         | y         | n        | 
|       9 |    470 | y         | y         | y         | n         | n        | 
|      10 |    303 | n         | y         | n         | y         | n        | 
|      11 |    250 | y         | y         | n         | n         | y        | 
|      12 |    105 | n         | y         | y         | n         | y        | 
|      13 |    215 | n         | n         | n         | y         | y        | 
|      14 |    223 | n         | y         | n         | n         | y        | 
+---------+--------+-----------+-----------+-----------+-----------+----------+

This is a scheduler for a local cafe. I dont want to make the complete schedule, just this will help me see whos available for certain areas or certain days.

 

Thanks again