?
Solved

mysql_query() dying without error

Posted on 2010-01-09
16
Medium Priority
?
427 Views
Last Modified: 2013-12-12
Hi all,
A couple of mysql queries have died in my app, without sending an error.

For example,
$query = "SELECT * FROM pages";
$result = mysql_query($query, $link) or die($query." : ".mysql_error());

returns the query with no error at the end.

First, the query can't die. The pages file exists, and $link points to the correct db, so there's no reason for it to die.  And why would it not tell me the error?

It's curious, and it's doing it on my XP development machine and my Debian server.

Any suggestions?

0
Comment
Question by:chestertbear
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 6

Expert Comment

by:dwaynecharrington
ID: 26272747
Although I cannot be sure what it causing the issue, it seems as though there isn't any error to return so it definitely doesn't have anything to do with your query, nor your database connection. I can tell you that much.

One thing to check is the database user you are using has sufficient privileges to administer the table and database you're connecting to.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26272924
Here is how to connect, select, run a query and see if you got anything.  Make sure your code has all of these "moving parts" then try it again.  After that, please post the revised code and the output.  We can get you running, for sure.

Best regards, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);

// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://us2.php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
   echo "<br/>QUERY FOUND NO DATA: ";
   echo "<br/>$sql <br/>";
}
else
{
   echo "<br/>QUERY FOUND $num ROWS OF DATA ";
   echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-fetch-assoc.php
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
   var_dump($row); // MAN PAGE: http://us2.php.net/manual/en/function.var-dump.php
}

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26272930
Just a note, "sufficient privileges" issues will throw a MySQL error.  Cannot remember the number but I am sure you will get FALSE back from the query and there will be data in mysql_errno() and mysql_error()
0
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 

Author Comment

by:chestertbear
ID: 26275219
hmmm...
thanks guys.  
and ray, thanks for that code and suggestions.
here's a little more info and a code snippet.
>the privileges are set correctly (nice thought though).  $link has been used a dozen times prior to this failure in the execution of this script
>the error occurs within a function, but $link is declared as a global variable so that should not be an issue.
>the query immediately before, on the same $link, performs as written.
>if i comment out the query that's dying, the error simply moves to the next query.
>if i comment out the remainder of the function, the error moves to the query that follows the function.
what's most odd, is that when this was a sequential script, it worked perfectly.
when i turned it into a function (it creates individualised email newsletters, and so is called recursively), it died.
this is a dedicated server.  i have root access, and i have high confidence in the people who set it up and manage it for me.  i therefore have high confidence that apache, php and mysql are configured correctly.

here's the code snippet...
function make7($zedition, $zdomain, $zsubdomain, $zpgr, $zlevel)
{
//set the edition selector
if($edition=="Pre-Launch")
{
$seled  = "AND MEedition = '".$zedition."' AND (MEsubdomain = '".$zsubdomain."' OR MEsubdomain = '_all') ";
            }
            else
            {
                  $seled  = "AND (MEedition = '".$zedition."' OR MEedition = 'Always') AND (MEsubdomain = '".$zsubdomain."' OR MEsubdomain = '_all') ";
            }
      //now the menu items that appear at this group level
            if($llevel!=9)
            {
                  $lpick = " AND MEaccess".$zlevel." = 'Y' ";//sets the level
            }

      //set where for _xcontrol
            if($zpgr=="_xcontrol"){$where = "AND PGsubdomain = ''";}else{$where = "AND PGsubdomain = '".$subdomain."'";}

      //gets the page
            $qrp = "SELECT * FROM pages WHERE PGcode = '$zpgr' ".$where;
            $rsp = mysql_query($qrp, $link);
            $nrp = mysql_num_rows($rsp);
            $pages = mysql_fetch_array($rsp);
            $pgname = $pages['PGname'];

      //gets the menu categories
            $c = 0;
            $qrc = "SELECT * FROM menucats
                                WHERE (MCpage = '$zpgr' OR MCpage = '_xall')
                                AND (MCsubdomain = '$zsubdomain' OR MCsubdomain = '_all')
                                AND (MCname = '[THINGS]' OR MCname = 'THINGS')";
            $rsc = mysql_query($qrc, $link) or die("38:".$qrc." : ".mysql_error());
0
 

Author Comment

by:chestertbear
ID: 26275228
oops... sorry... still getting used to this interface.

the above code is the function up to the point where it dies.  after that, there are many more lines of code, and, of course, a } to clode the function.

thanks for your assistance.
0
 

Author Comment

by:chestertbear
ID: 26276770
and after testing and tinkering all day, i decided to take the contents of the function and place it in-line in the script that called it...
...and it works without hitch.

So... to recap...
...link resource had been declared as a global
... query works in-line
...same query fails without error when called within a function, even though the variables are all properly seeded

I have no idea why.
0
 
LVL 9

Expert Comment

by:gtkfreak
ID: 26276965
Look at the error file for mysql on your server. This should be the mysqld.log file.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26277586
Have a look at the code snippet.  I think the problem is on line 4.  The variable you're testing is undefined in the scope of this function on line 4, and there may be other undefined variables that are in play, too.

Add this line to the top of your scripts:
error_reporting(E_ALL);

That will cause PHP to throw a notice when you seek to use an undefined variable.  VERY helpful to software development!

To sum up, I do not think the query is failing silently, I think it is not getting executed at all because of conditional logic.

Bet regards, ~Ray
function make7($zedition, $zdomain, $zsubdomain, $zpgr, $zlevel)
{
//set the edition selector
if($edition=="Pre-Launch")
{

Open in new window

0
 

Author Comment

by:chestertbear
ID: 26279925
Thanks Ray, but that didn't change the outcome.
I decided to start from scratch, take the in-line code, and make a whole new inserted script.
Now, I get a link resource error, even though the variable I use for the link resource is a declared global.  Should I send the link resource to the function with the other variables?

On balance, I think I've exhausted enough of my time (and yours) on this, so I'll just go with the in-line version that works, even if it is sloppy coding.  

Thanks everyone for your contributions, time and brain power.  It's very much appreciated.
Cheers
CTB

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26293991
Regarding this:

Thanks Ray, but that didn't change the outcome.

That is really amazing and I would love to see the code.  Are you saying that you added the error_reporting(E_ALL); statement to the top of your script, then tested and undefined variable and you got no "Notice" from PHP?  If that is the case, there is something else terribly wrong that we are not seeing here.

I think you must be connected to the data base (probably this is the "$link" you refer to) to do queries.  

I do not think that the connection is concerned about variable scope - unless you use the variable explicitly in the query statement, and it looks like you're doing that.

You wrote, "the link resource is a declared global" but that is not the case in the code you posted above.  The "global $link;" statement would need to be inside the function and I do not see it.  Please read about "global" here:
http://us2.php.net/manual/en/language.variables.scope.php

Choices seem to be (1) declare $link to be global or use the $GLOBALS array, or (2) pass the $link var to the function, or (3) omit it from the statements where it is not needed.  If all of your queries reference the same data base connection, it may be completely unnecessary.

Best regards, ~Ray
0
 

Author Comment

by:chestertbear
ID: 26298328
Thanks again Ray.

The problem is the CMS accesses 3 different databases within the same script, so I include the link resource as a matter of course in all queries across the entire CMS suite, just to make sure we're pointing in the right direction.

The script in question is assembling an email newsletter to members which includes a product offering.  As members have different member content and price levels, each email must be individually created for each member.  To do that, this script needs to:
-read the db pointer record from the mailrequired table in the master file ($mlink) that file tells php that it is to create outgoing email and save it in the mailqueue table.  To do that, it needs to know which website is sending the mail, and, therefore, which data set should be used to send it.  each domain hosted on the server has its own data file, with it's own mail lists.
-read records from the domain's db ($link or $xlink, depending on where it's used), especially the subscribers' list from the people table and the sender's email address from the configuration table.
-read the hosting configuration db ($dlink), and extract the email password and mail server alias (mail.domain.tld) for the sender's email address from the pop_access table so email can be sent via smtp through the server's mail gateway.
-save the individualised email in the mailqueue table in the master file

All 4 ($link, $xlink, $mlink and $dlink) are registered globals, and they're set at the top of the script.

As it cycles through each record in the master file, the script opens the domain's data file (as $xlink), extracts what it needs (including calling the function in question), and then closes it using mysql_close().

The function was intended to remove the actual email assembly from the script.  The script calls the function which reads the necessary data, assembles the individualised email for the subscriber, and then returns a string containing html for later use.

The script then saves that html string in the mailqueue for processing at a later time by a cron (to control the rate of output of mail).

As I said, the process works perfectly when the whole script is in-line.  It's just when I pull out the section that assembles the email, and place that in a function, that it goes wrong.

As I said, I think, for now, time pressure dictates that I go with what's working and move on, sloppy code not withstanding.  I'll get back to it at some point down the track.

Thanks once again for the time and brainpower you've devoted to this.  It's very much appreciated.

Cheers
CTB
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 26303489
@CTB:

"All 4 ($link, $xlink, $mlink and $dlink) are registered globals, and they're set at the top of the script."

That is NOT WHERE YOU IDENTIFY GLOBALS if you are using this kind of statement:

global $link;

I think we have some confusion in terms.  "Register Globals" is documented here
http://us.php.net/manual/en/security.globals.php

Your issue is not about "register globals" - it is about variable "scope," documented here:
http://us.php.net/manual/en/language.variables.scope.php

If you want a variable from outside a function to be available inside the function, you need to declare the variable in the function definition or you need to put the "global" statement INSIDE the function.

If only I could have convinced you to use the error_reporting(E_ALL); statement you would immediately be able to see and fix these things!

Best of luck with your project, ~Ray

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26303535
One final note, then I will sign off on this question until you can post the revised code snippet...

You wrote that "the CMS accesses 3 different databases" - this might be an issue for you because different data bases and different data base CONNECTIONS are entirely different things.  You can make a single data base connection and select different data bases on the same connection.  The link resource used in the query statements applies to the connection, not to the selection.

You can see the difference in the code sample I posted a few days ago here:
01/09/10 08:36 AM, ID: 26272924
0
 

Author Comment

by:chestertbear
ID: 26307518
Doh!.  Thanks Ray.
I will admit to completely misunderstanding the declaration of globals.
I had (wrongly) assumed that if you wanted a variable to be available across an entire script, you simply declared it as a global at the top of the script.  In my defence, it worked that way in another langauge, and it didn't even occur to me that in php it would be the other way around.  
Without your persistance, I'd have gone on thinking that way, and would waste even more time (mine and yours) chasing bugs that shouldn't have been there in the first place.
Cheers.
CTB
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26307546
"declared it as a global at the top of the script."

Yes, wouldn't that make sense for PHP!  There are a lot of things about PHP that are really great, but this is not one of them.  Another one that you will hit eventually, array indexes are loosely typed

Array[0] = 'X';
Array["0"] = 'Y';

Two entries in the array?  Nope - only one with a value of 'Y'.  I spent a morning of my life on this little bit of idiocy.
0
 

Author Comment

by:chestertbear
ID: 26307617
Just one final supplementary question Ray, if I may...
Can I (or perhaps that should be "Should I") use $GLOBALS['link'] instead of $link to define the link resource?
That is...
          $link = mysql_connect("localhost",$connect['user'], $connect['pass'], TRUE);
or
          $GLOBALS['link'] = mysql_connect("localhost",$connect['user'], $connect['pass'], TRUE);
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

764 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