syntax for picking mysql column from form

[glava]

Hi good people, i'm vary new in this and i'm having trouble with PHP while writing some project for school and because i find many answers on this forum till now i decide to post this.. so here is my problem:

I'm trying to make a web page for students and profesors where students (when they are loged in) will be able to sign a date for their exam so i made a form like this :

 

<form method="POST" action="">
choose exam:
<p><select name="exams">
<option value="k1D">exam 1</option>
<option value="k2D">exam 2</option>
<option value="k3D">exam 3</option>
</select></p>
choose date:
<p><select name="dates">
<optgroup label="Zimski rokovi">
<option value="2011-02-01">01.02.2011.</option>
<option value="2011-02-07">07.02.2011.</option>
<option value="2011-02-15">15.02.2011 </option>
<optgroup label="Ljetni rokovi">
<option value="2011-05-21">21.05.2011.</option>
<option value="2011-05-28">28.05.2011.</option>
<option value="2011-06-04">04.06.2011.</option>
</select></p>
<input type="submit" value="Prijavi ispit" name="prijavi">
</form>

 

table for students in mysql has columns for every exam (k1D , k2D..) but how can i make so that student can pick wich exam he wants to sign on some of dates (wich column he wants to fill with wich of dates)  ?

 

i tryed some variations of :

 

$k1D = $_POST['dates'];
$kol = $_POST['exams'];
mysql_query(" UPDATE studenti SET '$kol' = '$k1D'
WHERE ID = '3'");

 

but i'm just getting different errors..

 

[fugix]

what does your mysql table look like?

[glava]

here is code if that's what you mean..

 

students :

 

CREATE TABLE IF NOT EXISTS `studenti` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `indeks` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `k1D` date NOT NULL,
  `k1O` varchar(2) NOT NULL,
  `k2D` date NOT NULL,
  `k2O` varchar(2) NOT NULL,
  `k3D` date NOT NULL,
  `k3O` varchar(2) NOT NULL,
  `U_ID` int(11) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `indeks` (`indeks`),
  UNIQUE KEY `Email` (`email`),
  KEY `U_ID` (`U_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

 

and users :

 

CREATE TABLE IF NOT EXISTS `user` (
  `ID` int(4) unsigned NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `level` int(4) default '1',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

 

those 2 are "linked" with 'U_ID' from 'students' and 'ID' from users.. i also wanted to make that when some student is loged in (student1 for exemple) he can only sign exam date for himself..how's that possible ?

 

thank you for your time and willing to help !

[glava]

enybody HELP...please ?

[jcbones]

You shouldn't put quotes around the column name.

$k1D = mysql_real_escape_string($_POST['dates']);
$kol = mysql_real_escape_string($_POST['exams']);
mysql_query(" UPDATE studenti SET $kol = '$k1D'
WHERE ID = '3'");

[glava]

Thanks jcbones, that part works now but like i wrote already i don't know how to make that when some user is loged in (student1 for exemple) he can only sign exam date for himself..how's that possible ? i suppose i need to "pull" ID of user from database when he logs in and assign that number to some new variable, then instead of " ..WHERE ID = '3'  " in

 

mysql_query(" UPDATE studenti SET $kol = '$k1D'
WHERE ID = '3'");

 

put " ..WHERE ID = 'new variable'  "  but i realy don't know how.. 

 

somebody please HELP ??

 

 

[fugix]

are you saying that you want to make so that once a person signs up for a date to take the exam, no one else can sign up for that date? If so, what you can do is create another field in that table called "booked" or whatever you want the name to be. Set the type to INT and make the default value 0. Once a user registers under that date, set the column value to 1. In your loop to echo the available dates. change the query to add WHERE booked = 1....something like that perhaps?

[glava]

No, no, i didn't meant on that. Sorry for my bad ENG. What I want is :

1 user logs in (student1), and sign exam for any of offered dates and he's done. if that user have ID 3 i need to put that date in his row ( WHERE ID = '3' ) but if another user logs in, student5 for example and he has ID 5 i need then to put his date in his row ( WHERE ID = '5' ) .

 

I hope I explain good this time.

[fugix]

You shouldn't put quotes around the column name.

$k1D = mysql_real_escape_string($_POST['dates']);
$kol = mysql_real_escape_string($_POST['exams']);
mysql_query(" UPDATE studenti SET $kol = '$k1D'
WHERE ID = '3'");

jcbones post will work for you then

[glava]

Sorry but I am afraid not. obviously i don't know how to explain so i would try again..

 

Look, i have several users in "users" table in database. Everey user have his one ID, and that ID is linked with table of students.

So first user has ID "1", username "student1" and password "student1" ( for example ) and with ID is linked on first student in "students" table who has also ID "1", firstname "Chuck" , lastname "Norris" , exam1 "emty date" , exam2 "empty date" ...

 

what i want is when user "student1" logs in, and sign some date for exam1 PHP write that date in his row/line/whatever in mysql table "students" WHERE ID = '1' , but also when user "student2" logs in, and sign some date for his exam1 PHP write that date in his row/line/whatever in mysql table "students" WHERE ID = '2'

 

[fugix]

ah, well the only thing you will need to change will be the WHERE id = 3, you will need to set a variable for the user id. What i like to do, is when a user logs in, query the users id based on his/her credentials. so like $query = mysql_query("SELECT * FROM table_name where username = 'username' and password = 'password'"); grab the users id and set it to a session.

$row['user_id'] = $_SESSION['user_id']

. then use the session in your query to set the date to the correct user.

$k1D = mysql_real_escape_string($_POST['dates']);
$kol = mysql_real_escape_string($_POST['exams']);
mysql_query(" UPDATE studenti SET $kol = '$k1D'
WHERE ID = {$_SESSION['id']}");

just a watered down version of what you can do, any questions let me know

[glava]

OK, so should i put this query $query = mysql_query("SELECT * FROM table_name where username = 'username' and password = 'password'"); and $row['user_id'] = $_SESSION['user_id'] in same document with HTML form for submiting dates ?  or in one where is login check and redirect on form if user is student ?

[fugix]

the first query will go wherever your query is for checking user login information, you will need to change the values that i used to your column_names of course and the user input. Then once you that query is successful, use mysql_fetch_assoc() to grab the users id and set it to a session like I have shown. Then in the query that you assign the date to the user, instead of having WHERE id = 3 you would have

$k1D = mysql_real_escape_string($_POST['dates']);
$kol = mysql_real_escape_string($_POST['exams']);
mysql_query(" UPDATE studenti SET $kol = '$k1D'
WHERE ID = {$_SESSION['id']}");

[glava]

Hi, i tryed few variations of your code but i just can't get it work and i do not understend where the code grab the ID .. ? ok, here is my login.php

 

<?php
session_start();
require_once 'database.php';

    # making variable.
    $username = $_POST['user'];
    # safety
    $escaped_username = mysql_real_escape_string($username);
    
    
    $queryN = mysql_query("select * from user where username = '".$username."' and password = '".$_POST['pass']."' AND
level='1'");# check if user level is 1 (normal) 
    $queryA = mysql_query("select * from user where username = '".$username."' and password = '".$_POST['pass']."' AND
level='9'");# check if user level is 9 (admin)     
      
// redirect if user is level1      
    if(mysql_num_rows($queryN) == 1)
    {
        $resultID = mysql_fetch_assoc($queryN);    // <- this is your part  
        $ID['user_id'] = $_SESSION['user_id'];         //       this also
    
        $resultN = mysql_fetch_assoc($queryN);                    
$_SESSION['user'] = $_POST['user'];    
header("location:indexn.php");      
}
// redirect if user is level 9  

elseif(mysql_num_rows($queryA) == 1)
    {
        $resultA = mysql_fetch_assoc($queryA);                    
$_SESSION['admin'] = $_POST['user'];    
header("location:indexa.php");      
}

else{

... more html with msg wrong username or pass

 

and with this in login.php and with

 

mysql_query(" UPDATE studenti SET $kol = '$k1D'
WHERE ID = {$_SESSION['user_id']}");

 

..in indexn.php it shows no error but it doesn't write nothing into mysql table..

 

please more help ?

[fugix]

make these changes according.

if(mysql_num_rows($queryN) == 1)
    {
        $resultID = mysql_fetch_assoc($queryN);    
        $resultID['user_id'] = $_SESSION['user_id']; //change your $ID to $resultID
                        
$_SESSION['user'] = $_POST['user'];    
header("location:indexn.php");      
}

[glava]

make these changes according.

if(mysql_num_rows($queryN) == 1)
    {
        $resultID = mysql_fetch_assoc($queryN);    
        $resultID['user_id'] = $_SESSION['user_id']; //change your $ID to $resultID
                        
$_SESSION['user'] = $_POST['user'];    
header("location:indexn.php");      
}

 

nope, I've changed and it doesn't work again..

is "user_id" some new variable or it is name of table (user) and name of row (ID) ? I tryed with just "ID" and "user_ID" and also changed name in indexn.php and.. huh 

 

If it is table name then i got to change it because i have 2 tables, one is "users" and another "studenti" i should pull ID from user but write date to "studenti" table. 2 tables are linked with foreign key U_ID but ID's are same.

 

so first user in table user has ID 1 like first student in student table...

 

[fugix]

replace user_id with whatever the name of the column that holds the id's is

[glava]

replace user_id with whatever the name of the column that holds the id's is

 

I did this already, column name is " ID " . now is in login :

if(mysql_num_rows($queryN) == 1)
    {
        $resultID = mysql_fetch_assoc($queryN);
        $resultID['ID'] = $_SESSION['ID'];
    
        $resultN = mysql_fetch_assoc($queryN);                    
$_SESSION['user'] = $_POST['user'];    
header("location:indexn.php");      
}

 

and in index :

 

mysql_query(" UPDATE studenti SET $kol = '$k1D'
WHERE ID = {$_SESSION['ID']}");

 

any other idea ?

[fugix]

from the information that you gave me, that code should work..debug your query, insert

mysql_query(" UPDATE studenti SET $kol = '$k1D'
WHERE ID = {$_SESSION['ID']}") or die(mysql_error());

 

[glava]

yes now i got 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 '' at line 2 "

 

If it is near second line of index then is something with session starting , here is first 4 lines of index :

<?php
session_start();
require_once 'database.php';
?>

 

and if it is second line of mysql code then... ?

[Drummin]

In case you don't have log in and sessions working you can try this.

<?php
require_once 'database.php';
$username = mysqli_real_escape_string($_POST['user']);

$queryA = "SELECT ID,level FROM user WHERE username = '".$username."' AND password = '".$_POST['pass']."'";   $resultA = mysql_query($queryA);
   $query_dataA = mysql_fetch_row($resultA);

      IF (!$query_dataA[0]) {
  $error = "You have submited an incorrect login and password combination.  Please try again...."; 
      }
   ELSE {
      session_start();

      $_SESSION['user_id']=$query_dataA[0];            
      $_SESSION['level']=$query_dataA[1];
IF ($_SESSION['level']==1){    
header("location:indexn.php");      
} 
IF ($_SESSION['level']==9){    
header("location:indexa.php");       
}           
}
//Assuming this is part of the login page echo error within content
IF (isset($error)) { echo "<p><b>$error</b>"; }

  You can then pick up the user ID at the top of any page which you can use for query's etc.

$userID=$_SESSION['user_id']; 

This might at least help sort out the user id issue.  Not sure if the space around your equals sign might have been giving you problems as well so I tightened them up below.

mysql_query(" UPDATE studenti SET $kol='$k1D' WHERE ID=$userID");

[Pikachu2000]

yes now i got 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 '' at line 2 "

 

If it is near second line of index then is something with session starting , here is first 4 lines of index :

<?php
session_start();
require_once 'database.php';
?>

 

and if it is second line of mysql code then... ?

 

That error is an error from MySQL; it references the line of the database query string, not the script. Separate your query strings from the query executions so you can echo it along with any errors returned. Being able to see the actual query string that was executed is essential to debugging without wasting all day trying to guess what's wrong.

 

$query = "SELECT whatever FROM table WHERE somefield = 'somevalue'";
$result = mysql_query($query) or die( "<br>Query string: $query<br>Produced error: " . mysql_error() . '<br>In: ' . __FILE__ . '<br>on line: ' . __LINE__ . '<br>');

[fugix]

glava, if you can't figure out whats wrong with your query, post the actual query so we can help

[glava]

error was because stupid single quotes around {$_SESSION['ID']} .. if i single quote it like

 

 
mysql_query("UPDATE studenti SET $kol = '$k1D' 
WHERE ID = '{$_SESSION['ID']}'") or die(mysql_error());

 

I have no more error but I also have no result with writing date in DB table 

 

[fugix]

If your session is truly an integer, you do not need to wtapit in single quotes in your query. Have you tried echoing the session before use in your query, to make sure it is the value that you want