[kessycruz]

Please, I'm have problem with this. I'm trying to insert into a table allocate and update a table rooms. So that every room occupied will not show on the dropdown menu. Thanks

if (isset($_POST['submit']))
{
	$queryA =  "INSERT INTO allocate(surname, firstname, regno, gender, dept, year, hostel, rooms) VALUES('".$_SESSION['surname']."','".$_SESSION['firstname']."','".$_SESSION['regno']."','".$_SESSION['gender']."', '".$_SESSION['dept']."', '".$_SESSION['year']."', '".$_SESSION['hostel']."', '".$_POST['rooms']."') ";
	/*"SELECT * FROM rooms".
	"WHERE  name ='".$_POST['rooms']."'";*/
	
	$resultA = mysql_query($queryA) or die(mysql_error());
	
	if ($resultA)
	{
		$occupied = 'occupied';
		$queryA1 ="UPDATE rooms".
			"SET status = '".$occupied."'" .
			"WHERE name = '".$_POST['rooms']."'";
		
		$resultA1 = mysql_query($queryA1) or die (mysql_error());
	}
}

This post has been edited by RudiVisser: 15 January 2013 - 02:20 AM
Reason for edit:: Code formatting.

[kessycruz]

raghav.naganathan, on 15 January 2013 - 02:13 AM, said:

Is there a question that you would like to ask?

regards,
Raghav

The update part of d code is showing error, please help 2 look @ it & see what's d problem

[RudiVisser]

kessycruz, on 15 January 2013 - 09:19 AM, said:

The update part of d code is showing error, please help 2 look @ it & see what's d problem

We can only help you if you tell us what the error is.

[kessycruz]

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 'occupied'WHERE name ='2' at line 1.

I'm using phpmyAdmin-2.9.2.

This post has been edited by RudiVisser: 15 January 2013 - 02:56 AM
Reason for edit:: Please stop quoting yourself

[Dormilich]

I hazard a guess at missing spaces.

[ZevhEr]

Your SQL is wrong.

instead of

$queryA1 ="UPDATE rooms".
	  "SET status = '".$occupied."'" .
	  "WHERE name = '".$_POST['rooms']."'";

Add Space when joining the string.
try this :

$queryA1 ="UPDATE rooms ".
	  "SET status = '".$occupied."' " .
	  "WHERE name = '".$_POST['rooms']."'";

[kessycruz]

It worked! Thanks. I luv u all

This post has been edited by RudiVisser: 15 January 2013 - 02:58 AM
Reason for edit:: Stop quoting!

[Dormilich]

ZevhEr, on 15 January 2013 - 10:47 AM, said:

Add Space when joining the string.

though I’d ask myself, why using the separated strings at all? templating is much easier.

$sql = "UPDATE rooms SET status = '%s' WHERE name = '%s'";
$query = sprintf($sql, $occupied, $_POST['rooms']);

though that still leaves you open to SQL Injection. why not using Prepared Statements, similar to templates, but immune to SQl Injections

// simplified for the sake of explanation
$db->prepare("UPDATE rooms SET status = ? WHERE name = ?");
// the data types passed depend on the DB table setting
$db->bind(1, $occupied, $db_data_type_1);
$db->bind(2, $_POST['rooms'], $db_data_type_2);

This post has been edited by Dormilich: 16 January 2013 - 11:37 PM
Reason for edit:: typo

[kessycruz]

Dormilich, on 15 January 2013 - 05:51 AM, said:

ZevhEr, on 15 January 2013 - 10:47 AM, said:

Add Space when joining the string.

Thanks but i don't understand $db_data_type_1 and $db_data-type_2. Please can you throw more light on it.

This post has been edited by Dormilich: 15 January 2013 - 10:57 AM

[Dormilich]

Prepared Statements require that you pass along the data type of the data according to what is defined in the DB setup.

ex.

-- DB
CREATE TABLE test
(
    age INTEGER,
    name VARCHAR(32)
);

// using PDO as example
// connect
$pdo = new PDO(/* DSN and options here */);
// prepare
$qry = $pdo->prepare("INSERT INTO test (age, name) VALUES (?, ?);");
// bind
$qry->bindValue(1, $age, PDO::PARAM_INT);
$qry->bindValue(2, $name, PDO::PARAM_STR);
// finish
$qry->execute();

This post has been edited by Dormilich: 15 January 2013 - 11:04 AM

[kessycruz]

Thanks a million!
In my previous, a student is to a room but i want a situation were 4 students can be in a room. I added a column studInroom(to display the number of student in a room) to my allocate table(advise if wrong). So wen its 4 its updates d rooms table to 'occupied'. Please i need a query that can help me count d number of student.Thanks

[Dormilich]

kessycruz, on 16 January 2013 - 09:43 AM, said:

So wen its 4 its updates d rooms table to 'occupied'.

as a first idea, I’d use a Trigger to determine the numer of students and then take appropriate action (of course there is a multitude of possibilities to choose from).

given that you have a link table between rooms and students it’s as simple as counting, i.e. COUNT(students) FROM table WHERE room = ?.
what the best way is to mark a room as occupied, I’d recommend to ask in the DB forum, they’re the experts for that.