[RESOLVED] Need the Delimiters to store an sql statement as a field in mySQL

[MagicT]

php 5.0.4
mySQL 4.1.11a

I need to store the most recent query for each user when using a particular php script. The following works:

  $session_userid = $_SESSION['userid'];
  $newquery = "update password_table set pwdQueryAddress = 'whatever' where pwdLoginId = '$session_userid'";

and puts the string 'whatever' into the pwdQueryAddress field of the table.

But what I really want, of course, is:

  $query = "select * from address, name
		where adrAddressId > 0
                          and adrAddressId = namAddressId
		and adrDoNotCall like '$searchvalues[2]'
		and adrStreetNumber like '$searchvalues[3]'
		and adrStreetName like '$searchvalues[4]'
		and adrUnitNumber like '$searchvalues[5]'
		and adrZipCode  like '$searchvalues[6]'
		and adrPrecinct like '$searchvalues[7]'
		and adrWard like '$searchvalues[8]'";
  $newquery = "update password_table set pwdQueryAddress = '$query' where pwdLoginId = '$session_userid'";

Which gives mySQL fits because it treats $query as part of the update statement, not as text.

What's the delimiter I need aroudn '$query' to make mySQL treat it like common everyday characters?

-Thx
-MagicT

[john tindell]

Whats the error message that you are recieving, i cant see anything wrong with the code that you are using.

[MagicT]

--------------------------------------------------------------------------------
DataBase Error # 1064
--------------------------------------------------------------------------------
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 '11' and adrWard like '4'' where pwdLoginId = 'dave'' at line 1

--------------------------------------------------------------------------------
SQL Query: update password set pwdQueryAddress = 'select * from address, name where adrAddressId > 0 and adrAddressId = namAddressId and adrDoNotCall like '%' and adrStreetNumber like '%' and adrStreetName like '%' and adrUnitNumber like '%' and adrZipCode like '%' and adrPrecinct like '11' and adrWard like '4'' where pwdLoginId = 'dave'


so, clearly a single tick isn't good enough because it's treated equally to the ticks that wrap the values in $query.

[john tindell]

try this

$query = "select * from address, name
		where adrAddressId > 0
                          and adrAddressId = namAddressId
		and adrDoNotCall like '$searchvalues[2]'
		and adrStreetNumber like '$searchvalues[3]'
		and adrStreetName like '$searchvalues[4]'
		and adrUnitNumber like '$searchvalues[5]'
		and adrZipCode  like '$searchvalues[6]'
		and adrPrecinct like '$searchvalues[7]'
		and adrWard like '$searchvalues[8]'";
  $newquery = "update password_table set pwdQueryAddress = '" . addslashes($query) . "' where pwdLoginId = '$session_userid'";

[MagicT]

ok, that worked like a charm. I get the data into the field of the database and I can see the data in phpmyAdmin. So, now I'm having similar problems getting the data back out again. I've tried several variations of what you provided in ordrer to get the data in, to no avail...

function researchaddresses()
{
  $session_userid = $_SESSION['userid'];
  $query = "select pwdQueryAddress from password where pwdLoginId = '$session_userid'";
  $result = mysql_query($query);
  if (!$result) error_message($query);
   
  $newresult = mysql_query($result);
  if (!$newresult) error_message($query);
  return $newresult;
}

In the above routine, I'm attempting to retrieve the query that is stored in pwdQueryAddress.
My error routine displays my sql and reports it as:

select pwdQueryAddress from password where pwdLoginId = 'dave'

which, when I run straight from the database, it returns my query string just fine.


But when I run my php routine i get...

DataBase Error # 1064
--------------------------------------------------------------------------------
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 'Resource id #7' at line 1

--------------------------------------------------------------------------------
SQL Query: select pwdQueryAddress from password where pwdLoginId = 'dave'


I'm also not getting any useful info from my Apache log.

I'll keep researching this...I'm not sure about the reference to Resource id #7 at line 1...

thx-
-MagicT

[john tindell]

use stripslashes() to strip the slashes from the query you read from your DB before you try and exceute it.

[MagicT]

John,
I want to thank you for your help on this. Actually, I did not have to remove the slashes using stripslashes.

I actually had two problems:
My first problem was that I was trying to select fieldname from table where... instead of selecting * from table and retrieving the field via mysql_fetch_row...

My second problem was that I was storing my query in a varchar(255) field. I noticed that when I echoed my sql string out of the database, it was truncated.

So, I changed my fetch commands and my datatype in the database to type text. Now, everything works just fine... using the following:

function researchaddresses()
{
  $session_userid = $_SESSION['userid'];
  $query = "select * from password where pwdLoginId = '$session_userid'";
  $result = mysql_query($query);
  if (!$result) error_message($query);
  
  $query_data = mysql_fetch_row($result);
  $newquery = $query_data[10];                /** field 10 is old query **/

  $newresult = mysql_query($newquery);    /** redo historical query here **/
  if (!$newresult) error_message($newquery);  
  return $newresult;  
}

Works like a charm!