Issue with SQL Statement
Results 1 to 12 of 12

Thread: Issue with SQL Statement

  1. #1
    Junior Member
    Join Date
    Dec 2012
    Posts
    9

    Issue with SQL Statement

    A user checks off a series of checkboxes and the form results are sent to the next page in an array. Then on the second page, it uses the form results to filter the recordset and only display the profiles of users with the UID sent from the checkboxes. The UID is an integer that is unique to each profile. Although, the profile is made up from data from many tables that all share the same UID. There might be multiple records in a table for that UID. I think the sql statement I have used here is causing an issue. Thanks in advance for your help.

    Code:
    <?php
    if (!function_exists("GetSQLValueString")) {
    function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
    {
      if (PHP_VERSION < 6) {
        $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
      }
    
      $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
    
      switch ($theType) {
        case "text":
          $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
          break;    
        case "long":
        case "int":
          $theValue = ($theValue != "") ? intval($theValue) : "NULL";
          break;
        case "double":
          $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
          break;
        case "date":
          $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
          break;
        case "defined":
          $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
          break;
      }
      return $theValue;
    }
    
      }
    mysql_select_db($database_connect, $connect);
    $idList = implode(',', $_POST['UID']);
    $sql = 'SELECT *, scholarships.NAME as scholarshipname FROM scholarships, gc_codes, users  INNER JOIN stu_app2008 ON users.UID = stu_app2008.UID  INNER JOIN activities ON users.UID = activities.UID INNER JOIN coll_exp ON users.UID = coll_exp.UID  INNER JOIN employ ON users.UID = employ.UID INNER JOIN fin_app ON users.UID = fin_app.UID  INNER JOIN finaid ON users.UID = finaid.UID  INNER JOIN stu_special ON users.UID = stu_special.UID WHERE gc_codes.GCID = users.GC and scholarships.ID = stu_special.ID and UID in (' . $idList . ')';
    $res = mysql_query($sql);
    $varlist = array();
    while ($row = mysql_fetch_assoc($res)) {
      $varlist[$row['UID']] = $row;
    }
    ?>

  2. #2
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,884
    First, stop using mysql (see the link in my signature).

    Second, you didn't even bother checking the results of your mysql function calls. They might return an error because your SQL is broken.

    Third, you define a function and then do absolutely nothing with it.

    Fourth, you don't describe what the problem is.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  3. #3
    Junior Member
    Join Date
    Dec 2012
    Posts
    9
    The issue is that the SQL is broken and being a newbie, I am not sure how to fix it. I use the variables from the select statement in the body of the page like this

    Code:
    <td colspan="2" valign="top"><fieldset id="el11">
                          <legend><strong>Section 2: Parent / Guardian Information</strong></legend><table width="100%" border="0" cellspacing="1" cellpadding="6">
                            <tr>
                              <td colspan="2" valign="top"><strong>PARENT / GUARDIAN #1</strong></td>
                              <td colspan="2" valign="top" bgcolor="#cccccc"><strong>PARENT / GUARDIAN #2</strong></td>
                              </tr>
                            <tr>
                              <td width="19%" valign="top"><strong>Name: </strong></td>
                              <td width="30%" valign="top"><label for="Name"><?php echo $row['PARENT_A']; ?></label></td>
                              <td width="19%" valign="top" bgcolor="#cccccc"><strong>Name: </strong></td>
                              <td width="30%" valign="top" bgcolor="#cccccc"><?php echo $row['PARENT_B']; ?></td>
                              </tr>
                            <tr>
                              <td valign="top"><strong>Address:</strong></td>
                              <td valign="top"><?php echo $row['PAR_A_ADD']; ?></td>
                              <td valign="top" bgcolor="#cccccc"><strong>Address:</strong></td>
                              <td valign="top" bgcolor="#cccccc"><?php echo $row['PAR_B_ADD']; ?></td>
                              </tr>
                            <tr>
                              <td valign="top"><strong>Telephone:</strong></td>
                              <td valign="top"><?php echo $row['PAR_A_TEL']; ?></td>
                              <td valign="top" bgcolor="#cccccc"><strong>Telephone:</strong></td>
                              <td valign="top" bgcolor="#cccccc"><?php echo $row['PAR_B_TEL']; ?></td>
                              </tr>
                            <tr>
                              <td valign="top"><strong>Relationship to Applicant:</strong></td>
                              <td valign="top"><?php echo $row['PAR_A_REL']; ?></td>
                              <td valign="top" bgcolor="#cccccc"><strong>Relationship to Applicant:</strong></td>
                              <td valign="top" bgcolor="#cccccc"><?php echo $row['PAR_B_REL']; ?></td>
                              </tr>
                            <tr>
                              <td valign="top"><strong>Occupation:</strong></td>
                              <td valign="top"><?php echo $row['PAR_A_OCC']; ?></td>
                              <td valign="top" bgcolor="#cccccc"><strong>Occupation:</strong></td>
                              <td valign="top" bgcolor="#cccccc"><?php echo $row['PAR_B_OCC']; ?></td>
                              </tr>
                            </table>
                          </fieldset>
    Thanks again.

  4. #4
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,884
    Quote Originally Posted by sneakyimp View Post
    Fourth, you don't describe what the problem is.
    You could start by describing the problem. What, specifically, does it do or not do that you don't like?
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  5. #5
    Junior Member
    Join Date
    Dec 2012
    Posts
    9
    This is the error message that I get which seems to mean there is an error in my sql statement. It does nothing except display this. It works when I selecting data from only one table but, once I add additional table names, I get this error. So, it appears that it doesn't know how to handle data coming from multiple tables. Maybe I need an additional foreach somewhere?

  6. #6
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,884
    You could start by posting the error here and the SQL it's complaining about.

    You can use the php echo statement to output string data. Or you could use file_put_contents to write string data to a file so that sensitive information is not visible to your visitors.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  7. #7
    Junior Member
    Join Date
    Dec 2012
    Posts
    9
    Whoops. Here is the error message:

    Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\Scholarship\admin\printable_version2_test3.php on line 87

  8. #8
    Junior Member
    Join Date
    Dec 2012
    Posts
    9
    line 87 is while ($row = mysql_fetch_assoc($res)) {
    $varlist[$row['UID']] = $row;

  9. #9
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,884
    Quote Originally Posted by sneakyimp View Post
    Second, you didn't even bother checking the results of your mysql function calls. They might return an error because your SQL is broken.
    I have already mentioned that problem. Don't you ever run query statements without checking for error conditions. Sounds like one of your functions is returning FALSE when you assume it will be a valid data resource in your code.

    But, this brings me to the fact you are using mysql -- that was my first point:
    Quote Originally Posted by sneakyimp
    First, stop using mysql (see the link in my signature).
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  10. #10
    Junior Member
    Join Date
    Dec 2012
    Posts
    9
    this works:

    Code:
    mysql_select_db($database_connect, $connect);
    $idList = implode(',', $_POST['UID']);
    $sql = 'SELECT * FROM users WHERE UID in (' . $idList . ')';
    $res = mysql_query($sql);
    $varlist = array();
    while ($row = mysql_fetch_assoc($res)) {
      $varlist[$row['UID']] = $row;
    }
    this doesn't work:

    Code:
    mysql_select_db($database_connect, $connect);
    $idList = implode(',', $_POST['UID']);
    $sql = 'SELECT *, scholarships.NAME as scholarshipname FROM scholarships, gc_codes, users  INNER JOIN stu_app2008 ON users.UID = stu_app2008.UID  INNER JOIN activities ON users.UID = activities.UID INNER JOIN coll_exp ON users.UID = coll_exp.UID  INNER JOIN employ ON users.UID = employ.UID INNER JOIN fin_app ON users.UID = fin_app.UID  INNER JOIN finaid ON users.UID = finaid.UID  INNER JOIN stu_special ON users.UID = stu_special.UID WHERE gc_codes.GCID = users.GC and scholarships.ID = stu_special.ID and UID in (' . $idList . ')';
    $res = mysql_query($sql);
    $varlist = array();
    while ($row = mysql_fetch_assoc($res)) {
      $varlist[$row['UID']] = $row;

  11. #11
    Junior Member
    Join Date
    Dec 2012
    Posts
    9
    Well, I am a newbie and using Dreamweaver and unfortunately I am in a time crunch and I don't know mysqli yet.

  12. #12
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    Quote Originally Posted by lwaters View Post
    Well, I am a newbie and using Dreamweaver and unfortunately I am in a time crunch and I don't know mysqli yet.
    What a wonderful opportunity to Learn!

    mysqli has a procedural interface that closely mirrors the old mysql functions.
    While the object-oriented API is much more natural and fluid, the procedural functions have a more gradual learning curve - choose for yourself.

    An example:
    PHP Code:
    <?php

    ##  procedural example  ##

    // create a database connection using mysqli
    $DB mysqli_connect'databaseHost','username','password','databaseName' );

    // sanitize the user-supplied data
    $sanitized_POST_param mysqli_real_escape_string$DB,$_POST['param'] );

    // query
    $SQL "SELECT `column` FROM `table` WHERE `column`='$sanitized_POST_param'";

    // execute query
    $result mysqli_query$DB,$SQL );

    // check for result
    if( $result !== false ){

        
    // loop through results
        
    while( $row mysqli_fetch_assoc$result ) ){
            
    /* do whatever */
        
    }

        
    // close result
        
    mysqli_free_result$result );
    }


    ##  same example, object-oriented style  ##

    // create a database connection using mysqli
    $DB = new mysqli'databaseHost','username','password','databaseName' );

    // sanitize the user-supplied data
    $sanitized_POST_param $DB->real_escape_string$_POST['param'] );

    // query
    $SQL "SELECT `column` FROM `table` WHERE `column`='$sanitized_POST_param'";

    // execute query
    $result $DB->query$SQL );

    // check for result
    if( $result !== false ){

        
    // loop through results
        
    while( $row $result->fetch_assoc() ){
            
    /* do whatever */
        
    }

        
    // close the result
        
    $result->close();

    }

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •