Thanks!
Problem with 'quotation signs in MySQL requests
Moderator: General Moderators
Ok, im back and this thing behaves odd.... so I had problem on my local machine and remote worked fine, now I open my remote host and site aint working, when local server all over the suden works with the code it didnt like...
So how can I explain that, and more over how can I make those thing to be stable and solid?
So how can I explain that, and more over how can I make those thing to be stable and solid?
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
OK, in your phpMyAdmin tool, run the two queries one by one in each db and see what is getting thrown by the DB. For example, in your remote DB, using your MySQL utility, run one query with quotes and one without. Then, in the local DB, do the same thing and see if the errors are the same. It is a long shot, but there may be something going on with PHP code that is making the queries weird. I doubt it, but I would be interested in seeing what is happening at the DB level on each DB for each query.
ok, im going to do it...
but I have ONE db
let me define the setup:
"Remote Server" = HTTP Apache FreeBSD+PHP + MySQL Server
"Local Server" = HTTP Apache WinXP + PHP
As you can see there is no MySQL on local server, I use MySQL from remote server to work with data.
Also, I have only one virtual DB called -main-
Then what I call dua layer select is this:
SELECT * FROM club_events
thos will return me all Club Info inclusing the locations (address, but in terms of location id, so lets say we have Plannet Holywood = '23')
so newt qurey to find out where Plannet Hollywood is we wood run something like this
SELECT * FROM club_locations WHERE location_id='23'
So far we good if we run qureys independantly from each other and intering location_id manually, but we are not going to sit there and eneter mannuall all ids so we connect each qureys togather so second qurey will look something like this:
SELECT * FROM club_locations WHERE location_id = '$row[location]'
And here is when it starts doing strange things. Depanding on what quotes i use it works on local server but not on remote and wiseversa. Also sometimes it changes it behaviour, say it wos warking on remote and nnow it dosnt, but it now works on local....
Did i explain my situation clearly? Now
but I have ONE db
let me define the setup:
"Remote Server" = HTTP Apache FreeBSD+PHP + MySQL Server
"Local Server" = HTTP Apache WinXP + PHP
As you can see there is no MySQL on local server, I use MySQL from remote server to work with data.
Also, I have only one virtual DB called -main-
Then what I call dua layer select is this:
SELECT * FROM club_events
thos will return me all Club Info inclusing the locations (address, but in terms of location id, so lets say we have Plannet Holywood = '23')
so newt qurey to find out where Plannet Hollywood is we wood run something like this
SELECT * FROM club_locations WHERE location_id='23'
So far we good if we run qureys independantly from each other and intering location_id manually, but we are not going to sit there and eneter mannuall all ids so we connect each qureys togather so second qurey will look something like this:
SELECT * FROM club_locations WHERE location_id = '$row[location]'
And here is when it starts doing strange things. Depanding on what quotes i use it works on local server but not on remote and wiseversa. Also sometimes it changes it behaviour, say it wos warking on remote and nnow it dosnt, but it now works on local....
Did i explain my situation clearly? Now
Ok i just got crazy: now the remote doesnt want to execute even simple requests....
I did chek qureys in myPHPAdmin all wors fine... phpadmin is on remote server
now I used the same qurey as in phpadmin for my page and got empty result and no errors, although it does returns the number of rows...
Im giving up....
here is the page:
It works obwiously everywhere but on the remote server!
Any Ideas? should i chande hosting provider?
I did chek qureys in myPHPAdmin all wors fine... phpadmin is on remote server
now I used the same qurey as in phpadmin for my page and got empty result and no errors, although it does returns the number of rows...
Im giving up....
here is the page:
Code: Select all
<?php /* Created on: 6/25/2006 */
//www.confident.us/uc/event_gallery/select.php
require("../inc/db_conn.php");
# Content of db_conn.php
#
# $db_server = "****";
# $db_user = "****";
# $db_pass = "****";
# $db_name = "-main-";
#
$db = mysql_connect($db_server, $db_user, $db_pass);
mysql_select_db($db_name, $db)
?>
<html>
<body>
<?php
$sql_loc = 'SELECT * FROM `uc_club`';
$result_loc = mysql_query($sql_loc);
$rows = mysql_num_rows($result_loc);
echo "<b>Rows:</b> $rows <br>";
#Lets chek for errors
if(mysql_fetch_array($result_loc)){
//Good
}else{
//Errror
echo "There is an error in popolating variables ";
}
while ($row_loc = mysql_fetch_array($result_loc)) {
echo $row_loc["name"];
echo " ". $row_loc["name"] ."<br>";
}
?>
</body>
</html>It works obwiously everywhere but on the remote server!
Any Ideas? should i chande hosting provider?
-
Charles256
- DevNet Resident
- Posts: 1375
- Joined: Fri Sep 16, 2005 9:06 pm
add to the end of every mysql query and see what you get.
Code: Select all
or die(mysql_error())Same thing all perfect locally but on remote im getting only number of rows which is 5, and no errors
any one want to take a look at my PHPINFO();
Here is updated Page:
any one want to take a look at my PHPINFO();
Here is updated Page:
Code: Select all
<?php /* Created on: 6/25/2006 */
//www.confident.us/uc/event_gallery/select.php
require("../inc/db_conn.php");
# Content of db_conn.php
#
# $db_server = "****";
# $db_user = "****";
# $db_pass = "****";
# $db_name = "-main-";
#
$db = mysql_connect($db_server, $db_user, $db_pass) or die(mysql_error());
mysql_select_db($db_name, $db)or die(mysql_error());
?>
<html>
<body>
<?php
$sql_loc = 'SELECT * FROM `uc_club`';
$result_loc = mysql_query($sql_loc) or die(mysql_error());
$rows = mysql_num_rows($result_loc)or die(mysql_error());
echo "<b>Rows:</b> $rows <br>";
$row_loc = mysql_fetch_array($result_loc) or die(mysql_error());
while ($row_loc = mysql_fetch_array($result_loc)) {
echo $row_loc["name"];
echo " ". $row_loc["name"] ."<br>";
}
?>
</body>
</html>- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
This statement right here tells me this is a code issue, not a DB issue. Something is happening differently with the way you are interacting with your database on each server. Are running data through add_slashes() somewhere? If you are using one database, and hitting it from two different server setups, I can only surmise that the problem lies within the code that is processing the DB requests.alexus wrote:ok, im going to do it...
but I have ONE db
let me define the setup:
"Remote Server" = HTTP Apache FreeBSD+PHP + MySQL Server
"Local Server" = HTTP Apache WinXP + PHP
PS I would take your phpinfo() page now if I were you. There is information in there I wouldn't want everyone to see.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Echo out the WHERE var just before running it through the query. For example...
What gets echo'd from each server? Remember to clear your browser cache also before doing this.
Code: Select all
<?
require("../inc/db_conn.php");
$db = mysql_connect($db_server, $db_user, $db_pass) or die("Could not connect to the database: " mysql_error());
if (!mysql_select_db($db_name, $db))
{
die("Could not select the database $db_name");
}
$sql = "select * from `uc_event` WHERE `date` >= now() ORDER BY `date`";
$result = mysql_query($sql) or die('Could not grab the event list: ' . mysql_error());
while ($row_news = mysql_fetch_array($result))
{
//mysql_free_result($result_loc);
echo $row_news["id"] .'<br>';
$place = $row_news["place"];
echo '<strong>We are trying to pass ' . $place . ' to the next query...<br />';
$sql_loc = "select * from `uc_club` WHERE `id`= $place";
$result_loc = mysql_query($sql_loc) or die('Could not query clubs table for ' . $place . ': ' . mysql_error());
while ($row_loc = mysql_fetch_array($result_loc))
{
print("<b>Case #1 (Local) </b> ". $row_loc["name"] ."<br>");
}
$sql_loc2 = "select * from `uc_club` WHERE `id`='$place'";
$result_loc2 = mysql_query($sql_loc2) or die('Could not run second query for ' . $place . ': ' . mysql_error());
while ($row_loc2 = mysql_fetch_array($result_loc2))
{
print("<b>Case #2 (Remote) </b> ". $row_loc2["name"] ."<br><br>");
}
}
?>