Solved

MySQL query works in MySql Admin, but not from PHP

Posted on 2015-01-28
9
247 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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 and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

821 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