SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict Chris Roane's Avatar
    Join Date
    Jul 1999
    Location
    Helena, MT
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I encountered a php error that I can't seem to fix and I can't even find the problem for. When the browser gets to a certain point on the php page, it gives this error: "Error fetching article details: You have an error in your SQL syntax near 'AND CID=1' at line 1". I double spaced the line that the error is refering to.

    Now here is the whole area of the php script that the error was located in. Everything else on the page works fine.

    <?php
    while ($cat = mysql_fetch_array($cats)) {
    $cid = $cat["ID"];
    $cname = $cat["Name"];

    // Check if the joke is in this category
    $result = mysql_query(
    "SELECT * FROM ArticleLookup " .


    "WHERE AID=$id AND CID=$cid");


    if (!$result) {
    echo("<P>Error fetching article details: " .
    mysql_error() . "</P>");
    exit();
    }

    // mysql_num_rows gives the number of entries
    // in a result set. In this case, if the result
    // contains one or more rows, the condition
    // below will evaluate to true to indicate that
    // the joke does belong to the category, and the
    // checkbox should be checked.
    if (mysql_num_rows($result)) {
    echo("<INPUT TYPE=CHECKBOX CHECKED NAME='cat$cid'>$cname<BR>\n");
    } else {
    echo("<INPUT TYPE=CHECKBOX NAME='cat$cid'>$cname<BR>\n");
    }
    }
    ?>

    Thanks!

    Chris Roane

    ------------------
    Web Services: DevStorm | Intersurge
    Web Tools: Web Site Resource | Free Web Site Tools

  2. #2
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Isn't that an extra bracket right at the end? I could be wrong, just looked that way to me.



    ------------------
    Chris Bowyer - Programmer in Training
    DomainMailings.com: Who says all the good names are taken?
    MovieForums.com: Talk about you're favorite flicks!

  3. #3
    SitePoint Addict Chris Roane's Avatar
    Join Date
    Jul 1999
    Location
    Helena, MT
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't believe their is an extra bracket. There is one that ends the While loop and one that ends the If/Else Statement.

    Chris Roane

    ------------------
    Web Services: DevStorm | Intersurge
    Web Tools: Web Site Resource | Free Web Site Tools

  4. #4
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is not a PHP syntax error but a MySQL syntax error, I have not read the article you are following but I would check the field types of AID and CID adn make sure you have got them set to numeric types else MySQL will throw an error which is what your is likely to be.

    ------------------
    Karl Austin
    KDA Web Services
    "Everyone has a photographic memory. Some just don't have film."

  5. #5
    SitePoint Addict Chris Roane's Avatar
    Join Date
    Jul 1999
    Location
    Helena, MT
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    This is the exact code I used in MySQL to create the ArticleLookup table:

    Create Table Artcicle Loopup (
    AID INT NOT NULL,
    CID INT NOT NULL,
    PRIMARY KEY(AID,CID)
    );

    I am pretty sure that is current for what I am wanting to do, but I am not sure.

    Karl, I believe I did make them numeric types, as can be seen from above.

    Chris Roane

    ------------------
    Web Services: DevStorm | Intersurge
    Web Tools: Web Site Resource | Free Web Site Tools

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Chris,

    Are you providing the page with a value for the $id variable (e.g. http://localhost/editarticle.php?id=1)? If $id doesn't have a value, the MySQL query that gets sent will be as follows:

    SELECT * FROM ArticleLookup WHERE AID= AND CID=n

    ...which would result in the error you are getting.


    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!

  7. #7
    SitePoint Addict Chris Roane's Avatar
    Join Date
    Jul 1999
    Location
    Helena, MT
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, here is how it works......in that same script, editarticles.phtml, I have a page that is loaded when a link with the value of "edit=1" is clicked on. On that page, they click on the article that they want to edit which always has the "ID=$id" value to it which then brings up a form that has that articles curent text, title, category, and author in it. Now the author comes up perfectly fine and everything else does as well except the current category(s) that the article is in.

    I put this variable right before the form loads with all the articles' properties:

    $cats = mysql_query("SELECT ID, Name FROM ACategories");

    I checked to see if all the variables were labeled correctly about 4-5 times and I can't seem to figure out what is wrong.

    I really need to fix this problem asap because I can't move my site to my own server until I get everything setup.

    Chris Roane

    ------------------
    Web Services: DevStorm | Intersurge
    Web Tools: Web Site Resource | Free Web Site Tools

  8. #8
    SitePoint Addict Chris Roane's Avatar
    Join Date
    Jul 1999
    Location
    Helena, MT
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I finally figured out what was the problem.

    Kevin, you were right about what was causing the error. When the link was clicked on, I gave it the url of "editarticle.phtml?ID=$id". Now in my php code I put:

    "SELECT * FROM ArticleLookup " .
    "WHERE AID=$id AND CID=$cid");

    The error was in the "$id". I should have had id "$ID". A very simple error.

    Thanks for all your help!

    Chris Roane

    ------------------
    Web Services: DevStorm | Intersurge
    Web Tools: Web Site Resource | Free Web Site Tools

  9. #9
    SitePoint Addict Chris Roane's Avatar
    Join Date
    Jul 1999
    Location
    Helena, MT
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Can someone please tell me what is wrong with this mysql query?

    -----------------------------------
    $articlelist = mysql_query(
    "SELECT ID, ArticleTitle " .
    "FROM Articles, ACategories, ArticleLookup " .
    "WHERE CID=$catid AND ArticleLookup.AID=Articles.ID");
    -----------------------------------

    $catid is the id # that was in the link that they clicked on.

    I am trying to get it to link to all of the articles that are in the category that they clicked on. I believe the following code will work if I can get the select query correct.

    -----------------------------------
    While ($article = mysql_fetch_array($articlelist)) {
    $title = $article["ArticleTitle"];
    $id = $article["ID"];

    // Display the Linked Article in the specified category
    echo( "<P><a href='$PHP_SELF?id=$id'>$title</a></P>" );
    }
    -----------------------------------

    I would appreciate any help on this problem asap.

    I can be contacted with the following programs:

    ICQ: 81278659
    MSN Messanger: devstormsite@hotmail.com

    Thanks!

    Chris Roane

    ------------------
    Web Services: DevStorm | Intersurge
    Web Tools: Web Site Resource | Free Web Site Tools

    [This message has been edited by wsresource (edited July 22, 2000).]

  10. #10
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $articlelist = mysql_query(
    "SELECT ID, ArticleTitle " .
    "FROM Articles, ACategories, ArticleLookup " .
    "WHERE CID=$catid AND ArticleLookup.AID=Articles.ID");

    First of all you're selecting two columns from 3 tables. Cut down on the amount of tables. If you're referencing a table in the WHERE, you do NOT need to include it in FROM. Also the mysql interpreter has no idea about which ID you're talking about.

    If you can post the error message and the DB layout we can help you a bit more..

  11. #11
    SitePoint Addict Chris Roane's Avatar
    Join Date
    Jul 1999
    Location
    Helena, MT
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Okay, I changed the code a little bit:

    $articlelist = mysql_query(
    "SELECT Articles.ID, ArticleTitle " .
    "FROM Articles " .
    "WHERE CID=$catid AND ArticleLookup.AID=Articles.ID");

    It still gives me this error when I click on a category:

    Warning: 0 is not a MySQL result index in /home/resource/wsresource-www/admin/articles.phtml on line 32

    The line that it points to is the while statement that is right after the select statement. I know it is the select statement that is causing the error because I put in a simple select statement and there was no error.

    If I am not providing enough info, then just let me know and I can show you the whole articles.phtml file.

    Chris Roane

    ------------------
    Web Services: DevStorm | Intersurge
    Web Tools: Web Site Resource | Free Web Site Tools

  12. #12
    SitePoint Addict Chris Roane's Avatar
    Join Date
    Jul 1999
    Location
    Helena, MT
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just posted a topic that relates to this thread:
    http://www.sitepoint.com/forums/Forum7/HTML/000258.html

    Please go there if you know php/mysql and are willing to help me for a reward.

    Chris Roane

    ------------------
    Web Services: DevStorm | Intersurge
    Web Tools: Web Site Resource | Free Web Site Tools

  13. #13
    SitePoint Enthusiast Sparklit's Avatar
    Join Date
    Jul 2000
    Location
    Victoria,BC,Canada
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For security sake, put ticks (single quotes) around all the PHP variable in a MySQL query, even if they are numerical. (i.e. "id='$id'" instead of "id=$id")

    Otherwise it is very easy for someone change the value in the query string and do something like this:

    select * from foo where id=1;drop table foo



  14. #14
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Chris,

    You should always check for MySQL errors after doing a MySQL query:

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>$articlelist = mysql_query(
    "SELECT Articles.ID, ArticleTitle " .
    "FROM Articles, ArticleLookup " .
    "WHERE CID=$catid AND ArticleLookup.AID=Articles.ID");

    if (!$articlelist) {
    echo("&lt;P&gt;MySQL Error: " . mysql_error());
    exit();
    }


    while (...[/code]

    This will provide a more informative error message, and will hopefully help you track down the problem. If not, let me know here or on ICQ and I'll help you out! :-)

    Oh, and notice I put ArticleLookup back in the FROM clause. d3v lied -- it IS required for you to use the column in the WEHRE clause. ACategories is NOT needed, though (you're not using any of its columns).

    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!


    [This message has been edited by kyank (edited July 23, 2000).]


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
  •