Solved

MySQL query works in MySql Admin, but not from PHP

Posted on 2015-01-28
9
244 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 82

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

762 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

17 Experts available now in Live!

Get 1:1 Help Now