SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Mar 2003
    Location
    Olympia
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Data from Multiple tables.

    I'm trying to make my code a bit more portable by changing all the table names to varibles so it's easier to change the name of a table for the entire program. When I do this with queries that look at two tables I get this: "supplied argument is not a valid MySQL result resource".

    PHP Code:
    $conf['tbl']['projecttable'] = 'projecttable';
    $conf['tbl']['studentwork'] = 'studentwork';
    //these variables are called via an include

    $showing mysql_query("SELECT * FROM $conf[tbl][studentwork], $conf[tbl][projecttable] 
    WHERE("
    .$conf[tbl][studentwork].".teacher = '$HTTP_GET_VARS[teacher]' && 
    "
    .$conf['tbl']['projecttable'].".teacheruser = '$HTTP_GET_VARS[teacher]' && 
    "
    .$conf['tbl']['projecttable'].".publish = 'publish' && 
    "
    .$conf['tbl']['studentwork'].".active = 'approve' && 
    "
    .$conf['tbl']['projecttable'].".ID = '$HTTP_GET_VARS[ID]')");
    WHILE(
    $studentwork mysql_fetch_array($showing)) { 

  2. #2
    SitePoint Enthusiast BDKR's Avatar
    Join Date
    Sep 2002
    Location
    Clearwater, Florida
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Digi-Jeff
    I'm trying to make my code a bit more portable by changing all the table names to varibles so it's easier to change the name of a table for the entire program. When I do this with queries that look at two tables I get this: "supplied argument is not a valid MySQL result resource".

    PHP Code:
    $conf['tbl']['projecttable'] = 'projecttable';
    $conf['tbl']['studentwork'] = 'studentwork';
    //these variables are called via an include

    $showing mysql_query("SELECT * FROM $conf[tbl][studentwork], $conf[tbl][projecttable] 
    WHERE("
    .$conf[tbl][studentwork].".teacher = '$HTTP_GET_VARS[teacher]' && 
    "
    .$conf['tbl']['projecttable'].".teacheruser = '$HTTP_GET_VARS[teacher]' && 
    "
    .$conf['tbl']['projecttable'].".publish = 'publish' && 
    "
    .$conf['tbl']['studentwork'].".active = 'approve' && 
    "
    .$conf['tbl']['projecttable'].".ID = '$HTTP_GET_VARS[ID]')");
    WHILE(
    $studentwork mysql_fetch_array($showing)) { 
    The query is blowing up! You're executing the query and assuming it worked. Check for failure and you should be fine.

    You may also be better off to create the query string then pass it to the query function. There is a tad bit more overhead, but it's also a lot easier to read. What I like to do is have a function that builds the query for me and returns the string. Something like...

    PHP Code:
    <?
    $query_string
    =build_query_string($_datum);
    if(
    do_query(&$query_string)==false)
        { 
    /* do error stuff */ }
    else
        { 
    /* do display (or whatever) stuff */ }

    ?>
    Cheers,
    BDKR
    If you're not on the gas, you're off the gas!

  3. #3
    SitePoint Member
    Join Date
    Mar 2003
    Location
    Olympia
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know that it's blowing up! The problem is that when the table names were hardcoded it worked and now that I'm trying to use variables it isn't working, so yeah, it blows up. It doesn't blow up when only looking at one table, but somehow when I look at two tables, as in my example code, it blows up, and I want to know what I'm doing wrong.

  4. #4
    SitePoint Zealot
    Join Date
    Mar 2002
    Location
    Perth, Australia
    Posts
    164
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Digi-Jeff
    I know that it's blowing up! The problem is that when the table names were hardcoded it worked and now that I'm trying to use variables it isn't working, so yeah, it blows up. It doesn't blow up when only looking at one table, but somehow when I look at two tables, as in my example code, it blows up, and I want to know what I'm doing wrong.
    Something is wrong with your SQL string. I recommend that you create the string first, then output it to the screen so that you know exactly what it's value is before you pass it to any MySQL functions.

    PHP Code:
    $conf['tbl']['projecttable'] = 'projecttable';
    $conf['tbl']['studentwork'] = 'studentwork';
    //these variables are called via an include

    $sql "SELECT * FROM $conf[tbl][studentwork], $conf[tbl][projecttable]
    WHERE("
    .$conf[tbl][studentwork].".teacher = '$HTTP_GET_VARS[teacher]' &&
    "
    .$conf['tbl']['projecttable'].".teacheruser = '$HTTP_GET_VARS[teacher]' &&
    "
    .$conf['tbl']['projecttable'].".publish = 'publish' &&
    "
    .$conf['tbl']['studentwork'].".active = 'approve' &&
    "
    .$conf['tbl']['projecttable'].".ID = '$HTTP_GET_VARS[ID]')";

    // Now you can echo $sql to see what is going wrong
    echo $sql;

    // once your SQL string is sytactically correct, you can move on:
    $showing mysql_query($sql); 

  5. #5
    SitePoint Member
    Join Date
    Mar 2003
    Location
    Olympia
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both! I used peDey's code and it echod everything out so I could see that the table names on the first line were being seen as Array[studentwork], not just studentwork. I modified the first line to: "SELECT * FROM ".$conf['tbl']['studentwork'].", ".$conf['tbl']['projecttable']." and it works great!

  6. #6
    "Of" != "Have" bronze trophy Jeff Lange's Avatar
    Join Date
    Jan 2003
    Location
    Calgary, Canada
    Posts
    2,063
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you could have added {}, to use multi-dimensional arrays within strings:

    {$conf[tbl][studentwork]}
    Who walks the stairs without a care
    It shoots so high in the sky.
    Bounce up and down just like a clown.
    Everyone knows its Slinky.

  7. #7
    SitePoint Enthusiast BDKR's Avatar
    Join Date
    Sep 2002
    Location
    Clearwater, Florida
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Digi-Jeff
    I know that it's blowing up! The problem is that when the table names were hardcoded it worked and now that I'm trying to use variables it isn't working, so yeah, it blows up. It doesn't blow up when only looking at one table, but somehow when I look at two tables, as in my example code, it blows up, and I want to know what I'm doing wrong.
    Something you need to think about is that by checking for failure and using functions like mysql_error() or mysql_errrno(), your code with the assistance of the mysql engine could've have told you what the problem is and saved you the time here! Echo'ing the query string helps too, but what if that wasn't the problem?

    Cheers,
    BDKR
    If you're not on the gas, you're off the gas!


Bookmarks

Posting Permissions

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