Mysql Help please..

Phpwho · March 9, 2007

I am trying to learn php and how to write queries for it. I have the following that is driving me nuts:

die('Could not connect: ' . mysql_error());

}

mysql_select_db("masterdata",$con);

$result = mysql_query("SELECT * FROM employees

WHERE Personal_ID='$_POST[Personal_ID]'");

while($row = mysql_fetch_array($result))

{

mysql_select_db("clocked",$con);

$sql="INSERT INTO clocked(Personal_ID, First_Name, Last_Name)

VALUES('$_POST[Personal_ID]','$row[First_Name]','$row[Last_Name]')";

if (!mysql_query($sql,$con))

echo $row['First_Name'] . " " . $row['Last_Name'] . "<br/>";

echo $row['Division'];

echo "<br />";

{

die('Error: ' . mysql_error());

}

echo "1 record added";

}

;mysql_close($con)

?>

I need this to look up a record using the Personal_ID from one table that contains all the information about the person and than, insert the Name and department with the personal ID number into a database called Clockers and a table called Clocked.

Can someone show me what I am doing wrong?

Rick

bwcc · March 9, 2007

To insert data from one table to another, use the INSERT INTO T-SQL statement.

Basic form is : INSERT INTO TABLE2 SELECT * FROM TABLE1

You can specify columns from each table and select specific records (WHERE):

INSERT INTO TABLE2 (col1, col2, col3) SELECT col6, col5, col4 FROM TABLE1 WHERE col6='abcdef'

To insert data into different databases, use fully qualified linked server names :

<linked_server>.<database>.<owner>.<table_name>

Here, since your databases are on the same server, and probably have the same owner, you can use just <database>.<table_name>

i.e. -

INSERT INTO DATABASE1.TABLE2 (col1, col2, col3) SELECT col6, col5, col4 FROM DATABASE2.TABLE1 WHERE col6='abcdef'

The code below should work for what you're looking to do:

define ('DB_USER', 'setusername');
define ('DB_PASSWORD', 'setpassword');
define ('DB_HOST', 'yourservername');
$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) or die (mysql_error());

mysql_select_db ('databasename');
// assuming that $_POST['Personal_ID'] is set
$result = mysql_query("INSERT INTO clockers.clocked (Personal_ID, First_Name, Last_Name) 
SELECT Personal_ID, First_Name, Last_Name FROM databasename.employees WHERE employees.Personal_ID='{$_POST['Personal_ID']}'");
if (mysql_query($result)) {    // if ran successfully
  echo $row['First_Name'] . " " . $row['Last_Name'] . "";
  echo $row['Division'];
  echo "1 record added";
}else{ // if did not run successfully
  die('Error: '.mysql_error().'');
}

Phpwho · March 9, 2007

Thanks that worked but.. I had to modify the code a bit:

<?php

$con = mysql_connect("serverhere","passwordhere","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db ('clocked');

$result = mysql_query("INSERT INTO clockers.clocked (Personal_ID, First_Name, Last_Name)

SELECT Personal_ID, First_Name, Last_Name FROM masterdata.employees WHERE Personal_ID='{$_POST['Personal_ID']}'");

if (mysql_query($result)) { // if ran successfully

echo $row['First_Name'] . " " . $row['Last_Name'] . "";

echo $row['Division'];

echo "1 record added";

}else{ // if did not run successfully

die('Error: '.mysql_error().'');

}

;mysql_close($con)

?>

but now I get a "Error: You have an error in your SQL syntax near '1' at line 1"

This is the complete code:

^<html>

^{<title>Clock In</title>}

^<body>

^{<form action="clock.php" method="post"><br/>}

^{Employee Number:<br/> <input type="text" name="Personal_ID" />}

^{<input type="hidden" value="Clocked In" name="ClockType">}

^{<input type="hidden" name="First_Name">}

^{<input type="submit" value="Clock In" />}

^</form>

^<?php

^{$con = mysql_connect("server","password","");}

^{if (!$con)}

^{

^{die('Could not connect: ' . mysql_error());}

^}

^{mysql_select_db ('clocked');}

^{$result = mysql_query("INSERT INTO clockers.clocked (Personal_ID, First_Name, Last_Name)}

^{SELECT Personal_ID, First_Name, Last_Name FROM masterdata.employees WHERE Personal_ID='{$_POST['Personal_ID']}'");}

^{if (mysql_query($result)) { // if ran successfully}

^{echo $row['First_Name'] . " " . $row['Last_Name'] . "";}

^{echo $row['Division'];}

^{echo "1 record added";}

^{}else{ // if did not run successfully}

^{die('Error: '.mysql_error().'');}

^}

^{;mysql_close($con)}

^?>

^</body>

^</html>

BUT... it works... however.. it wont show the info after you clock in like it did before.. it would show your name and department below the clock in.. where the error now appears...

HOWEVER... it still writes to the database correctly.. BUT... if you refresh the page... it will log the last person who clocked in.. again to the database. How can I prevent that? Put a javascript valdation in?

Thanks for all your help!!

Richard

bwcc · March 12, 2007

Your query is slightly off. From the if statement, your code is:

$result = mysql_query("INSERT INTO clockers.clocked (Personal_ID, First_Name, Last_Name) 
   SELECT Personal_ID, First_Name, Last_Name FROM masterdata.employees WHERE Personal_ID='{$_POST['Personal_ID']}'");
if (mysql_query($result))

to the server, it reads :

if (mysql_query(mysql_query("INSERT INTO clockers.clocked (Personal_ID, First_Name, Last_Name) 
   SELECT Personal_ID, First_Name, Last_Name FROM masterdata.employees WHERE Personal_ID='{$_POST['Personal_ID']}'")))

so, change your query line to:

$result = "INSERT INTO clockers.clocked (Personal_ID, First_Name, Last_Name) 
   SELECT Personal_ID, First_Name, Last_Name FROM masterdata.employees WHERE Personal_ID='{$_POST['Personal_ID']}'";

This will give you the "1 record added", but not the $row array, because it is not defined. You need to define the array before calling it.

For the script to either echo the form or the results, you'll need to add another if statement - depending on whether the script was executed or not. The following if statement will echo the results of the form submission only if the form is submitted. Otherwise, it will echo the form:

<html>
<head>
<title>Clock In</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?php
if (isset($_POST['submit'])) { // if form is submitted

<!--- Your code here --->

}else{
?>
<form action="" method="post">

<!---  Your form here --->

</form>
<?php
} // end if form is submitted
?>

Sign In

Mysql Help please..

Recommended Posts

Phpwho

Link to comment

Share on other sites

bwcc

Link to comment

Share on other sites

Phpwho

Link to comment

Share on other sites

bwcc

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information