SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql syntax error

    Hello Folks. I have developed a small news script where news items are placed in a news section (category). I want to be able to list all the news items underneath the category in which it belongs.

    The mysql tables are as follows:

    news:
    id (int, auto_inc , pk)
    newsdate
    newstitle
    newstext
    nsid (news section id)

    newssections:
    id (int, auto_inc, pk)
    newssec_name

    the id of the newssections is also submited into the nsid field in the news table when I insert into the database. Here is the code I am using:

    PHP Code:
    //query for a news section 
    $newsseclist mysql_query('SELECT id, newssec_name FROM newssections ORDER BY id ASC'); 
    //create the newssection list array using the while loop 
    while($newssec mysql_fetch_array($newsseclist)) { 
        
    $id $newssec['id']; 
        
    $newssec_name $newssec['newssec_name']; 
    //display the news sections 
        
    echo("<h5>$newssec_name</h5>"); 
    // select rows from the db matching current news item 
    $newslist mysql_query
         
    "SELECT newssec_name, news.id AS news_id, newstitle, 
            FROM newssections, news 
            WHERE nsid = newssections.id AND 
            nsid = 
    $newssectionid ORDER BY news.id ASC 
            " 
    ) or die (mysql_error()); 
        while(
    $news mysql_fetch_array($newslist)) { 
            
    $newssec_name $news['newssec_name']; 
            
    $newstitle $news['newstitle']; 
            
    $news_id $news['news_id']; 
            
    $newssectionid $news['nsid']; 
     
    //output code for each news item... 
            
    echo("<table width='350' align='center' border='0' cellpadding='5' class='text2'><tr> 
     
            <td width='79%'><a href='aircraftdetails.php?ac_desc_ID=
    $news_id'>$newstitle</a></td> 
            <td width='10%'><a href='newac_edit.php?ac_desc_ID=
    $news_id&type=$newssec_name'>Edit</a></td> 
            <td width='10%'><a href='newac_del.php?ac_desc_ID=
    $news_id&type=$newssec_name'>Delete</a></td> 
            </tr> 
            </table>"
    ); 
        } 

    I am getting the following output:

    Home Page (this is one of the newsection names)

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM newssections, news WHERE nsid = newssections.id AND nsi' at line 2

    I don't see where I'm having a syntax error... I'm using php 5.04
    and mysql 4.1.7

    Any ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you have a "dangling comma" in front of the keyword FROM

    this would have been so much easier to see if you had adopted the "leading comma" style of sql --
    Code:
    SELECT newssec_name
         , news.id AS news_id
         , newstitle
         , 
      FROM newssections
         , news 
     WHERE nsid = newssections.id 
       AND nsid = $newssectionid 
    ORDER BY news.id ASC
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmmm:

    PHP Code:
    $newslist mysql_query
           
    "SELECT newssec_name
       , news.id AS news_id
       , newstitle  
            FROM newssections
       , news 
            WHERE nsid = newssections.id
        AND nsid = 
    $newssectionid 
      ORDER BY news.id ASC
      "
    ) or die (mysql_error()); 
    new syntax error:
    Home Page

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY news.id ASC' at line 8

    to test it out I got rid of this line:
    PHP Code:
    ORDER BY news.id ASC 
    And got this error:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

    leading comma style is nice. . But syntax is off somewhere still.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    syntax is fine

    variable substitution is probably not

    test your query outside of php to be sure
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would the variables work is testing outside of php? Running in in mysql query browser, I got the following:
    Unknown column '$newssectionid' in 'where clause'
    what would be a valid variable assignment in this case?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by jive
    what would be a valid variable assignment in this case?
    replace the php variable with an actual id value from the appropriate table column, so that the query has a chance to return something

    if it works (and i expect it will), this tells you that the php variable is not being set correctly before the query is executed by your script
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try echo-ing $newssectionid just before your query executes. Check that it is what you expected it to be.

    Also try encasing it in quotes: "SELECT ... nsid = '$newssectionid' ... "

    Does that make any difference?
    Rick

  8. #8
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you know what, your right Rick. I found the problem that way.I didn't have the correct variable. Changed it to the correct variable and it works fancy.

    Thanks guys.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rick
    Also try encasing it in quotes: "SELECT ... nsid = '$newssectionid' ... "
    no, please, don't do that if it's a numeric column (what would be the point of forcing mysql to do an unnecessary datatype conversion?)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    no, please, don't do that if it's a numeric column (what would be the point of forcing mysql to do an unnecessary datatype conversion?)
    sorry my bad - I just saw newssecname and assumed it was a string. oops!
    Rick

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no prob

    you get my vote for Quick Apology Guru of the Year
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •