SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Mysql Query

  1. #1
    SitePoint Zealot bensheard's Avatar
    Join Date
    Jun 2006
    Location
    Cape Town | South Africa
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql Query

    Hi. I have been having problems with a mysql query. It comes up with errors saying that it is not a valid result resource. Here is my query:
    PHP Code:
    function get_dives($no_start$limit$order$direction$userid){
         
    $no_start $no_start 1;
         if(
    $limit == "")
          
    $limit == "";
          else
          
    $limit " LIMIT 0, $limit";
          
         
    $dives mysql_query("SELECT `diveno`, UNIX_TIMESTAMP(date) as date,`purpose` ... FROM dives WHERE `diveno` > $no_start && `userid` = '$userid' ORDER BY `$order` '$direction' '$limit'");
    ... 

    Any ideas??? I am probably doing something obviously wrong that i can't see... thanks
    Ben Sheard
    -------------
    Log all your dives easily and quickly from anywhere in the world
    www.divinglogs.cafe150.com

  2. #2
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,424
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Echo your query out to your browser. Is it what you expect? If so, run it from the mysql command line or phpmyadmin. Also, using mysql_error() will tell you what error mysql is generating.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    pretty sure the error will end up being caused by '$direction'

    that's a string

    if $direction is intended to be ASC or DESC, remove the quotes

    ditto for '$limit'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot bensheard's Avatar
    Join Date
    Jun 2006
    Location
    Cape Town | South Africa
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    I have tried various different things and I still can't get it to work. There are no syntax errors but the page doesn't load at all. A blank white page comes up and it says Done. This is the code i'm trying to use:
    PHP Code:
    [size=2]$sql "SELECT `diveno`, UNIX_TIMESTAMP(date) as date ........ FROM dives WHERE `diveno` > $no_start && `userid` = '$userid' ORDER BY `$order$direction $limit";[/size]
    [
    size=2]echo $sql;[/size]
    [
    size=2]     $dives mysql_query($sql) or die(mysql_error());[/size]
    [
    size=2][/size
    When I echo it it returns exactly the right thing but then what i descibed above happens. As soon as I put quotes around $direction and $limit the page loads but then the mysql error comes up saying that it does not have the right syntax being the quotes around $direction and $limit.

    Any thing else I can try??

    Thanks
    Ben Sheard
    -------------
    Log all your dives easily and quickly from anywhere in the world
    www.divinglogs.cafe150.com

  5. #5
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what are you doing with $dives? see mysql_fetch_assoc()

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bensheard View Post
    When I echo it it returns exactly the right thing ...
    any chance you could show us this query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot bensheard's Avatar
    Join Date
    Jun 2006
    Location
    Cape Town | South Africa
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont think the problem is with what I am doing with dives - I am trying to get the query to execute.

    Quote Originally Posted by r937 View Post
    any chance you could show us this query?
    This returns
    PHP Code:
    SELECT `diveno`, UNIX_TIMESTAMP(date) as date,`purpose`,`visibility`,`temperature`,`start_pressure`,`end_pressure`,`avg_depth`,`max_depth`,`cyl_size`,`consumption`,`divetime`,`buddy`,`location`,`site`,`dive_type`,`wind_conditions`,`extra1`,`extra2`,`extra3`,`extra4`,`commentsFROM dives WHERE `diveno` > && `userid` = '7' ORDER BY `diveno'DESC' ' LIMIT 0, 30' 
    This exactly what I want - except it has the quotes around DESC and LIMIT which if i remove - the page wont load.
    Ben Sheard
    -------------
    Log all your dives easily and quickly from anywhere in the world
    www.divinglogs.cafe150.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bensheard View Post
    This exactly what I want - except it has the quotes around DESC and LIMIT which if i remove - the page wont load.
    well, you must remove those quotes, because they are invalid sql

    i.e. you want to generate this:
    Code:
    ORDER BY diveno DESC LIMIT 0, 30
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bensheard View Post
    the page wont load.
    what does that mean? what specifically makes you think the page "wont load"?

    whats the output of this, without the invalid quotes you have been using.
    PHP Code:
    $dives mysql_query($sql) or die(mysql_error());
    var_dump($dives);
    var_dump(mysql_num_rows($dives)); 


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
  •