Small PHP Problem

[Chris Roane]

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

[TWTCommish]

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!

[Chris Roane]

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

[Karl]

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."

[Chris Roane]

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

[Kevin Yank]

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!

[Chris Roane]

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

[Chris Roane]

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

[Chris Roane]

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).]

[qslack]

$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..

[Chris Roane]

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

[Chris Roane]

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

[Sparklit]

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

[Kevin Yank]

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).]