Phpwho Posted March 9, 2007 Share Posted March 9, 2007 God, I know this is so simple and yet I have spent all day to get where I am... !!! ERRRR! So, I have this script pulling info from one table and inserting it depending on the Personal_ID that is entered in the form... Well I am having a few problems.. one.. I get "Error:You have an error in your SQL syntax near '1' at line 1" and it wont display the rows that it should. and... if I refresh the page, it will enter the last personal_ID I typed in the box again in the database... AS WELL I need the time and date when they clock in but my code is sooo not working.... Here is the whole code: <html> <head> <title>Clock In</title> </head> <body> <script language="JavaScript1.2"> function checknumber(){ var x=document.checknum.Personal_ID.value var anum=/(^\d+$)|(^\d+\.\d+$)/ if (anum.test(x)) testresult=true else{ alert("You must use a valid number. Do not enter your name!") testresult=false } return (testresult) } </script> <script> function checkban(){ if (document.layers||document.all||document.getElementById) return checknumber() else return true } </script> <form action="clock.php" method="post" name=checknum onSubmit="return checkban()"><br/> Employee Number:<br/> <input type="text" name="Personal_ID" /> <input type="hidden" value="<?php $b = time (); print date("m/d/y",$b); ?> " name="Date"> <input type="hidden" value="<?php $b = time (); print date("g:i A",$b); ?> " name="ClockTime"> <input type="submit" name="ClockType "value="Clock In"> </form> <?php $con = mysql_connect("cefdata","root",""); 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, Division)SELECT Personal_ID, First_Name, Last_Name, Division FROM masterdata.employees WHERE Personal_ID='{$_POST['Personal_ID']}'"); if (mysql_query($result)) { echo $row['First_Name'] . " " . $row['Last_Name'] . ""; echo "1 record added"; } else{ die('Error:' .mysql_error().''); } ;mysql_close($con) ?> </body> </html> Can someone help me fix this? Richard Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/ Share on other sites More sharing options...
obsidian Posted March 9, 2007 Share Posted March 9, 2007 What are you trying to do with this query: INSERT INTO clockers.clocked (Personal_ID, First_Name, Last_Name, Division)SELECT Personal_ID, First_Name, Last_Name, Division FROM masterdata.employees WHERE Personal_ID='{$_POST['Personal_ID']} ??? That's not valid at all Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/#findComment-203876 Share on other sites More sharing options...
Glyde Posted March 9, 2007 Share Posted March 9, 2007 The syntax error is from the semicolon before the mysql_close function. It should be AFTER the function call. In addition, you need to fix the SQL as stated above. Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/#findComment-203958 Share on other sites More sharing options...
Phpwho Posted March 11, 2007 Author Share Posted March 11, 2007 What I am trying to do is: Using the form, I am supplied with a Personal_ID that will look up data from a master table and than copy the information over to a new table. It works other than the date and time. Thanks for showing me that stray semicolon..I am new to programming with php so I am sure my query is funny to look at lol.. Anyone think they can help me straighten out my query? Thanks Richard. Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/#findComment-204827 Share on other sites More sharing options...
Orio Posted March 11, 2007 Share Posted March 11, 2007 You need to divide it into two different queries. Also, you need to use mysql_fetch_array() to get the results (or mysql_result() or mysql_fetch_assoc()). Here's how I think it should be: <?php $con = mysql_connect("cefdata","root",""); if (!$con) die('Could not connect: ' . mysql_error()); mysql_select_db ('clocked'); $q1 = "INSERT INTO clockers.clocked (Personal_ID, First_Name, Last_Name, Division)"; $result1 = mysql_query($q1) or die("Error inserting: ".mysql_error()); if(mysql_affected_rows()) echo "1 record added<br>"; $q2 = "SELECT Personal_ID, First_Name, Last_Name, Division FROM masterdata.employees WHERE Personal_ID='{$_POST['Personal_ID']}'"; $result2 = mysql_query($q2) or die("Error selecting: ".mysql_error()); if (mysql_num_rows()) { $row = mysql_fetch_array($result2); echo $row['First_Name'] . " " . $row['Last_Name'] . ""; } mysql_close($con); ?> Orio. Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/#findComment-204835 Share on other sites More sharing options...
Phpwho Posted March 11, 2007 Author Share Posted March 11, 2007 Awesome, I can not try it out yet, as I am not at work.. I will first thing monday morning.. how about obtaining the date and time? How can I get that and have it insert into Clockers.clocked? Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/#findComment-205091 Share on other sites More sharing options...
redarrow Posted March 11, 2007 Share Posted March 11, 2007 use now() Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/#findComment-205095 Share on other sites More sharing options...
Phpwho Posted March 12, 2007 Author Share Posted March 12, 2007 Nub question but how would I include now() to be inserted into the database? Using the code above. Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/#findComment-205148 Share on other sites More sharing options...
JasonLewis Posted March 12, 2007 Share Posted March 12, 2007 you'd need another field then on insert just make that field now(). if that makes sense. Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/#findComment-205204 Share on other sites More sharing options...
Phpwho Posted March 12, 2007 Author Share Posted March 12, 2007 The below code gives me the following error: Error inserting: You have an error in your SQL syntax near '' at line 1 and no longer works at all. Richard <?php $con = mysql_connect("cefdata","root",""); if (!$con) die('Could not connect: ' . mysql_error()); mysql_select_db ('clocked'); $q1 = "INSERT INTO clockers.clocked (Personal_ID, First_Name, Last_Name, Division)"; $result1 = mysql_query($q1) or die("Error inserting: ".mysql_error()); if(mysql_affected_rows()) echo "1 record added<br>"; $q2 = "SELECT Personal_ID, First_Name, Last_Name, Division FROM masterdata.employees WHERE Personal_ID='{$_POST['Personal_ID']}'"; $result2 = mysql_query($q2) or die("Error selecting: ".mysql_error()); if (mysql_num_rows()) { $row = mysql_fetch_array($result2); echo $row['First_Name'] . " " . $row['Last_Name'] . ""; } mysql_close($con); ?> Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/#findComment-205401 Share on other sites More sharing options...
trq Posted March 12, 2007 Share Posted March 12, 2007 Your completely missing the VALUES clause of your insert. eg; INSERT INTO tbl (fld) VALUES ('value'); Link to comment https://forums.phpfreaks.com/topic/42039-going-mad-syntax-error/#findComment-205407 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.