Problem with 'quotation signs in MySQL requests

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

alexus
Forum Contributor
Posts: 159
Joined: Fri Jul 04, 2003 10:49 pm

Problem with 'quotation signs in MySQL requests

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What kind of quotes are you using and where?
alexus
Forum Contributor
Posts: 159
Joined: Fri Jul 04, 2003 10:49 pm

Post 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";
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
alexus
Forum Contributor
Posts: 159
Joined: Fri Jul 04, 2003 10:49 pm

Post 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.?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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();
alexus
Forum Contributor
Posts: 159
Joined: Fri Jul 04, 2003 10:49 pm

Post by alexus »

im using only one DB, but many tables, otviously

Here is the version: 4.1.7
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

If you are only using one DB how do you have a local output and a remote output?
alexus
Forum Contributor
Posts: 159
Joined: Fri Jul 04, 2003 10:49 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
alexus
Forum Contributor
Posts: 159
Joined: Fri Jul 04, 2003 10:49 pm

Post by alexus »

nothing works so fur from any udgestions from thatt site... I did chek tables status, MySQL returns OK
alexus
Forum Contributor
Posts: 159
Joined: Fri Jul 04, 2003 10:49 pm

Post 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...
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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>");
                                }     

}
?>
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Ack I missed that one... Backticks should be used as well though.
Post Reply