Solved

MySQL query works in MySql Admin, but not from PHP

Posted on 2015-01-28
9
258 Views
Last Modified: 2015-01-29
I have a head-scratching issue.

The php mysql_query() command is as follows:
$qry = "INSERT INTO Referrer (`firstName`,`lastName`,`token`,`email`,`appfname`,`applname`)
				VALUES ('$ref[0]','$ref[1]',PASSWORD('$token'),'$ref[2]','$appfname','$applname');
				SELECT token FROM Referrer WHERE referrerID = (SELECT @@identity)";
$success = mysql_query($qry) or die(mysql_error());

Open in new window

firstName varchar(32)
lastName varchar(32)
token varchar(64)
email varchar(128)
appfname varchar(32)
applname varchar(32)
Here is an echo of the $qry variable:
INSERT INTO Referrer (`firstName`,`lastName`,`token`,`email`,`appfname`,`applname`) VALUES ('John','Doe',PASSWORD('aaaaaaaaaaa'),'name@domain.com','Jane','Doe'); SELECT token FROM Referrer WHERE referrerID = (SELECT @@identity)
Here is the MySQL error I'm getting:
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 'SELECT token FROM Referrer WHERE referrerID = (SELECT @@identity)' at line 3

Now, if I run that query I posted from the echo, it will insert a record and return the token without any modifications to the string I paste.  If I run this from PHP, it will return the error I posted.
0
Comment
Question by:Shane Kahkola
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 6

Assisted Solution

by:rjohnsonjr
rjohnsonjr earned 50 total points
ID: 40576686
You have to use mysqli_multi_query

http://php.net/manual/en/mysqli.multi-query.php
0
 
LVL 9

Accepted Solution

by:
Brian Tao earned 450 total points
ID: 40576846
I agree to rjohnsonjr, but if you are not ready to switch to mysqli yet, you can keep the insert statement and remove the select @@identity, and then use mysql_insert_id function instead.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40576902
They're right, the PHP mysql_query() function does not support doing more than one query in a single call.
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 3

Author Comment

by:Shane Kahkola
ID: 40577423
I tried the mysqli methods instead and I keep getting a boolean result for multi_query.  I'm kind of done messing with that.  I looked at a hundred different examples of it and none of the examples return anything but a boolean for me.  So, when I try to run mysqli_fetch_assoc(mysqli_multi_query($con,$qry)), I get an error about how fetch_assoc is looking for a result and not a boolean.

Abandoning that, I went to taoyipai's method of doing it where I use mysql_insert_id() function to run a select query using mysql_query().  Then I get an error: "Call to undefined function mysql_insert_id()."

Here's my code:
function createToken($ref, $appfname, $applname) {
	$id = 0;
	$token = $ref[1].$applname; 
	$qry = "INSERT INTO Referrer (`firstName`,`lastName`,`token`,`email`,`appfname`,`applname`)
			VALUES ('$ref[0]','$ref[1]',PASSWORD('$token'),'$ref[2]','$appfname','$applname')";
	$tqry = "SELECT token FROM Referrer WHERE referrerID = (SELECT %d)";
	#echo $qry;
	
	$result = mysql_query($qry);
	
	if ($result) {
		$id = msyql_insert_id();
	}
	
	if ($id>0) {
		$token = mysql_query(sprintf($tqry,$id));
		if (strlen($token['token'])>0) {
			$sent = emailRefRequest($ref[0],$ref[1],$ref[2],$token['token'],$appfname,$applname);
			$resmsg .= $sent;
		}
		else {
			$resmsg .= '<li style=\"color: red\">We were unable to create a reference form for '.$ref[0].' '.$ref[1];
		}
	}
	return $resmsg;
}

Open in new window

Any suggestions?
0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 40577827
mis-spelling: msyql_insert_id should be mysql_insert_id
0
 
LVL 3

Author Comment

by:Shane Kahkola
ID: 40577830
Oi.
0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 40577831
Also, mysqli_multi_query returns a boolean value telling you if it's successful or not.
To retrieve the resultset from the first query you can use mysqli_use_result() or mysqli_store_result(). All subsequent query results can be processed using mysqli_more_results() and mysqli_next_result().
0
 
LVL 3

Author Closing Comment

by:Shane Kahkola
ID: 40577835
A nod to rjohnsonjr for being the first to identify the limitations for me.  The rest to Taoyipai for giving me the solution.
0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 40578575
Thanks for the points.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question