insert data into two separate table / 2 queries in same php file

[scias23]

i can't get this from working. i want to execute these two queries ebcause i want to insert data to two separate table.

is it possible to do this with one query?

this is what i've got so far:

$sql = "INSERT INTO login SET 
		studNo = $studNo,
		userName = $user,
		password = $pass,
		dateRegistered = NOW()";
		
	$sql2 = "INSERT INTO users SET 
		studNo = $studNo,
		lastName = $lName,
		firstName = $fName,
		gender = $gender,
		bMonth = $month,
		bDay = $day,
		bYear = $year,
		address = $address,
		cellNo = $phone,
		email = $email";
		
	if(mysql_num_rows(@mysql_query("SELECT * FROM classlist WHERE num = '$studNo'"))) {
		if(mysql_num_rows(@mysql_query("SELECT studNo FROM login WHERE studNo = '$studNo'"))) {
			$flag = true;
			$headMsg = "Registration Error";
			$msg = "You have already registered.";
		}
		else{
			@mysql_query($sql);
			@mysql_query($sq2);
		}
	}
	else {
		$flag = true;
		$headMsg = "Registration Unsuccessful";
		$msg = "Your student number doesn't exist in the database.";
	}

[diafol]

I don't understand the purpose of your 'login' table. If it is to keep a login log - just use the id from the users table and a datetime. You'll need to make 2 sql queries on registering. Using mysql_insert_id() to get the id of the newly added user to the users table.

[digital-ether]

i can't get this from working. i want to execute these two queries ebcause i want to insert data to two separate table.

is it possible to do this with one query?

this is what i've got so far:

$sql = "INSERT INTO login SET 
		studNo = $studNo,
		userName = $user,
		password = $pass,
		dateRegistered = NOW()";
		
	$sql2 = "INSERT INTO users SET 
		studNo = $studNo,
		lastName = $lName,
		firstName = $fName,
		gender = $gender,
		bMonth = $month,
		bDay = $day,
		bYear = $year,
		address = $address,
		cellNo = $phone,
		email = $email";
		
	if(mysql_num_rows(@mysql_query("SELECT * FROM classlist WHERE num = '$studNo'"))) {
		if(mysql_num_rows(@mysql_query("SELECT studNo FROM login WHERE studNo = '$studNo'"))) {
			$flag = true;
			$headMsg = "Registration Error";
			$msg = "You have already registered.";
		}
		else{
			@mysql_query($sql);
			@mysql_query($sq2);
		}
	}
	else {
		$flag = true;
		$headMsg = "Registration Unsuccessful";
		$msg = "Your student number doesn't exist in the database.";
	}

You can only insert into one table per SQL query. http://dev.mysql.com/doc/refman/5.1/en/insert.html

However, there is nothing preventing you from doing two MySQL queries on the same PHP script.

It is hardly ever a good practice to force error suppression on function calls. ie: @mysql_query($sql);

If you remove the @ signs you'll see the errors that is preventing the SQL query from executing.

A better alternative is to set the error reporting level.

eg:

ini_set('display_errors', '1');
ini_set('error_reporting', E_ALL);

http://www.php.net/manual/en/function.error-reporting.php

or using:

set_error_handler()

http://www.php.net/manual/en/function.set-error-handler.php

When in development, you can choose to display errors. However, when in production mode, you can hide errors.

ini_set('display_errors', '0');
ini_set('error_reporting', E_ALL ^ E_NOTICE);
ini_set('log_errors', '1');

This hides errors in the PHP script output, but they are still logged to the PHP error log.

[digital-ether]

Here is a good reference to error handling in PHP: http://www.addedbytes.com/php/php-ini-guide-error-handling-and-logging/

[scias23]

You can only insert into one table per SQL query.

i have two separate insert query:

@mysql_query($sql);
@mysql_query($sql2);

but the data don't appear on the tables. why?

[digital-ether]

If you remove the @ signs you'll see the errors that is preventing the SQL query from executing.

You won't know what is wrong, unless you view the errors.

[diafol]

Ditto DE. However, I'm really confused as to why you've got all those loops just for inserting/checking for registration.

Have you confused table 'users' and 'classlist' in your code or are they separate entities? Is 'login' your 'registered users' table?
Could you supply info w.r.t the purpose of the tables. So far I see 3 tables:

users, classlist and login.

Your first post doesn't make it clear why you should have a separate 'login' and 'users' tables. Although, it may just be me being thick.

[glycerine]

Try this

$sql = "INSERT INTO login (studNo, userName, password, dateRegistered) VALUES ($studNo, $user, $pass , NOW())";
		
	$sql2 = "INSERT INTO users (studNo, lastName, firstName, gender, bMonth, bDay, bYear, address, cellNo, email) VALUES ($studNo, $lName, $fName, $gender, $month, $day, $year, $address, $phone, $email)";
		
	if(mysql_num_rows(@mysql_query("SELECT * FROM classlist WHERE num = '$studNo'"))) {
		if(mysql_num_rows(@mysql_query("SELECT studNo FROM login WHERE studNo = '$studNo'"))) {
			$flag = true;
			$headMsg = "Registration Error";
			$msg = "You have already registered.";
		}
		else{
			@mysql_query($sql);
			@mysql_query($sq2);
		}
	}
	else {
		$flag = true;
		$headMsg = "Registration Unsuccessful";
		$msg = "Your student number doesn't exist in the database.";
	}

[liamfriel]

Remove error suppression (the @ in @mysql), then you might have more reason as to why the information is not going in.

Also you must make sure that the information going in the database it the correct data type i.e. only inserting numbers into INT field

[liamfriel]

Remove error suppression (the @ in @mysql), then you might have more reason as to why the information is not going in.

Also you must make sure that the information going in the database it the correct data type i.e. only inserting numbers into INT field

[liamfriel]

Remove error suppression (the @ in @mysql), then you might have more reasons as to why the information is not going in.

Also you must make sure that the information going in the database it the correct data type i.e. only inserting numbers into INT field