Thread: Query problems

Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0

    Query problems


    How would I go about converting this mysql query to a postgres query:
    Code:
    $query = "SELECT subsection,sectionID,subsectionID FROM content WHERE sectionID=$sectionID && subsectionID!='0' && public='show' ORDER BY subsection";
    $query = pg_query($query);
    while($row = pg_fetch_array($query,NULL,PGSQL_ASSOC))
    {
    I keep getting errors like

    Code:
    Warning: pg_query() query failed: ERROR: parser: parse error at or near "public" in /test.php on line 12
    
    Warning: pg_fetch_array(): supplied argument is not a valid PostgreSQL result resource in /test.php on line 13
    When I removed public='show' I get the error;

    Code:
    Warning: pg_query() query failed: ERROR: Attribute 'sectionid' not found in /md0/external_www/nieuw/test.php on line 12
    I've been looking for a good tutorial on this for dummies but I can't really find one.

    Thanx for the help!
    Hendricus
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Try changing the && to AND .

    HTH,
    -b
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0
    Ok I changed it... but getting another error

    Code:
    pg_connect ("host=$host dbname=$dbname user=$user password=$pass");
    $query = "SELECT subsection,sectionID,subsectionID FROM content WHERE sectionID=$sectionID AND subsectionID!='0' AND public='show' ORDER BY subsection";
    $result = pg_query($query);
    while($row = pg_fetch_array($result,NULL,PGSQL_ASSOC))
    {
        echo "section: ".$row['section']."<br />";
        echo "sectionID: ".$row['sectionID']."<br />";
        echo "subsection: ".$row['subsection']."<br />";
        echo "subsectionID: ".$row['subsectionID']."<br />";
        echo "content: ".$row['content']."<br />";
        echo "public: ".$row['public']."<br />";
        echo "date: ".$row['date']."<br />";
    }
    Warning: pg_query() query failed: ERROR: parser: parse error at or near "AND" in /md0/external_www/nieuw/test.php on line 14

    Warning: pg_fetch_array(): supplied argument is not a valid PostgreSQL result resource in /md0/external_www/nieuw/test.php on line 15

    Darn someone hexed my project...

    Cheers,
    Hendricus
  6. #4
  7. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Try changing sectionID=$sectionID to sectionID = '$sectionID'
    if that doesn't work, can you try to echo out the parsed query?
    -b
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0
    First of all, thanx for your help... due to frustration almost forgot ab out that

    Except for the login vars this is the complete code:

    Code:
    if(!IsSet($sectionID))
    	{
    	$sectionID = "1";
    	}
    if(!IsSet($subsectionID))
    	{
    	$subsectionID = "0";
    	}
    pg_connect ("host=$host dbname=$dbname user=$user password=$pass");
    $query = "SELECT subsection,sectionID,subsectionID FROM content WHERE sectionID = '$sectionID' AND subsectionID! = '0' AND public = 'show' ORDER BY subsection";
    $result = pg_query($query) or die("Error in query: $query." . pg_last_error($connection));
    
    while($row = pg_fetch_array($result,NULL,PGSQL_ASSOC))
    {
        // print_r($row);
        // Uncomment the preceding line to see the entire array.
        echo "section: ".$row['section']."<br />";
        echo "sectionID: ".$row['sectionID']."<br />";
        echo "subsection: ".$row['subsection']."<br />";
        echo "subsectionID: ".$row['subsectionID']."<br />";
        echo "content: ".$row['content']."<br />";
        echo "public: ".$row['public']."<br />";
        echo "date: ".$row['date']."<br />";
    }
    And this is the error:

    Warning: pg_query() query failed: ERROR: parser: parse error at or near "public" in /md0/external_www/nieuw/test.php on line 16

    Warning: pg_last_error(): supplied argument is not a valid PostgreSQL link resource in /md0/external_www/nieuw/test.php on line 16
    Error in query: SELECT subsection,sectionID,subsectionID FROM content WHERE sectionID = '1' AND subsectionID! = '0' AND public = 'show' ORDER BY subsection.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    public is a reserved word in SQL. Change the column name.
  12. #7
  13. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Either change the column name or use double quotes around it when referring to it, like "public" = 'show'
    PostgreSQL, it's what's for dinner...
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0
    Ah that's the problem... I'm going to try and change it.
    Ok man thanx a million!

    But I'm wondering,, if it's a reserved word in SQL than why does this same database/tabel and query work with mySQL?

    And a new problem arrived when I just temporarily removed public='show' in the query.

    Warning: pg_query() query failed: ERROR: Attribute 'sectionid' not found in /md0/external_www/nieuw/test.php on line 16

    I guess this has to do with uppercase/lowercase but the collums are named exactly the same sectionID/subsectionID etc...

    Cheers,
    Hendricus
  16. #9
  17. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Postgres defaults to lowercase, if your table names are mixed case you have to double quote them, like when naming tables after reserved words.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0
    So I'll have to change the names of my collumns and in the query to lowercase? I'll give it a go...

    Thanx to both for the help,
    Hendricus
  20. #11
  21. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    No you have to change the query so that the names are the proper case, but you have to double quote them. Any place where you refer to a table with a mixed case name just add double quotes around that name, note that you're going to have to escape those with a backslash if you're query is in double quotes. Sorry if that's confusing, here's what I mean:
    say you have:
    SELECT "MixedCaseColumn" FROM "MixedCaseTable" WHERE lowercasecolumn = 'value'

    would be valid. Now if you had to write that in PHP

    you would do:
    $szQuery = "SELECT \"MixedCaseColumn\" FROM \"MixedCaseTable\" WHERE lowercasecolumn = 'value' ";
    Or you could change the names of all the columns.
    Hope I'm helping more than confusing you.

    -b
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0
    PostGres is confusing me... You instead are helping me

    I changed to collumnnames to lowercase and no more error like before. I only left the autoID collumn as it was.

    autoID int4 4 Yes nextval('"content_autoID_seq"'::text)
    section text var Yes
    sectionid text var Yes
    subsection text var Yes
    subsectionid text var Yes
    content text var Yes
    publiek text var Yes
    date timestamp 8 Yes

    I get a new error though:
    Warning: pg_query() query failed: ERROR: Unable to identify an operator '=' for types 'text' and 'int4' You will have to retype this query using an explicit cast in /md0/external_www/nieuw/test.php on line 16

    Warning: pg_fetch_array(): supplied argument is not a valid PostgreSQL result resource in /md0/external_www/nieuw/test.php on line 18

    on this script:
    $query = "SELECT subsection,sectionid,subsectionid FROM content WHERE sectionid = $sectionID AND subsectionid! = '0' AND publiek = 'show' ORDER BY subsection";
    $result = pg_query($query);

    while($row = pg_fetch_array($result,NULL,PGSQL_ASSOC))
    {
    I wish there was some more information on this in my own language...

    Thanx again bcyde!
    HEndricus
  24. #13
  25. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Change sectionid = $sectionID to sectionid = '$sectionID' .

    Most likely this is because when the query is fully parsed and your query ends up looking like sectionid = 5, but you defined sectionid to be a text field and that's why it's saying you need to cast it.

    The transition from mysql to postgres may be a bit tricky, but if you do it, it will be worth it. MySQL is more lenient (easier) in certain ways, but that's actually not a good thing when you want to insure data integrity. Once you get adjusted to postgres you should be able to write more cross vendor code that adheres closer to SQL standards.

    -b
    PostgreSQL, it's what's for dinner...
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0
    I've read about PostGres being a better system for databases. And I want to learn more about it indeed. (But I guess you will be seeying more of me on this forum then )

    I made the changes you told me to do:

    Code:
    $query = "SELECT subsection,sectionid,subsectionid FROM content WHERE sectionid = '$sectionID' AND subsectionid! = '0' AND publiek = 'show' ORDER BY subsection";
    And now I get this error:

    Warning: pg_query() query failed: ERROR: Unable to identify a right operator '!' for type 'text' You may need to add parentheses or an explicit cast in /md0/external_www/nieuw/test.php on line 16

    Warning: pg_fetch_array(): supplied argument is not a valid PostgreSQL result resource in /md0/external_www/nieuw/test.php on line 18


    Grrrr... ...one bright thing though... almost all my queries look kind of the same so if I get one to work the rest will hopefully follow

    Cheers,
    Hendricus
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0
    my bad.... subsectionid! = '0'
    I inserted a wrong space... sorry for the bother! Seems like I need a break, been behind my computer for almost 13 hours straight...

    Cheers,
    Hendricus
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo