mysql_query result True on first iteration, then False

samtediou · #1 22-Jul-2003, 13:11

Hello again!

Why would an mysql_query result change from True to False? I've got two (bogus) speakers of Creek in my DB (dataid's 12 and 36), yet my code only shows the first one.

I stripped down the code to make it more viewable:

First, here is the output:
=======================
The dataid returned is 12.
The inner SQL returned TRUE.

Name: Eigfa
Primary language: Creek
Language(s) of interest: Khmer [basic], Lithuanian [basic]

The dataid returned is 36.
The inner SQL returned FALSE.
========================

Here's my code:
=======================
<?php
$sql_host="localhost";
$sql_user="su_user1";
$sql_pass="password1";
$sql_db="su_db1";

// The langid would normally be passed to this page from another page,
// but for purposes of testing, I'll set it here. There are two members in
// the database whose language id is 16 (Creek)

$Langid = 16;

// Suppress errors and handle them internally
$link = mysql_connect("$sql_host", "$sql_user", "$sql_pass");
if(!empty($link))
{
// Select the MySQL database
if (mysql_select_db("$sql_db", $link) == True)
{

// sort results by language (plang)

$SQL = "SELECT ap_temp_pal_data.dataid, ap_temp_pal_data.fname, ";
$SQL .= "ap_temp_languages.lang_eng, ap_temp_pal_languages.langid, ";
$SQL .= "ap_temp_pal_languages.level FROM ( ap_temp_pal_data ";
$SQL .= "INNER JOIN ap_temp_pal_languages ON ap_temp_pal_data.dataid = ";
$SQL .= "ap_temp_pal_languages.dataid ) INNER JOIN ap_temp_languages ON ";
$SQL .= "ap_temp_pal_languages.langid = ap_temp_languages.langid ";
$SQL .= "WHERE ( ( ( ap_temp_pal_languages.langid ) = ".$Langid." ) ";
$SQL .= "AND ( ( ap_temp_pal_languages.level ) = 'pri' ) )";

// Execute the SELECT query
$search_result = mysql_query($SQL, $link);

if ($search_result == True)
{

// Fetch a row and print two fields
while ($row = mysql_fetch_array($search_result))
{

$Palid = $row["dataid"]; //to be submitted to email page
echo "The dataid returned is $Palid. ";

//Build SQL2 for languages of interest for each pal here

$SQL2 .= "SELECT ap_temp_pal_data.fname, ap_temp_pal_languages.dataid, ";
$SQL2 .= "ap_temp_pal_languages.langid, ap_temp_pal_languages.level, ";
$SQL2 .= "ap_temp_languages.lang_eng FROM ap_temp_languages ";
$SQL2 .= "INNER JOIN ( ap_temp_pal_data INNER JOIN ap_temp_pal_languages ";
$SQL2 .= "ON ap_temp_pal_data.dataid = ap_temp_pal_languages.dataid ) ";
$SQL2 .= "ON ap_temp_languages.langid = ap_temp_pal_languages.langid ";
$SQL2 .= "WHERE (((ap_temp_pal_languages.dataid)= " . $Palid . "))";

//execute SQL2 here; if passes, process the rest
$search_result2 = mysql_query($SQL2, $link);
if ($search_result2 == True) {

echo "The inner SQL returned TRUE. ";

//Set an index counter
$i = 0;

//Get languages, put into array, and close
while ($row2 = mysql_fetch_array($search_result2)) {

$lang = $row2["lang_eng"];
$lev = $row2["level"];

if ($lev == "bas") {$lev = "basic";}
elseif ($lev == "int") {$lev = "intermediate";}
elseif ($lev == "adv") {$lev = "advanced";}

$langint[$i] = $lang;
$levellangint[$i] = $lev;

$i++;
}//end of while loop processing search_result2

mysql_free_result($search_result2);

$Plang = $row["lang_eng"];
$Count = $row["CountOflang_eng"];
$Fname = $row["fname"];

$Langint1 = $langint[1];
$Levellangint1 = $levellangint[1];
$Langint2 = $langint[2];
$Levellangint2 = $levellangint[2];

echo " Name: $Fname </td></tr>";
echo " Primary language: $Plang</td></tr>";
echo " Language(s) of interest: ";
echo "$Langint1 [$Levellangint1]";
if ($Langint2 != '') { echo ", $Langint2 [$Levellangint2]";}

} // end of if condition search_result true
else {
echo "The inner SQL returned FALSE. ";

}

echo " ";

}// end of while loop processing search_result

mysql_free_result($search_result);
}
else
{
print("Query failed ");
}
}
else
{
print("Unable to select DB ");
}
}
else
{
print("Unable to connect to DB server ");
}

?>

=======================

Any ideas?

samtediou

JdS · #2 23-Jul-2003, 06:25

Why do you use INNER JOINs? I really find them unnecessary as far as I can figure out what you're trying to do with your script.

Maybe if you attached a MySQL dump file with some dummy data, I could figure out a better SQL statement...

BTW, please use syntax highlighting whenever you paste code, since I like everything in colour and get easily put off (and I am sure many others too) when I see lines and lines of code in plain 'ol black.

samtediou · #3 23-Jul-2003, 14:25

OK, I've just read the faqs on formatting (www.desilva.biz) and they're a good idea...I wondered how some of the members were getting all the colors!

As for the MySQL data dump, I'll try to figure out how to do that.

Thanks,

Samtediou

samtediou · #4 23-Jul-2003, 14:42

I think I've got some usable data dumps of my tables (bogus data). Please go to www.braindivot.com to download them.

thanks

samtediou

STOP!!! Hold the presses! Now it's working...yesterday it wasn't.

I'm not making this up, go here to see (if you want):

www.braindivot.com

Anyway, JDS, you don't need to waste your time on trying to find a solution, since it works.

Thanks,

samtediou

samtediou · #5 24-Jul-2003, 09:35

Whoops...I'd like to RETRACT my last post. I wasn't looking at my pages very carefully when I wrote it, and I see I'm still having problems with that sql!

You can go here
www.braindivot.com

to look at a stripped down version of what this code produces:

PHP Code:


// Select the MySQL database
  if (mysql_select_db("$sql_db", $link) == True)
  {

    // sort results by language (plang)

    $SQL  = "SELECT ap_temp_pal_data.dataid, ap_temp_pal_data.fname, ";
    $SQL .= "ap_temp_languages.lang_eng, ap_temp_pal_languages.langid, ";
    $SQL .= "ap_temp_pal_languages.level FROM ( ap_temp_pal_data ";
    $SQL .= "INNER JOIN ap_temp_pal_languages ON ap_temp_pal_data.dataid = ";
    $SQL .= "ap_temp_pal_languages.dataid ) INNER JOIN ap_temp_languages ON ";
    $SQL .= "ap_temp_pal_languages.langid = ap_temp_languages.langid ";
    $SQL .= "WHERE ( ( ( ap_temp_pal_languages.langid ) = ".$Langid." ) ";
    $SQL .= "AND ( ( ap_temp_pal_languages.level ) = 'pri' ) )"; 

    // Execute the SELECT query
    $search_result = mysql_query($SQL, $link);

    if ($search_result == True) 
    {

      // Fetch a row and print two fields
      while ($row = mysql_fetch_array($search_result))
      {
        
        $Palid = $row["dataid"];  //to be submitted to email page
        echo "The dataid returned is $Palid.<br>";

        //Build SQL2 for languages of interest for each pal here

    $SQL2 .= "SELECT ap_temp_pal_data.fname, ap_temp_pal_languages.dataid, ";
    $SQL2 .= "ap_temp_pal_languages.langid, ap_temp_pal_languages.level, ";
    $SQL2 .= "ap_temp_languages.lang_eng FROM ap_temp_languages ";
    $SQL2 .= "INNER JOIN ( ap_temp_pal_data INNER JOIN ap_temp_pal_languages ";
    $SQL2 .= "ON ap_temp_pal_data.dataid = ap_temp_pal_languages.dataid ) ";
    $SQL2 .= "ON ap_temp_languages.langid = ap_temp_pal_languages.langid ";
    $SQL2 .= "WHERE (((ap_temp_pal_languages.dataid)= " . $Palid . "))"; 

        //execute SQL2 here; if passes, process the rest
        $search_result2 = mysql_query($SQL2, $link);
        if ($search_result2 == True) {

          echo "The inner SQL returned TRUE.<br>";

          //Set an index counter
          $i = 0;

          //Get languages, put into array, and close       
          while ($row2 = mysql_fetch_array($search_result2)) {
            
            $lang = $row2["lang_eng"];
            $lev = $row2["level"];
            
            if ($lev == "bas") {$lev = "basic";}
            elseif ($lev == "int") {$lev = "intermediate";}
            elseif ($lev == "adv") {$lev = "advanced";}
            
            $langint[$i] = $lang; 
            $levellangint[$i] = $lev; 

            $i++;
          }//end of while loop processing search_result2

          mysql_free_result($search_result2);


        $Plang = $row["lang_eng"];
        $Count = $row["CountOflang_eng"];
        $Fname = $row["fname"];

        $Langint1 = $langint[1];
        $Levellangint1 = $levellangint[1];
        $Langint2 = $langint[2];
        $Levellangint2 = $levellangint[2];


        echo "<br><b>Name:</b> $Fname </td></tr>";
        echo "<br><b>Primary language:</b> $Plang</td></tr>";
        echo "<br><b>Language(s) of interest:</b> ";
        echo "$Langint1 [$Levellangint1]";
        if ($Langint2 != '') { echo ", $Langint2 [$Levellangint2]";}

        } // end of if condition search_result true
        else {
          echo "The inner SQL returned FALSE.<br>"; 
       
        }

        echo "<br><br>";

      }// end of while loop processing search_result

      mysql_free_result($search_result);

Besides Eigfa (member id 12), there should be data displayed for one more Creek-speaking member (member id 36), but the query fails after one successful iteration. I should say that the sql queries used above produce the desired results when tested in the mysql db itself, just not in the php code on my page.

Stumped,

samtediou

JdS · #6 24-Jul-2003, 11:41

I don't know what to do with the CSV files you've uploaded.

Could you do a SQL batch / dump file for me? Here's a simple how-to: http://www.desilva.biz/mysql/mysqlbak.html

If you don't have shell access to your web server, ask your host to make you one and attach that here. Of course, I am talking about the one with dummy data...

samtediou · #7 24-Jul-2003, 12:49

Ok, great. Thanks for looking at it, JDS.

Here's the location of the SQL dump files: www.braindivot.com

I took out all extraneous fields and changed the table names to make it read more easily. The changed code is below.

samtediou

============================

PHP Code:


<?php
$sql_host="localhost";
$sql_user="su_user1";
$sql_pass="password1";
$sql_db="su_db1";


// The langid would normally be passed to this page from another page,
// but for purposes of testing, I'll set it here.  

    $Langid = 64;


// Suppress errors and handle them internally
$link = mysql_connect("$sql_host", "$sql_user", "$sql_pass");
if(!empty($link))
{
  // Select the MySQL database
  if (mysql_select_db("$sql_db", $link) == True)
  {

    // sort results by language (plang)

    $SQL  = "SELECT pal.dataid, pal.fname, ";
    $SQL .= "languages.lang_eng, pal_languages.langid, ";
    $SQL .= "pal_languages.level FROM ( pal ";
    $SQL .= "INNER JOIN pal_languages ON pal.dataid = ";
    $SQL .= "pal_languages.dataid ) INNER JOIN languages ON ";
    $SQL .= "pal_languages.langid = languages.langid ";
    $SQL .= "WHERE ( ( ( pal_languages.langid ) = ".$Langid." ) ";
    $SQL .= "AND ( ( pal_languages.level ) = 'pri' ) )"; 


    // Execute the SELECT query
    $search_result = mysql_query($SQL, $link);

    if ($search_result == True) 
    {

      // Fetch a row and print two fields
      while ($row = mysql_fetch_array($search_result))
      {
        
        //Set $Palid to the result returned
        //from SQL (dataid).  $Palid will be used in SQL2.  
        //It will also be sent to the email page

        $Palid = $row["dataid"];  
        echo "The dataid returned is $Palid.<br>";

        //Build SQL2 for languages of interest for each pal here

        $SQL2 .= "SELECT pal.fname, pal_languages.dataid, ";
        $SQL2 .= "pal_languages.langid, pal_languages.level, ";
        $SQL2 .= "languages.lang_eng FROM languages ";
        $SQL2 .= "INNER JOIN ( pal INNER JOIN pal_languages ";
        $SQL2 .= "ON pal.dataid = pal_languages.dataid ) ";
        $SQL2 .= "ON languages.langid = pal_languages.langid ";
        $SQL2 .= "WHERE (((pal_languages.dataid)= " . $Palid . "))"; 

        //execute SQL2 here; if passes, process the rest
        $search_result2 = mysql_query($SQL2, $link);
        if ($search_result2 == True) {

          echo "The inner SQL returned TRUE.<br>";

          //Set an index counter
          $i = 0;

          //Get languages, put into array, and close       
          while ($row2 = mysql_fetch_array($search_result2)) {
            
            $lang = $row2["lang_eng"];
            $lev = $row2["level"];
            
            if ($lev == "bas") {$lev = "basic";}
            elseif ($lev == "int") {$lev = "intermediate";}
            elseif ($lev == "adv") {$lev = "advanced";}
            
            $langint[$i] = $lang; 
            $levellangint[$i] = $lev; 

            $i++;

          }//end of while loop processing search_result2

          mysql_free_result($search_result2);


        //Extract member's primary language and first name
        $Plang = $row["lang_eng"];
        $Count = $row["CountOflang_eng"];
        $Fname = $row["fname"];


        //Extract member's languages of interest and expertise
        $Langint1 = $langint[1];
        $Levellangint1 = $levellangint[1];
        $Langint2 = $langint[2];
        $Levellangint2 = $levellangint[2];


        //Display member information to screen
        echo "<br><b>Name:</b> $Fname </td></tr>";
        echo "<br><b>Primary language:</b> $Plang</td></tr>";
        echo "<br><b>Language(s) of interest:</b> ";
        echo "$Langint1 [$Levellangint1]";
        if ($Langint2 != '') { echo ", $Langint2 [$Levellangint2]";}

        //Try to clear out the variables (desparate attempt to debug)
        $search_result2 = "";
        $row2 = "";    
        //******No effect***********8 


        } // end of if condition search_result2 true
        else {
          echo "The inner SQL returned FALSE.<br>"; 
       
        }

        echo "<br><br>";

      }// end of while loop processing search_result

      mysql_free_result($search_result);
    }
  }
}
?>

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Dx 9 hlp :(	TheKoolGuy	C Programming Language	0	14-Sep-2003 06:19

mysql_query result True on first iteration, then False

Why use INNER JOINS?

Whoa!