Solved

MySQL query works in MySql Admin, but not from PHP

Posted on 2015-01-28
9
246 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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Apache/PHP: Cannot make phpinfo() and phpmyadmin work 4 33
phpmyadmin 3 31
Saving image into server to get url later 1 30
WordPress syntax 2 23
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 …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

937 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now