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
Solved

MySQL query works in MySql Admin, but not from PHP

Posted on 2015-01-28
9
251 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

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.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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 …

808 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