Page 1 of 2

Problem with 'quotation signs in MySQL requests

Posted: Thu Jun 22, 2006 5:14 pm
by alexus
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi,
I really got boared of this, have dual layer SQL request and it tends to work diferently on diferent servers. If I dont have quotes inside SQL then it works localy but not remotely, and wiseversa, if i have quotes, works remotely but not locally... Yes I can bare w/ this but kind of hard to develop app. Can some one advice on how to make it work the same locally and remotely? Maybe cahnge in code or php.ini?

Here is my code:

Code: Select all

<? 
require("../inc/db_conn.php");

$db = mysql_connect($db_server, $db_user, $db_pass);
mysql_select_db($db_name, $db);
$sql = "select * from uc_event WHERE date >= now() ORDER BY date";
$result = mysql_query($sql);
while ($row_news = mysql_fetch_array($result)) {
			//mysql_free_result($result_loc);
            print("". $row_news["id"] ."<br>"); 
            $place = $row_news["place"];
            // Here Entering the second level, by using result from
            //previous SELECT to SELECT WHERE 
                
                //Working LOCAL
                $sql_loc = "select * from uc_club WHERE id=$place";
				$result_loc = mysql_query($sql_loc);
                while ($row_loc = mysql_fetch_array($result_loc)) {
					print("<b>Case #1 (Local) </b> ". $row_loc["name"] ."<br>");
				}
                
                //Working REMOTE
                $sql_loc2 = "select * from uc_club WHERE id='$place'";
				$result_loc2 = mysql_query($sql_loc2);
                while ($row_loc2 = mysql_fetch_array($result_loc2)) {
					print("<b>Case #2 (Remote) </b> ". $row_loc2["name"] ."<br><br>");
				}      

}
?>
And here is local output:

Code: Select all

28
Case #1 (Local) QUO

Warning: mysql_query(): Unable to save result set in c:\program files\apache group\apache\htdocs\template_9837_bu54w0isvwh26c90uh42\site\events\bug_tracer.php on line 24

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\program files\apache group\apache\htdocs\template_9837_bu54w0isvwh26c90uh42\site\events\bug_tracer.php on line 25
27
Case #1 (Local) Gypsy Tea

Warning: mysql_query(): Unable to save result set in c:\program files\apache group\apache\htdocs\template_9837_bu54w0isvwh26c90uh42\site\events\bug_tracer.php on line 24

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\program files\apache group\apache\htdocs\template_9837_bu54w0isvwh26c90uh42\site\events\bug_tracer.php on line 25
29
Case #1 (Local) Rasputin

Warning: mysql_query(): Unable to save result set in c:\program files\apache group\apache\htdocs\template_9837_bu54w0isvwh26c90uh42\site\events\bug_tracer.php on line 24

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\program files\apache group\apache\htdocs\template_9837_bu54w0isvwh26c90uh42\site\events\bug_tracer.php on line 25

And Remote Output

Code: Select all

28
Case #1 (Local) 
Case #2 (Remote) QUO

27
Case #1 (Local) 
Case #2 (Remote) Gypsy Tea

29
Case #1 (Local) 
Case #2 (Remote) Rasputin



As you can see, im getting errors locally for remote output,but remotelly the local string doesnt even shoes up :-(


Any ideas?


Thanks!@


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu Jun 22, 2006 5:22 pm
by feyd
What kind of quotes are you using and where?

Posted: Thu Jun 22, 2006 5:26 pm
by alexus
singe quote, in second SQL

This Works remotely, but not locally and in the PHP docs at to what i recall

Code: Select all

$sql_loc2 = "select * from uc_club WHERE id='$place'";
And this Worls locally, but not remmotely

Code: Select all

$sql_loc = "select * from uc_club WHERE id=$place";

Posted: Thu Jun 22, 2006 5:39 pm
by RobertGonzalez
It almost looks like you are having a storage problem with the errors that are getting thrown. I know that single quote typically wrap not-integer values. Some DB's (like MySQL don't strictly enforce this) and some (like SQL Server) do.

Are your table types the same on both DB's (ie, MyISAM, HEAP, InnoDB)? Can you run the query in phpMyAdmin (or whatever DB utility you use) without errors?

Posted: Thu Jun 22, 2006 5:44 pm
by alexus
Yes all tables are the same MyISAM

Im using MySQL CC, and I have phpMySQLAdmin, but how can I run double select qurey there? I though it not relational db so i have to run them in stages.?

Posted: Thu Jun 22, 2006 5:54 pm
by RobertGonzalez
MySQL is a relational database management system (RDBMS). Depending on the version, there are some pretty cool things you can do.

My recommendation is in each DB, run the following query and post back the results...

Code: Select all

SELECT VERSION();

Posted: Thu Jun 22, 2006 5:56 pm
by alexus
im using only one DB, but many tables, otviously

Here is the version: 4.1.7

Posted: Thu Jun 22, 2006 6:06 pm
by RobertGonzalez
If you are only using one DB how do you have a local output and a remote output?

Posted: Thu Jun 22, 2006 6:09 pm
by alexus
oh what I ment by that was this:
I have only one remote MySQL server and I have two HTTP servers, local and remote... and when Im raning PHP, each of them behaves diferently... diged down and I found that they um interprete ' or " some how in opposite ways in 2 layers SQL statemnt, but now i dont know how to make them to work good w/ the sme code, rather then doing special code for each server

Posted: Thu Jun 22, 2006 6:20 pm
by RobertGonzalez
Now I get it. Thanks for clearing that up. So your local HTTP server is on the same server as your MySQL server and it is not handling the lack of single quotes well. Your other HTTP server is not on the same box as your DB server, but it is handling single quotes well.

I googled your error message and found quite a few hits in the list. Here a few links...

http://bugs.php.net/bug.php?id=11765 (This one is from PHP.net)
http://textpattern.com/faq/36/warning-u ... result-set
http://www.vbulletin.com/forum/showthread.php?t=99673
http://www.faqts.com/knowledge_base/view.phtml/aid/8339

Sounds like there may be a corruption in your tables when you get that error message. You might not be getting it on the other server because of an error_reporting setting in PHP. Just a thought.

Posted: Thu Jun 22, 2006 6:28 pm
by alexus
nothing works so fur from any udgestions from thatt site... I did chek tables status, MySQL returns OK

Posted: Thu Jun 22, 2006 6:43 pm
by alexus
just went through all of the site, nithing helps.... it look strange for me, they have problom of accessing data, in my case i can access data but i have to use diferent syntax for my HTTP servers...

Posted: Thu Jun 22, 2006 6:52 pm
by Benjamin
This is a longshot but try adding backticks...

Code: Select all

<?
require("../inc/db_conn.php");

$db = mysql_connect($db_server, $db_user, $db_pass);
mysql_select_db($db_name, $db);
$sql = "select * from `uc_event` WHERE `date` >= now() ORDER BY `date`";
$result = mysql_query($sql);
while ($row_news = mysql_fetch_array($result)) {
                        //mysql_free_result($result_loc);
            print("". $row_news["id"] ."<br>");
            $place = $row_news["place"];
            // Here Entering the second level, by using result from
            //previous SELECT to SELECT WHERE
               
                //Working LOCAL
                $sql_loc = "select * from `uc_club` WHERE `id`=$place";
                                $result_loc = mysql_query($sql_loc);
                while ($row_loc = mysql_fetch_array($result_loc)) {
                                        print("<b>Case #1 (Local) </b> ". $row_loc["name"] ."<br>");
                                }
               
                //Working REMOTE
                $sql_loc2 = "select * from `uc_club` WHERE `id`='$place'";
                                $result_loc2 = mysql_query($sql_loc2);
                while ($row_loc2 = mysql_fetch_array($result_loc2)) {
                                        print("<b>Case #2 (Remote) </b> ". $row_loc2["name"] ."<br><br>");
                                }     

}
?>

Posted: Thu Jun 22, 2006 9:31 pm
by AKA Panama Jack
Actually, you should be using single quotes on all entries.

Code: Select all

$sql_loc2 = "select * from uc_club WHERE id='$place'";
That should be used instead of the one without the single quotes. It is the valid sql syntax.

Posted: Thu Jun 22, 2006 10:06 pm
by Benjamin
Ack I missed that one... Backticks should be used as well though.