Mysql Problem

[creacon]

So far the actual login logic all works correctly (I think!?). It stores the CustID in a session variable and the second page picks it up to display as a "Welcome" line at the top. The second page is where the job applicant's personal information is entered, then passed via more session variables to the third page which is the actual questionnaire. When the questions on the third page are answered and the submit button pressed, the php script tries to; 1) see if there's an existing record for this customer/applicant and if so update it, or 2) if no existing record, insert one. No matter how I set up the second form to access the database table, I get errors. Here's the php script:

   <?php	session_start();	// Fetch Origination date from session variable	// and add to welcome banner	$custident = $_SESSION['MM_CustID'];	$origndate = $_SESSION['OrignDate'];	$custid	   = "Welcome ".$custident." ".$origndate;	// fetch the personal info array from previous page	// and set up a heading line for the applicant's name	$arrappl  = $_SESSION['sesarrappl'];	$applname = $arrappl[fullname].", ".substr($arrappl[ssn],7);	$ssn	  = $arrappl[ssn];		if (isset($_POST['submit'])){		// fetch the questionnaire fields		$radiogp1  = $_POST['RadioGroup1'];		$radiogp2  = $_POST['RadioGroup2'];		$radiogp3  = $_POST['RadioGroup3'];		$FdStmRec  = $_POST['FdStmpRec'];		$FdStmLoc  = $_POST['FdStmpLoc'];		$radiogp4  = $_POST['RadioGroup4'];		$radiogp5  = $_POST['RadioGroup5'];		$radiogp6  = $_POST['RadioGroup6'];		$radiogp7  = $_POST['RadioGroup7'];		$radiogp8  = $_POST['RadioGroup8'];		$FdStRec2  = $_POST['FdStmpRec2'];		$FdstLoc2  = $_POST['FdStmpLoc2'];		$radiogp9  = $_POST['RadioGroup9'];		$radiogp10 = $_POST['RadioGroup10'];		$radiogp11 = $_POST['RadioGroup11'];		$radiogp12 = $_POST['RadioGroup12'];		$radiogp13 = $_POST['RadioGroup13'];		$radiogp14 = $_POST['RadioGroup14'];		$radiogp15 = $_POST['RadioGroup15'];		$TANFRec   = $_POST['TANFRec'];		$TANFLoc   = $_POST['TANFCtySt'];		$radiogp16 = $_POST['RadioGroup16'];		$FlnyCnvDt = $_POST['FlnyCnvDt'];		$FlnyRelDt = $_POST['FlnyRelDt'];		$radiogp17 = $_POST['RadioGroup17'];		$RRCNm	   = $_POST['RRCNm'];		$radiogp18 = $_POST['RadioGroup18'];				// This is where the questionnaire data		// w/get saved in a MySQL table		$con = mysql_connect("localhost","root","raisin4312");		if (!$con)		{			die('Could not connect: ' . mysql_error());		}				$db_selected = mysql_select_db("infoondemand");				$appl__query=sprintf("SELECT * FROM Customer WHERE CustID=$custident AND SSN=$ssn");    						$applRS		   = mysql_query($appl__query, $con) or die(mysql_error());		$applFoundUser = mysql_num_rows($applRS);	// see if a record already exists				echo "<br /> HEY there are ".$applFoundUser." rows found";		if ($applFoundUser)							// If one does, then update it		{			$applrec   = mysql_fetch_assoc($applRS); // fetch the existing record			mysql_query("UPDATE applicant						SET Q1YN = $radiogp1, Q2YN = $radiogp2, Q2aYN = $radiogp3,						    Q2aName = $FdStmRec, Q2aLocn = $FdStmLoc, Q2b1YN = $radiogp4,							Q2b2YN = $radiogp5, Q2cYN = $radiogp6, Q31YN = $radiogp7,							Q32YN = $radiogp8, Q3Name = $FdStRec2, Q3Loc = $FdstLoc2,							Q41YN = $radiogp9, Q42YN = $radiogp10, Q43YN = $radiogp11,							Q51YN = $radiogp12, Q52YN = $radiogp13, Q5aYN = $radiogp14,							Q5bYN = $radiogp15, Q5Name = $TANFRec, Q5Locn = $TANFLoc,							Q6YN = $radiogp16,Q6Cnvct = $FlnyCnvDt, Q6Rels = $FlnyRelDt,							Q7YN = $radiogp17, Q7RRC = $RRCNm, Q8YN = $radiogp18,							OriginationDate = $origndate, ActionCode = ' ', ActionDate = ''						WHERE CustID = $custident AND SSN = $ssn)");		}		else										// No record exists so add it		{			mysql_query("INSERT INTO applicant						VALUES ($custIdent, $ssn, $arrappl[lastname], $arrappl[fstname],								$arrappl[midinit], $arrappl[dlname], $arrappl[staddr],								$arrappl[staddr2], $arrappl[city], $arrappl[state],								$arrappl[phone], arrappl[dob], $arrappl[race], $arrappl[gender],								$origndate, ' ', '', 								$radiogp1, $radiogp2, $radiogp3, $FdStmRec, $FdStmLoc, $radiogp4,								$radiogp5, $radiogp6, $radiogp7, $radiogp8, $FdStRec2, $FdstLoc2,								$radiogp9, $radiogp10, $radiogp11, $radiogp12, $radiogp13,								Raddiogp14, $radiogp15, $TANFRec, $TANFLoc, $radiogp16, 								$FlnyCnvDt, $FlnyRelDt, $radiogp17, $RRCNm, $radiogp18)");		}					// And this is where the Company ID,		// Applicant's name and SSN/last 4 		// w/get emailed to Info				echo '<META http-equiv="refresh" content="0;URL=WOTCLogoff.php">';	//		header("Location: WOTCLogoff.php");}?> 

Here's where the trouble begins. When I execute the page as is shown above, I get the following error:Warning: session_start()[function.session-start': Cannot send session cache limiter - headers already sent (output started at C:\xampp\htdocs\IOD\WOTCPg2Test.php:1) in C:\xampp\htdocs\IOD\WOTCPg2Test.php on line 3You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near"at line 1.I think the first part is a result of a debugging "echo" line right after the "mysql_num_rows" code line; I seem to get that message any time I use an echo for debugging. The second part, I just don't understand. I used the code exactly as shown in the w3schools examples. Also, the message is fuzzy about just which part of the code is wrong?!?!?What also confuses me is that in the connection script on the login form, the connection is "mysql_pconnect", which, as I understand the w3schools information, is supposed to continue persistently. If, however, I remove the connection code in the third page, I get this errors:

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at C:\xampp\htdocs\IOD\WOTCPg2Test.php:1) in C:\xampp\htdocs\IOD\WOTCPg2Test.php on line 3Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\xampp\htdocs\IOD\WOTCPg2Test.php on line 57Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in C:\xampp\htdocs\IOD\WOTCPg2Test.php on line 57Warning: mysql_query() expects parameter 2 to be resource, null given in C:\xampp\htdocs\IOD\WOTCPg2Test.php on line 61Access denied for user 'ODBC'@'localhost' (using password: NO)

This is really gettin frustrating. Can someone PLEASE help me. This is driving me sane.

[justsomeguy]

Here's where the trouble begins. When I execute the page as is shown above, I get the following error:

Check this thread for a description about the header error, it's because you're using echo to send output.http://w3schools.invisionzone.com/index.php?showtopic=12509

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"at line 1.

Figure out which query it's complaining about and print the SQL code out to check it, there's an error in the query somewhere (yeah, sometimes errors from MySQL aren't all that clear). If it's this query:sprintf("SELECT * FROM Customer WHERE CustID=$custident AND SSN=$ssn");You may need single quotes around the values if they're strings. If the SSN column is varchar, you'll need quotes around the value. It's good to print the query out to make sure all of the values got replaced correctly.

What also confuses me is that in the connection script on the login form, the connection is "mysql_pconnect", which, as I understand the w3schools information, is supposed to continue persistently.

Using a persistent connection doesn't mean that you don't need to connect on every page, you still need to connect. A persistent connection differs from a normal connection because the server will keep the connection open after your code ends. If you try to create another persistent connection on another page, first it will look to see if there is already a connection open and use that if so, or else it will create a new connection. All of that is done behind the scenes, you still need to use the connect function on each page.

[creacon]

OK I'm gainin' on it; somewhat. I dunn whut you told me; sort of. I cut/pasted all that generated code from the loging page to the third page, so now the connection gets opened and the query works. I'm still getting errors, however. Here's a printout (from a print_r in the third page - the one giving the errors) showing the arrad data that is passed from the second page (i.e. the personal information):

Array ( [fullname] => dudley doright [lastname] => doright [fstname] => dudley [midinit] => d [dlname] => doright, dudley d. [dob] => 12/31/1966 [ssn] => 111-22-6666 [race] => caucasian [gender] => m [staddr] => 100 dogooder dr penthouse [ctstzp] => left overshoe, Wi 66666 [city] => left overshoe [state] => Wi [zip] => 66666 [phone] => (888) 666-2222 [recname] => tillie t. toiler [rectitl] => chief heiffer [recphn] => (800) 555-1212 [recphnext] => 000 [recaddr] => 555 pickyune pl [recste] => outhouse [reccity] => snootyville [recstate] => We [reczip] => 66777 [recemail] => me@you.com ) 

And here's the (revised) MySql code that's giving me the headache:

			$insert = ("INSERT INTO applicant						VALUES ('$custIdent', '$ssn', '$arrappl[lastname]', '$arrappl[fstname]',								'$arrappl[midinit]', '$arrappl[dlname]', '$arrappl[staddr]',								'$arrappl[staddr2]', '$arrappl[city]', '$arrappl[state]',								'$arrappl[zip]', $arrappl[phone]', 'arrappl[dob]',								'$arrappl[race]', '$arrappl[gender]','$origndate', 'N',								'$orgdt', 								'$radiogp1', '$radiogp2', '$radiogp3', '$FdStmRec', '$FdStmLoc',								'$radiogp4', '$radiogp5', '$radiogp6', '$radiogp7', '$radiogp8',								'$FdStRec2', '$FdstLoc2', '$radiogp9', '$radiogp10',								'$radiogp11', '$radiogp12', '$radiogp13',								'$radiogp14', '$radiogp15', '$TANFRec', '$TANFLoc', '$radiogp16',								'$FlnyCnvDt', '$FlnyRelDt', '$radiogp17', '$RRCNm',								'$radiogp18')");			if (!mysql_query($insert,$login))			{				die('Error: ' . mysql_error());			}

And here's the error that's driving me crazy:

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 '666-2222', 'arrappl[dob]', 'caucasian', 'm','10/9/2009 1630:39', 'N', ' at line 5

For the life of me, I can't figure out what MySQL is bellyachin' about. I've gone over the code etc. for hours and hours, but can't seem to satisfy whatever the problem is. Maybe it's staring me in the face and I don't know it (like a typist trying to proofread his own typing), but I certainly can't figure it out. I see three things that are throwing me:1) The error message refers to "near '666-2222'. That's the applicant's phone number, which is really "(888) 666-2222" as shown in the array printout.2) The next item in the error is 'arrappl[dob]', which is the applicant's date of birth. Why is the array reference shown instead of the element's value - it's quite correct in the array "12/31/1966".3) The last item in the error is simply a ', which should be a date (i.e. $orgdt), which is fetched from the system date at the top of the page, where the array and customer id are fetched.Any ideas about what my problem is???????

[justsomeguy]

It would be a good idea to just print the whole query out to look at everything in context, e.g.:echo $insert;after you've created the query. For the array showing up in the query, you're missing the $ before that when you build the query. For the phone number issue, it looks like you missed the single quote at the start.

[creacon]

It would be a good idea to just print the whole query out to look at everything in context, e.g.:echo $insert;after you've created the query. For the array showing up in the query, you're missing the $ before that when you build the query. For the phone number issue, it looks like you missed the single quote at the start.

Whatta call! Those two miniscule items were the key to the whole thing. I'd been staring at the query for most of the day, and didn't see those two errors. That's what I mean about a typist proofreading his own typing. I must have read over those boo boos a hundred times or more, but it took a fresh (although experienced) eye to catch them.\Thanks a million for your help.

[creacon]

Now another problem. When all the data are captured, I first check to see if a record with that key already exists. This can happen if some of the information was entered incorrectly.What I'm trying to do when this occurs is to simply delete the existing record and add a new one with the new data. Here's the code to do that (I think):

		// This is where the questionnaire data		// w/get saved in a MySQL table		$dbSeld = mysql_select_db($database_login, $login);		 				$appl__query=sprintf("SELECT * FROM applicant WHERE CustID=%s",GetSQLValueString($custident, "text"));    						$applRS = mysql_query($appl__query, $login) or die(mysql_error());				$applFoundUser = mysql_num_rows($applRS);	// see if a record already exists				if ($applFoundUser)							// If one does, then delete it		{			mysql_query("DELETE FROM applicant WHERE CustID=%s",GetSQLValueString($custident, "text"));		}

The first query works correctly, as does the mysql_num_rows, therefore the "if" condition is True. However, the second query (i.e. the "DELETE FROM" errors out with the following message:

Warning: mysql_query() expects parameter 2 to be resource, string given in C:\xampp\htdocs\IOD\WOTCPg1.php on line 100

I coded the query directly from the w3schools "PHP MySQL Delete" page, substituting my information which I copied from the first query. I don't understand what's wrong here.Am I missing something?????

[justsomeguy]

mysql_query("DELETE FROM applicant WHERE CustID=%s",GetSQLValueString($custident, "text"));You just need to add a call to sprintf there, it looks like.

[PravinBhat]

Hello there..........!!!Try closing the connection to the mysql server before ending the PHP Script....Use<?php//code mysql_close();?>