11 Replies - 349 Views - Last Post: 16 January 2013 - 01:53 AM Rate Topic: -----

#1 kessycruz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-November 11

insert & update in two differnt table

Posted 15 January 2013 - 02:09 AM

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.


Is This A Good Question/Topic? 0
  • +

Replies To: insert & update in two differnt table

#2 kessycruz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-November 11

Re: insert & update in two differnt table

Posted 15 January 2013 - 02:19 AM

View Postraghav.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
Was This Post Helpful? 0
  • +
  • -

#3 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1002
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: insert & update in two differnt table

Posted 15 January 2013 - 02:22 AM

View Postkessycruz, 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.
Was This Post Helpful? 0
  • +
  • -

#4 kessycruz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-November 11

Re: insert & update in two differnt table

Posted 15 January 2013 - 02:36 AM

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

Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3485
  • View blog
  • Posts: 10,052
  • Joined: 08-June 10

Re: insert & update in two differnt table

Posted 15 January 2013 - 02:46 AM

I hazard a guess at missing spaces.
Was This Post Helpful? 1
  • +
  • -

#6 ZevhEr  Icon User is offline

  • New D.I.C Head

Reputation: 9
  • View blog
  • Posts: 38
  • Joined: 18-July 09

Re: insert & update in two differnt table

Posted 15 January 2013 - 02:47 AM

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']."'";


Was This Post Helpful? 0
  • +
  • -

#7 kessycruz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-November 11

Re: insert & update in two differnt table

Posted 15 January 2013 - 02:57 AM

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!

Was This Post Helpful? 0
  • +
  • -

#8 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3485
  • View blog
  • Posts: 10,052
  • Joined: 08-June 10

Re: insert & update in two differnt table

Posted 15 January 2013 - 05:51 AM

View PostZevhEr, 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

Was This Post Helpful? 1
  • +
  • -

#9 kessycruz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-November 11

Re: insert & update in two differnt table

Posted 15 January 2013 - 10:18 AM

View PostDormilich, on 15 January 2013 - 05:51 AM, said:

View PostZevhEr, 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

Was This Post Helpful? 0
  • +
  • -

#10 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3485
  • View blog
  • Posts: 10,052
  • Joined: 08-June 10

Re: insert & update in two differnt table

Posted 15 January 2013 - 10:57 AM

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

Was This Post Helpful? 0
  • +
  • -

#11 kessycruz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-November 11

Re: insert & update in two differnt table

Posted 16 January 2013 - 01:43 AM

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
Was This Post Helpful? 0
  • +
  • -

#12 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3485
  • View blog
  • Posts: 10,052
  • Joined: 08-June 10

Re: insert & update in two differnt table

Posted 16 January 2013 - 01:53 AM

View Postkessycruz, on 16 January 2013 - 09:43 AM, said:

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

as a first idea, Id 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 its as simple as counting, i.e. COUNT(students) FROM table WHERE room = ?.
what the best way is to mark a room as occupied, Id recommend to ask in the DB forum, theyre the experts for that.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1